Contained databases in SQL server 2012

New Administration features in SQL server 2012

Contained databases

Contained databases are a new concept in SQL Server 2012. Currently if you want to move database one server to another server or even one instance to another, most cases the applications that are associated with it cannot use it immediately, even if that database is a secondary database or mirror for the principal.

You have to create Logins, scheduled jobs and endpoints and are sometimes not identified until a problem occurs.

In simple terms it is a database that is isolated from other databases, and isolated from the instance of SQL Server that is hosting the database.

There are four ways that SQL Server 2012 helps to isolate databases from the instance:

• Much of the metadata that describes a database is maintained in the database and not in the master database
• All metadata is defined using the same collation
• User authentication can be performed by the database, reducing the database’s dependency on the logins of the instance of SQL Server
• The SQL Server environment reports, DMVs and xEvents can act upon containment information.

Why do we need Contained Databases?

SQL Server security has always managed at two levels:

Logins are managed at Server level, and users are managed at Database level. This means permissions for SQL Server have to be defined in two or more locations, and this can cause confusion.

Manage logins and users separately can cause problems when it comes to maintaining high availability and disaster recovery solutions and the need to regularly synchronise logins against failover and secondary servers; to avoid problems such as orphaned users.

So with the introduction of containment and the concept of boundaries in SQL Server 2012; a database can become free of external dependencies, server level metadata, settings and security logins.

Microsoft has provided a DMV that lists any objects that do not comply with containment rules for the current database.

SELECT object_name (major_id), *

FROM sys.dm_db_uncontained_entities

Making an existing database partially contained

Step1.  Make a SQL Server instance level change to allow a contained database to authenticate with the database engine.

sp_configure 'contained database authentication', 1

reconfigure

Step2. If you intend to use the GUI tools to change the containment type, you will need to kill all connections to the database that you want to make partially contained. Note that this is only necessary when taking a database from non-contained to partially contained; setting it back to non-contained does not require this step, nor does it apply if you use T-SQL to change the containment mode to partial.

Step3.  Set the database to partially contained mode.

ALTER DATABASE test

SET containment = PARTIAL

For revert

           

                        SET containment = NONE

It can be performed through the GUI tools.

Migrating your logins to a contained database

After existing database has been converted to a contained database, you are left with the task of migrating your logins. These logins can be easily migrated to a partially contained database level using the Below T-SQL code:

DECLARE @UserName sysname

DECLARE csrUsers cursor

FOR

SELECT dbprin.name

FROM sys.database_principals AS dbprin

INNER JOIN

sys.server_principals AS svrprin

ON dbprin.sid = svrprin.sid

WHERE dbprin.authentication_type = 1

AND svrprin.is_disabled = 0

OPEN csrUsers

FETCH NEXT

FROM csrUsers

INTO @UserName

WHILE @@fetch_status = 0

BEGIN

exec sp_migrate_user_to_contained

@username = @UserName,

@rename = N'keep_name',

@disablelogin = N'disable_login'

-- disable the server level login

FETCH NEXT

FROM csrUsers

INTO @UserName

END

CLOSE csrUsers

DEALLOCATE csrUsers