redeploying a .NET assembly that contains CLR stored procedures

Question

I have written a CLR stored procedure that is in an assembly.

I have a build system that can auto-build and deploy .net application from our source control repository.

I want the two things to work together so I can redeploy the assembly that hosts the CLR stored proc.

However it looks like, unlike IIS, simply replacing the binaries doesn't work. It seems like you have to DROP ASSEMBLY on the database. In order to do that you need to drop all the objects that reference that assembly.

That seems reasonable in one way -- i.e., from a database integrity point of view-- and unreasonable in another -- the JIT approach that applies for runtime evaluation of dependencies for .NET in general.

So, is it possible to do something so I can replace the binary then give SQL server a kick and make it figure out that the new assembly satisfies all the requirements (i.e., has the right public namespaces, types, methods etc to satisfy the sprocs that are bound to it).

Answer 1

As Remus's answer states, you can use ALTER ASSEMBLY ... to update an assembly.

From the MSDN page ALTER ASSEMBLY (Transact-SQL) for SQL Server 2008 R2 [emphasis mine]:

If the FROM clause is specified, ALTER ASSEMBLY updates the assembly with respect to the latest copies of the modules provided. Because there might be CLR functions, stored procedures, triggers, data types, and user-defined aggregate functions in the instance of SQL Server that are already defined against the assembly, the ALTER ASSEMBLY statement rebinds them to the latest implementation of the assembly. To accomplish this rebinding, the methods that map to CLR functions, stored procedures, and triggers must still exist in the modified assembly with the same signatures. The classes that implement CLR user-defined types and user-defined aggregate functions must still satisfy the requirements for being a user-defined type or aggregate.

So, if the functions, stored procedures, etc. that reference the assembly haven't changed, you can simply update the assembly. Also, doing so doesn't disrupt currently running sessions; from the same MSDN page as mentioned above:

ALTER ASSEMBLY does not disrupt currently running sessions that are running code in the assembly being modified. Current sessions complete execution by using the unaltered bits of the assembly.

However, you could fairly easily re-deploy an assembly and its dependent objects automatically, but to do so generally, you would need to drop and re-create it. If you do so, you may find it easier to deploy the assembly by 'embedding' it in a script by first converting the bytes of the assembly file to hexadecimal digits which can then be included in the relevant CREATE ASSEMBLY statement.

Answer 2

I agree with what AlexS suggested except the last sentence.

First off, reflection will not truly work as the datatypes used in the CLR functions do not necessarily determine the SQL datatypes. For example, you could have SqlString on the CLR side but use NVARCHAR(50) or NVARCHAR(MAX) instead of NVARCHAR(4000) on the SQL side.

However, it is still possible to automate this. You should be using the source code repository to be storing the Stored Proc and Function definitions that point to the CLR code, just as you would any Stored Proc or Function. So you could grab all of those definitions and run all of the CREATE PROCEDURE and CREATE FUNCTION statements as Step 4.

Also, Steps 1 and 2 can be a single SQL script.

Essentially, this entire process can be automated :).

Answer 3

Short answer is 'No, it will not work this way'. As it was pointed by Remus, SQL Server stores assemblies inside your database and not somewhere in a file system. Thus there's no such place that is monitored by the server and where you should place updated binaries.

Uploading an updated assembly(ies) to the database should be an integral part of your deployment process. And the only way of doing it to perform the following actions explicitly:

1. Drop all objects that are defined in an assembly (i.e. all external SPs/UDFs/Triggers/Types)
2. Drop assembly(ies)
3. Create assembly(ies) - with either "FROM 'disklocation'" (as advised by Remus, but note that the path should refer to SQL Server's local path) or "FROM 'binary content'"
4. Create all [external] objects

Step 1 can actually be implemented in T-SQL in a generic way (so you don't have to list objects explicitly). But there's not such way for p.4 except custom tool (which will use reflection in order to discover assembly content and generate appropriate T-SQL for creating all objects).

Answer 4

The CLR assemblies are stored in the database, not on disk, so you cannot simply replace some binary dll. To refresh them you use ALTER ASSEMBLY [assemblyname] FROM 'disklocation'.