Contained Databases – Pros and Cons

By Ginger Daniel | Beginner

Oct 23

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?

  1. Authentication – normally logins are managed at the server level (as logins) and at the database level (as users), so they are defined in two locations.  The mapping that connects logins to users gets lost when a database is moved to a different server, which results in orphaned users.  When moving a database, the logins must be created on the new server, and must match exactly…otherwise this mapping will be broken.Orphaned users can also cause HA/DR to be compromised in this situation, because of trying to manage logins and users separately. Logins have to be synchronized against secondary or failover servers to avoid orphaned users.With contained databases, you won’t run into the issue of orphaned users. Contained database users do not have to authenticate with the instance. They don’t have to have a corresponding server login. The contained database user has no dependency on the server. They will connect directly to the database.

    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.

  2. Use with AlwaysOn – in case of failover, contained database users will be able to connect to the secondary without creating logins on the instance hosting the secondary.
  3. Development – because developers don’t always know on which instance their database will be deployed, creating a contained database can limit the environmental effects on the database, and can lessen the work and difficulties for the developer.
  4. Administration – database owners maintain the database settings in the contained database, instead of master database, so it avoids users having to be given sysadmin permissions.
  5. Collation – temp objects are created with the collation of contained database in the case where the server collation is different from database.  There is no need to COLLATE anymore. There may be some problems if a session accesses both contained and non-contained databases. For information about collation when crossing between contained and uncontained databases, click here https://msdn.microsoft.com/en-us/library/ff929080%28v=sql.110%29.aspx 

What are the Issues or Limitations?

  1. Features you can’t use :
    • Replication
    • Change Data Capture
    • Change Tracking
    • Numbered Procedures
  2. Connection Strings – must specify the contained database in the connection string – you cannot rely on the login’s default database to establish the connection.
  3. Cross-database queries – applications are not able to cross-database query.  Even if the same user/password has been created in both databases.
  4. Passwords – creating a contained database user on a system that has a password policy is difficult, if the user has been created previously without a password on another system that has no password policy.  CREATE USER doesn’t support bypassing a server’s password policy
  5. Collation – If your code uses explicit collate or DATABASE_DEFAULT, you might need to change your code to use CATALOG_DEFAULT instead.  Also, you can’t use:
    • Schema-bound objects dependent on built-in functions with collation changes
    • Binding changes resulting from collation changes
    • Non-contained databases use the tempdb database’s collation settings by default, whereas contained databases use their own collation settings by default when interacting with the tempdb database. It’s important to note that although contained databases can interact with the tempdb database to create temporary tables, the use of explicitly named constraints defined against those temporary tables isn’t allowed once databases become contained.

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’.

Blog_20151023_1

–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:

Blog_20151023_2

Type in the name of your contained database in the ‘Connect to database’ box, and hit Connect:

Blog_20151023_3

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.

Blog_20151023_4

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!

About the Author

  • YOGI says:

    IN REAL TIME WHICH TYPE IS PREFERS ? IF BOTH MEANS ANY SCENARIOS OF RELATED? PLZ ANSWER

  • Venkataraman R says:

    thanks very useful post.

  • ali says:

    i see another disadvantage that is the domain users also u can not migrate is there another disadvantages?

  • >