Redistributing a .NET Assembly Containing CLR Stored Procedures
I wrote a CLR Stored Procedure that is in assembly.
I have a build system that can automatically build and deploy a .net application from our source repository.
I want two things to work together so that I can redeploy the assembly that stores the CLR stored procedure.
However, unlike IIS, simply swapping the binaries does not seem to work. It seems that you should DROP ASSEMBLY on the database. To do that you need to discard all objects that reference this assembly.
This seems reasonable in one way - i.e. in terms of database integrity - and otherwise unreasonable - the JIT approach that is used to evaluate the dependencies of the .NET runtime in general.
So, is there anything that can be done so that I can replace the binary and then give the SQL server a kick and figure out that the new assembly meets all the requirements (i.e. has the correct public namespaces, types, methods, etc. . satisfy the associated sprocs).
source to share
The short answer is "No, it won't work." As Remus pointed out, SQL Server stores assemblies inside your database, not somewhere on the filesystem. Thus, there is no place that is controlled by the server and where you should put the updated binaries.
Uploading the updated assembly (s) to the database should be an integral part of the deployment process. And the only way to do it is to explicitly do the following:
- Drop all objects that are defined in the assembly (i.e. all external SP / UDF / triggers / types)
- Reset node (s)
- Create assembly - either with "FROM" disklocation "(as recommended by Remus, but note that the path must refer to the local SQL Server path) or" FROM "binary content
- Create all [external] objects
Step 1 can indeed be implemented generically in T-SQL (so you don't need to explicitly specify the objects). But there is no such way for p.4 other than a custom tool (which will use reflection to discover the contents of the assembly and generate the appropriate T-SQL to create all objects).
source to share
Like Remus's answer , you can use ALTER ASSEMBLY ...
to update the 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 the latest copies of the supplied modules. Because an instance of SQL Server that is already defined against an assembly can have CLR functions, stored procedures, triggers, data types, and UDFs, the ALTER ASSEMBLY statement interlaces them with the latest implementation of the assembly. To perform this relinking, methods that map CLR functions, stored procedures, and triggers must still exist in the modified assembly with the same signatures. Classes that implement CLR user-defined types and user-defined aggregate functions must meet the requirements to be a user-defined type or aggregate.
So, if the functions, stored procedures, etc. referencing the assembly have not changed, you can simply update the assembly. Also, it does not break current sessions; from the same MSDN page as above:
ALTER ASSEMBLY does not interrupt currently running sessions that are running code in the assembly being modified. The execution of the current sessions is done using unmodified assembly bits.
However, you could quite easily redeploy the assembly and its dependent objects automatically, but to do so, you generally need to delete and re-create it. If you do this, you may find it easier to deploy the assembly by "embedding" it in a script, first converting the bytes of the assembly file to hexadecimal digits, which can then be included in the appropriate statement CREATE ASSEMBLY
.
source to share
I agree with what AlexS suggested except for the last suggestion.
First, reflection won't actually work, because the data types used in CLR functions do not necessarily define the SQL data types. For example, you can have SqlString on the CLR side, but instead of NVARCHAR (4000) on the SQL side, use NVARCHAR (50) or NVARCHAR (MAX).
However, it is still possible to automate this. You should use a source code repository to store the stored Proc and Function definitions that point to CLR code, just like any stored Proc or Function. So you can grab all of these definitions and execute all CREATE PROCEDURE and CREATE FUNCTION statements as step 4.
Also, steps 1 and 2 can be one SQL script.
Essentially, this whole process can be automated :).
source to share