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.
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)','datetime') AS EVENT_TIME , D.VALUE('(@name)','varchar(100)') AS FIELD , D.VALUE('(value)', '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:
- Using Management Studio
- In Object Explorer, expand Databases, right-click the database you need to convert, and then click Properties.
- On the Options page, change the Containment type option to Partial
- 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 (http://blog.SQLAuthority.com)