SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...