Contained Databases

Contained databases are a new feature that currently exist only in SQL Server 2011 “Denali” (currently in CTP 1). According to Microsoft’s documentation on this new feature, contained databases remove any dependency from the SQL Server management level, all objects, settings etc. are contained in the database, this includes user authentication (instead of a user being authenticated by the database engine, they are authenticated at the database level).

The benefit I thought of when I read about this feature was good bye orphaned logins!!! The other, obvious benefit is that the database is self contained. There are no settings to keep in mind when moving a database from 1 instance to another, it’s all there inside the database.

There are some restrictions. Currently, there are 3 different levels of containment; the first is ‘none’ as in, no containment. The second is ‘partial’, partial is the hybrid; it allows you to have some objects that reference other databases, whilst giving you the benefit of self-contained users. The final one is ‘full’, meaning that the database is entirely self contained. In a contained database, you cannot use database replication, change data capture or change tracking.

So, contained databases are divided into 2 parts, the application model; and the management model. The application model is the bit that holds all the information pertinent to the application, all the tables, users, stored procedures etc. that the application depends on; it also contains another new little feature – application level agent jobs! I’m not quite sure what that is yet, because I’ve not looked at it yet (I’ll check that one next ). The management model is the bit that holds things like your TCP end points, users mapped to logins etc. Basically everything that exists outside the database.

So, are contained databases useful to us? I think so, it looks a feature that’s going to prove very useful to DBAs and developers alike.

To see the Microsoft documentation about this new feature, click this link  http://msdn.microsoft.com/en-us/library/ff929071(v=SQL.110).aspx.