Multiple schemas vs. multiple databases

  Multiple Schemata Multiple Databases Notes
Referential integrity via FKs Y N Loopholes in referential integrity
Different recovery options N Y  
HA option – replication Y Y  
HA option – mirroring N Y  
HA option – log shipping N Y  
HA option – clustering N Y  
Load balancing / load distribution N Y By dividing up the DBs among multiple servers
Different maintenance options N Y  
Granularity of Backup & Restore FileGroup/File DB/FileGroup/File  
Complexity of Backup & Restore Lower Higher Esp. point-in-time restores
Specific Perf. Monitoring & Tuning N Y DMVs & monitoring apps don’t support schema-level monitoring
Effort for DB-level Perf. Monitoring & Tuning Factor 1 Factor n N databases need to be monitored and tuned
RI inconsistencies after restore N Y Parent-child tables in different schemas/DBs
Granularity of data placement on storage FileGroup/File DB/FileGroup/File  
Permission granularity Role/User/Schema/
Isolation provided by schemas are undermined by:

.) DBO having access to all schemas

.) Application accessing DB with just 1 login

Handover of data Requires data export By handing over a backup  

Use-cases for schemas:

  1. ETL schema / Operational schema / BI- Reporting- DataMart schema (in case of low volume DBs or pay-per-DB cost scenarios)
    BI.MyTable (Fact table or dimension table)
  2. CDC (Change Data Capture)
    CDC.MyTable (containes the historical changes made to DBO.MyTable)
  3. Automatically granting permissions for objects to be created in the future e.g.
    Users/Logins/AD groups è Role è Object The role has to be given the necessary permissions for each new object
    Users/Logins/AD groups è Role è Schema          Once the schema is given the necessary permissions, the role automatically gets access to all new objects. extension of this idea is putting all tables into DBO schema but putting all views, UDFs, SPs one or more additional schemas, one for each application or user-group.
  4. Ability to drop users (e.g. employees who left the company) but leaving behind the objects they own
  5. Experimenting with new table designs, new queries/SPs while still using the existing data without cloning/copying, by:
    1. Creating a new schema, e.g. NEW
    2. Creating synonyms for all objects (tables, views, UDFs, SPs,) except for those for which new versions have been created and will be tested


Make up your decision using the above information.

If you opt for schemas, then create at least one separate FileGroup for each schema to store all objects of that schema. This helps DB availability due to faster DB Restore & Recovery using Piecemeal Restores, faster Backups using FileGroup Backups and better performance by placing FileGroups on different storage devices

Links: (Piecemeal Restores is an Enterprise Edition feature