Contained Databases in SQL Server 2012

Contained Databases have been an oft requested feature which has finally arrived in SQL Server 2012.
In prior SQL Server versions, database were not 100% portable. In case you need to move a database from one server to another using backup and restore, the data in the
database will of course be preserved. However, to work with the database, you need several more objects such
as logins etc. However, these objects are outside the database and so would not be included in any backup.

With Contained databases, there is only a small functional
dependency on SQL Server Instance or in other words, the database is
self-contained and all the objects will reside in the database itself.

 There are three Contained types.

Contained
Type

Description

NONE

Default
contained type is what we are used to have in SQL Server 2008 R2 and
prior versions.

PARTIAL

Partially
Contained Databases provides some isolation from the instance of SQL Server
but not full containment.

FULL

(This is currently not available in SQL Server 2012 but in FULL mode, users will not be not permitted to cross the database)

Configuring Contained Databases

Firstly, you will need to enable the Contained Database feature at the
aerver level either from the user interface or from a script.

sp_configure 'show advanced options',1
 GO

 RECONFIGURE WITH OVERRIDE
 GO

 sp_configure 'contained database authentication', 1
 GO

 RECONFIGURE WITH OVERRIDE
 GO

Alternatively you can use the SSMS user interface:

Right click the server, select Properties > Advanced and set Enabled Contained Databases to True.

Description: C:\Users\dinesha\Desktop\5528EN _03_1stDraft\Images\5528EN _03_01.png

Next you will need to configure Contained Database at the database level, to do this from using code:

CREATE DATABASE [ContainedDatabase]

 CONTAINMENT = PARTIAL
 ON PRIMARY

 ( NAME = N'ContainedDatabase', FILENAME = N'C:\myPath\ContainedDatabase.mdf')
 LOG ON
 ( NAME = N'ContainedDatabase_log', FILENAME = N'C:\myPath\ContainedDatabase_log.ldf')

 GO
 

As you can see in the above script, the CREATE DATABASE syntax
is tweaked to enable the contained database feature.

You can use the SSMS user interface
to enable contained database at the database level:

Description: C:\Users\dinesha\Desktop\5528EN _03_1stDraft\Images\5528EN _03_02.png

In SQL Server 2012, you will see only two options i.e. None
and Partial however we may see the Full contained type in future releases
of SQL Server 2012.

The new Contained Databases feature will be reflected in the sys.databases but not in sys.sysdatabases:

SELECT Name,containment
FROM sys.databases

Description: C:\Users\dinesha\Desktop\5528EN _03_1stDraft\Images\5528EN _03_03.PNG

As you can see in the above image, the new column is included to
identify whether the database is contained database or not.

You can change the containment to PARTIAL or to NONE by
executing following script.

USE [master]
GO

ALTER DATABASE [ContainedDatabase]
SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

Let us see how user login works with Contained Databases.

If you are creating a user in any of SQL Server 2012 prior
versions, this is the syntax you need to use.

USE [master]
GO

CREATE LOGIN [None_Contained_Login]
WITH PASSWORD=N'Qwerty123',
DEFAULT_DATABASE=[master]
GO

USE [AdventureWorks2008R2]
GO

CREATE USER [None_Contained_Login]
FOR LOGIN [None_Contained_Login]
GO

This script will create a login with s password and then
create a user by adding the previously created login.

In Contained databases this is how you create a user.

USE [ContainedDatabase]
GO

CREATE USER [Contained_User]
WITH PASSWORD=N'Qwerty123'
GO

Note the two two differences:

  • Unlike in the standard (pre SQL Server 2012) user creation, you have only one create
    statement. In this, you will have only a CREATE USER statement and no CREATE LOGIN
    statement.
  • In SQL server 2012 you assign the password to the USER and not to the LOGIN unlike in the standard
    user creation.

The above query cannot be run on ‘None’ contained or standard
databases. If you attempt to run this statement you will trigger the following error.

Msg 33233, Level 16, State 1, Line 1

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

However, you can create a standard user in contained
databases since you have used PARTIAL contained database type (Note that in future this
will fail in the FULL contained database type).

For UI lovers, right click the users under the Security node in
the Contained database and select New User…  where you will be taken to
the following screen.

In the above screen, you will see that the user type is a SQL user
with password which is what contained database type requires.

Pages: 1 2




Array

One Response to “Contained Databases in SQL Server 2012”

  1. Nice introduction to contained databases.

    One thing I found is that the server authentication mode needs to be set to Mixed Mode (and not Windows Authentication). I guess this makes sense as the user accounts need to be independent of a domain.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |