Contained Databases in Denali

Microsoft recently announced the availability ofthe Community Technology Preview 1 (CTP1) ofthe next version of SQL Server, which is codenamed 'Denali'. You can download the same fromhttp://ld2.in/11k. Here I explain one of the new featuresof the new version which developers and administratorsneed to know.

In Microsoft SQL Server, to connect to a database you firstconnect to the instance of the server in which the database re-sides. Because the instance authenticates you, the databasedoes not. Metadata for the database is stored in the masterdatabase of the instance. In the new concept of ContainedDatabases, metadata is a part of the database and the databasehas no configuration dependencies on the instance.

The default for a database is non-contained. Fully con-tained databases are not currently available, but a database canbe partially contained.

Benefit of Contained Database

Currently a lot of application specific data like jobs and logins get stored external to the database. This causes concerns when you move the database from one instance to another.Many of these limitations will be overcome with the new concept of Contained Databases. This demonstration only scratches the surface of the potential of contained databases. Irecommend you download and install the CTP and explore the possibilities.

So, let's first create a normal database, and in the Optionstab note the new 'Containment type' option. Here you must ex-plicitly enable contained database authentication on the in-stance.sp_configure 'show advanced', 1;RECONFIGURE WITH OVERRIDE;GOsp_configure 'contained database authentication', 1;RECONFIGURE WITH OVERRIDE; GO

Repeat the steps to create a contained database as shown insteps below and how using Windows authentication we canconnect to the database.