What is a contained databases in SQL Server 2012 – Denali?
A contained database is a database which includes all the required settings, metadata and operates in isolation from the SQL Server Database Engine. In other words it has no functional dependency on SQL Server Instance be it Login, collation setting or metadata info.
The most popular feature being, user connecting to the database without having a Login at SQL Server Instance level; means there is no login registered for this user in Master DB.
It’s very easy to migrate\move these databases to another SQL Instance, since there is no dependency at the Instance level. This also makes it easy and practical for DB Owner to manage all the configuration settings independently without any intervention of SysAdmin.
In SQL Server 2012 Release Candidate 0 (RC 0) Microsoft introduced 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.
How a Contained DB is different from Partially Contained DB?
A fully contained database does not allow any objects or functions that cross the application boundary whereas a partially contained database is a contained database that allows features that cross the application boundary.
Inside of the application boundary is the application model, where the applications are developed and managed. Examples of entities located inside of the application model include, system tables like sys.tables, contained database users with passwords, and user tables in the current database referenced by a two-part name. Outside of the application boundary is the management model, which pertains to instance-level functions and management. Examples of entities located outside of the application boundary include, system tables like sys.endpoints, users mapped to logins, and user tables in another database referenced by a three-part-name.
SQL Server includes the ability to determine when the containment boundary is crossed. By default, all Microsoft SQL Server 2012 Release Candidate 0 (RC 0) databases have a containment set to NONE. Fully contained databases are not available in Microsoft SQL Server 2012 Release Candidate 0 (RC 0).
Types of Users in Partially Contained Databases
There are two types of users for contained databases.
- Contained database user with password: Contained database users with passwords are authenticated by the database.
- Windows principals: Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database.
Users based on logins in the master database can be granted access to a contained database, but that would create a dependency on the SQL Server instance, so Microsoft doesn’t recommend doing this. Moreover, there is one more problem associated to it, will blog that shortly.
Benefits of using Partially Contained Databases
The major problem in any DB movement is application related data being left behind. This happens since application related data is being stored out of App DB e.g., Logins which is generally left behind while DB movement. Then you must identify and move the data which was left behind to correct application working which can be time consuming and difficult.
The partially contained DB keeps the contained users within Database and doesn’t have any collation dependency on the SQL instance which makes it easy to Move the DB.
Because Contained Database rather Partial Contained Database contains its own users without any dependency on Master DB for related Logins and don’t even have any dependency on Collation level setting of the SQL Server Engine, the application owner (DB Owner) can very well administer the DB according to the needs without any intervention of SysAdmin.
Benefit of Contained Database Users with AlwaysOn
Heard of AlwaysON? You’ll soon, this is a great High Availability feature and also much awaited feature in SQL Server. Will put a blog related to this shortly. In AlwaysON your DB can failover to another SQL Instance just like SQL Instance in Windows Cluster, and while doing so there will always be a problem to sync-up the logins so that the application can connect on the secondary server. But if you are using Partially Contained Databases your Applications can connect to the other server without any problem and workaround since the users are always there in the DB itself. I just love it.
Hope you learned something new, do leave comments.