Sql-server – CLR Assemblies and SQL Server Availability Groups

availability-groupssql serversql-clrsql-server-2012

I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE. 

CREATE ASSEMBLY drawing 
  FROM C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Drawing.dll' 
  WITH PERMISSION_SET = UNSAFE;
GO

When the system fails over to the Secondary Node the Assemblies don't work with an error like:

Could not load file or assembly System.Drawing….

I can't seem to find a good reference on how CLR assemblies function in an Availability Group. Any assistance is greatly appreciated.

Best Answer

When you create a mirror of the database the "trustworthy" flag is set to false by default. And you need to sync the SID of the dbo on each side (I don't understand how this works for the SA user. Need to study more.

To resolve the issue I failed the database over to the secondary node. Set Trustworthy to True. After that I attempted to run the Procedure and got a failure related to the SID not being registered (sorry forgot to capture the message). Since the database was owned by SA i decided to remove and readd the assemblies.

After removing and readding the assemblies i was able to then run the assembly on the node. At that point I failed everything back to the primary node where I was able to run the assembly. So I failed back and forth a few times and the assembly works on both sides. While this worked for us I'm still not entirely certain how SIDs for the SA user work on both sides.

To summarize: - You have to failover to the secondary to set Trustworthy to True - While the secondary was in read-write Drop and ReADD the assemblies on the second node (while acting as primary) - That seemed to enable the assembly to run on both sides.

Thanks to Aaron and srutzky who guided me to a working solution. If anyone can better explain why what I did worked I'd be glad to know.

Related Question