This is the third in a multipart series on the exciting new features of SQL Server 2012. Currently AIS is assisting a performing arts center with an upgrade to SQL Server 2012. During the research for this project I’ve had a chance to deploy many of these new features. These posts highlight the best of what SQL Server 2012 has to offer.

We’ve already discussed AlwaysOn High Availability, and integrated SSRS. Today I want to talk about the new contained database feature of SQL Server 2012. Contained databases make the life of the DBA and developer much easier. They also streamline the deployment of high availability scenarios.

So what are some of the new offerings of this feature?

• Overall Portability: Databases can be moved between instances without issue.
• Contained Security: Databases are independent of the instance level logins.
• User Isolation: Users do not require instance level access to manage databases.
• High Availability: No need to transfer logins to replica instances of your data.
• Initial Development: The developer doesn’t have to worry about the eventual host.

With previous versions of SQL, database portability was a huge concern. This was especially true if you inherited the environment from another DBA. At the time of migration you were never 100% sure what instance level dependencies were present. It seemed like no matter how many scenarios were tested, you always missed something.

In response to this very relevant constraint, Microsoft included a new feature in SQL Server 2012: contained databases. With this feature, not only can security be handled at the database level but metadata about the database can also be contained. This allows for a new portability that has never been possible before.

The most exciting part of this new feature is that logins can now be contained in the database. In the past the DBA would have to migrate all logins from one instance to another. This would involve a script or some other service level replication before the dependent application could use that new instance. With this new feature the DBA simply moves the database and points the application to it. Done!

From an instance level security perspective, there are also notable advantages. Now the DBA can give a user access to manage just their database without giving them permissions to the entire instance. This allows for a more granular security model than previous versions.

For those customers that are deploying AlwaysOn or other high availability solutions, this feature helps too. There is no longer a need for initial or ongoing synchronization of logins between fail over instances. Now if a failure occurs, the data has already been exchanged between availability replicas and will contain the required information. This is a huge improvement — especially since up to four replicas of your data can now exist.

The developer can benefit from contained databases as well. The developer no longer has to be concerned about the instance where their database will reside. Now all logins and other metadata their application requires will be maintained no matter what instance the database is assigned to.

All of these new offerings of contained databases make it a powerful new feature — one with the ability to assist the DBA, developer, and even the end user. Once again, this makes for a compelling argument to upgrade to SQL Server 2012.

How does this new feature solve problems in your organization? Feel free to comment!