Understanding Contained Database in SQL Server 2012

SQL Server 2012 supports contained databases and partially contained databases, which provide a high degree of isolation from the database engine instance but are not fully contained. The contained database becomes highly portable that you can migrate it to any instance or even in SQL Azure.

In SQL Server 2012, the implementation of partially contained database does not support the following

  • Change Tracking
  • Change Data Capture
  • Binding changes resulting from collation changes
  • Replication

You can enable contained databases at the server level by opening the server properties in SSMS or by running the following statements

USE [master]
GO

–Turn on contained database at server level
sp_configure ‘show advanced’, 1
RECONFIGURE
GO
sp_configure ‘contained database authentication’, 1
RECONFIGURE
GO

Then after everything is OK, proceed to the database which you wish to enable containment. Right click and open the database properties. You can also use the ALTER DATABASE statement with the SET CONTAINMENT option to configure.

Now to test and play around with the database containment feature, run the following scripts below;

–Create containted database
CREATE DATABASE [BlogDemoDBContained]
CONTAINMENT = PARTIAL
GO

–Create SQL Login contained user in database
USE [BlogDemoDBContained]
GO

CREATE USER democontaineduser WITH PASSWORD=N’Passw0rd’, DEFAULT_SCHEMA=[dbo]
GO

Then disconnect your current query window. Try to connect to database via the GUI and but click on OPTIONS to open window same as below;

Containment Login

Make sure to select the contained database in the Connect to database drop down list. Enter the credentials of your demo contained user we created above and viola you should be able to connect to the instance showing only the contained database. There you go.

 

 

 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s