In a SSDT project how to avoid redeploying the assembly

sql-clrssdt

I have a SSDT project that has a few sql clr elements. They rarely get altered but every time I redeploy my db project it decides it must drop the assembly and recreate it.

I think its somewhat wasteful to keep doing that. Is there anyway I can configure it so that it will only recreate the assembly anytime I actually change the correlated C# sprocs?

If this will be helpful here is the profile I am using to generate the change script:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>#{DatabaseName}</TargetDatabaseName>
    <DeployScriptFileName>#{DatabaseScriptName}</DeployScriptFileName>
    <TargetConnectionString>Data Source=#{DatabaseServer}</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
  </PropertyGroup>
</Project>

I use octopus to do the deployment.

Best Answer

I've got the same problem when using the Schema Compare tool in Visual Studio 2013. From what I can tell, the reason it wants to keep deploying the SQLCLR assemblies is the Module Version ID (MVID) in the DLL. The MVID is a guid that is automatically generated by the C# compiler during compilation so each rebuild get the DLL a new MVID.

One option is to try and avoid a rebuild of the DLLs. If you're building locally with Visual Studio, you'll have to avoid the "clean" and "rebuild" options. If you're using a build server such as TFS you'll need to configure the build to not clean the workspace.

The other option that I'm going to try is to have the C# code in a separate project, and "import" the DLLs into the main database project as and when the C# code changes. Pretty much as I used to do when using the older style "DataDude" projects. Using the latest version of SSDT for VS2013, I've added the SQLCLR assembly DLL as a reference in the main SQL Server project, and set the "Model Aware" property to "True". This properties seems to be required to allow me to reference the assembly in the store procedure / function DDL statements.

Related Question