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

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'.

Answer 2

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).