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
Database Movement
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.
Application Administration
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.
Regards
Sarabpreet Anand
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
Hi Sarab…Could you please elaborate how a user can login to the database without having a login. I am bit confused on contained DB part.
Also, can you explain me two-part name & three-part name that you’ve mentioned in the application model & mangement model?
Hi,
For your first Question: ” Hi Sarab…Could you please elaborate how a user can login to the database without having a login. I am bit confused on contained DB part.”
Don’t worry about this, i am writing another article on “How to Create and Manage Contained Databases” and i’ll explain everything regarding these users in the article.
I am busy with lots of other stuff today….will try to publish that tommorow.
Your second question: “Also, can you explain me two-part name & three-part name that you’ve mentioned in the application model & mangement model?”
Let’s say you are connected to Sales Database and want to get some data out of Employee database, how will you get that data?
you’ve two options:
1. Connect to Employee DB …. query for the data you need & then connect back to Sales Database to continue working on whatever you were doing.
2. Query directly to Employee database by using three part Name i.e., select * from Employee.dbo.PersonalDetails
Note: Employee is database Name, dbo is Schema Name and PersonalDetails is Table\view Name.
The same way you can connect to some other Server or SQL Instance also to get some data by providing a four part name:
ServerName.DatabaseName.SchemaName.TableName
in some cases you may skip giving schemaName if the schema of the object is either in DBO schema or your default schema.
Hope this helped. Smile
Thanks
Sarab
Thanks. That was a wonderful explanation. Thanks for the elaborative answer!
Hi,
As promised, I’ve submitted the article on Contained Database Implementation & Users Authentication. Here is the link:
https://www.sqlservergeeks.com/articles/sql-server-bi/82/step-by-step-guide-to-implement-contained-databases-sql-server-denali
Thanks,
Sarabpreet Singh
A picture explaining everything would have explained everything better