SQL Server 2012: Sometimes Partial Is Preferable

SQL Server 2012 will support partially contained databases, which should ease some migration and consolidation issues.

Denny Cherry

Partially contained databases resolve some of the major consolidation and migration issues that have been around SQL Server databases since SQL Server version 4.2. Thankfully, this will be one of the many new features being introduced in SQL Server 2012.

The first problem that partially contained databases resolve is moving a database from one instance of SQL Server to another without having to identify the SQL Server Logins used by the database. It also covers creating those logins on the destination database instance.

SQL Server 2012 does this using a contained SQL Server login. This is a SQL Server user created within the contained database, but without a matching SQL Server login. This contained user within the contained database has a password stored within the contained database, so authentication works as expected.

The second problem resolved is that collation conflicts between tables within contained databases and temporary tables effectively go away. SQL Server automatically creates temp tables within the scope of the contained database when it’s collated. This allows the same instance of SQL Server to host databases of different collations, without needing to modify the CREATE TABLE statements to specify the collation or having to specify the COLLATE statement within the syntax of the JOIN statement.

Unlike some of the new features that require the database compatibility level set to the newest value, partially contained databases are currently supported back to SQL Server 2005. The contained database feature, on the other hand, is not yet complete. It should be completed somewhere between the SQL Server 2012 CTP3 release and when the product is released to manufacturing in early- to mid-2012.

Set up a Partially Contained Database

Before you can change a database from being uncontained to partially contained, you must first change a server setting using the sp_configure system stored procedure. Use the sp_configure system stored procedure to change the “contained database authentication” setting from 0 to 1, then use the RECONFIGURE statement to activate the new setting, like so:

EXEC sp_configure 'contained database authentication', 1 RECONFIGURE GO

After enabling the “contained database authentication” setting, you can change a specific database to partially contained. You can use the SQL Server Management Studio as shown in Figure 1, the ALTER DATABASE statement or by creating a new database as a contained database:

ALTER DATABASE: USE [master] GO ALTER DATABASE [Cont] SET CONTAINMENT=PARTIAL GO CREATE DATABASE: CREATE DATABASE [Cont1] CONTAINMENT=PARTIAL GO

To create a new partially contained database or alter a database so that it’s partially contained, connect to the SQL Server instance in the object explorer. Right-click to create a new database, or select an existing database. Select properties (depending on if you’re creating a new database or changing an existing database). In either case, select the “Options” tab and change the “Containment type” drop-down from “None” to “Partial.”

Figure 1 The Database Properties window lets you change the database containment setting.

To change an existing database from uncontained to partially contained, use T/SQL and the ALTER DATABASE statement. In order to successfully change the database containment setting, the ALTER DATABASE statement must be able to take an exclusive lock of the entire database. Therefore, you’ll have to schedule a brief outage with the business unit using this particular database.

Avoid Temporary Table Collation Errors

New partially contained databases allow multiple different collations to exist on the same instance of SQL Server. You won’t have to worry about collation problems when joining temporary tables. You can test this by taking a Microsoft SQL Server instance and creating a partially contained database of a different collation. Then create a physical table and a temporary table within the partially contained database. Load data into the two tables and attempt to join them.

Create a database using the Albanian_100_CI_AI_KS_WS database collation, while the instance is using the SQL_Latin1_General_CP1_CI_AS collation, as shown in Figure 2. It creates a table named dbo.Employee within the new databases and loads three rows into the table. It also creates a temporary table named #emp and inserts a single row into the table. When the tables are joined at the end of the query, one row is returned. Without the contained database setting set to partial, it would instead return an error.

Figure 2 Create and use a partially contained database.

use master GO CREATE DATABASE [Cont] CONTAINMENT = PARTIAL ONPRIMARY (NAME = N'Cont', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont.mdf', SIZE = 4096KB, FILEGROWTH= 1024KB) LOGON (NAME = N'Cont_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%) COLLATE Albanian_100_CI_AI_KS_WS GO Use Cont GO Create table Employee (EmployeeId INTPRIMARYKEY, LastName nvarchar(100), FirstName nvarchar(100)) GO Insert into Employee (EmployeeId,LastName,FirstName) values (1,'last1','first1'), (2,'last2','first2'),(3,'last3','first3') GO Create table #emp (LastName nvarchar(100)) GO Insert into #emp (LastName) values ('last1') GO select* from Employee join #emp on Employee.LastName = #emp.LastName

Contained Users

Contained users are similar to traditional SQL Server Logins, except they don’t have a login that aligns to the contained database user. Create a contained user with the SQL Server Management Studio or the CREATE USER T/SQL statement.

To use SQL Server Management Studio, connect to the instance hosting the contained database in the object explorer. Navigate to Databases | {Your Contained Database} | Security | Users. Right-click on the Users folder and select “New User” from the context menu. In the “User type” drop-down, select “SQL user with password.” Fill in the user name and password fields (see Figure 3), as well as the default schema and any database role membership you need to configure.

Figure 3 Create a contained SQL user with SQL Server Management Studio.

After creating the contained user, you can examine the sys.database_principals catalog view within the contained database. The recordset returned by querying the catalog view will show the new column named authentication_type set to a value of 2. It will also show the authentication_type_desc column set to a value of “DATABASE” when the user is a contained user.

Create a contained database user using T/SQL with the CREATE USER statement. SQL Server knows you’re creating a contained user instead of a user that aligns to a login at the instance level by specifying the WITH PASSWORD statement, as shown here:

CREATE USER MyContainedUser WITHPASSWORD = 'MyContainedUserPassword' GO

You can create contained SQL users, as well as contained Windows Logins. Change the “User Type” drop-down (see Figure 4) to “Windows user” and enter the user name without selecting a login.

Figure 4 Create a contained Windows Login using SQL Server Management Studio.

You can also create contained Windows users with the CREATE USER T/SQL statement, as shown here:

CREATE USER [CAPT-MAL\test] WITH DEFAULT_SCHEMA = [dbo] GO

Log on to the contained database with a contained login, just like a traditional (non-contained) user. The big trick to the contained user is that you must specify the database name in the connection string when connecting. If you don’t, SQL Server will assume a traditional SQL Server Login is making the connection attempt. The login will fail if there is no login that matches the user name and password defined at the instance level.

Do this in the SQL Server Management Studio connection dialog by clicking the Options button at the bottom of the login window. Select the “Connection Properties” tab. On the “Connection Properties” tab the database name of the contained database can be entered into the “Connect to database” drop-down (see Figure 5).

You won’t be able to query for the list of databases hosted on the SQL Server Instance as the contained user specified on the “Login” tab. That contained user doesn’t have rights to the master database without first authenticating against the contained database that holds its user name and password. Because of this, you’ll have to know the database name and type it into the box manually.

Figure 5 The “Connection Properties” tab of the SQL Server Management Studio connection dialog window.

As you can see from these two features, consolidating databases instances onto an instance of SQL Server 2012 will be much easier. It will take a little time and testing to ensure that your database app is fully compatible with the contained database feature. But once you’ve enabled the contained database setting, future database migrations and consolidations will be much smoother, and that’s really the goa.

Denny Cherry, MVP, is an independent consultant with more a decade of experience working with Microsoft SQL Server, Hyper-V, vSphere and Enterprise Storage solutions. He currently holds several Microsoft Certifications related to SQL Server versions 2000 through 2008, including the Microsoft Certified Master.  He has written several books and dozens of technical articles on SQL Server management.

Related Content

• SQL Q&A: Backup and Setup
• SQL Server: Manage the Migration
• SQL Server: User-Defined Roles