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

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We will improve your knowledge

SQL Studies

My SQL Studies

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Microsoft BI consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Terli Srinivasa rao Blog

Sql server and Tableau practices

Follow

Get every new post delivered to your Inbox.

Join 46 other followers

%d bloggers like this: