SQL Server 2012 (“Denali”): Contained Databases
A problem that has plagued SQL Server for a long time is that a database is not very portable. Sure, you can backup or detach a database and restore or attach it elsewhere, but when you do that, you are missing a lot of the pieces that make that database a part of an application, and a lot of those pieces are really considered administration pieces as opposed to application pieces. You don’t get any synchronization of “outside of the database” items such as security, roles, linked servers, CLR, database mail, service broker objects, replication, and SQL Server Agent jobs.
In SQL Server 2012, some of these issues are being addressed with a feature called “contained databases.” A contained database is a concept in which a database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed. Users connect to the database without authenticating a login at the Database Engine level. Isolating the database from the Database Engine makes it possible to easily move the database to another instance of SQL Server. Including all the database settings in the database enables database owners to manage all the configuration settings for the database.
SQL Server 2012 Release Candidate 0 (RC 0) includes a first step toward contained databases, introducing partially contained databases (also known as Partial-CDB). Partially Contained Databases provide some isolation from the instance of SQL Server but do not yet provide full containment.
In SQL Server 2012, the first iteration of the contained databases feature will provide the following solutions:
- You can create a database-specific user without a SQL Server login (and you can create multiple such users with the same name for different databases). A user at the database level is called a “contained database user“. The user authentication is done at the database level and the applications just need to change their connection strings. This solves a very common problem of “Orphaned Users“
- You can have *some* compatible use of tempdb with databases of different collations, since #temp tables will be created in the collation of the calling database context. In SQL Server 2012, tempdb will automatically create the temporary objects by using the collation of the “Contained Database”, instead of the collation of the server
- You can use a DMV to show most objects or code you have that will threaten containment
Note in a partially contained database you cannot use replication, change data capture, or change tracking.
Expect Microsoft to move toward have true fully contained databases in future versions of SQL Server.
More info:
Contained Databases in SQL Server 2012
Step-by-Step guide to Implement Contained Databases
SQL Server v.Next (Denali) : Contained Databases
CONTAINED DATABASE – SQL SERVER 2012
Video SQL Server Code Named “Denali” Contained Database Authentication Demo
What is Contained Database in SQL Server
SQL Server 2012 – New Features – Contained Databases
SQL Server 2012: Sometimes Partial Is Preferable
Surely the issue is more that the requirements for using databases in environments that are now very different from when they were first designed by Sybase i.e. cloud environments. This is more so than that sql server has been “plagued” with the problem you describe. That original model has served SQL Server very well and the time has come to decouple the database from server for modern requirements.
Also, I realise you were not emphasising this but the capability to create a database-specific user without a SQL Server login first became possible in SQL Server 2005. The deliberate and explicit creation of a database user not mapped to a SQL Login (which is the definition of an orphaned user), made it possible since that time to solve the problem of how to create a low-privilege user account that could be impersonated as and when access to a secured resource in the database was needed. My point is that the capability for creating this kind of user has been around for some time.
Many thanks for the new information – Tony S.
Great feedback Tony, thanks!
why do I want to comment here? a much more interesting place to comment about the article would be the website I read it on, which was sqlservercentral.com. Why do people insist on doing this?
Good question – I prefer to have comments posted on my site so that all the comments are done in one place instead of comments being made on different sites for the same posting. It seems readers would benefit by having the comments in one place, but not a big deal.
er.. the link from SQLServerCentral redirect the commenter to this site!
How do you set the file locations with a contained database? If I want my database and logs to be on different drives (best practice) I need to be able to set file locations for both, but these settings would seem to be very server specific and thus would have to be re-set for the new server if the database was moved.