RSS Feed

Contained Databases

Contained Databases

Fully contained databases: the database is independent, and users cannot access resources outside it.

Partially contained databases: the user is defined inside the database, so the database is independent, but it also can access resources outside the database (dependency on SQL Server instance is considerably reduced).

SQL Server 2012 supports only partially contained databases as of now. The partially contained databases solve two problems: login/user mapping dependencies and collation of temporary tables.

More about these two problems below:
Collation Hell
The Database Shuffle

Advantages of contained databases
1. A contained database has all the required settings which make it independent (no external dependencies) further making it easily portable to another server.

2. With this contained database other features like database mirroring and always on are also improved.

3. Benefits of contained databases also revolve around separating the application development and management from the underlying database instance infrastructure needed to support it. A contained database makes administering applications easier, because there is no real dependency on the host instance.

Disadvantages of contained databases
1. The user, who has access to multiple databases on a server, must use separate logons with (potentially) different passwords. Also once there is an established connection, the user is not able to switch from one database to another (or switch to a contained database). Therefore, the user is able to use only objects in the original database. Kerberos- authentication cannot be used with contained databases.

2. Another disadvantage is that only a PARTIAL option for contained databases is available. This means that some of the dependencies still exist.

You can learn more about contained databases from below books:
a. Microsoft SQL Server 2012 Security Cookbook, by Rudi Bruchez
b. Beginning SQL Server 2012 Administration, by Robert Walters, Grant Fritchey
c. What's New In SQL Server 2012: Highlighting the best new features, by Thorsten Kansy

Below are some of the links which will help you understand contained databases in depth:
1. Contained Databases. [Link]
2. Contained Databases in SQL Server 2012. [Link]
3. Contained Databases in SQL Server 2012 – Denali. [Link]
4. Guest Post: How to use Contained Databases in SQL Server 2012. [Link]
5. SQL Server 2012 Contained Databases. [Link]
6. SQL Server 2012: Sometimes Partial Is Preferable. [Link]
7. Step-by-Step guide to Implement Contained Databases - SQL Server Denali. [Link]
8. What is Contained Database ?, Why Contained Databases feature was Required ?, What all is being saved inside a Contained Database?, What are the Benefits of using Contained Database ?, What type of Authentication Modes are supported by Contained Database?, How we create a Contained Database ?, How to create Users in Contained Databases ?, How to create a user inside a Contained Database ?, How to connect to Contained database using a user which exists in Contained Database, How The Authentication Process works in case of Contained Database ? [Link]
9. Getting started with Contained Databases in SQL 11 (“Denali”) – Part 02 – Creating contained databases & changes to ALTER DA. [Link]
10. SQL Server v.Next (Denali) : Contained Databases. [Link]
11. An example of how to create and access a partially contained database from scratch. [Link]

Features of contained databases
1. Benefits and characteristics of Contained Databases in SQL Server 2012. [Link]
2. Modified Features (Contained Database). [Link]
3. Enhance Portability (and other features) with Partially Contained Databases in SQL Server 2012. [Link]
4. The ins and outs of the SQL Server 2012 contained database. [Link]
5. What is the real benefit of contained databases? [Link]

Limitations of contained databases
1. Are there disadvantages to contained databases? [Link]
2. Limitations of Partially contained databases: [Link]
3. Limitations / Disadvantages of Contained Databases in SQL Server 2012. [Link]
4. Contained Database Limitations. [Link]
5. The ins and outs of the SQL Server 2012 contained database. [Link]

Below posts give you steps if you are thinking of migrating to a contained database:
1. Migrate to a Partially Contained Database. [Link]
2. Converting a "normal" database to a partially contained database from scratch. [Link]
3. Migrating to a Contained Database in SQL Server 2012 (Video). [Link]
4. Moving a Database: Dependencies (not directly related to contained databases, but still can be useful). [Link]