Category Archives: SQL Azure

Partially Contained Databases

SQL Server 2012, still as RC0 at the time of this writing, comes with a new feature called Partially Contained Databases also known as CDB (contained database).

First of all, what does it mean and what is it good for and secondly, why partially?

Let’s have a look at a none contained database to see the disadvantages opposed to a contained database. As you know, moving a database to another instance comes with a few issues such as orphaned users and SQL Server agent jobs which you need to recreate. Wouldn’t it be great if you just take the db, backup and restore or detach and attach or even script it to the new instance and go? Well, that’s the idea of self contained databases.

With contained databases, which you explicit ally need to create, it is no longer necessary to authenticate to the instance using a login in the master database. The idea is to create a database user either choosing a SQL authenticated user or a Windows principal to authenticate directly to the contained database.

Now, why partially? You should know that not everything is contained in SQL Server 2012. The fully contained databases should be for a next release of SQL Server. Worth mentioning that SQL Azure already supports fully contained databases!

To find out what is contained, look at http://technet.microsoft.com/en-us/library/ff929188(SQL.110).aspx

To find out what is not contained, look at http://technet.microsoft.com/en-us/library/ff929118(SQL.110).aspx

For more information on Partially Contained Databases, check out http://technet.microsoft.com/en-us/library/ff929071(SQL.110).aspx