Don’t split your data across multiple databases if it belongs to a single domain and there are inter-dependencies between the data. Seems like common sense advice but I’ve seen it done more than once……
This approach has the following (non exhaustive and common sense) list of drawbacks,
Backup/restore – you have to backup and restore all the databases as a complete set which is obviously more time consuming and error prone than backing up/restoring a single databases. This also massively complicates disaster recovery should you have issues with a single db.
Security – you have to duplicate permissions in each database for the same user. Even if you use database roles to manage permissions, you still have to grant the role the appropriate rights in each database.
The two points above are specific cases of the general point concerning maintenance – any maintenance effort is multiplied by the number of databases you have.
Ease of use – it is a pain hunting around multiple databases for the table/stored proc/etc. You cant use dm_sql_referencing_entities or dm_sql_referenced_entities to find out where a database object is used (as it could be referenced from another db). You have to resort to text searches on text in each db (RedGate SQL Search helps with this and its free).
Foreign key constraints – you cant create foreign key constraints cross database – self-explanatory.
Indexed views – you cant created indexed views – indexed views require schema binding which doesn’t work with a cross db reference.
Transactions – cross database transactions are DTC controlled which uses more resources than transactions in a single db.
Since SQL 2005, SQL Server has ‘schemas’ which are for logically grouping similar objects together that dont have any of the drawbacks listed above.
Use SQL Server schemas instead !