How to host SQL CLR stored procedure on multiple servers

Question

I inherited the SQL CLR project as part of the code maintenance project I am working on for a client. It's clear that I'm pretty new to the SQL CLR, so I'm trying to figure out how this works.

I noticed that the database connection string is stored in the project properties, so I know how to change it if necessary. The only question I have is this: is it possible to set up multiple connection strings for deployment on multiple instances of SQL Server? In my case, I have a local development machine, an intermediate server, and a production server (with a separate copy of the target database on each server). I would like to be able to deploy the SQL CLR assembly for all 3 without having to change the connection string and rebuild for each one.

Answer 1

You should not deploy anywhere other than development through Visual Studio, so the connection string in Project should always point to your developer's environment.

After you have tested the code on the development server, you can script to build in SSMS by right-clicking on the Assembly in question and making "Script Assembly As ...", then "Create To" ... ", and then" New query window. ”This will give you the base script that should be used for deployment in QA, Staging, and Production.

General format:

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

You do not need to distribute assembly files in other environments, but if you want it, it will not hurt.

If you want to automate this as soon as you have this basic script, you can always take the updated assembly code (which is indicated as 0x0000 above) with:

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

Edit: For the sake of completeness, as Jeremy mentioned in a comment below, the above information describes the deployment of the Assembly itself, and not wrapper objects to access the code inside the Assembly. Complete deployment process:

• Discard existing wrapper objects (stored procedures, functions, triggers, types and aggregates)
• Remove assembly
• Create new assembly
• Creating Shell Objects

Answer 2

When you deploy the code to the development server, Visual Studio creates the .sql file in the bin / Release folder.

This can be useful for deployment, it requires some cleaning.

Here is the perl script that I use to deploy the script from a script created by VS.

It is closely related to my needs and file format (I use VS 2010 SP1, SQL 2008 R2, perl in cygwin), consider this as an example that may not work automatically for everyone.

 use strict; use warnings; use Text::Unidecode 'unidecode'; # http://search.cpan.org/dist/Text-Unidecode/ sub ProcessBlock($) { my $lines = $_[0]; if ($lines =~ "Deployment script for") { return 0; } if ($lines =~ "^SET ") { return 0; } if ($lines =~ "^:") { return 0; } if ($lines =~ "^USE ") { return 0; } if ($lines =~ "^BEGIN TRANSACTION") { return 0; } if ($lines =~ "extendedproperty") { return 0; } if ($lines =~ "^PRINT ") { return 0; } if ($lines =~ "#tmpErrors") { return 0; } if ($lines =~ "^IF \@\@TRANCOUNT") { return 0; } my $drop = $lines; if ($drop =~ m/^DROP (FUNCTION|PROCEDURE) ([^ ]+);/m) { printf("if OBJECT_ID('$2') IS NOT NULL\n"); } elsif ($drop =~ m/^DROP ASSEMBLY \[([^ ]+)\];/m) { printf("IF EXISTS (SELECT 1 FROM sys.assemblies WHERE name = '$1')\n"); } printf($lines); printf("GO\n"); my $create = $lines; if ($create =~ m/^CREATE PROCEDURE (\[[^]]+\])\.(\[[^]]+\])/m) { printf("GRANT EXECUTE ON $1.$2 TO PUBLIC\nGO\n"); } elsif ($create =~ m/^CREATE FUNCTION (\[[^]]+\])\.(\[[^]]+\]).*RETURNS .* TABLE /ms) { printf("GRANT SELECT ON $1.$2 TO PUBLIC\nGO\n"); } elsif ($create =~ m/^CREATE FUNCTION (\[[^]]+\])\.(\[[^]]+\])/m) { printf("GRANT EXECUTE ON $1.$2 TO PUBLIC\nGO\n"); } } my $block=""; while (<>) { my $line = $_; $line = unidecode($line); if ($line =~ "^GO") { ProcessBlock($block); $block = ""; } else { $block .= $line; } } 

Using:

 perl FixDeploy.pl < YourAssembly.sql > YourAssembly.Deploy.sql 

Answer 3

Have a look here: Difference between connection strings in SQLCLR I think you should use a contextual connection if possible. This way you do not need to reconfigure.

If you need different credentials or something else, you can request a settings table that contains these settings. Use a contextual connection to connect, query the settings table to get the login details, and then use them to reconnect.

Also: the connection string is in the properties, but, as I understand it, settings.xml is not expanded, so you will always get the default values ​​that are hardcoded in the settings class.