A problem that has plagued SQL Server for a long time is that a database is not very portable. Sure, you can backup or detach a database and restore or attach it elsewhere, but when you do that, you are missing a lot of the pieces that make that database a part of an application, and a lot of those pieces are really considered administration pieces as opposed to application pieces. You don’t get any synchronization of “outside of the database” items such as security, roles, linked servers, CLR, database mail, service broker objects, replication, and SQL Server Agent jobs.
In SQL Server 2012, some of these issues are being addressed with a feature called “contained databases.” A contained database is a concept in which a database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed. Users connect to the database without authenticating a login at the Database Engine level. Isolating the database from the Database Engine makes it possible to easily move the database to another instance of SQL Server. Including all the database settings in the database enables database owners to manage all the configuration settings for the database.
SQL Server 2012 Release Candidate 0 (RC 0) includes a first step toward contained databases, introducing partially contained databases (also known as Partial-CDB). Partially Contained Databases provide some isolation from the instance of SQL Server but do not yet provide full containment.
In SQL Server 2012, the first iteration of the contained databases feature will provide the following solutions:
- You can create a database-specific user without a SQL Server login (and you can create multiple such users with the same name for different databases). A user at the database level is called a “contained database user“. The user authentication is done at the database level and the applications just need to change their connection strings. This solves a very common problem of “Orphaned Users“
- You can have *some* compatible use of tempdb with databases of different collations, since #temp tables will be created in the collation of the calling database context. In SQL Server 2012, tempdb will automatically create the temporary objects by using the collation of the “Contained Database”, instead of the collation of the server
- You can use a DMV to show most objects or code you have that will threaten containment
Note in a partially contained database you cannot use replication, change data capture, or change tracking.
Expect Microsoft to move toward have true fully contained databases in future versions of SQL Server.