Partially Contained Database - New Feature in SQLServer 2012

Partially Contained Database is new feature in SQL Server 2012. It is a database that is isolated from other databases and instance of SQL Server which hosts the SQL Server.

In earlier version of SQL Server, separating database from the SQL Server instance is the time consuming process and required detailed knowledge of relationship between database and its instance of SQL Server [i.e. login information is stored in the instance of SQL Server not within the database]. But in SQL Server 2012, it becomes very easy through partially contained database, as it keeps itself with all the settings and metadata required to define the database. And it has no configuration dependencies with the instance of the SQL Server where this partially contained database is installed.

While enabling partially contained databases, it delegates control over access to the instance of SQL Server to the owners of the database.

Portability feature in partially contained database provides solution to all underlying data platform business requirements. Portability is always be a key factor in the software industry.

As per Microsoft, partially contained databases do not allow the following features.

1)   Partially contained databases cannot use replication, change data capture, or change tracking.

2)   Numbered procedures

3)   Schema-bound objects that depend on built-in functions with collation changes

4) Binding change resulting from collation changes, including references to objects, columns, symbols, or types.

5)   Replication, change data capture, and change tracking.

6) Temporary stored procedures are currently permitted. Because temporary stored procedures breach containment, they are not expected to be supported in future versions of contained database.

Let’s walk through with configuration guidelines.

First we need to enable the partially contained database option in the current SQL Server instance.

SP_CONFIGURE 'contained database authentication', 1;

GO

RECONFIGURE

GO

Now we can create a partially contained database.

--Create partially contained database.

USE [master]

GO

CREATE  DATABASE [Test_Contained_DB]

CONTAINMENT=PARTIAL

GO

Next create a sample table with records in the newly created partially contained database.

-- Create sample table and insert records on it.

USE [Test_Contained_DB]

GO

Create Table Employee

(           Id INT IDENTITY(1,1),

Name Varchar(100),

salary INT,

ReportsTo Varchar(100)

)

Insert into Employee values ( 'Joseph', 7000, NULL)

Insert into Employee values ( 'Mary', 3000, 'Joseph')

Insert into Employee values ( 'Rakshan', 2000, 'Joseph')

Insert into Employee values ( 'Jack', 6000, 'Joseph')

Insert into Employee values ( 'Akilan', 1000, 'Jack')

Insert into Employee values ( 'Mark', 3500, 'Jack')

Insert into Employee values ( 'Premji', 5000, 'Jack')

Insert into Employee values ( 'Carolin', 2500, 'Jack')

Last step is to create a user to access the partially contained database.

USE [Test_Contained_DB]

GO

CREATE USER Test_Cont_User WITH PASSWORD=N'partial@123',DEFAULT_SCHEMA=[dbo]

GO

EXEC sp_addrolemember'db_owner', 'Test_Cont_User'

GO

We can also use the Windows login if required.

Now let’s try accessing the partially contained database.

 

Let’s use the SQL Server Authentication with newly created username and password.

Next in the connection properties, specify the database name and click connect.

Now we will able to access the Employee table from partially contained database.

 

Portability or easy Database movement across instances are the main features in partially contained database in SQL Server 2012.