Contained Databases in SQL Server 2012

 

 

When a database or application is migrated from on SQL instance to another server, the current database model is left behind. Contained database in SQL Server 2012 retain all information and objects in the database. A few examples are functions, tables, schemas and types. Settings such as linked servers, sql agent jobs and other application scope objects are also kept within the database. SQL 2012 contained database includes all mentioned settings and metadata required so it doesn’t have dependencies on the current database instance. This allows for application and database portability. This allows database administrators to simply migrate databases to another SQL server without worrying.

 

 

Disadvantages of Contained Databases
All these great features do come with some disadvantages. Majority are related to USER WITH PASSWORD authentication process. Database administrators can accidently give user’s access to the database by attaching a contained database. To prevent this, you must bring up the database in RESTRICTED_USER mode. Kerberos authentication cannot be used with contained databases.

 

 

Moving to contained database
After you understand the risk of moving to a contained database, follow below instructions to move to a contained database. To convert a database to a contained database using SQL Management Studio, follow:

  1. Open SQL Management Studio
  2. Rightclick your SQL Server and choose properties.
  3. Got o Advanced page, configure “enable contained database” to True, under Containment section.
  4. Now, rightclick on the database you wish to convert, choose properties
  5. Go to Options page, configure “Containment type” to “Full” (or partial, if you wish), click OK.

 

You may also perform the same task using TransactSQL. Run the below commands to convert to contained database.

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE ;
GO
sp_configure 'show advanced options', 0 ;
GO
RECONFIGURE ;
GO
USE [master]
GO
ALTER DATABASE [ApplicationDB] SET CONTAINMENT = PARTIAL
GO