SQL SERVER – How to Migrate Existing Database to Contained Databases

Most of the queries that land into my inbox are based on something I have already written or something people want to explore more on. When it comes to learning and exploring the skills, there is no better way compared to people asking me some tough questions. This blog is an extension to couple of blogs I have already published on a concept called as Contained databases.

SQL SERVER – ‘Denali’ – A Simple Example of Contained Databases

Beginning Contained Databases – Notes from the Field #037

The question asked was, how can we convert an existing database to contained database. What is the process and how do I need to plan? I thought this blog will be a direction in that journey.

There are some initial considerations to determine if there are any existing containment breaches in the existing database. SQL Server makes this easy by providing a DMV and a XEvent to highlight the containment breaches for the database. The complete steps to convert a database to a contained database are as follows:

Identify database containment breaches

There are two tools to help identify the containment status of your database. The sys.dm_db_uncontained_entities (Transact-SQL) is a view that shows all the potentially uncontained entities in your database. The database_uncontained_usage Xevent fires when any actual uncontained entity is identified at run time. Once the containment breaches have been identified and resolved, you are ready to go to the next step as part of migration.

View : sys.dm_db_uncontained_entities

This view shows any entities in your database that have the potential to be uncontained. This includes those user entities that may use objects outside the application 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.

SELECT * FROM sys.dm_db_uncontained_entities

XEvent : database_uncontained_usage

This Xevent fires whenever an uncontained entity is identified at run time, including entities originating in client code. Since this XEvent will fire for actual uncontained entities at run time, it will not identify any uncontained user entities that you have not run.

----------------------------------------------------------
-- Setup the Xevent to log into a ring buffer as follows.
--
-- Create the event session
CREATE EVENT SESSION track_uncontained_usage ON SERVER
ADD EVENT sqlserver.database_uncontained_usage
(
    ACTION(
        sqlserver.session_id,
        sqlserver.session_nt_username,
        sqlserver.client_app_name,
        sqlserver.client_pid,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.sql_text
    )
    WHERE
        sqlserver.database_name='MyDatabase'
)
ADD TARGET package0.ring_buffer
GO
	
-- Start the event just created
ALTER EVENT SESSION track_uncontained_usage ON SERVER STATE=START
GO
		
-- Dump events logged so far
DECLARE @X XML
SELECT @X=CAST(XET.TARGET_DATA AS XML) 
FROM SYS.DM_XE_SESSION_TARGETS XET
    JOIN SYS.DM_XE_SESSIONS XE
    ON (XE.ADDRESS = XET.EVENT_SESSION_ADDRESS)
WHERE XE.NAME = 'track_uncontained_usage'
SELECT EVENTS.VALUE('(@timestamp)[1]','datetime') AS EVENT_TIME
    , D.VALUE('(@name)[1]','varchar(100)') AS FIELD
    , D.VALUE('(value)[1]', 'varchar(max)') AS VALUE
FROM (SELECT @X AS RINGBUF ) B
CROSS APPLY B.RINGBUF.NODES('//RingBufferTarget/event') AS RB(EVENTS) 
CROSS APPLY RB.EVENTS.NODES('data') AS EV(D)

Convert the database to a contained database

The steps here are super simple as follows:

1. Using Management Studio
   1. In Object Explorer, expand Databases, right-click the database you need to convert, and then click Properties.
   2. On the Options page, change the Containment type option to Partial
   3. Click
2. Using T-SQL, we take advantage of the new CONTAINMENT option of the ALTER DATABASE command

USE [master]
GO
ALTER DATABASE [Accounting] SET CONTAINMENT = PARTIAL
GO

Determine if the containment option has been taken

The sys.databases view has two columns, namely containment and containment_desc, which can be used to determine the containment state of the databases. The following T-SQL will select all databases with containment enabled:

SELECT * FROM sys.databases WHERE containment > 0

As I conclude this blog, I hope this will give a rough process for you to work with as you would like to take an existing database to contained database in your environments.

On a side note, would love to know if any of you is using contained databases in your environments? What are some of the usecase for using these? Let me know via your comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)