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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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
UNSAFE then you will need to do a few extra steps:
ON, then you will need to ensure that it is set to
ONon 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.
[master]database and you need to make sure they are copied or recreated on the destination:
[master]on the destination
[master]on the destination
GRANT EXTERNAL ACCESS ASSEMBLYand/or
GRANT UNSAFE ASSEMBLY
[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
UNSAFE, you might need to at least reset the PERMISSION_SET property to
SAFEand 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.
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.