SSDT Prevent Synonym Dropping on Publish

Question

Background

We are using SQL Server Data Tools (SQL Project) that targets SQL Server 2012 which uses DACPAC to publish to our different environments.

Problem

Whenever we publish SSDT, the synonyms are being dropped and re-created based on the generated publish script. We want to prevent this from happening since we have a stored procedure that is responsible for setting the correct synonym targets per environment.

GO
PRINT N'Dropping [dbo].[MY_SYNONYM]...';
GO
DROP SYNONYM [dbo].[MY_SYNONYM];
GO
PRINT N'Creating [dbo].[MY_SYNONYM]...';
GO
CREATE SYNONYM [dbo].[MY_SYNONYM] FOR [MY_DEFAULT_TARGET];

I've tried setting the build action of the synonyms to anything else other than Build but it causes a problem with the compiler and results to an unsuccessful build.

Our main goal is to prevent synonym re-creation for synonyms that didn't change targets. We know that we can handle this on our stored procedure by leveraging sys.synonyms for comparisons. Any help is appreciated, thank you.

EDIT:

I can see the ff on the Advanced Publish Settings but we do not want to enable Drop objects in target but not in source. We want the synonyms in the project to not get re-created during publish.

Have you tried SqlPackage.exe /a:publish ... /p:DoNotDropObjectType=Synonyms or SqlPackage.exe /a:publish ... /p:ExcludeObjectType=Synonyms to see if that changes the generated deployment script? (SqlPackage documentation) — Bill Jetzer
@BillJetzer I'll look into this. Is there an equivalent configuration for /p:DoNotDropObjectType=Synonyms in Visual Studio? I keep on fiddling with the configurations and haven't found any except for objects found in the target database but are not in the project, for these I can configure what objects not to drop but I have to enable dropping if it doesn't exist in the project first which is not something we would want. — jegtugado
What if you do a schema compare between project and DB? Click the gear, then flip to the Object Types tab and uncheck Synonyms, then click "Compare" and then "Generate Script" — Bill Jetzer
@BillJetzer /p:ExcludeObjectType=Synonyms worked, found it under the Advanced Publish Settings > ignore. With this I can proceed on changing our SP that creates synonyms with the correct underlying object. Feel free to post it as an answer to this question. — jegtugado
Make sure that database properties are the same as set in the project advanced properties. Try to align them all. — Dmitrij Kultasev

jegtugado jegtugado · Accepted Answer · 2020-07-16T07:34:31

Thanks to @Bill Jetzer's recommendation I was able to find the ff in the publish profile:

Apparently there's an option /p:ExcludeObjectType=Synonyms for SqlPackage if we want to do this using command line.

After ticking the exclude synonyms, in the publish profile the line below was added:

<PropertyGroup>
    ...
    <ExcludeSynonyms>True</ExcludeSynonyms>
</PropertyGroup>

SSDT Prevent Synonym Dropping on Publish

1 Answers