SQL Server 2012 – New Features – Contained Databases

  • Article

A very common problem that has plagued both the DBAs and developers for a
long time is migrating databases to different SQL Server instances for
application development and testing purposes OR for various failover conditions.
Databases are not really independent units. They have many server dependent
objects like logins, SQL Server Agent jobs, linked servers, different collations
etc. A lot of these dependencies cause problems when working with temporary
objects and therefore it is always challenging to migrate databases for e.g.,
for testing and quality assurance.

SQL Server 2012 introduces a new feature called Contained Databases, which
intends to reduce and eventually eliminate SQL Server instance dependencies that
databases currently have. The intention of this feature is to make database
migrations easier with less work involved in reproducing and validating the
instance level dependencies.

SQL Server 2012 introduces a new feature called Contained Databases, which
intends to reduce and eventually eliminate SQL Server instance dependencies that
databases currently have. The intention of this feature is to make database
migrations easier with less work involved in reproducing and validating the
instance level dependencies.

Following is a list of some of the issues that are solved in the SQL Server
2012 RC0 release

  1. We can now create a user with a password at the database level – a

    “contained database user“. The user authentication is done at

    the database level and the applications just need to change their connection

    strings. This solves a very common problem of “Orphaned Users“.

    In case of a failover, users would be able to connect to the secondary server,

    without creating logins on the instance hosting the secondary.

  2. Collation conflict between the SQL Server instance and the

    database is one common problem that every DBA and developer has experienced. In

    SQL Server 2012, tempdb will automatically create the temporary objects by using

    the collation of the “Contained Database”, instead of the collation of the

    server. This works very well if you are working with one database or multiple

    databases having the same collation. The issue with queries joining
    temporary tables from multiple databases     with different collations is still

    not resolved.

  3. We can get a list of queries and objects that are not contained or
    cannot be separated from the instance     by using DMVs (sys.dm_db_uncontained_entities (Transact-SQL) and events (database_uncontained_usage_event). In other words, SQL Server

    2012 provides the information about when the migration crosses the containment

    boundary.

  4. Using the contained databases, a cloud environment can be
    simulated     with clear boundaries between the database and the SQL Server

    instance.

Given all the advantages of contained databases, the only containment
option SQL Server 2012 gives is PARTIAL - which means that it
allows the features that cross the application boundary – containment is only
observed not enforced. In the future releases, we can hope for further extension
of this feature dealing with other dependencies. Even though SQL Server 2012
does not provide fully contained databases, it is a great first step towards
database autonomy. I have had many incidents in my past jobs
where I had to migrate databases (as part of application development) to new
servers and it was not something I really enjoyed doing. I am really glad that
this feature is finally available. Though not complete but it is a step in the
right direction.

Reference Links:

https://sql-articles.com/articles/general/contained-database-sql-server-2012/