Beginning with SQL Server 2012 and beyond, Microsoft has developed a new feature in all editions of SQL Server that allows us to create databases independent of the instance hosting that database. In this article I will cover some of the features, pros and cons, and how to create a contained database.
What is it?
A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. Contained databases have no dependencies on server-level metadata and settings. And currently, there are only two options available:
NONE – no boundaries defined or visible – SQL versions 2012 and later have default set to NONE
PARTIAL – can define boundaries between databases and server. Contained databases can allow some features to cross the database boundary. SQL server can determine when containment boundary has been crossed.
Boundaries can be defined with PARTIAL contained databases. Boundaries are the borders that define where the metadata and settings for a database ends…and where the metadata and settings for the server begin. Boundaries separate settings, capabilities, features, and data. A contained element exists entirely within the database boundary. Uncontained elements cross the database boundary.
To identify these elements, you can query the sys.dm_db_uncontained_entities DMV which can help you identify uncontained entities that might prevent portability (what parts of your database won’t be portable (contained)). By determining the containment status of the elements of your database, you can determine what objects or features you need to replace or alter to support containment.
Why Use a Contained Database?
The main purpose of a contained database is to enable isolation and portability. To allow for a seamless move of your database from one server to another. To create a database that stores user information and other metadata inside the database, instead of an instance-level login which is stored in the master database. So there is no need to create logins on the instance level.
What are the Benefits?
For applications to connect, they have to specify the database in the application connection string, and include the parameter called ‘initial catalog’ in order to connect directly to the database.
What are the Issues or Limitations?
The items in the list above are not necessary problems, but issues you should be aware of before creating and using a contained database.
How to Create a Contained Database
–To create or enable a contained database, you must first enable this feature on the SQL instance:
SP_CONFIGURE ‘contained database authentication’, 1
GO
RECONFIGURE
GO
–Create your database:
USE master
Go
CREATE DATABASE ContainDB
GO
–Change database type to contained:
USE master
GO
ALTER DATABASE ContainDB
SET CONTAINMENT = PARTIAL
You can do this either through TSQL, or you can open the Properties of the database, go to the Options page, and change the Containment Type to ‘Partial’.
–create a contained database user
CREATE USER [CDB_User]
WITH PASSWORD=‘password’,
DEFAULT_SCHEMA=[dbo]
GO
–give the newly created user permissions to access the database:
EXEC sp_addrolemember ‘db_owner’, ‘CDB_User’
GO
If you prefer to grant memberships in roles other than db_owner, substitute the appropriate role(s) as shown:
EXEC sp_addrolemember ‘db_datareader’, ‘CDB_User’
GO
EXEC sp_addrolemember ‘db_datawriter’, ‘CDB_User’
GO
Log into the Contained Database
In order to log into the contained database with the login created above, open SSMS and change authentication to SQL Server Authentication. Type in the Login and Password, and click the Options>> button:
Type in the name of your contained database in the ‘Connect to database’ box, and hit Connect:
You will then be connected to only the contained database. You should not be able to see any of the other databases or instance features.
Conclusion
Contained databases make it easier to move databases between systems. Some complications related to non-contained databases can be resolved by using a partially contained database. They are fully portable, which makes it easier for a DBA to move databases between instances without having to deal with issues like orphaned users.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
IN REAL TIME WHICH TYPE IS PREFERS ? IF BOTH MEANS ANY SCENARIOS OF RELATED? PLZ ANSWER
thanks very useful post.
i see another disadvantage that is the domain users also u can not migrate is there another disadvantages?