Advertisment

Contained Databases in Denali

author-image
PCQ Bureau
New Update

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.

Advertisment

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

Advertisment

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

Advertisment

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

Advertisment