Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lady
Starting Member
32 Posts |
 Posted - 2002-11-22 : 06:47:28
|
| what is the best why to store Word document into SQL Server?as ntext or as varbinary?(it's nessessaty, to store file in database, instead of in disk)thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-22 : 07:23:06
|
quote:
(it's nessessaty, to store file in database, instead of in disk)
Why? |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-22 : 07:25:50
|
| Word document will be a binary file so consider image.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lady
Starting Member
32 Posts |
Posted - 2002-11-22 : 07:42:49
|
| robvolk:why not?if we store them in database we resolve a lot of problemm with security and backup.And what trouble if all images and documents store in own table? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-11-22 : 07:44:42
|
quote:
why not?
Because you may find it hugely more efficient to store the path to the file instead of a binary object, amongst other reasons.Edited by - mr_mist on 11/22/2002 07:59:54 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-22 : 08:08:53
|
quote:
why not?if we store them in database we resolve a lot of problemm with security and backup.
Not really.By putting the data into the database, anyone that has access to the table or the image column can access all of the files stored there. If there happen to be any confidential Word docs, unless they are password protected, anyone can read them. And putting a password on the document doesn't really solve the problem either. They cannot be secured through SQL Server because SQL Server does not have row-level permissions (you have to kludge something together to make it work)By storing documents in a folder as a file, you can secure the folder, the file, or both, and grant or revoke file permissions on a user-by-user basis. You can have any number of folders or disks available to store files, some totally open, others secured and restricted access; it's totally up to you. This also allows people to view the file WITHOUT needing database or SQL Server access. This last bit WILL come up sooner or later, and you won't want to have to grant database access just to show someone one file. And it ADDS a layer of security in case someone hacks your SQL Server; they still won't be able to access the files.Backups? What if the database backup goes kablooey? Now all of the documents are potentially lost. Image and text columns are not logged by default, and if you put in a document that wasn't logged, you can't always back it up. You'd need to take a full database backup, and therefore have to do a full restore. If the files are kept on disk, they don't need to be backed up with the database and can be on a completely different schedule than the DB. They can also be edited, copied, etc. without needing database access. You can also restore an individual file very easily; you cannot restore an individual row in SQL Server without doing a lot of work. quote:
And what trouble if all images and documents store in own table?
Plenty:http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=10477http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=10813http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=10662The one single ADVANTAGE I've seen in storing documents in a SQL Server table is the ability to full-text index them (under SQL Server 2000), however files can still be FT-indexed using Index Server, and Index Server can be linked into SQL Server and queried. To me, it's not enough of an advantage to warrant storing documents in a table. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-22 : 08:19:18
|
| >> if we store them in database we resolve a lot of problemm with security and backup. If that's your reason I think you should reconsider this>> (it's nessessaty, to store file in database, instead of in disk) >> And what trouble if all images and documents store in own table? Lots - you will probably find out.I would store them in their own database if you are going to do this.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
store document...