Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server / SQL Server 2012

Contained Database – A New Feature in SQL SERVER 2012 TIP #81

Rate me:
Please Sign up or sign in to vote.
4.50/5 (2 votes)
26 Feb 2015CPOL2 min read 7.4K   5   1
Contained Database–A new feature in SQL SERVER 2012 TIP #81

While working on a project, sometimes it happened that we need to shift the database from one server to another due to various reasons (sometimes, it is only money Smile ).

We are aware that this easy step takes backup and restores another machine but the glitch is what about the users which have access to the database ?

Now, the next step for us is to create each individual user on new database server which is a very tedious job sometimes.

In SQL SERVER 2012, a new feature was introduced by Microsoft which is Contained Database. This feature allows us to move meta data as well with database backup.

Let me explain it in more detail what I mean to say here. A contained database is a feature which isolates database from other database and from SQL SERVER host for authentication which means now the authentication can be done on the database level.

For example, you want to move an in house Employee management system which has around 100 users which access this database so if the database in contained enabled, then in that case, we do not have to create new users on another sever.

Now let me explain here how to enable this feature.

Step 1

Enable this feature on server level by right clicking on server and from property TAB.

ServerProperties

We can enable this feature by the following command as well.

ContainedFeatureEnable

SQL
EXEC sp_configure ‘show advanced’, 1
GO
RECONFIGURE
GO
EXEC sp_configure ‘contained database authentication’, 1
GO
RECONFIGURE
GO
GO

Step 2

Now right click and go to property TAB of database on which we have to enable Containment Type feature.

Database_Level_ContainmentType

We have to enable this feature and set the value Partial.

We can enable this feature via SQL command:

SQL
ALTER DATABASE IndiandotnetDB SET CONTAINMENT = PARTIAL

Once we have enabled this feature above, we are good to go.

Now, when you create user on database, you can easily move this database with the META DATA which means user.

Warning: If you see the image below, you will find Replication, change data Capture,Change Tracking are not supported contained databases.

Warning

So, if you are using Replication, change data capture, change tracking please avoid enabling this feature and use the traditional way of creating user.

I hope this feature might help you somewhere.

Thanks!

Filed under: CodeProject, DENALI, SQL Server, SQL SERVER 2014, TIPS Tagged: Contained Database, META DATA, SQL server 2012, SQL SERVER Feature

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader
India India
I am Rajat Jaiswal from India. I am working as a Technology specialist in one of the reputed company in India with 12+ years of experience. I am a Microsoft Data Platform MVP & Alibaba Cloud MVP.
I have always had an affinity to learn something new in technology. Database, Web development, Cloud computing are the areas of my interests always.
I have been a regular contributor to technologies with more than 300+ tips. Learning & Sharing is one of my aims always. Writing blogs, delivering sessions, helping on forums is my hobby.

Comments and Discussions

 
GeneralNew featues Pin
Close Man7-May-15 19:34
Close Man7-May-15 19:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.