How to deploy SQL CLR stored procedure to multiple servers

Question

I have inherited a SQL CLR project as part of a code maintenance project that I'm working on for a client. I'm pretty new to SQL CLR, admittedly, so I'm trying to figure out how it works.

I noticed that the database connection string is stored in the project's Properties, so I know how to change it if I need to. The one question I have though is this: is it possible to set multiple connection strings for deployment to multiple SQL Server instances? In my case I have a local dev machine, a staging server, and a production server (with a separate copy of the target database on each server). I'd like to be able to deploy the SQL CLR assembly to all 3 without having to change the connection string and re-build for each one.

Answer 1

You should not deploy to anywhere but development via Visual Studio, hence the connection string in the Project should always point to your dev environment.

Once you have the code tested in the development server, you can script out the Assembly in SSMS by right-clicking on the Assembly in question and do "Script Assembly As..." then "Create To..." and then "New Query Window". This will give you the basic script that should be used to deploy to QA, Staging, and Production.

The general format is:

USE [DBName]
GO

CREATE ASSEMBLY [AssemblyName]
AUTHORIZATION [dbo]
FROM 0x0000...
WITH PERMISSION_SET = SAFE

You do not really need to propagate the Assembly Files to the other environments, though if you want to it does not hurt.

If you want to automate that, once you have that basic script you can always grab the updated Assembly code (what is noted as 0x0000 above) via:

SELECT Content FROM sys.assembly_files WHERE name = 'AssemblyName'

Edit: For the sake of completeness, as Jeremy mentioned in a comment below, the above info only describes deployment of the Assembly itself, not of the wrapper objects to access the code within the Assembly. A full deployment process would:

1. Drop existing wrapper objects (Stored Procs, Functions, Triggers, Types, and Aggregates)
2. Drop the Assembly
3. Create the new Assembly
4. Create the wrapper objects