Sr. Consultant Daniel Taylor explains steps to prevent DACPACs from rebuilding all objects participating in transactional replication but has not changed in your release.

Recently I was engaged in an interesting situation where every time a DACPAC was applied to the target database all replication objects were rebuilt even after all transactional replication objects were dropped from the database and there were no DDL modifications to the table.

As part of this post I will not be walking thru the steps how to create a DACPAC. Provided is a link with multiple ways to extract your database changes to a DACPAC.

Extract a DAC From a Database

To reproduce the situation, I downloaded and or installed the following:

SQL Server Management Studio 18

SQL Server 2019 Developer Edition with latest CU

• • Database Engine
  • Replication

Sqlpackage for Windows

I wanted to see firsthand if we could replicate the issue within my configured environment. To replicate the customer scenario, I took the following steps within my configured environment. First step was to restore the AdventureWorks2016 database. The AdventureWorks2016 database was restored to my development environment, publication environment, and subscriber environment. Once that was complete, I set up transactional replication from the subscriber to the publisher. In my case I set up as a push, but to be honest just setting up replication will help reproduce the issue. No changes were made to the development AdventureWorks2016 database environment. A DACPAC was then created using SSMS using all default options. Transactional Replication was removed from the Publisher and Subscriber. The methods used to remove replication were via the GUI as well as by executing sp_removedbreplication producing identical results.

**note for the purpose of reproducing the issue table ContactType was made an article of the Publication pictured below**

Here you can see the databases involved as well as the local publication prior to removal of transactional replication:

Provided is a post screen shot showing replication has been removed:

The provided command line was used to deploy the DACPAC against the target database that was once a publication database for our transaction replication solution.

SqlPackage.exe /Action:Publish /SourceFile:"C:\Users\datay.NORTHAMERICA\OneDrive - Microsoft\Documents\SQL Server Management Studio\DAC Packages\AdventureWorks2016.dacpac" /TargetDatabaseName:AdventureWorks2016_Pub /TargetServerName:"026-OIP7KJD\SQL2019" /p:IgnoreNotForReplication=True

For the first deployment, I decided to use the parameter IgnoreNotForReplication. When looking at the output there was no evidence that changes had been made to the ContactType table.

This same command was executed without the IgnoreNotForReplication parameter.

By executing the command without the IgnoreNotForReplication parameter the sqlpackage.exe deploy of the DACPAC has determined that there are differences to the table. As a note, I recovered the AdventureWorks2016_Pub database, deployed transactional replication, and then deployed the DACPAC. The deployment did not identify any necessary changes either. Something from the transactional replication deployment has left an artifact on the ContactType table saying to the DACPAC deployment I have at least one change that needs to be deployed to the ConatctType table. Imagine the time it would take to do a deploy if you have a large number of tables being replicated that have not had any changes made to them in dev. In the case, I was investigating there were 30 plus tables with millions of rows.

Next approach taken was to query sys.tables to look and see if any of the replication flags were still set. The image below shows what I expected. None of the replication flags were set to 1.

Next thought was let’s look for drift in the target environment. Executing the following sqlpackage no drift was reported.

SqlPackage.exe /Action:DriftReport /TargetDatabaseName:AdventureWorks2016_Pub /TargetServerName:"026-OIP7KJD\SQL2019" /op:"C:\backups\DriftReports\AW_Pub.xml"

This is interesting one, right? It took me a bit (a couple of pots of coffee) to figure this one out. I scripted out the ContactType table for dev and the database that once had transaction replication applied. Well there are differences which I have highlighted in red, bolded, and italicized below. This is what is indicating to the DACPAC deploy that there are differences between development and the production database that once had transaction replication deployed.

--Table from the source database
CREATE TABLE [Person].[ContactType](
    [ContactTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_ContactType_ContactTypeID] PRIMARY KEY CLUSTERED
(
    [ContactTypeID] ASC
)
--Table from the target database that once contained transaction replication
CREATE TABLE [Person].[ContactType](
    [ContactTypeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_ContactType_ContactTypeID] PRIMARY KEY CLUSTERED
(
    [ContactTypeID] ASC
)

Next question was to answer why? Above we saw that all transactional replication had been removed.

In a nutshell when deploying replication to a table(article) which in this case was ContactType any identity columns will be marked as not for replication for the column property. You can identify this by executing the following T-SQL Statement.

use AdventureWorks2016;
SELECT
tables.object_id,tables.name AS table_name, identity_columns.name AS column_name,
COLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') AS IsIdNotForRepl
FROM sys.tables
JOIN sys.identity_columns ON tables.object_id = identity_columns.object_id
--where tables.name = 'ContactType'

As you can see, there are no local publications deployed and the IsIdNotForRepl is still set to 1. This column property indicated to the DACPAC deploy that it needed to rebuild all tables that once participated in transaction replicational replication even thou no changes were made within the development environment.

To prevent the DACPAC from rebuilding all objects that once participated in transactional replication and have no changes in development the following recommendations were made:

1. Make the same tables articles of a publication in development and production
2. Change the column property IsIdNotForRepl to 0 in production matching the development environment
3. Change the column property IsIdNotForRepl to 1 in development matching the production environment

My preferred option is number 3. Changing the column property IsIdNotForRepl to 1 matching the production environment. Doing this will have the following affects:

• Table DDL in development will contain NOT FOR REPLICATION
• DACPAC deployments will only rebuild tables that have been changed in development
• Minimizes how much we need to touch the production environment outside of the automated deployments

To complete option number 3 setting the isIdNotForREpl to 1 in development you can execute the following T-SQL.

--use DevelopmentDatabaseName;
--Enable or disable IsIdNotForReplication column proprty
--0 to disable IsIdNotForReplication 1 to enable IsIdNotForReplication
DECLARE @EnableNotForReplication CHAR(1)
SET @EnableNotForReplication = 1
--Return status of column property IsIdNotForReplication
--0 to return with IsIdNotForReplication disabled 1 to return with IsIdNotForReplication enabled
DECLARE @CurrentNotForReplicationStatus CHAR(1)
SET @CurrentNotForReplicationStatus = 0
SELECT  
'EXEC sys.sp_identitycolumnforreplication '+ cast(identity_columns.object_id as varchar(128)) +','+ @EnableNotForReplication +'' as sqlcmd,
tables.object_id,tables.name AS table_name, identity_columns.name AS column_name,
COLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') AS IsIdNotForRepl
FROM sys.tables
JOIN sys.identity_columns ON tables.object_id = identity_columns.object_id
WHERE COLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') = @CurrentNotForReplicationStatus
--and tables.name = 'ContactType' --added to narrow down by table or tables only involved in replication

The provided T-SQL will create a set of Exec statements that look like the following one provided. Execute these against your development database to set the not for replication column property.

EXEC sys.sp_identitycolumnforreplication 14623095,1

You can the validate that the changes you have made match expected results by executing the following.

--use DevelopmentDatabaseName;
--Return status of column property IsIdNotForReplication
--0 to return with IsIdNotForReplication disabled 1 to return with IsIdNotForReplication enabled
DECLARE @CurrentNotForReplicationStatus CHAR(1)
SET @CurrentNotForReplicationStatus = 1
SELECT
tables.object_id,tables.name AS table_name, identity_columns.name AS column_name,
COLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') AS IsIdNotForRepl
FROM sys.tables
JOIN sys.identity_columns ON tables.object_id = identity_columns.object_id
WHERE COLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') = @CurrentNotForReplicationStatus
--AND tables.name = 'ContactType'

Results returned will indicate if not for replication has been enabled. You are looking for a 1 which indicates the column property has been set.

object_id     | table_name  | column_name  | IsIdNotForRepl

132195521 | ContactType | ContactTypeID | 1

With not for replication column property matching between our development and production environment tables that once had replication deployed and then removed will no longer trigger a rebuild of these tables during a DACPAC deployment.

Keep calm and DACPAC on! Your Friendly @DBABulldog

At Microsoft we are always learning so please feel free to share additional thoughts or solutions.

As Always please validate, validate, validate any code within your development or isolated environment. Environments and versions can have different results and you should always vet out information found on the internet.

Helpful links that I utilized during research:

Borrowed some T-SQL to identify column property information regarding replication

https://blogs.technet.microsoft.com/umairkhan/2019/05/11/demystifying-the-native-sql-replication-co-existence-issues-with-resident-configmgr-drs/

Blog post with additional information around the not for replication column property

https://www.mssqltips.com/sqlservertip/1274/change-not-for-replication-value-for-sql-server-identity-columns/

DAC Support for SQL Objects and Versions

https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/dac-support-for-sql-server-objects-and-versions?view=sql-server-ver15

SQLPackage documentation

https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-ver15