Sql-server – Migrating CLR Assemblies

migrationsql serversql-clr

I am migrating a SQL Server 2008R2 database to a new server. (also 2008R2)

There are a number of CLR assemblies on the server. Will these automatically migrate with the database or will I have to manually script them?

Thanks!

Best Answer

How are you migrating the database? Copying a database to another server via Backup/Restore or Detach/Attach will include the Assemblies as well as the T-SQL wrapper objects that point to the code in the Assemblies. Using a tool that lets you select object types might require that you at least verify that Assemblies have been selected to migrate.

If all of your Assemblies have a PERMISSION_SET of SAFE then you should be fine with no additional steps (outside of the obvious enabling of CLR Integration as mentioned in another answer).

If any of the Assemblies have a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE then you will need to do a few extra steps:

  1. If you are using the non-preferred but easier and hence more common method of setting the database property of TRUSTWORTHY to ON, then you will need to ensure that it is set to ON on the new server. I would expect the Backup/Restore method to carry this setting over correctly, but other methods might not, especially if they create the database new on the destination.
  2. If you are using the preferred method of having an Asymmetric Key-based Login, then those pieces live in the [master] database and you need to make sure they are copied or recreated on the destination:
    1. Make sure that all Asymmetric Keys (that are used for CLR security) exist in [master] on the destination
    2. Make sure that all Logins based on those Asymmetric Keys exist in [master] on the destination
    3. Make sure that those Logins have the same server-level permissions on the destination: GRANT EXTERNAL ACCESS ASSEMBLY and/or GRANT UNSAFE ASSEMBLY
  3. Regardless of which of the two options noted above you are using, make sure that the database owner SID matches between what is shown in the database properties and what is recorded in the [master] database meta-data. This is usually not an issue if creating the destination DB and then copying objects into it, but if using Backup/Restore then there can be a mismatch in DB owner SIDs, and that will prevent CLR code from running
  4. I am less certain of this one as I haven't encountered it in a few years, but if the DB owner SID does change, then for any Assemblies that have a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE, you might need to at least reset the PERMISSION_SET property to SAFE and then back to what it was originally. If that doesn't help then you might need to DROP and reCREATE the Assemblies and the associated T-SQL wrapper objects.

It would also be a good idea to make sure that any of the pre-4.0 versions of the .Net framework that exist on the current / source server also exist on the destination server. It won't matter if additional framework versions exist on the destination, but it will make the transition smoother if there is at least nothing missing. Even though SQL Server 2005 - 2008 R2 is statically linked to the .Net 2.0 series (2012 and 2014 are statically linked to the 4.0 series) it is possible that an Assembly can require 3.0 or 3.5. If that happens and those framework versions have not been loaded then you will get a cryptic message that will not imply that a .Net framework version is missing. But any version that is not on the source server is clearly not needed if the CLR objects currently function.

Related Question