Voice recordings in SQL database

Question

Voice recordings in SQL database

a_ud

SSCrazy

Points: 2457
More actions
January 26, 2011 at 7:54 am

#232784

We're starting a project where we'd like to have voice recorded as a field/object or somehow in a SQL 2005 database.
I'm familiar with Access field formats and the possibility of saving OLE objects (which might include images), but I'm not sure if there's any format that might allow to save voice recordings, specially MP3 files or the like.
Any ideas?
Thanks in advance, alvaro

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

The datatype is varbinary(max), it will store any binary format, which is all that an MP3 or any such thing is.

Now, with that out of the way.. I always urge caution people about saving BLOBs in the database. What is this field going to serve? How often will it be accessed?

For cases where there is a file that is served up repeatedly by a web server it is a bad choice to make the web server call the database and send the file each time. This negates much of the normal caching that the web server will do on its own and only causes longer waits and higher utilization with effectively no real benefit.

For cases where the file may be accessed a couple times and then not, or rarely storing it in the database may make more sense.

I'd love to hear your scenario..

CEWII

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Our application stores voice recordings in IMAGE type columns. This was migrated from SQL 2000, and I'm sure there are better methods ... but it works. They are not accessed very often, usually just if they need to get audited or verified.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

homebrew01 (1/26/2011)
Our application stores voice recordings in IMAGE type columns. This was migrated from SQL 2000, and I'm sure there are better methods ... but it works. They are not accessed very often, usually just if they need to get audited or verified.

I believe the IMAGE datatype has been depracated and MS is recommending using varbinary(max) now.. But coming from 2000 IMAGE is a completely reasonable datatype.. And given your use I would probably not squawk on storing BLOBs..

CEWII

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

SharePoint stores files in a SQL Server database. I haven't tried saving an MP3 in Sharepoint, but I know that we have used it to store training videos, so there is no reason why an MP3 wouldn't work.

Newer versions of SQL Server (2008 or 2008 R2) allow SQL Server to store files under control of the database, but on the file system so that they can be accessed via a special file system API using a file handle supplied by SQL Server for faster access. They are still part of the database for purposes of security, backups, etc. Newer versions of SharePoint take advantage of this feature.

a_ud SSCrazy Points: 2457 More actions · Answer 5

To answer your questions:

• Architecture: I don't have the skills (or the equipment) to use Sharepoints or webservers. There will be just an Access front-end + a SQL 2005 back-end.

• Scenario: the idea is to store patients' interviews (so it will be a few minutes each interview).

• There will not be too many accesses to the files. Eventually you may use it and then forget the file for a long time.

I'm interested in one of these solutions:

1) Either the recording is part of the DB (the var binary type you mentioned could be the solution).

2) If that's not easy (I'm no SQL 2005 expert), then keeping the files in a hard-drive folder and pointing the folder and name of the file from the DB.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Actually getting it into the database isn't all that difficult and the scenario you paint is a good one for storing the data in database.. I would recommend two things.

1. Create another database file and filegroup to store the blobs.

2. Create a very simple table to hold ONLY the blob and any necessary tracking info, a table as simple as InterviewId and InterviewAudio, this table would be placed on the new filegroup..

CEWII

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

If you move to SS2K8, then the Filestream can help you, but it depends on what the size of the files. What's the average MP3 size?

If they are small, < 1MB, I think there are tests that show this comes faster from the db. It gives you integrated backup, but I would put MP3s on a separate filegroup so you don't have to back them up if you don't want to.

In the past I have leaned towards is storing these on a file server and streaming them out.