Microsoft SQL Server 2012 Bible

Strategy Considerations

When you have finalized all the design patterns that will be used throughout your database, a pivotal decision about the physical location of each table (entity) must be made. Following are three schools of thought:

Store all tables in one database and one schema.

Store subject-specific tables in separate databases.

Store subject-specific tables in one database and separate schemas.

Traditionally, most database designers using SQL Server have followed the pattern to store each table in the same database under the dbo schema. However, this approach does limit the scalability of the database. Deciding whether to store the tables in the same database with different schemas or in different databases affects the scalability and flexibility of the database.

When to Separate into Different Databases

If you anticipate the need to scale you database, you may need to consider storing related tables in separate databases. For example, you may have tables specific to Human Resources and another set of tables specific to Sales. In the Human Resources database you may store Employee information. On the other hand, you may track orders and products in the Sales database.

This approach lends itself directly to scalability and flexibility. If all the objects for a particular database were all contained within one database and the need to move a specific set of objects to a different server arose, how would this be handled? If the related tables were stored in individual ...