Or… we could talk some more

Posted on by LSN Leave a comment

This is a followup note to my cautionary note on
thinking that SQL Server 2012 partial containment is going to give you a free lunch.

As a quick summary: people are falling over themselves telling you how great it is that you can now move a database to another server with a different default collation easily, by using partial containment. 

I indicated that, while there were very good reasons to make your new server use different collations than the old default, it would be better to bite the bullet when moving a database to convert it properly to the new collation, rather than taking the easy way out with partial containment.

I further indicated at least one good use case for using partial containment: if you are regularly copying a database from one server to another, using an automated process, it may not be possible to have your automated process “bite the bullet” and transparently do all that conversion work on a regular basis.

Unless you can convert both servers to the new collation, partial containment looks like a beautiful thing… for those particular database copies.  (For all other databases you’re moving to the new server, you still shouldn’t take the easy way out.)

OK so far?  Not so fast…

I tried to follow this strategy and ran into a brick wall.

Partial containment is just that, partial.  While you’ve resolved a potential collation issue if you’re joining with temp tables by using it, if your database code does one of several other things, you’re still screwed. 

SQL Server won’t let you set the database containment level to partial if it gets the idea that doing so will de-stabilize your code. 

I’m pretty sure, although not 100% sure, that having dynamic SQL in your code may cause SQL Server to get this idea. 

But I’m 100% sure that joining with system metadata — which  you can think of as similar to joining with temp tables, but isn’t covered by “partial” containment — gives SQL Server the idea that it wouldn’t be safe to apply partial containment on a database.

Here’s a very quick example

  1. Create a test database

    create database [db1];

  2. Create a table with at least one string column

    create table [db1].[dbo].[test](onefield char(10));

  3. Create some code — a view or stored procedure — in this database that joins the table with some system metadata.  Don’t use the new CATALOG_DEFAULT collation clause on the join; remember, this is for code that was written before SQL 2012, and you wouldn’t have had a chance to use it when the code was written.

    create view [dbo].[testView] as
       select     * from sys.columns cols ;
       left join test on cols.name = test.onefield

  4. Now try to set containment on this database:

    alter     database [db1] set containment = partial;

  5. … See that?  Your error will be something like this:

    Msg 468, Level 16, State 9, Procedure testView, Line 2
    Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “Latin1_General_100_CI_AS_KS_WS_SC” in the equal to operation.
    [… more here…]
    ALTER DATABASE statement failed. The containment option of the database ‘db1’ could not be altered because compilation errors were encountered during validation of SQL modules. See previous errors.
    [… more here…]

Back to the drawing board

While all my trivial tests with partial containment went pretty well, it’s now clear to me that many, if not most, non-trivial databases could have a harder time taking advantage of the feature.

I know, I know.  Some of you are going to say the above example is contrived.  Believe me, it’s not.  There are plenty of good reasons to try to extend system metadata using tables of  your own design in a database.

You’re also going to say “I don’t have metadata joins like that and I really need the user piece of partial containment”. And, certainly, yes, the ability to ensure that SQL identities stay intact when copying a database from one server to another is one of the advantages of partial containment, right up there with having your temp tables follow the collation of the database instead of the collation of the server.

But my own automated “copy” procedures already take care of the sql identity problem — and I’m sure yours could do that too. 

If anybody is interested, I’ll write a post on how I do take care of sql identities when moving databases between servers, although I don’t think it’s terribly slick or difficult.  It certainly doesn’t require SQL 2012 to work.

Meanwhile, I’m going to steer clear of partial containment for the next little while.