SQL Server 2005 - Multiple Databases

Question

We are using SQL Server 2005 at work and when development started it was decided that multiple databases would be created. For example, we have one database for Individual say dbIndividual and another one for Translation say dbLocale.

So far this has simplified the backing up and potential restore greatly as we just need to backup what we need (unless there's a way to backup/restore a schema only that we don't know of.)

This cause us to have multiple queries with JOINS over multiple database table and files.

for example:

select customer.firstname, address.addressL1, addressType.value
from dbIndividual.dbo.customer
inner join dbIndividual.dbo.address
    on fkCustomerID = iCustomerID
inner join dbLocale.translation.addressType
    on fkAddressTypeID = iAddressTypeID
    and fkLangID = 1

Is there any drawback to this? Beside the obvious that we can't enforce data integrity across multiple db. Will it slow things down when joining since the data is potentially on totally different part of the disk?

Reason I am asking is because we are in the middle of reorganizing our infrastructure and this is one potential issue that kept popping up.

Answer 1

Current best practice is to use the same database with multiple schemas. Each schema can be put on its own file, which can then be backed up easily. It also allows you to enforce foreign key constraints.

We used to use the multiple database model like your company, and we recently switched all new development to just use schemas. We have not lost anything by doing so and have gained the ability to enforce our foreign keys, which is huge.

Answer 2

why didn't you use SCHEMAs and FILEGROUPS to do this instead? This way you still have separation and you can restore and backup just filegroups if you don't want to do complete backups. And you would have no problem with referential integrity either since you can have foreign keys

Answer 3

Foreign keys are one of the main reason to use a relational database. if you can't use them when they are needed, because data is in different databases, then splitting your tables across multiple databases is bad.

Answer 4

I will point out that you can enforce referential integrity across databases by using triggers and should be doing so in your current design.That said, I like the same database with multiple schema model that Keith Rousseau and SQLMenace suggested.