Category: SQL Server development
SQL Server versions: 2005
Level: Advanced
SQL Server 2005 introduced support for .Net in SQL Server.
While this is a very powerful feature, it does come with a high cost.
The CLR support allows writing stored procedures, functions,
triggers using programming languages like C#. It allows creating complicated
user defined types, and so forth. But while these new constructs can improve
query performance, they can make the lives of developers rather difficult. The
problem is with updating the new CLR objects.
SQL Server 2005 stores the CLR assemblies in the database
itself. Therefore stored procedures and data types that depend on a CLR
assembly do not depend on anything else but the database, and are persisted
completely in a database backup. Restoring such a backup on a different server
will make the CLR objects available in the restored database.
But, like stored procedures need to be changed sometimes, CLR
object code is evolving too. This means that the assembly containing CLR
objects needs to be updated in a database. TSQL contains an “ALTER ASSEMBLY”
command to replace a CLR assembly with a new one. Visual Studio 2005 deploys
CLR objects exactly this way. However this statement has some restrictions. One
of the most important is that method signatures must remain the same. If this
is not the case, and a single method signature has changed, the ALTER ASSEMBLY
statement will fail. So how can the assembly be upgraded in this case? Simple.
One needs to drop all the CLR objects (stored procedures, functions, DDL and
DML triggers), drop the assembly, create the assembly using the new DLL, and
recreate all the CLR objects. Sounds tedious, but this is still not the end of
the story. CLR assemblies may contain user defined types. Rebuilding (i.e.
dropping and creating) an assembly requires that such user defined types are
dropped before the assembly is dropped. But this is not possible if there are
tables that use the user defined data type.
So, would dropping the tables that use the CLR user defined
types, then dropping the user defined type together with all the relevant CLR
stored procedures and other objects, then the assembly be the right way to
start an assembly upgrade? Well, if the data in our tables is not important,
then the answer is yes. This is rarely the case though.
What SQL Compare 5.x does is that it first analyzes the CLR
assemblies in the two databases that are compared. It intelligently decides
whether a simple ALTER ASSEMBLY statement can be used to modify the assembly,
or whether the assembly needs to be rebuilt. In the latter case SQL Compare unbinds
the data from tables that depend on a CLR user defined type by migrating the
data into a set of temporary tables. These temporary tables use the string
representation of the user defined type. Having done this all the relevant CLR
objects are dropped, and finally the CLR assembly can be rebuilt. Following
this all the above CLR objects are rebuilt based on the new CLR assembly, and
the data in the temporary tables is converted back to use the new CLR user
defined type. By default we assume that the string representation of a CLR UDT
is the same in the two versions. This assumption holds in the majority of the
cases. However, in the cases where the string representation changes between
the different CLR assembly versions, an extra conversion function can be added
manually to the migration script.
The nice thing is that all of this is done automatically in
SQL Compare 5.x, thus upgrading to a new version of CLR assembly does no longer
require potentially dropping all dependent CLR objects by hand and risking data
loss.
Andras