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".
Please take in consideration that I have a dedicated server and I can create as many Sql Databases as I want.
Which perform better single database or multiple databsae?
You can use filegroups to separate your data files. You can create one separate filegroup for storing the file data.
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
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.
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.
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.