Yesterday, I attempted to explore the concept of contained databases. Today, we will see how to create our first contained database in the very latest SQL 11 (“Denali”) CTP01.

As discussed yesterday, contained databases are new to SQL 11, and hence if you need to experiment with contained databases, you will need a instance of SQL 11 (“Denali”) deployed and running.

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."

Creating a “contained” database

Here is a step-by-step guide to create your first “contained” database:

Assumption: It is assumed that the reader have fair amount of exposure to SSMS and SQL Server database fundamentals before beginning this exercise.

1. Login to the SQL 11 (“Denali”) SQL Server Management Studio (SSMS) (while you are at it, enjoy the new Visual Studio 2010 shell)
2. Right-click on the Server instance name in the Object Explorer and click “Properties”
3. Select the “Advanced” page
4. Set the “Enabled contained databases” to TRUE
   

   Alternate mechanism: --Enabled Advanced options sp_configure 'show advanced options',1 RECONFIGURE GO --Enabled Database Containment sp_configure 'contained database authentication',1 RECONFIGURE GO --Disable Advanced options sp_configure 'show advanced options',0 RECONFIGURE GO

5. Now, restart the SQL Server Instance by right-clicking the instance name in SSMS and selecting “Restart”
6. After the restart, log back into SSMS, right-click on the Database node and select New database
7. Name the database “ContainedDB”
8. Navigate out to the Options page and select the containment mode as “Partial”
   

   Alternate method (via T-SQL): --Notice that we now have a new option - CONTAINMENT! USE master; GO CREATE DATABASE ContainedDB CONTAINMENT=PARTIAL ON ( NAME = Sales_dat, FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAContainedDB.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales_log, FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAContainedDB.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) ; GO

9. Click “OK” to create the database

Creating a contained user within the newly created contained database

1. Login to SQL 11 (“Denali”) instance used in the test above via SSMS
2. Using the Object Explorer, navigate out to “ContainedDB”->Security
3. Right-click on “Users” and choose “New User”
4. In the New User dialog, enter the user name and password of your choice to create a new user. I created a user called “ContainedUser”
5. Navigate out to the Membership page, and select the “db_owner” checkbox
   

Let’s Login!

It’s time to login to SQL Server using our newly created user. But wait! Don’t we need that this database user is mapped to a login? No! That’s the whole beauty of contained databases!

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 user created in the steps above
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 “ContainedDB”
   
6. Now click on “Connect” to connect to the SQL Server
7. Notice the level of rights that this user is granted
   

   NOTE: Although the user (ContainedUser) had db_owner membership, the server/instance-level objects are not exposed to this user under this connection.

Does this database contain any non-contained  entities?

So, now that the database and the users are created and login process tested, it is essential to ensure that the database is indeed contained and there are no uncontained entities.

Launch a new query editor window using the contained database connection created above, and execute the following query against the “ContainedDB”

SELECT * FROM sys.dm_db_uncontained_entities 

You should only see one record for the ROUTE class_desc. This is expected.

Changes to the ALTER DATABASE clause

With contained databases being a radical change in SQL 11 (“Denali”), there is no wonder that we have quite a few commonly used clauses which have changed. One of them is the ALTER DATABASE clause.

To study what changed, run the following query against the “ContainedDB”, and note the error that occurs.

ALTER DATABASE ContainedDB SET CONTAINMENT=NONE; 

Msg 33233, Level 16, State 1, Line 1

You can only create a user with a password in a contained database.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

You did not expect that, did you?

Well, the behaviour of several commands has changed when used in context of a contained database. Please refer the Books On Line page (http://msdn.microsoft.com/en-us/library/ff929143(SQL.110).aspx) for all the associated details.

Okay, now let us run the following:

ALTER DATABASE CURRENT SET MULTI_USER; 

The query will succeed, but for those who came in late, note the use of the CURRENT keyword, which indicates that all changes must be made to the current database only, and must not cross the application boundary.

Now run the following query:

ALTER DATABASE ContainedDB SET compatibility_level = 110; 

This query will be successful.

Finally, close the SQL Server Management Studio.

Some finer points…

1. Only the partial containment mode is supported in SQL 11 (“Denali”) CTP01
2. The following entities are not allowed in a partially contained database
   • Uncontained tables (for example, a table that uses an uncontained function in a constraint or computed column)
   • Check constraints that directly use uncontained entities or functions
   • Numbered procedures
   • Computed columns that directly use uncontained entities or functions
   • Schema-bound objects that depend on built-in functions with collation changes
   • Binding change resulting from collation changes, including references to objects, columns, symbols or types

Tomorrow, we will move this newly created database to another SQL Server 11 (“Denali”) instance, and see how easy it can be.

Until tomorrow,

Be courteous. Drive responsibly.