Limiting File Attachments to a SQL Server 2005 DB to a value > 8000 bytes

Question

I have a .Net app that will allow the users to attach files to a SQL Server 2005 database. I want to limit the filesize to 10MB, so from what I can tell, I have to declare the datatype varbinary(max), since the max size I can actually specify is 8000 bytes. But the ~2GB filesize varbinary(max) allows seems like overkill. Is there a way for me to limit it at 10MB in the database, or do I just need to check that they're not trying to attach something larger on the frontend.

Answer 1

I think you need to make it in the frontend. You can limit the HTTP request size in the web.config like that:

</configuration>
   </system.web>
      <httpRuntime maxRequestLength="60000"/>
   </system.web>
</configuration>

You may also need to explicitly check the posted file size when the user try to upload a file more than the specified threshold.

Answer 2

In ASP.NET you already have a limit on the upload size (default: 4MB IIRC). You could use that to limit your downloads.

IMO it would be better because it's sooner on the path to the DB, so you minimize the overload on the server.

The only downside is that the user will see an error page if he tries to upload a file bigger that the ASP.NET limit.

Answer 3

Ah - my fault. This is a VB.Net desktop app. I forgot to mention that. But I would imagine that the suggestion of having to handle it on the frontend still holds.

Thanks

Answer 4

I would be checking the file limitation in your application, you don't want to go through the process of inserting it into the db, just to find out it is too big.

A little application logic before hand can save time, and provide a better user experience.

Answer 5

Using varbinary(max) is totally okay, since it is varbinary, you will only use as much place as the content requires, up to 2 gigs. However, you could add a CHECK CONSTRAINT in the db, checking the datalength() of the upload. I would rather just check the upload in the application though.

Answer 6

Slightly off-topic: While it is certainly possible, I have found it is usually a bad idea to actually store attachments directly in the database.

The primary problem is the explosive growth of the overall database size due to large attachments, which makes backups and other routine maintenance much more difficult.

Simply storing the path information of the file in the database, and storing the actual file in a shared network folder is an alternative I recommend considering. (Of course, this means that you now have to worry about network file permissions, and some other issues, but is still a better alternative in many cases.)

Answer 7

It seems like it would be a best practice to limit attachment size on the front end and in the database. Kaboing's check constraint solution seems to fit the database side of things.