Contained Database - SQL Server 2012 - New Features | SansSQL

Thursday, June 21, 2012

Contained Database - SQL Server 2012 - New Features

There has been lot of talk about SQL Server 2012 and its features - out of which there are few which are definitely a big leap. One among them is CONTAINED DATABASES

What is Contained Database?

Any Database which is INSTANCE INDEPENDENT with no external dependencies and has self-contained mechanism of authenticating users is referred to as Contained DB.

The advantage of having such database is that they can be moved easily to another server and we can start working on it instantly without the need of any additional configuration since they do not have any external dependencies.

Frequently used terminologies when using contained databases:
  • Application Boundary - It is the boundary between the server instance and the application code

  • Application Model - The application model is the programming surface of a contained database to which SQL Server applications are written. This surface area differs from non-contained databases in that it clearly separates the application from the instance. This separation is made by the application boundary.
Contained database functionality can be classified into -
  • Unchanged and available for use within the application model. This classification includes most SQL Server objects, CREATE and ALTER statements, procedures, and so on.

  • Not available within the application model. This classification includes purely instance-level functions such as instance settings, processor affinity, resource governance, or the creation of databases themselves.

  • Available within the application model with certain changes or restrictions. This classification includes collations and authentication.
The management model includes everything that lies outside of the application model on the instance level. The management model concerns the maintenance and operation of the instance as a whole as it resides outside of a contained database.
  • ContainedIt is a user entity that resides entirely within the application boundary.
  • UncontainedIt is a user entity that crosses the application boundary.
  • Non-contained databaseDatabase whose containment is set to NONE.
  • Fully contained databaseDatabase that does not allow any objects or functions to cross the application boundary.
  • Partially contained databaseDatabase that allows features to cross the application boundary. It is available in RTM

 Contained user

There are two types of users for contained databases.
  1. Contained database user with password which are authenticated by the database.
  2. Windows principals that can directly connect to the database and does not need any login in the master database.

What is the purpose of Contained DBs?

  1. Is there a way to avoid any loss of information during Database movement?
  2. Is there a way to avoid checking for orphan users when we do a DB restoration and end up fixing the same one by one? Saving time J
  3. Is there a way to move all the jobs related to a DB (agent job information) along with the Database movement? Etc.
Answer to all the above questions is CONTAINED Databases feature in DENALI (SQL Server 2012)

What are the contents of Contained DB?

The contained database, keeps all necessary information and objects in the database, for example
  • Tables
  • Functions
  • Constraints
  • Schemas
  • Types
It also stores all application-level objects in the database, including
  • Logins
  • Application-level agent jobs
  • Persisted error messages
  • Linked server information
  • System settings

Implementing\Creating Contained DBs

There are 2 ways of implementing contained DB
  1. Using SQL Server Management Studio (SSMS)
  2. Using system stored procedures\scripts

Using SQL Server Management Studio (SSMS)

Step 1: Enable "Contained Database Authentication" property on the SQL Server instance Level.
Right click on the instance and select 'properties'and then select ‘Advanced’ option to set the Containment property to True.

Step 2: Create a database and set its CONTAINMENT property to Partial.

NOTE: The current RTM version only supports PARTIAL containment
  • Once the DB is created either using SSMS or scripts – set the DB containment property to ‘Partial’
Step 3: Create a Contained User within the newly created contained database.

Step 4: Login to the contained database using the user that exists in Contained Database.

Using system stored procedure

--Enabled Advanced options
Exec sp_configure 'show advanced', 1;

--Enabled Database Containment
Exec sp_configure 'contained database authentication', 1;

Changing Non Contained DB to Contained DB:

A non-contained DB can be set as contained but with few things to take care (mentioned below)
Step 1: To identify the NonContained objects

Step 2: To change the DB property to Partial containment

Step 3: To migrate users associated with Sql server logins
@RENAME = N'testusercontained',

The sp_migrate_user_to_contained stored procedure is needed in order to contain the users that are associated with Sql Server logins. It will convert the Sql Server logins to users with password.

Step 4: We can repeat STEP 1 if there are any more non contained DB objects

How do we backup and restore contained DB?

The normal process of backup and restore will also apply in using contained DB as well. However, if we are restoring the backed up contained DB on any other instance we need to Enable "Contained Database Authentication" property on the SQL Server instance Level which is turned off by Default.

Else we get to see the following error –
Msg 12824, Level 16, State 1, Line 1 The sp_configure value 'contained database authentication' must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use. Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Using scripts:

--Enabled Advanced options
Exec sp_configure 'show advanced', 1

--Enabled Database Containment
Exec sp_configure 'contained database authentication', 1;

Can we use CREATE\ALTER Database Statement on Contained DBs?

The normal ALTER command doesn’t work on contained DB. Instead, a new option called ‘CURRENT ‘has been added which ensures that if we move the database to a new instance or change the database name, the command will still work.


Benefits of using contained DBs

There are both Pros and Cons in using contained DBs –


Database Movement
One of the problems facing the existing database model is data being left behind when an application is moved from one instance to another. Ex: Orphan Users

Some data connected with an application, (for example, login information and agent job information) is currently stored within the instance instead of in the database. When you move a non-contained database application from one instance to another instance of SQL Server, this data is left behind. Then you must identify the data left behind and move it with your application to the new instance of SQL Server. This process can be time consuming and difficult.

Initial Application Development and administration
In a contained database, all elements of the application will reside within the database itself, thereby limiting instance-level impacts on the application and instance-level concerns for the developer.

A contained database stores data within the database itself, the lack of a disconnect between the application and the information previously held at an instance level means that the application administrator can easily verify production environment settings that may affect the application, access the necessary permissions to perform required duties, have the ability to backup and restore the database, and so on.

Threats of using contained DBs

There are disadvantages on using contained DBs
Related to Users
The major disadvantage of using contained DBs is to do with users –  

Db_owner and Db_securityadmin roles- Users in a contained database that have the ALTER ANY USER permission, such as members of the db_owner and db_securityadmin fixed database roles, can grant access to the database without the knowledge or permission if the SQL Server administrator. Granting users access to a contained database increases the potential attack surface area against the whole SQL Server instance. 
  • Guest Account - Database owners and database users with the ALTER ANY USER permission can create contained database users. After connecting to a contained database on an instance of SQL Server, a contained database user can access other databases on the Database Engine, if the other databases have enabled the guest account.  
  • Keep away from duplication user and login ids - If a contained database user with password is created, using the same name as a SQL Server login, and if the SQL Server login connects specifying the contained database as the initial catalog, then the SQL Server login will be unable to connect.
For more we can refer to Books Online.

Where can we have contained DBs?

Few scenarios where we find the usage of contained DBs - 

Two SQL Server instances which are being used by different team and different set of SQL Logins created to each team respectively. 
  • The same can be implemented using contained DBs and creating users and not logins (reducing the dependency on logins) 
If we need to create a copy of any of the existing DBs – the process would be to Take backup, restore and verify all the orphan users. 
  • The same can be reduced to just backup and restore – no need to verify the users (if the same set of users need access to the newly created DB)
  • In addition we can create users if any new needed with no login needed at instance level.