As you know, I had visited Tech-Ed 2011 (India) last month. The last day of Tech-Ed was a special day for DBAs because we had a dedicated DBA track – and to add to it, we had a power packed hands-on lab from Jacob Sebastian and break-out sessions from Pinal Dave, Vinod Kumar, Prem Mehra and Balmukund Lakhani. You can read all about Tech-Ed Day 03 at my post here.

For this week, I will be sharing with you the content of the hands-on lab on “Contained Databases in SQL 11 (“Denali”)” from Tech Ed. The hands-on lab was conducted by Jacob Sebastian.

Database containment – the current situation

What does a typical day in the life of the DBA (especially in a development shop) consist of? Let me list a few of these activities down:

1. Create new databases
2. Setup database options
3. Create Logins on the SQL Server and assign permissions to various objects
4. Move databases across SQL Servers
5. Repeat the same steps on demand

SQL Server is huge, and there are a variety of objects and tasks to be done within the server. Unfortunately, these objects and tasks are spread all over the entire product, which leads to the following disadvantages:

Database Deployment

This is the most feared situation – it is the time when an application is moved from one instance to another. When this movement happens, some part of the application’s data (eg. the logins and agent job information) is left behind.

The DBA must then painstakingly recreate and map each login to required database users on the new instance. Maintenance and other SQL Agent jobs also need to be recreated on the new instance. Depending upon the number of such operations to be carried out, this process is time-consuming and error-prone.

Application Development

Application development has to currently keep into consideration the finer points of the final deployment/production environment. Database and server configuration and deployment scenarios are no longer isolated. It is quite possible that some of the dependencies and conditions that application developers assumed to be available may not be true (eg. the availability of the “sa” login, permissions to create new logins on the server, or “xp_cmdshell” being enabled).

Another major influencer in application development is the collation of the database. Since this is initially determined by the server collation, developers need to constantly ensure that the collation is exactly as required by the application.

Such situations take the focus away from application development and instead divert it to application deployment, which should not be the case.

Application Administration

Because logins and agent jobs are spread across the instance and the database, administration is a virtual nightmare. In high-security enterprise class installations, the need to administer a single database typically requires that the user be granted permissions to the entire instance – thus providing access to many other databases to which the user has no relation with.

Contained Databases – The solution

There exists a distinct boundary between the multiple areas of any application:

1. Application boundary – the boundary between the application code and functionality and the server instance
2. Application Model – inside the application boundary, this is where applications are developed and managed
3. Management Model – outside of the application boundary, this is where instance-level management happens

Here is how you may classify some of the objects into Application model & Management model:

Application Model	Management Model
System tables like sys.tables	Instance level system tables like sys.endpoints
Database users and passwords	Database server logins Database server login and database user mapping
User tables in the current database (i.e. referenced by a 2-part name)	User tables in another database (i.e. referenced by a 3-part name)

Once these boundaries are defined, and the objects completely contained within these boundaries, they will become independent of the objects on the other side of the boundary.

A “Contained Database” paradigm ensures:

1. Simplified database movement
2. Application development environment independent of the deployment environment considerations
3. More granular, yet powerful administrative groups possible

SQL 11 (“Denali”) and Contained Databases

As of SQL 11 (“Denali”) CTP 01, SQL Server supports the concept of “Contained databases”. Here’s how you can define contained database:

"A contained database has no external dependencies on server-based settings or metadata to define the database. Users defined in a contained database do not have a dependency on logins created on the server, and therefore contain all information necessary to authenticate a user within the database."

By default, this feature is turned OFF, meaning everything is just as it was before, i.e. “uncontained”.

Tomorrow, we will soon see how to turn Contained Database support ON, and also create a new contained database, after which we will see how to migrate a contained database across severs, and finally, how to convert your non-contained database to a contained one.

You can understand more about Contained Databases at: http://msdn.microsoft.com/en-us/library/ff929071(SQL.110).aspx

Until tomorrow,

Be courteous. Drive responsibly.