Contained Databases in DENALI

Contained Database : A Brand new interesting feature introduced by Microsoft in SQL Server Denali(2011).
let’s get straight to the point. Simple and straightforward use of contained Database(s) – Let’s say you’ve multiple applications sharing the same SQL Instance and you’ve multiple Jobs,Linked Servers, Logins for respective Databases. consider the scenario, where you are trying to migrate the database, the entire pain comes when dealing with respective Logins, Jobs,Server Objects.
With the introduction of “CONTAINED DATABASE”, you can assume that you are isolating your application(database) from rest of the applications(Databases) on this SQL Instance. So…They are independent of Instance itself in terms of Instance level dependencies!
Are they really independent in every aspect?? No….definitely not! let’s say your SQL Instance is DOWN, then of course these Database(s) will also be Down ;-p. So basically we are going to introduce some level of isolation from rest of the Databases and some level of freedom(independence) from actual SQL Server itself!

Terminology :
I’ve taken this terminology(definitions) from MSDN Library. The link is provided below FYI.
http://msdn.microsoft.com/en-us/library/ff929071(v=sql.110).aspx
Application boundary:The boundary between the application model and the instance. Application model functions are within the application boundary, and instance functions are outside the application boundary.
Contained:A user entity that lives entirely within the application boundary.
Uncontained:A user entity that crosses the application boundary.
Non-contained database:A database with containment set to NONE.
Fully contained database:A fully contained database does not allow any objects or functions that cross the application boundary. Fully contained databases are not currently available.
Partially contained database: A partially contained database is a contained database that allows features that cross the application boundary.
Contained user:There are two types of users for contained databases.

Contained database user with password (SQL Auth)
Contained database users with passwords are authenticated by the database.
Windows principals (Windows Auth)
Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database.

Note: Users based on logins in the master database can be granted access to a contained database, but that would create a dependency on the SQL Server instance, so creating users based on logins is not recommended.

So what exactly are we gaining from this new feature?? It stores the below mentioned thingswhich can make a DBA life much more painless…:)

It stores all the Cool info like Tables,Procs,Functions,Constraints,Views,Schemas..etc as any other regular Database. Besides this, It also stores Instance level objects like Logins, Application Agent Jobs, Error Msgs, Linked Servers etc. Hurrrray….we can escape from dealing with Orphaned Users, SID Mappings blabla…:) Tadaaaaaaaaaaaaa!!

Let’s start a Quick DEMO:

Step 1: By default “Contained Database Authentication” property is set to ‘0’-False as shown below.

We’ve to set this Option to TRUE from GUI or use the below code from T-SQL:

/*Enabled Advanced options*/

sp_configure ‘show advanced’, 1;

RECONFIGURE WITH OVERRIDE;

/*Enabled Database Containment */

sp_configure ‘contained database authentication’, 1;

RECONFIGURE WITH OVERRIDE;

Step 2: Now we can create a Contained Database as shown in below Screenshot.

Change the Containment type to “PARTIAL” from “NONE”. Default for any Database is NONE.

From T-SQL:

CREATE DATABASE [Contained_tst] CONTAINMENT = PARTIAL ON PRIMARY ( NAME = N’Contained_tst’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\Contained_tst.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N’Contained_tst_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\Contained_tst_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)GO

Step3: Now we’ve created a contained Database. Let’s play a bit with creating a test user and connecting via the new test user.

From TSQL:

USE [Contained_tst]GOCREATE USER [contained_usr] WITH PASSWORD=N’test’, DEFAULT_SCHEMA=[dbo]GO

Now let’s try connecting to the SQL Instance using the “contained_usr”.

Note: it might fail if you try to connect to default Master database because this particular login doesn’t exist at the Server level in the Master Database.Once connected through SSMS, It looks like the below Screenshot.

I’m not seeing any other Databases, Agent, Server objects…Literally I’m not able to see anything beyond this particular database. waiting for testing this Brand new thing in real environment!One More GOTCHA :“ALTER DATABASE” Syntax will fail for Contained Databases. We’ve to use “ALTER DATABASE CURRENT“!!Conclusion: IMHO this is a great new feature which we all(at least I was) were waiting since ages! Even though it is well publicized as a Contained Database is instance or server independent, has no external dependencies and has self contained mechanism of authenticating users, I would say no! It’s actually not Server independent. A contained Database can’t escape anything which impacts the Physical Server(Instance SHUT DOWN, SERVER REBOOTS etc).Hope this helps to give a Kick Start for exploring this new added feature of DENALI.