"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."















































































































Follow @MyTechMantra on Twitter
We're on Facebook





Contained Databases in SQL Server 2012


Introduction

Contained Databases is a new feature which is available in SQL Server 2012. A contained database is a database that will store all its metadata within the database thereby not storing any configuration information within the master database of the SQL Server Instance where the Contained Database is created. A contained database is isolated from other databases which are available on the instance of SQL Server.

The biggest advantage of using this feature is it makes it easier to move a contained database from one instance to another instance of SQL Server 2012. In this article we will take a look at how to configure and implement Contained Database feature of SQL Server 2012.

How to Configure a Contained Database Feature in SQL Server 2012

Different ways to configure Contained Database feature in SQL Server 2012 are:-

  • Using SQL Server Managment Studio
  • Using a TSQL script

Configure Contained Database Feature in SQL Server 2012 Using SSMS

1. Connect to SQL Server 2012 Instance using SQL Server Management Studio and then right click the SQL Server Instance and choose Properties from the drop down list.

2. In Server Properties choose Advanced Page and set the value as True for Enable Contained Databases as highlighted in the below snippet and then click OK to save the changes.

Enable Contained Databases Feature in SQL Server 2012

Configure Contained Database Feature in SQL Server 2012 Using TSQL Code

Execute the below TSQL to configure contained database feature in SQL Server 2012 at instance level.

USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GO

Once the above TSQL Script is executed successfully the Contained Database feature will be enabled at the SQL Server 2012 instance level.

How to create a Contained Database in SQL Server 2012

Once Contained Database feature is enabled successfully at SQL Server 2012 Instance Level then the next step will be to create a contained database.

1. Connect to SQL Server 2012 Instance

2. In Object Explorer, right click Databases and choose New Databases…. option from the drop down menu

3. In General Page enter the name of Contained Database as ContainedDatabaseSQL2012 and click on Options page on the left side pane of New Database window

4. In Options page choose Containment Type as “PARTIAL” from the drop down as shown in the below snippet

Creating Contained Database Using SSMS

5. Finally click OK to create your first Contained Database in SQL Server 2012.

Newsletter Signup

How to create a Contained Database in SQL Server 2012 Using TSQL Query

Execute the below mentioned TSQL query to create a Contained Database.

CREATE DATABASE [ContainedDatabaseSQL2012]
CONTAINMENT = PARTIAL
ON PRIMARY
(
NAME = N'ContainedDatabaseSQL2012',
FILENAME = N'D:\MSSQL\DATA\ContainedDatabaseSQL2012.mdf',
SIZE = 3072KB,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'ContainedDatabaseSQL2012_log',
FILENAME = N'D:\MSSQL\DATA\ContainedDatabaseSQL2012_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10%
)
GO

Create a SQL Server or Windows Login which will have access to the Contained Database

A contained database can be accessed by a SQL Server or a Windows Login. This login should be created within the Contained Database.

  • To create a login Expand Databases …. Expand ContainedDatabaseSQL2012…. Expand Security …. Right click Users and select New User… from the drop down menu as shown in the snippet below.

Create New SQL Server Using in SQL Server

  • To create a New Database User, enter User Name, enter Password, Confirm Password and enter Default schema as “dbo” as shown in the snippet below and click OK.

Create New SQL Server User

Create New SQL Server User Using TSQL code

USE [ContainedDatabaseSQL2012]
GO
CREATE USER [ContainedDatabaseLogin]
WITH PASSWORD=N'Str0ngP@$$', DEFAULT_SCHEMA = [dbo]
GO

How to Connect to Contained Database

In Connect to Server, Login tab enter Server Name, choose Authentication, enter Login and Password as shown in the below snippet and click on Connection Properties tab.

SQL Server Login Tab

In Connection Properties tab specify the database name as shown in the snippet below and finally click the Connect button.

Connection Properties in SQL Server 2012

In the below snippet you could see that using the contained database login you could just see only one database to which your login belongs and the login doesn’t have access to Database Engine.

Contained Database in SQL Server

Conclusion

Now that you have successfully contained database you can easily easily create the Backup of the Contained Database and Restore it on any other instance of SQL Server 2012.

Share this Article

Related Articles…