SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Configure a Contained Database Feature in SQL Server

Contained Databases is a new feature which was initially introduced 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. In this article we will take a look at how to configure and implement Contained Database feature of SQL Server 2012.

The biggest advantage of using contained databases feature is it makes it easier to move a contained database from one instance to another instance of SQL Server.

This article is divided into four parts for easier understanding of the concept for DBA’s and Developers.

How to Configure a Contained Database Feature in SQL Server

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

  • Using SQL Server Management Studio
  • Using a T-SQL script

How to Configure Contained Database Feature in SQL Server Using SSMS

Step 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.

Step 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
Enable Contained Databases Feature in SQL Server

How to Configure Contained Database Feature in SQL Server Using T-SQL Code

Execute the below T-SQL to configure contained database feature in SQL Server 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 Instance level.

Click the below Number Buttons to continue reading the rest of the article.

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Add comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement