We have a database project for our SQL Server 2012 database. This database has a few SQL CLR assemblies which are very rarely updated that I wish to exclude from an automated dacpac deployment. I created a publish profile with the following settings.
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<TargetConnectionString></TargetConnectionString>
<AllowIncompatiblePlatform>True</AllowIncompatiblePlatform>
<CreateNewDatabase>False</CreateNewDatabase>
<IgnorePermissions>True</IgnorePermissions>
<DropObjectsNotInSource>False</DropObjectsNotInSource>
<IgnoreUserSettingsObjects>True</IgnoreUserSettingsObjects>
<IgnoreLoginSids>True</IgnoreLoginSids>
<IgnoreRoleMembership>True</IgnoreRoleMembership>
<ExcludeObjectTypes>"Assemblies;AsymmetricKeys"</ExcludeObjectTypes>
</PropertyGroup>
</Project>
The most important part of this profile is the tag ExcludeObjectTypes which per the MS documentation should exclude those types from the deployment. When I try to do a publish in Visual Studio with this profile it is still generating create/alter scripts for the Asymmetric Keys and Assemblies. How can I exclude these types?
...
GO
PRINT N'Creating Asymmetric Key [MyKey]...';
GO
CREATE ASYMMETRIC KEY [MyKey]
AUTHORIZATION [dbo]
WITH ALGORITHM = RSA_1024;
GO
PRINT N'Altering Assembly [MyAssembly]...';
GO
ALTER ASSEMBLY [MyAssembly]
DROP FILE ALL;
...
The syntax for SQL Server 2012 must be different from new versions of SQL Server which are linked in the documentation. When I opened the profile through the wizard and click Advanced.. -> Ignore Tab and select Exclude assembles and Exclude asymmetric keys
Then it creates entries in the publish profile with the below syntax:
<ExcludeAssemblies>True</ExcludeAssemblies>
<ExcludeAsymmetricKeys>True</ExcludeAsymmetricKeys>
This definition takes effect properly.