7

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!

7

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.

3

That depends.

If the assemblies were created and live in the database that is being migrated, then they will go with the database as they are already objects inside of it.

If the assemblies were created in other databases that aren't being migrated then they will need to be re-created in the source instance somewhere. For example, if the assembly was made in the master database then it is not part of the user database you are migrating and would need to be created in master on the destination server.

Since you're migrating from and to the same version of SQL Server I don't see this being an issue. However, for future reference, when migrating form say 2005 to 2012 there could potentially be some issues with execution of assemblies as they were made in a different version of .net and had different classes that were considered safe.

  • 2
    The new instance will need exec sp_configure 'clr enabled',1 before it will actually execute the assemblies in question. – Max Vernon Aug 18 '14 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.