Using SQL Server 2012 Contained Databases

A couple of years ago, I wrote an editorial about the future of database virtualization ("Riding the Virtualization Express: From Desktops to Database"). In that scenario, it wasn’t just the server instance or hardware that was virtualized, but the database itself. Contained databases are a big step in that direction. Although they're not completely independent of the SQL Server instance—in SQL Server 2012, it might be more correct to call them partially contained databases—they're much more portable than traditional SQL Server databases.

Contained databases maintain their own user login information separate from the SQL Server instance. Much of the metadata that describes the database is stored in the contained database itself, rather than just being stored in the master database. Plus, dynamic management views (DMVs) and Extended Events (XEvents) can report upon the information for the contained database. Contained databases make it easier to use availability features such as database mirroring and AlwaysOn availability groups by storing the users with the database. Let’s take a closer look at how to use SQL Server 2012’s new contained database feature.

Enable the Server Instance to Support Contained Databases

Before you can use a contained database, you need to enable contained databases in the SQL Server instance. Here's how to enable authentication for contained databases by using sp_configure:

sp_configure 'contained database authentication', 1; GO RECONFIGURE GO

Create a Contained Database

After you enable support for contained databases in the SQL Server instance, you can create a contained database. To create the contained database, use the CREATE DATABASE command, but add the new CONTAINMENT option:

USE master GO CREATE DATABASE [MyContainedDB] CONTAINMENT=PARTIAL GO

Alter an Existing Database

You don’t necessarily need to create a new database in order to take advantage of containment. You can also use the ALTER DATABASE command to enable an existing database to become a contained database. The following listing shows how to change the AdventureWorks2012 database into a contained database:

ALTER DATABASE AdventureWorks2012 SET CONTAINMENT = PARTIAL GO

Create Users in the Contained Database

After you create a contained database, the next step in using the contained database is to create the users who will access it. You should note that you can also use Windows authentication to connect to a contained database. The following CREATE USER command illustrates the WITHPASSWORD clause, which is available only for creating users in contained databases:

USE [MyContainedDB] GO CREATE USER MYContainedUser WITHPASSWORD=N'Pa$$w0rd!', DEFAULT_SCHEMA=[dbo] GO

Limitations to Contained Databases

As you might expect, contained databases aren't exactly like standard databases. Contained databases don't support replication, change data capture, or change tracking. In addition, contained databases don't support numbered procedures, schema-bound objects that depend on built-in functions with collation changes, or binding changes resulting from collation changes.