Contained databases in SQL Server 2012 overview

SQL Server 2012 features a lot of new improvements in all of its aspects. Microsoft boasts their cloud readiness in all the new products – Windows Server, System Center and SQL Server as well. This post will describe one of the Azure-like features – contained databases.

A contained database, as stated in Books Online, is a database which isolated from other database and SQL Server instance. This means that part of the metadata which is required for database operation is included in it. In SQL Server 2012 two containment model are introduced:

  • full containment – all the metadata exists in a database and there is no dependence on SQL Server instance
  • partial containment – some of the metadata exists in a database and some on the instance-level

At this moment SQL Server 2012 supports only partial containment of databases which implies also that uncontained entities are only identified, where in full mode containment could be enforced (see Aaron Bertrand’s post about containment). There are two ways of identifying uncontained entities in a database – sys.dm_db_uncontained_entities DMV and database_uncontained_usage Extended Event. Beware – both show only those entities which are identified at run time.

The term which is closely related to containment level is database boundary, which separates elements that exist in a database (called database model) from those on the instance level (management model). We can say that contained elements exist within a database whereas uncontained elements cross database boundary. Of course, being uncontained is transitive – if an entity references or depends on another, which is uncontained, it is uncontained as well.

There are several benefits of contained databases:

  • probably the most straightfoward is easy movement of contained databases – since the metadata is stored inside a database, it is easier to move it around. Till now, you could do backup\restore, import\export or detach\copy\attach, but none of these methods takes care of login mapping, server-level security, linked servers, Database Mail settings or SQL Server Agent jobs;
  • following from the previous is the usefulness for database mirroring or AlwaysOn failover – if a database is contained, all you have to care for after failover is to point your connection strings to new location because the database is already there with all the information required to operate
  • database development – developer might not have all the details required to properly set up a database at initial stage and may focus at the job at hand instead of concerning of all possible environmental impacts
  • simplified database administration – in certain cases it is required for a database owner to have elevated permissions to server-level roles; this may be mitigated with database containment

Of course, there is always a downside – since a database is supposed to be contained, collation dependence is also to be redesigned. For a non-contained database there are three collations to be considered:

  • DATABASE_DEFAULT – for user data (default)
  • tempdb collation – for temporary data (default)
  • instance default collation – for variables, cursor names and goto labels

In a contained database we are not supposed to rely upon instance information and tempdb, hence a CATALOG_DEFAULT collation which is used by system metadata and temporary objects in a database (by default equal to Latin1_General_100_CI_AS_WS_KS_SC). There are only two collations to be considered:

  • DATABASE_DEFAULT – for user data and temporary data (default)
  • CATALOG_DEFAULT – for metadata, temporary metadata, variables, cursor names and goto labels.

See more details in Books Online article on contained database collations.

Lastly, there are several limitations of contained databases which follow from separation from system databases and collation changes (taken from Books Online):

  • replication, change tracking and change data capture is not permitted
  • numbered procedures are not allowed
  • schema-bound objects that depend on built-in functions with collation changes
  • binding change resulting from collation changes
  • temporary stored procedures are currently permitted but they are marked for deprecation in future versions of contained databases

Wrapping all up – database containment is an interesting option and it surely will be a hot subject in future versions of SQL Server. It will enhance database portability but there will be some points to consider implemeting them (see collation and supported features). I for certain am looking forward to working with it.

[Story] Database collation change issues

So another story coming… This one distrubs chronological order, as I have already posted a story about replication, which ultimately was caused by the actions depicted here.

A little background – there is a database which is about 10 years old, which had a refresh just recently. During development it turned out that in order to keep reporting working a collation change is required, mostly due to data interchange via linked servers. It was easier than to work with collation on a object level, instead of trying to identify all the queries and modify them to align collation. As you might expect, a simple default database collation change was not possible due to check constraints being dependent on text columns. I had to drop and recreate those check constraints after collation change, but then it also appeared to me that while applying database_default collation on columns in some tables it couldn’t be completed simply due to indexes on those columns. So those indexes had to be dropped prior to table alteration just to be recreated after that. A lot of scripting was involved and whole operation took about 8 hours to complete.

One more recollection – since it was in a virtual machine environment, low disk space occured in a meantime, so if you come across such massive collation change, keep in mind that a lot of disk space is required for logging. In this case I required about 4 times of database size for logging, not to mention backups before and after.

Setting a new collation for SQL Server instance

Collation can be a little bit tricky sometimes. Changing it is not a problem (theoretically) if you want to do it on a column, table or database level – you have to issue respectful ALTER statement and that’s it.

Another thing is changing default server collation. You set it during installation and if you want to change it, you have to rebuild the system databases. Here’s the procedure:

  1. Log on to administrative account.
  2. Run command prompt (as administrator, if you’re on Windows Vista or later).
  3. Go to folder with your SQL Server installation source.
  4. Run the following statement:
    setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=SA_Password SQLCOLLATION=Collation_Name

And that’s it. Tested on SQL Server 2005 and SQL Server 2008 R2. Important thing is that it preserves service pack version (for example you don’t have to reapply SP3 for SQL Server 2005).