Is it a good idea to store files in a different SQL Server 2005 database "FileDb" and point to it (files) in the "AppDb" by "FileId".
将文件存储在不同的SQL Server 2005数据库“FileDb”中,并通过“FileId”指向“AppDb”中的文件(文件)是一个好主意。
Please take in consideration that I have a dedicated server and I can create as many Sql Databases as I want.
请考虑我有一个专用服务器,我可以创建任意数量的Sql数据库。
update:
Which perform better single database or multiple databsae?
哪个执行更好的单个数据库或多个数据库?
4 个解决方案
#1
You can use filegroups to separate your data files. You can create one separate filegroup for storing the file data.
您可以使用文件组来分隔数据文件。您可以创建一个单独的文件组来存储文件数据。
#2
I prefer to have a column which stores the file path + filename I never liked the idea of storing it in the database because of the following reason:
我更喜欢有一个存储文件路径+文件名的列我从来不喜欢将它存储在数据库中的想法,原因如下:
bloats the database...backup and restore will take longer
膨胀数据库...备份和恢复将需要更长时间
image and text datatype is a pain in the neck to work with
图像和文本数据类型是一个痛苦的工作
In SQL Server 2008 you can use FILESTREAM which is still transactional but stores it as a file
在SQL Server 2008中,您可以使用FILESTREAM,它仍然是事务性的,但将其存储为文件
#3
An alternative is to use table partitioning to have your file table on a different disk partition, or even server for performance reasons, but still have the table in one database.
另一种方法是使用表分区将文件表放在不同的磁盘分区上,甚至出于性能原因使用服务器,但仍然在一个数据库中有表。
- SQL Server 2005 Partitioned Tables and Indexes
- Database Table Partitioning Tutorial
- Performance and scalability of table and index partitioning for use with very large tables
SQL Server 2005分区表和索引
数据库表分区教程
表和索引分区的性能和可伸缩性,用于非常大的表
#4
It can be extremely useful if you want to have different back up strategies for different sets of data.
如果您希望针对不同的数据集采用不同的备份策略,那么它将非常有用。
Say for instance, you only need to back up files/emails/attachments every week, while the App data needs to be backed up daily.
比如说,您只需要每周备份文件/电子邮件/附件,而App数据需要每天备份。
This allows you to do that with ease.
这使您可以轻松地完成此操作。
I would highly recommend putting anything you would consider having different persistent strategies for on a different database.
我强烈建议将您认为具有不同持久策略的任何内容放在不同的数据库中。
#1
You can use filegroups to separate your data files. You can create one separate filegroup for storing the file data.
您可以使用文件组来分隔数据文件。您可以创建一个单独的文件组来存储文件数据。
#2
I prefer to have a column which stores the file path + filename I never liked the idea of storing it in the database because of the following reason:
我更喜欢有一个存储文件路径+文件名的列我从来不喜欢将它存储在数据库中的想法,原因如下:
bloats the database...backup and restore will take longer
膨胀数据库...备份和恢复将需要更长时间
image and text datatype is a pain in the neck to work with
图像和文本数据类型是一个痛苦的工作
In SQL Server 2008 you can use FILESTREAM which is still transactional but stores it as a file
在SQL Server 2008中,您可以使用FILESTREAM,它仍然是事务性的,但将其存储为文件
#3
An alternative is to use table partitioning to have your file table on a different disk partition, or even server for performance reasons, but still have the table in one database.
另一种方法是使用表分区将文件表放在不同的磁盘分区上,甚至出于性能原因使用服务器,但仍然在一个数据库中有表。
- SQL Server 2005 Partitioned Tables and Indexes
- Database Table Partitioning Tutorial
- Performance and scalability of table and index partitioning for use with very large tables
SQL Server 2005分区表和索引
数据库表分区教程
表和索引分区的性能和可伸缩性,用于非常大的表
#4
It can be extremely useful if you want to have different back up strategies for different sets of data.
如果您希望针对不同的数据集采用不同的备份策略,那么它将非常有用。
Say for instance, you only need to back up files/emails/attachments every week, while the App data needs to be backed up daily.
比如说,您只需要每周备份文件/电子邮件/附件,而App数据需要每天备份。
This allows you to do that with ease.
这使您可以轻松地完成此操作。
I would highly recommend putting anything you would consider having different persistent strategies for on a different database.
我强烈建议将您认为具有不同持久策略的任何内容放在不同的数据库中。