File storage in databases

I have written a few websites that host files and I have not been able to
resolve in my mind the issue of storing files in the file system vs. storing
them in the database as a blob and writing code to serve them into the web
stream. The latter approach got a lot easier with the dotnet framework, but
I still don't know if it is the most accepted approach.
The reason I like the idea of storing a file in SQL is that the file is
"bonded" to the rest of the record. (i.e. deletes, changes, etc. do not
require moving a file around). However, using regular files and storing
just the filename in the database makes retrieval of the files much more
straightforward for other applications that may need access to it without
using my usual web interface. Also, something inside me wants to say that
file systems should store files and databases should store data.
I know there is not a one-size-fits-all answer, but I would love to hear
anyone's comments.
Thanks!
-RyanRayn
There are lots of discussion about this issue on NG. Do some searching to
find out.
Personally, I prefer to store the file/s on the filesystem rather in the
database (the anme file just points to the actuall file on the disk)
As fas as I know by default SQL Server stores an IMAGE datatype in separate
pages but the table contains a pointer that points to the data page when
stored IMAGE datatype
For example, if these datatypes hold large quantities of data, and they were
stored in a table with the rest of the data, this would require SQL Server
to work harder when accessing the table. The larger the table, the more I/O
SQL Server has to perform in order to accomplish its tasks
Look at sp_tableoption 'tablename', 'text in row', 'on' in the BOL for more
details
"Ryan" <noones@.home> wrote in message
news:1170r48on54mb75@.corp.supernews.com...
> I have written a few websites that host files and I have not been able to
> resolve in my mind the issue of storing files in the file system vs.
storing
> them in the database as a blob and writing code to serve them into the web
> stream. The latter approach got a lot easier with the dotnet framework,
but
> I still don't know if it is the most accepted approach.
> The reason I like the idea of storing a file in SQL is that the file is
> "bonded" to the rest of the record. (i.e. deletes, changes, etc. do not
> require moving a file around). However, using regular files and storing
> just the filename in the database makes retrieval of the files much more
> straightforward for other applications that may need access to it without
> using my usual web interface. Also, something inside me wants to say that
> file systems should store files and databases should store data.
> I know there is not a one-size-fits-all answer, but I would love to hear
> anyone's comments.
> Thanks!
> -Ryan
>

File size limit / offline cache

Hi,

I have 2 questions:

Is there any way of getting around the 128MB file size limit when creating and adding SSEv databases to VS2005? Currently I get the following error when trying to connect to a database:"The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only...". This after I altered the app.config file to "...Max Database Size=600;..." Have anyone tried to use SSEv to cache data with the use of the Smart Application Offline Building Block? Is there a provider I can use for doing this?

Thanks in advance!

A SQLEv database can be up to 4GB in size. You control the maximum size of the database through the connection string. See the System.Data.SqlServerCe.SqlCeConnection MSDN documentation for details on this and other connection string parameters that you'll want to know about when working with large databases (e.g. Autoshrink threshold comes to mind)

http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx

Have a look at the new Mobile Client Software Factory published on MSDN - it includes an off-line app block for use with SQL Mobile and should be directly applicable to SQLEv.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/mcsflp.asp

Darren

File size limit / offline cache

Hi,

I have 2 questions:

Is there any way of getting around the 128MB file size limit when creating and adding SSEv databases to VS2005? Currently I get the following error when trying to connect to a database:"The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only...". This after I altered the app.config file to "...Max Database Size=600;..."

Have anyone tried to use SSEv to cache data with the use of the Smart Application Offline Building Block? Is there a provider I can use for doing this?

Thanks in advance!

A SQLEv database can be up to 4GB in size. You control the maximum size of the database through the connection string. See the System.Data.SqlServerCe.SqlCeConnection MSDN documentation for details on this and other connection string parameters that you'll want to know about when working with large databases (e.g. Autoshrink threshold comes to mind)

http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx

Have a look at the new Mobile Client Software Factory published on MSDN - it includes an off-line app block for use with SQL Mobile and should be directly applicable to SQLEv.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/mcsflp.asp

Darren

File permissions

I've been having problem in my asp.net 2 web when using the "User Instance=true" style connections to databases.

I would get ASP.NET event log errors like this:
Exception information:
Exception type: SqlException
Exception message: An attempt to attach an auto-named database for file c:\temp\dnn\dnn\dnn455\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

After I used ProcessMonitor it showed that the user ASPNET was trying to access C:\ directly after trying to open the database.mdf file I wanted open.

I had to give the ASPNET account read/execute permission to c:\, which was very scary.

Could someone please enlightenment me as to the correct file permissions needed to use databases in user instances?

Thanks,
Alex

You have the option to connect the User Instance to Management Studio because of known issues with User Instance. Try the link below for details.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

File Group and Databases

I currently have a database that is 16gig data. I would like to create a
secondary file in the same filegroup and split that 16gig across the two
files. This would allow for growth and some performance perhaps.
I can not figure out how to migrate the data from the first file group to
the second or split it across the two.
Any suggestions would be helpful. I am running sql2k sp4.Thom,
To my knowledge objects can only move across filegroups directly. So if you
want to place an table on another file in another filegroup, create a
user-defined filegroup, add the file to the filegroup and then create a
clustered index for the table using the ON FILEGROUP clause. If the index
already exists, use the WITH DROP_EXISTING clause as well.
HTH
Jerry
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:46737483-EA39-4007-BCF9-9DA970C45E9B@.microsoft.com...
>I currently have a database that is 16gig data. I would like to create a
> secondary file in the same filegroup and split that 16gig across the two
> files. This would allow for growth and some performance perhaps.
> I can not figure out how to migrate the data from the first file group to
> the second or split it across the two.
> Any suggestions would be helpful. I am running sql2k sp4.

File Group and Databases

I currently have a database that is 16gig data. I would like to create a
secondary file in the same filegroup and split that 16gig across the two
files. This would allow for growth and some performance perhaps.
I can not figure out how to migrate the data from the first file group to
the second or split it across the two.
Any suggestions would be helpful. I am running sql2k sp4.
Thom,
To my knowledge objects can only move across filegroups directly. So if you
want to place an table on another file in another filegroup, create a
user-defined filegroup, add the file to the filegroup and then create a
clustered index for the table using the ON FILEGROUP clause. If the index
already exists, use the WITH DROP_EXISTING clause as well.
HTH
Jerry
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:46737483-EA39-4007-BCF9-9DA970C45E9B@.microsoft.com...
>I currently have a database that is 16gig data. I would like to create a
> secondary file in the same filegroup and split that 16gig across the two
> files. This would allow for growth and some performance perhaps.
> I can not figure out how to migrate the data from the first file group to
> the second or split it across the two.
> Any suggestions would be helpful. I am running sql2k sp4.

File Group and Databases

I currently have a database that is 16gig data. I would like to create a
secondary file in the same filegroup and split that 16gig across the two
files. This would allow for growth and some performance perhaps.
I can not figure out how to migrate the data from the first file group to
the second or split it across the two.
Any suggestions would be helpful. I am running sql2k sp4.Thom,
To my knowledge objects can only move across filegroups directly. So if you
want to place an table on another file in another filegroup, create a
user-defined filegroup, add the file to the filegroup and then create a
clustered index for the table using the ON FILEGROUP clause. If the index
already exists, use the WITH DROP_EXISTING clause as well.
HTH
Jerry
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:46737483-EA39-4007-BCF9-9DA970C45E9B@.microsoft.com...
>I currently have a database that is 16gig data. I would like to create a
> secondary file in the same filegroup and split that 16gig across the two
> files. This would allow for growth and some performance perhaps.
> I can not figure out how to migrate the data from the first file group to
> the second or split it across the two.
> Any suggestions would be helpful. I am running sql2k sp4.