How to Migrate Existing Database to Partially Contained Database in SQL Server
SQL Server 2012 introduced a new feature namely Contained Databases. In this article we will learn the steps which a DBA needs to follow to Migrate an Existing Database to Partially Contained Database in SQL Server.
Definition of Contained Database
Contained database is a database that will store all its metadata within the database thereby not storing any configuration information within the master database of the SQL Server Instance where the Contained Database is created. A contained database is isolated from other databases which are available on the instance of SQL Server. For more information see, Contained Database Feature in SQL Server 2012 and Higher Versions.
Limitations of Contained Database in SQL Server
As per MSDN Partially contained databases do not allow any of the below mentioned features.
- Partially contained databases cannot use Replication, Change Data Capture, or Change Tracking
- Partially contained databases cannot have numbered procedures
- Schema-bound objects that depend on built-in functions with collation changes
- Binding change resulting from collation changes, including references to objects, columns, symbols, or types
Important Note:- Temporary stored procedures are currently permitted. Because temporary stored procedures breach containment, they are not expected to be supported in future versions of contained database.
How to Plan Migration of an Existing Database to a Partially Contained Database in SQL Server
DBA must first identify if there are any Containment Breaches (mentioned above) within the existing database. If there are any then, they need to be removed before migrating the existing database to a partially contained database.
Different Ways to Identifying Database Containment in SQL Server
In SQL Server there are two ways by which you can identify the containment status of any database.
- Using sys.dm_db_uncontained_entities Dynamic Management View (DMV) which shows all the potentially uncontained entities within the database.
- Using database_uncontained_usage XEVENTS which occurs when any actual uncontained entity is identified at run time.
Method One: Using sys.dm_db_uncontained_entities DMV Identifying Database Containment Breaches in SQL Server
Dynamic Management View sys.dm_db_uncontained_entities shows any entities in the database that have the potential to be uncontained, such as those that cross-the database boundary. This includes those user entities that may use objects outside the database model. However, because the containment of some entities (for example, those using dynamic SQL) cannot be determined until run time, the view may show some entities that are not actually uncontained. For more information, see sys.dm_db_uncontained_entities (Transact-SQL).
SELECT * FROM sys.dm_db_uncontained_entities GO
Method Two: Using DATABASE_UNCONTAINED_USAGE XEvent to Identifying Database Containment Breaches in SQL Server
DATABASE_UNCONTAINED_USAGE XEvent occurs whenever an uncontained entity is identified at run time. This includes entities originated in client code. This XEvent will occur only for actual uncontained entities. However, the event only occurs at run time. Therefore, any uncontained user entities you have not run will not be identified by this XEvent.
Step by Step Guide to Covert an Existing Database to Contained Database
1. Enable Contained Databases Feature at Instance Level
2. Convert an Existing Database to Partially Contained Database in SQL Server
Step One: Enable Contained Databases Feature at Instance Level
Contained databases must be enabled on the instance of SQL Server Database Engine, before contained databases can be created. The following example enables contained databases on the instance of the SQL Server Database Engine.
EXEC sp_configure 'contained database authentication', 1; GO RECONFIGURE ; GO
Step Two: How to Convert a Database to Partially Contained Database in SQL Server
DBA can convert a database to a contained database by changing the CONTAINMENT option. This can be done either by using a TSQL Command or by using SQL Server Management Studio. Both options to convert database to partially contained database in SQL Server is mentioned below for your quick reference:-
Converting a Database to Partially Contained Using Transact-SQL Command
Execute the below mentioned example TSQL script which converts a database named MyTechMantra to a partially contained database.
USE [master] GO ALTER DATABASE [MyTechMantra] SET CONTAINMENT = PARTIAL WITH NO_WAIT GO
Converting a Database to Partially Contained Using SQL Server Management Studio (SSMS)
DBA or a Developer can follow the below steps to converts a database to a partially contained database.
1. Connect to SQL Server Instance Using SSMS.
2. In Object Explorer, expand Databases, right-click the database to be converted, and then click Properties.
3. On the Options page, change the Containment type option to Partial and finally cick Ok to save the configuration change.
- How to Configure and Use Contained Database Feature in SQL Server
- Security Best Practices with Contained Databases
Last Updated On: Aug 10, 2015
Please leave your Valuable Comment or Let us know how this article helped you: