Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

SQL Server 2012 (“Denali”): Contained Databases

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.

More info:

Contained Databases in SQL Server 2012

Step-by-Step guide to Implement Contained Databases

SQL Server v.Next (Denali) : Contained Databases


Video SQL Server Code Named “Denali” Contained Database Authentication Demo

What is Contained Database in SQL Server

SQL Server 2012 – New Features – Contained Databases

SQL Server 2012: Sometimes Partial Is Preferable

Contained Database Authentication in SQL Server 2012


Leave a comment on the original post [, opens in a new window]

Loading comments...