Thursday, March 20, 2008

.NET Assembly and DB Backup/Restore

We recently had to transfer a utility database from our loal development server to a remote server hosted at our data center on the East Coast. We backed up the local database and restored it on the remote server. All went smoothly and DBCC CHECKDB('…') showed no errors. But when we tried to use the CLR functions inside the restored DB, some worked while other did not. A quick analysis showed that those that worked were the simple ones whose assemblies only referenced the .NET assemblies "approved " for SQL, and those that did not work had their assemblies reference .NET assemblies not in the "approved" list, such as System.EnterpriseServices. To give some background, we need those "unapproved" assemblies for our .NET remoting client applications inside SQL; basically, we have made SQL a .NET remoting client which consumes data services with endpoints at our application server. Examples of such data services: SSN decoder, VIN decoder, Address parser, Geo decoder, White Page search engine. All those data services have been implemented as .NET remoting objects hosted in Windows services and are accessed via TCP channels.

Back to our topic. In this particular case, the local server was 32-bit while the remote server 64-bit. It turns out that SQLCLR automatically loads the (most recent) "approved" assemblies in the server's GAC. So, the 64-bit SQL server always has the newest 64-bit .NET assemblies in the "approved" list ready, and since our user assemblies were built to target "Any" platforms, it is no wonder that the CLR functions whose assemblies reference only the "apprioved" assemblies worked immediately after the restore.
The case with "unapproved" assemblies is another story. Their loading is outside SQLCLR's domain, and we have to manually load them. SQLCLR checks the signature of a loaded assembly against that in GAC when the loaded assembly is accessed and reports error if the signatures do not match, for example, when there is an update. Obviously, the 32-bit assembly loaded in the backup could not match its 64-bit counterpart. To fix the problem, ALTER ASSEMBLY [System.EnterpriseServices] FROM … did not work because it was not a compatible upgrade. We ended up dropping all custom assemblies referencing [System.EnterpriseServices] and [System.EnterpriseServices] itself, and recreating all of them in the reverse order.

No comments: