The biggest problem with SQL Server for a very long time has been that databases on an instance are always tightly coupled to it. So once you move your database to a different SQL server instance the database will always face compatibility issues though the target and source server instances were of the same version and Service packs.
A database can be dependent on lot of server instance properties such as security, roles, linked servers, CLR, database mail, service broker objects, replication, and SQL Server Agent jobs. Also if the target instance is of different collations as compared to source instance there would be issues with collation compatibility when creating temp objects, executing order by clauses etc.
Characteristics of Contained Databases
One of the most exciting and new feature of SQL Server 2012 is “Partially Contained Databases” which to a very significant level overcomes all the shortcomings stated above. As the name implies the word “contained” means something within itself and the least dependent on something external. So in short a contained database is independent of the SQL server instance on which it’s hosted. Database that is dependent on the SQL server instance are otherwise called as non-contained database.
Prior to SQL Server 2012 when a database had to be moved from one instance to another we would had copy the logins existing on the source instance to the target instance else we had the issues of orphan users. More information on ways to fix orphan users can be found here.Users in a contained database are no longer associated with logins on the instance of SQL Server. You just have to create a database user with password in a partially-contained database and then directly connect to that database.
So in short a contained database includes all database settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed. A contained database will overcome the most painstaking issues a DBA always have to face when moving the databases from instance to another
- You can create a database-specific user without a login (and you can create multiple such users with the same name for different databases);
- Objects in tempdb would be created in the collation of the calling database context which is not true in case of non-contained databases.
1. Setting up a Contained Database
To be able to create a partially contained database you need to enable them at instance level.
Sp_Configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO
Lets check the property which sets the value for contained databases
Enable the contained database on the instance
sp_configure 'contained database authentication', 1 GO RECONFIGURE WITH OVERRIDE GO
Now let’s create a contained database using the following TSQL command.
CREATE DATABASE [ContainedDB] CONTAINMENT = PARTIAL ON PRIMARY ( NAME = N'ContainedDB', FILENAME = N'D:ContainedDB.mdf' ) LOG ON ( NAME = N'ContainedDB_log', FILENAME = N'D:ContainedDB_log.ldf') GO
Now if you right click the database and select Options property you would see that the database “Containment Type” is set to “Partial”.This shows that the database is a Contained database.If the database is not a contained database then the property would be “None”
Creating an user without a login
USE [ContainedDB] GO CREATE USER [Sachin] WITH PASSWORD=N'sachin123' GO ALTER ROLE [db_owner] ADD MEMBER [Sachin] GO
As you can see creating an user for a contained database is as same as creating for an non contained database BUT as seen from the above command the user for a non contained database is not tied up to a SQL server instance login.If you try doing something like this on a non contained database you would get an error
Msg 33233, Level 16, State 1, Line 1
You can only create a user with a password in a contained database.
I have made the user a member of db_owner database role as well.
So when I log into the SQL Server instance with the above username/password I would only be able to see the contained database for which I have been granted access to. Prior to SQL Server 2012 one could see the list of databases in the instance for which he/she does not have rights but could not access them. So if I have access to say two contained databases in a SQL instance I would be able to see only 2 databases in the object explorer though there might be 100 databases hosted on the instance. This was not the case prior to SQL Server 2012.This feature would give you an edge if you would like to hide the list of databases from users who do not have access to them.
One more interesting fact about creating users in contained databases is that you can create users with the same usernames on different contained databases for the same instance but that does not hold true for logins in SQL Server. So if you do not want a headache of to maintaining different usernames you take this approach.
One of the biggest drawbacks with contained databases when creating users is that you cannot set the password complexity checking policy to OFF. The following command would error out on a contained database.
USE [ContainedDB] GO CREATE USER [Sachin] WITH PASSWORD=N'sachin123',CHECK_POLICY=OFF GO
Collation Issues with Contained database
Try running the following command by creating non contained database
USE [master] GO CREATE DATABASE UN_Contained_DB COLLATE Albanian_BIN2 GO USE [UN_Contained_DB]; GO CREATE TABLE tbl ( name varchar(10) ); CREATE TABLE #tbl ( name varchar(10) ); SELECT * FROM #tbl WHERE name IN(SELECT name FROM tbl)
You would get the following error
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Albanian_BIN2" and "Latin1_General_CI_AS" in the equal to operation.
But doing the same on a contained database would work fine as the collation for the temporary object created in the TempDB is same as the collation of the contained database.
Incompatible database objects in Non Contained database
There can be a possibility a function or a procedure in an uncontained database might be using a property which would be incompatible when the database is changed to contained. There is a new DMV named sys.dm_db_uncontained_entities which would list out the objects which would be incompatible and dependent on the external objects. Following is the list of incompatible objects for a contained database.
- User bound to a Login
- Dynamic SQL
- Objects using command line utilities like xp_cmdshell, xp_fixeddrives
- Stored Procedures used to send DB mails
- Objects referring to external objects like sys.sysobjects or any another system catalogs.
- CLR Assemblies
- DDL Triggers
- Partially contained databases cannot use replication and change data capture
It is highly recommended that you run the sys.dm_db_uncontained_entities and list out the objects which are incompatible and then decide whether you want to change the database to a contained one.
Contained database is a good new feature if you fully understand the limitations and flexibility it provides. But I personally believe it still needs to be a bit more evolved and enhanced to overcome the limitations which have been highlighted before.