Since the start of the week, I am attempting to explore the concept of contained databases – something that I learnt about during a hands-on lab conducted by Jacob Sebastian. Please read my posts Part 01, Part 02 and Part 03 in case you missed them.

To get us back into context, let’s revisit the definition of a 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."

On Day #2, we saw how to create our first contained database in the very latest SQL 11 (“Denali”) CTP01, and also learnt a bit about the changes made to the ALTER DATABASE clause in order to support contained databases. Yesterday, we saw how to move a contained database from database server instance #1 to #2.

But, most (I believe the number to be close to 80%) of the applications today are pre-existing applications, which means that they are already up and running on SQL Server 2008 or below. This also means that post-release, they will make the move to SQL 11 (“Denali”). At that time, would it not be good to know how to migrate or convert a non-contained database to a contained database?

For our exercise today, we will attempt to convert the uncontained AdventureWorks2008R2 databases to a contained database.

Creating uncontained users on an uncontained database

Pre-requisites: Please restore the AdventureWorks2008R2 on the SQL 11 (“Denali”) from the CodePlex links – http://msftdbprodsamples.codeplex.com/releases/view/55330

1. Login to the SSMS and connect to our test instance of SQL 11 (“Denali”)
2. Run the following script against the master database
   

   --Create a login on the server CREATE LOGIN UncontainedUser WITH PASSWORD = 'pass@word1' --Create a "non-contained" users for the newly created login on the server USE AdventureWorks2008R2 GO CREATE USER UncontainedUser FOR LOGIN UncontainedUser GO

3. This query creates a new server-level login, and an associated database-level user mapped to the server-level login

Identifying uncontained objects

Now it is time to identify uncontained objects that will cause issues with the migration. Our diagnostic tool in this exercise would be nothing else except the Dynamic Management Views (DMV)! With each passing release of SQL Server, DMVs are gathering immense power, and we will find them especially useful when working with contained databases.

The DMV that we will be using is sys.dm_db_uncontained_entities. This view is new to SQL 11 (“Denali”) and according to Books On Line,

“Shows any uncontained objects used in the database. Uncontained objects are objects that cross the application boundary in a contained database. This view is accessible from both a contained database and a non-contained database. If sys.dm_db_uncontained_entities is empty, your database does not use any uncontained entities.”

1. In the Object Explorer pane, expand the Databases node, select the AdventureWorks2008R2 database, and then click the New Query button
2. In the query window, run the following T-SQL command
   

   USE AdventureWorks2008R2 GO SELECT class_desc,statement_type,feature_name,feature_type_name FROM sys.dm_db_uncontained_entities

3. Here are the results from executing the query
   

   As discussed earlier in the week, we do not need to worry about the entry related to the ROUTE class. Besides this, we have the following uncontained objects in our AdventureWorks2008R2 database: A computed column based on the output of a system built-in function A database principal (the user “UncontainedUser” that we created above Three (3) full-text indices

4. Run the following query against the AdventureWorks2008R2 database. This query produces a list of any users that have been configured in the database for SQL Server authentication and are mapped to a login that is not disabled
   

   USE AdventureWorks2008R2 GO SELECT dp.name FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE dp.authentication_type = 1 AND sp.is_disabled = 0

5. In our case, the result is the one uncontained user that we created above
   

Migrating AdventureWorks2008R2 database to a contained database

1. Before we begin, please ensure that the SQL 11 (“Denali”) instance is all setup to handle contained databases (refer my posts from earlier this week for details)
2. In the Object Explorer pane, expand the System Databases node, select the Master database, and then click the New Query button
3. After ensuring that there are no open connections to AdventureWorks2008R2, run the following T-SQL command
   

   USE master GO ALTER DATABASE AdventureWorks2008R2 SET CONTAINMENT=PARTIAL; GO

4. After the query completes successfully, select the AdventureWorks2008R2 database in the database list
5. Run the following T-SQL command:
   

   USE AdventureWorks2008R2 GO EXEC sp_migrate_user_to_contained @username = N'UnContainedUser', @rename = N'keep_name', @disable_login = N'disable_login'

   Per Books On Line (refer BOL page here): ”Converts a database user that is mapped to a SQL Server login, to a contained database user with password. In a contained database, use this procedure to remove dependencies on the instance of SQL Server where the database is installed.”

6. Now run the following query which we originally used to identify uncontained users in a database
   

   USE AdventureWorks2008R2 GO SELECT dp.name FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE dp.authentication_type = 1 AND sp.is_disabled = 0

Note that “UnContainedUser” no longer shows up in the list. This means that it has been migrated to a contained user and the login has been disabled at the server level. The server-level login has now been disabled.

An attempt to login conventionally fails:

Validating the move to Partially Contained database

To validate whether or not the partial containment succeeded, let’s see if we can login using the newly contained login to the AdventureWorks2008R2 in the contained mode.

1. In the Object Explorer, click on the Connect drop-down list and choose “Database engine”
2. Change the authentication to “SQL Server Authentication”
3. In the login name and password boxes, pass in the login name and the password of the “UncontainedUser”
4. Do not hit “Enter” key or click on “Connect”. Instead, click on the “Options” button on the “Connect to Server” dialog box
5. In the Connect to Database box, type in the name of the contained database, in our case “AdventureWorks2008R2”

Now, click on “Connect” and confirm that we are indeed in “contained mode”.

In conclusion

Contained databases hold a lot of promise in reducing the administrative overhead associated with a database. I hope that you liked all the posts this week, and hope that you will now go and experiment with this wonderful concept. If you have any interesting findings or and/or links to other great posts on contained databases, do feel free to share them in the comments on this page.

Until we meet next time,

Be courteous. Drive responsibly.