Let's explore the concept of 'Contained Databases' which is a new feature in the upcoming MS SQL Server 2011,code named 'Denali'
BANGALORE, INDIA: Microsoft recently announced the availability ofthe Community Technology Preview 1 (CTP1) of the next version of SQL Server, which is codenamed 'Denali'.
You can download the same from http://ld2.in/11k. Here I explain one of the new features of the new version which developers and administrators need to know.
In Microsoft SQL Server, to connect to a database you first connect to the instance of the server in which the database resides. Because the instance authenticates you, the database does not. Metadata for the database is stored in the master database of the instance. In the new concept of Contained Databases, meta data is a part of the database and the database has 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. I recommend 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.