SQL Change Automation 3

Deploying Assemblies (SQLCLR)

SQL Change Automation features a code first workflow for building and deploying SQLCLR: edit your database objects and code libraries together in Visual Studio, then start the solution to deploy the assemblies to your local database. 

During Continuous Integration build, your assemblies are packaged into a single SQLCMD Package (.sql) artifact for easy deployment with Octopus or any tool that can execute a PowerShell script.

Download the sample project used in this article: ClrTestDB.zip (8 kB).

Getting started

Start by creating a solution with two new projects:

  • A C# Class Library project named ClrTestLib.csproj
  • A SQL Change Automation project named ClrTestDb.sqlproj

Within the class library project, add a file called UserDefinedFunctions.cs with the following content:

 

UserDefinedFunctions.cs

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString HelloWorld()
    {
        return new SqlString("Hello world!");
    }
}

Now add a project reference from your SQL Change Automation project to your class library:

Set the Copy Local property to True for the newly-added reference (this copies the assembly to the SQL Change Automation project at build time):

Next we'll create a re-usable script to deploy the assembly to the database. Start by adding a new folder called SQLCLR to the root of your SQL Change Automation project:

Include the new folder in the database deployment by opening the project designer and scrolling down to the Additional Scripts section. Check the item matching the name of the folder you just added:

Using the Solution Explorer, right-click the SQLCLR folder and add a script called ClrTestLib.sql. The following metadata should be included at the top of the script:

ClrTestLib.sql

-- <Migration InlineDependencies="true" />

This metadata indicates that the script contains dependency references that should be serialized (i.e. replaced with the contents of the dependent files) at build time.

Complete the script by adding statements to DROP and CREATE your assembly and associated CLR functions/procs:

ClrTestLib.sql

-- <Migration InlineDependencies="true" />
GO

IF OBJECT_ID('[dbo].[HelloWorld]') IS NOT NULL
BEGIN
  DROP FUNCTION [dbo].[HelloWorld]
END

IF EXISTS(select 1 from sys.assemblies where name = 'ClrTestLib')
BEGIN
  PRINT N'Dropping [ClrTestLib]...';
  DROP ASSEMBLY [ClrTestLib];
END
GO

PRINT N'Creating [ClrTestLib]...';
CREATE ASSEMBLY [ClrTestLib]
  AUTHORIZATION [dbo]
  FROM "{ClrTestLib}";

ALTER ASSEMBLY [ClrTestLib]
  ADD FILE FROM "{ClrTestLib.pdb}"
  AS N'ClrTestLib.pdb';
GO

PRINT N'Creating [dbo].[HelloWorld]...';
GO
CREATE FUNCTION [dbo].[HelloWorld] ( )
 RETURNS NVARCHAR (4000)
AS
 EXTERNAL NAME [ClrTestLib].[UserDefinedFunctions].[HelloWorld];

GO
PRINT 'Result from [dbo].[HelloWorld]: ' + [dbo].[HelloWorld]();
GO

Ensuring your script is idempotent

Performing a conditional drop/create of all the SQLCLR related objects ensures that your script is re-runnable, allowing incremental changes to your class library to be continuously deployed.

Note that the CREATE ASSEMBLY statement contains a special token, enclosed in quotes and curly braces:

CREATE ASSEMBLY [ClrTestLib]
AUTHORIZATION [dbo]
FROM "{ClrTestLib}";

The "{ClrTestLib}" token – which in this case refers to the assembly name – will be replaced with a serialized copy of the assembly at build time. Be sure to include the file extension if you wish to deploy files other than assemblies (e.g. .pdb files, as in the above example).

To deploy the assembly and related files to the database, click Build… Deploy Solution:

The output window will show the assembly being built and subsequently deployed to the database:

Switching over to SQL Server Management Studio reveals the two objects created by the deployment:

Upon subsequent deployments, you’ll notice that your script is executed each time you perform a “clean” build of your solution. This is because the CLR compilation process results in a different assembly being created with every build, even if the code itself hasn’t changed.

Conversely, during an incremental build/deployment, your script may not be executed as a result of artifact caching (this is normal MSBuild behavior).

Packaging & Deploying to Remote Servers

When it comes to packaging your assemblies for deployment to other environments, SQL Change Automation will automatically include all artifacts within the ClrTestDb_Package.sql file (provided you have the SQLCMD Package (.sql) output option enabled in Project Settings): deploy the package file within SQL Management Studio to target database servers, or via the accompanying PowerShell script.


Didn't find what you were looking for?