Contained Databases

Contained Databases

Contained Databases is new feature introduced in SQL 2012. It provide a means for effectively decoupling SQL Server databases (and their users, collations, and other assets) from the underlying SQL Server instance itself. , contained databases are much more "portable" in the sense that they can be easily moved from one server to another -- without worries about orphaned users and other issues that have typically made moving databases problematic. Thus, not only are contained databases a big win in corporate environments where DBAs need to shunt databases around for load-balancing purposes, they're also a huge win for ISVs (and developers) who want to more easily copy, move, or deploy databases with their software. Similarly, for web developers who rely on SQL Server as a back-end database, contained databases help make developers less dependent on hosting platforms as their databases become more portable

 

N.B: The containment setting of a database can be NONE, PARTIAL or FULL. But only NONE and PARTIAL are supported on SQL Server 2012. 

 

 

 

To create a contained database we must enable sql instance for containment using:

 

GUI -> Server properties -> Advance – choose Enable contained database.         Or

TSQL -> Exec sp_configure ‘contained database authentication’, 1

              Reconfigure

 

Then we must set the database option to Partial while creating new database in containment type option. Or using TSQL

 

Create/Alter Database Con_test Containment = Partial

 

 

Once DB Is ready , we can create user on database with password and this user won’t be dependent on server login, this can be done by GUI or TSQL :

 

Create user Con_User with password =’********’ default_Schema= [Dbo]

 

N.B; There is one very important note we must know that while connecting server using contained database user we must set the default database to contained db from option window in GUI in server connection. Same while connecting application using connection string.

 

Using SQLCmd we must use –d switch or else we can get login error ->

 

Ø   SQLCMD –S .\Servername –U con_user –P ********* -d Con_db

 

 

 

While changing to uncontained database to contained we might need to convert existing user to contained user:

 

First find out out the uncontained entity using ->

Select * from sys.dm_db_uncontained_entities

Select * from Sys.database_priciples

 

To migrate from uncontained user to contained user :

Exec Sp_migrate_user_to_contained

         @Username = ‘TestUser’,

         @Rename = N’Keep_Name’,

            @Disable = N‘Disable_login’/’do_not_Disable_login’

 

Caution: Make sure not to disable login while migrating unless you really want to. Because, this login might have a user mapping in some other databases which will break things severely.

 

Now this can be used to login to database using existing password of user.

 

 

Backup/Restore is the same as other database and we don’t need to worry of orphan user anymore.

 

 

There are few disadvantages also of using it:

 

1.     Connection string must specify the database name.

2.     Cross db query not possible using same name/password user as both are different in in different contained database until it on server level as well.

3.     Collation cannot rely on tempdb we might need to tweak codes as well.