File storage with SQL server

Hello,

i'm thinkink about design of a new WinApp.

In this app, i need to store files (xls, doc and pdf essentialy), i need to be able to open and modify it.

I want to use SQL Server 2005 to store those file to be able to use replication process.

Is it possible, is it a good idea ?

Thanks a lot

yes its possible and in most cases prefered. you would store them in an image field.

programmatically, your app would open the file via a stream and read the file to a byte array. You would then use a parameterized a parameterized query to write the bytes to the array.

http://support.microsoft.com/default.aspx/kb/317016/EN-US/

|||Thank you very much for this answer !|||I wont slag you for your inglish if you dont slag me for my typink

File Storage to DB

Our company is planning to store the projects they have been creating for
years. The projects contain many different kinds of files (Adobe, Microsoft
Office e.t.c). The storage and the interface application should allow the
projects (Hundereds of Analysis documents also) to be reviewed, modified and
saved back in the same original format. Initial data will be around 1 TB and
expected to grow.
Is this something an SQL Server Database can handle ?. What would be the
good Software-Hardware suggestion ? Software to allow the above mentioned
modifications ?
Thanks for any input.I would take a serious look at SQL 2008 and the new FileStream datatype as
it sounds perfect for this type of application.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:698AAA6B-3B03-4379-8E4E-34BC400972CB@.microsoft.com...
> Our company is planning to store the projects they have been creating for
> years. The projects contain many different kinds of files (Adobe,
> Microsoft
> Office e.t.c). The storage and the interface application should allow the
> projects (Hundereds of Analysis documents also) to be reviewed, modified
> and
> saved back in the same original format. Initial data will be around 1 TB
> and
> expected to grow.
> Is this something an SQL Server Database can handle ?. What would be the
> good Software-Hardware suggestion ? Software to allow the above mentioned
> modifications ?
> Thanks for any input.
>|||Thanks Andrew for a quick response. FileStream datatype would help me to
stored the data but how about retrieve, modify and save ? Do I need a third
party application that will help me do this ? If so, any suggestions ?
What about hardware ? What will be an efficient setup this kind of work ?
Thanks again.
"Andrew J. Kelly" wrote:
> I would take a serious look at SQL 2008 and the new FileStream datatype as
> it sounds perfect for this type of application.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:698AAA6B-3B03-4379-8E4E-34BC400972CB@.microsoft.com...
> > Our company is planning to store the projects they have been creating for
> > years. The projects contain many different kinds of files (Adobe,
> > Microsoft
> > Office e.t.c). The storage and the interface application should allow the
> > projects (Hundereds of Analysis documents also) to be reviewed, modified
> > and
> > saved back in the same original format. Initial data will be around 1 TB
> > and
> > expected to grow.
> >
> > Is this something an SQL Server Database can handle ?. What would be the
> > good Software-Hardware suggestion ? Software to allow the above mentioned
> > modifications ?
> >
> > Thanks for any input.
> >
>|||"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:BEC98495-7594-43ED-BD3C-3102F4D61591@.microsoft.com...
> Thanks Andrew for a quick response. FileStream datatype would help me to
> stored the data but how about retrieve, modify and save ? Do I need a
> third
> party application that will help me do this ? If so, any suggestions ?
> What about hardware ? What will be an efficient setup this kind of work ?
>
> Thanks again.
>
If you aren't planning to develop your own application then take a look at
one of the Content Management System (CMS) offerings on the market. Choose a
complete solution rather than trying to choose a DBMS first.
--
David Portas|||As David stated SQL Server is only a database, it is not an application.
Many applications work with SQL Server but you can not use it as is for your
intended purpose.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:BEC98495-7594-43ED-BD3C-3102F4D61591@.microsoft.com...
> Thanks Andrew for a quick response. FileStream datatype would help me to
> stored the data but how about retrieve, modify and save ? Do I need a
> third
> party application that will help me do this ? If so, any suggestions ?
> What about hardware ? What will be an efficient setup this kind of work ?
>
> Thanks again.
>
> "Andrew J. Kelly" wrote:
>> I would take a serious look at SQL 2008 and the new FileStream datatype
>> as
>> it sounds perfect for this type of application.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:698AAA6B-3B03-4379-8E4E-34BC400972CB@.microsoft.com...
>> > Our company is planning to store the projects they have been creating
>> > for
>> > years. The projects contain many different kinds of files (Adobe,
>> > Microsoft
>> > Office e.t.c). The storage and the interface application should allow
>> > the
>> > projects (Hundereds of Analysis documents also) to be reviewed,
>> > modified
>> > and
>> > saved back in the same original format. Initial data will be around 1
>> > TB
>> > and
>> > expected to grow.
>> >
>> > Is this something an SQL Server Database can handle ?. What would be
>> > the
>> > good Software-Hardware suggestion ? Software to allow the above
>> > mentioned
>> > modifications ?
>> >
>> > Thanks for any input.
>> >
>>

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 Storage

I am seeking some suggestion of storing files in SQL Server, currently I have
a lot of files ~1MB each. I am to store them into the database, what kind of
data type should I use? Binary? Varbinary? It seems that Binary/Varbinary
support file up to 8000bytes only, is that true? Thx.Hi
Look at the datatype 'Binary'. It supports up to 2GB. It is referred to as a
BLOB in many places. There are a few code examples on microsoft.com on how to
store and retrieve the data.
Regards
Mike
"Sean" wrote:
> I am seeking some suggestion of storing files in SQL Server, currently I have
> a lot of files ~1MB each. I am to store them into the database, what kind of
> data type should I use? Binary? Varbinary? It seems that Binary/Varbinary
> support file up to 8000bytes only, is that true? Thx.|||The SQL data types that support 'blobs' (> 8000 bytes) are text, ntext and
image. Choose the data type that is appropriate for the underlying file
contents.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:192C9D72-3B5F-4A92-963F-EBA82FBDF831@.microsoft.com...
>I am seeking some suggestion of storing files in SQL Server, currently I
>have
> a lot of files ~1MB each. I am to store them into the database, what kind
> of
> data type should I use? Binary? Varbinary? It seems that Binary/Varbinary
> support file up to 8000bytes only, is that true? Thx.

File Storage

I am seeking some suggestion of storing files in SQL Server, currently I have
a lot of files ~1MB each. I am to store them into the database, what kind of
data type should I use? Binary? Varbinary? It seems that Binary/Varbinary
support file up to 8000bytes only, is that true? Thx.
Hi
Look at the datatype 'Binary'. It supports up to 2GB. It is referred to as a
BLOB in many places. There are a few code examples on microsoft.com on how to
store and retrieve the data.
Regards
Mike
"Sean" wrote:

> I am seeking some suggestion of storing files in SQL Server, currently I have
> a lot of files ~1MB each. I am to store them into the database, what kind of
> data type should I use? Binary? Varbinary? It seems that Binary/Varbinary
> support file up to 8000bytes only, is that true? Thx.
|||The SQL data types that support 'blobs' (> 8000 bytes) are text, ntext and
image. Choose the data type that is appropriate for the underlying file
contents.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:192C9D72-3B5F-4A92-963F-EBA82FBDF831@.microsoft.com...
>I am seeking some suggestion of storing files in SQL Server, currently I
>have
> a lot of files ~1MB each. I am to store them into the database, what kind
> of
> data type should I use? Binary? Varbinary? It seems that Binary/Varbinary
> support file up to 8000bytes only, is that true? Thx.

File Location Change

Hello, I was wondering how to change the default storage location for the
data and log files. I relaize that as I create new tables, I can override
th "default" location, but I was wanting to change the "default" value to
be the new location where most SQL databases will be stored. How do I
accomplish this?In SQL 2000 Right click on your Server in EM and choose
Properties. Select the Database settings tab and you will
see the input boxes for Default data and default log file location
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns949578AF4C7E7JHeaveyBDUP@.207.46.248.16...
> Hello, I was wondering how to change the default storage location for the
> data and log files. I relaize that as I create new tables, I can override
> th "default" location, but I was wanting to change the "default" value to
> be the new location where most SQL databases will be stored. How do I
> accomplish this?

File Location Change

Hello, I was wondering how to change the default storage location for the
data and log files. I relaize that as I create new tables, I can override
th "default" location, but I was wanting to change the "default" value to
be the new location where most SQL databases will be stored. How do I
accomplish this?In SQL 2000 Right click on your Server in EM and choose
Properties. Select the Database settings tab and you will
see the input boxes for Default data and default log file location
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns949578AF4C7E7JHeaveyBDUP@.207.46.248.16...
> Hello, I was wondering how to change the default storage location for the
> data and log files. I relaize that as I create new tables, I can override
> th "default" location, but I was wanting to change the "default" value to
> be the new location where most SQL databases will be stored. How do I
> accomplish this?

File Groups for table partitioning and storage

Hello,

I am building partitiong tables, partitioning on different file groups:

the question is:

Partitioned table referred to old data that are not frequent accessed for reporting can be stored on separate location(External storage, tape and so on) or to make partitioning functioning must all file groups must be presents?

If not, how can I separate old data from current ones (still using partitioning) to reduce the size of DB?

What it is the best for storage data and easy to access it when needs arise (eg reporting): Tape, external storage, others?

Thank

SQL doesn't support moving the data to tape. There are two ways to look at the partitioning.

1. I need way more disk performance than a single RAID array will give me. You would therefor partition the table over several high speed disk arrays.

2. We have old data that isn't accessed all that often any more, and high performance isn't needed for the old data. You would therefor parititon the table with the new data on high speed disks, and the old data on slower, less expensive disks.

The database size won't get any smaller, but the storage costs will be reduced.