I’ve had to amend a continuous integration / deployment build process recently to include the deployment of SQL CLR assemblies. This was interesting to say the least – there are a few gotchas:
- You cant ALTER a clr assembly if any functions (in that assembly) signatures change or are removed
- You cant really drop and recreate an assembly that has schema bound dependencies (like computed columns)
- Using SQL CREATE / ALTER with a file path is difficult due to permissions (I have to qualify this – it works very well, but for me I don’t want to be messing around with file system permissions whenever I spin up a new deployment environment)
- You need to be careful if you have have multiple files linked to your assembly in SQL server
One thing I will say is that as much as you might read about the benefits of continuous integration (etc), there are a lot of development teams that don’t source control their databases and are not used to applying best practice in this area. When working with procedural code we tend to apply a lot of constraints to the way we work, but sql databases seem like a free for all! What this leads me to is that in order to have a successful CI setup with sql you must
APPLY CONSTRAINTS TO DATABASE USAGE
What I mean by this is apply some sensible constraints to say, never schema bind anything to CLR assembly functions, or always use synonyms for cross database activity. These are just a couple of ideas, but there doesn’t seem to be any conventional wisdom out there that I can find.
Deploying the Assembly
So the best way I can find to deploy an assembly in this situation is by hex encoding the file and passing it directly into a sql function. I found this GetHexString function on stack overflow which will get the hex string of an assembly:
static string GetHexString(string assemblyPath)
{
if (!Path.IsPathRooted(assemblyPath))
assemblyPath = Path.Combine(System.Environment.CurrentDirectory, assemblyPath);
var builder = new StringBuilder();
builder.Append("0x");
using (var stream = new FileStream(assemblyPath,
FileMode.Open, FileAccess.Read, FileShare.Read))
{
int currentByte = stream.ReadByte();
while (currentByte > -1)
{
builder.Append(currentByte.ToString("X2", CultureInfo.InvariantCulture));
currentByte = stream.ReadByte();
}
}
return builder.ToString();
}
Using this function, you can get the hex string you need and pass it to some sql to deploy your assembly. There are a few things that you need to be careful to drop any other files associated with your assembly (like pdb files that may have been introduced). I’ve also found that altering the assembly causes the least problems, but doesn’t always work. For this reason I use a function that looks for any associated pdb files and drops them if they exist, then tries to alter the assembly – if it fails it then tries to drop and recreate:
DECLARE @PdbName NVARCHAR(500) = @ClrAssemblyName + '.pdb'
DECLARE @DropFileSQL NVARCHAR(500) = 'ALTER ASSEMBLY [' + @ClrAssemblyName + '] DROP FILE ''' + @PdbName + ''''
DECLARE @AlterAssemblySQL NVARCHAR(MAX) = 'ALTER ASSEMBLY ['+ @ClrAssemblyName + '] FROM ' + @ClrAssemblyHex + ' WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA'
DECLARE @DropAssemblySQL NVARCHAR(MAX) = 'DROP ASSEMBLY [' + @ClrAssemblyName + ']'
DECLARE @CreateAssemblySQL NVARCHAR(MAX) = 'CREATE ASSEMBLY [' + @ClrAssemblyName + '] FROM ' + @ClrAssemblyHex + ' WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA'
BEGIN TRY
IF EXISTS (SELECT * FROM sys.assemblies AS a WHERE a.name = @ClrAssemblyName)
BEGIN
IF EXISTS (SELECT * FROM sys.assembly_files WHERE name = @PdbName)
EXEC sp_executesql @DropFileSQL
EXEC sys.sp_executesql @AlterAssemblySQL
END
END TRY
BEGIN CATCH
IF ERROR_NUMBER() IN (6270,6271,6272)
BEGIN
EXEC sp_executesql @DropAssemblySQL
EXEC sp_executesql @CreateAssemblySQL
END
IF NOT ERROR_NUMBER() IN (6285,6270,6271,6272)
BEGIN
declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
END
END CATCH
I usually save this sql snippet into source control somewhere within the database section, and pick this up as part of the database deployment – I then feed it into a SqlCommand with the required parameters:
- @ClrAssemblyHex – the hex from the previous function
- @ClrAssemblyName – the name of the assembly (without the .dll extension)
This is by no means perfect and could definitely be cleaned up a bit – but it gets the job done most of the time!
Other Issues
The main other issue that I encountered was that in order to drop and recreate the assembly, you need to drop any dependent clr functions, clr stored procs etc beforehand, and then recreate them afterwards. But thats for a different post!
