{"id":38132,"date":"2019-12-29T06:00:48","date_gmt":"2019-12-29T13:00:48","guid":{"rendered":"http:\/\/devblogs.microsoft.com\/premier-developer\/?p=38132"},"modified":"2019-12-24T09:06:37","modified_gmt":"2019-12-24T16:06:37","slug":"dacpac-always-changes-previously-replicated-tables-on-the-target","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/premier-developer\/dacpac-always-changes-previously-replicated-tables-on-the-target\/","title":{"rendered":"DACPAC always changes previously replicated tables on the target"},"content":{"rendered":"<p>Sr. Consultant <a href=\"https:\/\/www.linkedin.com\/in\/danieltaylor3\/\">Daniel Taylor<\/a> explains steps to prevent DACPACs from rebuilding all objects participating in transactional replication but has not changed in your release.<\/p>\n<hr \/>\n<p>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.<\/p>\n<p>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.<\/p>\n<p><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/data-tier-applications\/extract-a-dac-from-a-database?view=sql-server-ver15\">Extract a DAC From a Database<\/a><\/p>\n<p>To reproduce the situation, I downloaded and or installed the following:<\/p>\n<p style=\"padding-left: 40px;\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/download-sql-server-management-studio-ssms?view=sql-server-ver15\">SQL Server Management Studio 18<\/a><\/p>\n<p style=\"padding-left: 40px;\"><a href=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/sql-server-downloads\">SQL Server 2019 Developer Edition with latest CU<\/a><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>Database Engine<\/li>\n<li>Replication<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p style=\"padding-left: 40px;\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/tools\/sqlpackage-download?view=sql-server-ver15\">Sqlpackage for Windows<\/a><\/p>\n<p>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.<\/p>\n<p><strong>**note for the purpose of reproducing the issue table ContactType was made an article of the Publication pictured below**<\/strong><\/p>\n<p>Here you can see the databases involved as well as the local publication prior to removal of transactional replication:<\/p>\n<p><img decoding=\"async\" width=\"696\" height=\"382\" class=\"wp-image-38133\" src=\"http:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-18.png\" srcset=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-18.png 696w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-18-300x165.png 300w\" sizes=\"(max-width: 696px) 100vw, 696px\" \/><\/p>\n<p>Provided is a post screen shot showing replication has been removed:<\/p>\n<p><img decoding=\"async\" width=\"471\" height=\"355\" class=\"wp-image-38134\" src=\"http:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-19.png\" srcset=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-19.png 471w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-19-300x226.png 300w\" sizes=\"(max-width: 471px) 100vw, 471px\" \/><\/p>\n<p>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.<\/p>\n<pre class=\"lang:tsql decode:true \">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<\/pre>\n<p>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.<\/p>\n<p>This same command was executed without the IgnoreNotForReplication parameter.<\/p>\n<p><img decoding=\"async\" width=\"780\" height=\"43\" class=\"wp-image-38135\" src=\"http:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-20.png\" srcset=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-20.png 780w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-20-300x17.png 300w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-20-768x42.png 768w\" sizes=\"(max-width: 780px) 100vw, 780px\" \/><\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p><img decoding=\"async\" width=\"961\" height=\"60\" class=\"wp-image-38136\" src=\"http:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-21.png\" srcset=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-21.png 961w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-21-300x19.png 300w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2019\/12\/word-image-21-768x48.png 768w\" sizes=\"(max-width: 961px) 100vw, 961px\" \/><\/p>\n<p>Next thought was let\u2019s look for drift in the target environment. Executing the following sqlpackage no drift was reported.<\/p>\n<pre class=\"lang:default decode:true \">SqlPackage.exe \/Action:DriftReport \/TargetDatabaseName:AdventureWorks2016_Pub \/TargetServerName:\"026-OIP7KJD\\SQL2019\" \/op:\"C:\\backups\\DriftReports\\AW_Pub.xml\"<\/pre>\n<p>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.<\/p>\n<pre class=\"lang:default decode:true\">--Table from the source database\r\nCREATE TABLE [Person].[ContactType](\r\n\u00a0\u00a0\u00a0\u00a0[ContactTypeID] [int] IDENTITY(1,1) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0[Name] [dbo].[Name] NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0[ModifiedDate] [datetime] NOT NULL,\r\nCONSTRAINT [PK_ContactType_ContactTypeID] PRIMARY KEY CLUSTERED\r\n(\r\n\u00a0\u00a0\u00a0\u00a0[ContactTypeID] ASC\r\n)\r\n--Table from the target database that once contained transaction replication\r\nCREATE TABLE [Person].[ContactType](\r\n\u00a0\u00a0\u00a0\u00a0[ContactTypeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0[Name] [dbo].[Name] NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0[ModifiedDate] [datetime] NOT NULL,\r\nCONSTRAINT [PK_ContactType_ContactTypeID] PRIMARY KEY CLUSTERED\r\n(\r\n\u00a0\u00a0\u00a0\u00a0[ContactTypeID] ASC\r\n)<\/pre>\n<p>Next question was to answer why? Above we saw that all transactional replication had been removed.<\/p>\n<p>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.<\/p>\n<pre class=\"lang:default decode:true\">use AdventureWorks2016;\r\nSELECT\r\ntables.object_id,tables.name AS table_name, identity_columns.name AS column_name,\r\nCOLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') AS IsIdNotForRepl\r\nFROM sys.tables\r\nJOIN sys.identity_columns ON tables.object_id = identity_columns.object_id\r\n--where tables.name = 'ContactType'<\/pre>\n<p>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.<\/p>\n<p>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:<\/p>\n<ol>\n<li>Make the same tables articles of a publication in development and production<\/li>\n<li>Change the column property IsIdNotForRepl to 0 in production matching the development environment<\/li>\n<li>Change the column property IsIdNotForRepl to 1 in development matching the production environment<\/li>\n<\/ol>\n<p>My preferred option is number 3. Changing the column property IsIdNotForRepl to 1 matching the production environment. Doing this will have the following affects:<\/p>\n<ul>\n<li>Table DDL in development will contain NOT FOR REPLICATION<\/li>\n<li>DACPAC deployments will only rebuild tables that have been changed in development<\/li>\n<li>Minimizes how much we need to touch the production environment outside of the automated deployments<\/li>\n<\/ul>\n<p>To complete option number 3 setting the isIdNotForREpl to 1 in development you can execute the following T-SQL.<\/p>\n<pre class=\"lang:default decode:true\">--use DevelopmentDatabaseName;\r\n--Enable or disable IsIdNotForReplication column proprty\r\n--0 to disable IsIdNotForReplication 1 to enable IsIdNotForReplication\r\nDECLARE @EnableNotForReplication CHAR(1)\r\nSET @EnableNotForReplication = 1\r\n--Return status of column property IsIdNotForReplication\r\n--0 to return with IsIdNotForReplication disabled 1 to return with IsIdNotForReplication enabled\r\nDECLARE @CurrentNotForReplicationStatus CHAR(1)\r\nSET @CurrentNotForReplicationStatus = 0\r\nSELECT\u00a0\u00a0\r\n'EXEC sys.sp_identitycolumnforreplication '+ cast(identity_columns.object_id as varchar(128)) +','+ @EnableNotForReplication +'' as sqlcmd,\r\ntables.object_id,tables.name AS table_name, identity_columns.name AS column_name,\r\nCOLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') AS IsIdNotForRepl\r\nFROM sys.tables\r\nJOIN sys.identity_columns ON tables.object_id = identity_columns.object_id\r\nWHERE COLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') = @CurrentNotForReplicationStatus\r\n--and tables.name = 'ContactType' --added to narrow down by table or tables only involved in replication<\/pre>\n<p>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.<\/p>\n<pre class=\"lang:default decode:true\">EXEC sys.sp_identitycolumnforreplication 14623095,1<\/pre>\n<p>You can the validate that the changes you have made match expected results by executing the following.<\/p>\n<pre class=\"lang:default decode:true\">--use DevelopmentDatabaseName;\r\n--Return status of column property IsIdNotForReplication\r\n--0 to return with IsIdNotForReplication disabled 1 to return with IsIdNotForReplication enabled\r\nDECLARE @CurrentNotForReplicationStatus CHAR(1)\r\nSET @CurrentNotForReplicationStatus = 1\r\nSELECT\r\ntables.object_id,tables.name AS table_name, identity_columns.name AS column_name,\r\nCOLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') AS IsIdNotForRepl\r\nFROM sys.tables\r\nJOIN sys.identity_columns ON tables.object_id = identity_columns.object_id\r\nWHERE COLUMNPROPERTY(identity_columns.object_id, identity_columns.name, 'IsIdNotForRepl') = @CurrentNotForReplicationStatus\r\n--AND tables.name = 'ContactType'<\/pre>\n<p>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.<\/p>\n<p style=\"padding-left: 40px;\">object_id \u00a0 \u00a0 | table_name\u00a0 | column_name\u00a0 | IsIdNotForRepl<\/p>\n<p style=\"padding-left: 40px;\">132195521 | ContactType | ContactTypeID | 1<\/p>\n<p>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.<\/p>\n<p>Keep calm and DACPAC on! Your Friendly @DBABulldog<\/p>\n<p>At Microsoft we are always learning so please feel free to share additional thoughts or solutions.<\/p>\n<p><strong>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.<\/strong><\/p>\n<p>Helpful links that I utilized during research:<\/p>\n<p>Borrowed some T-SQL to identify column property information regarding replication<\/p>\n<p><a href=\"https:\/\/blogs.technet.microsoft.com\/umairkhan\/2019\/05\/11\/demystifying-the-native-sql-replication-co-existence-issues-with-resident-configmgr-drs\/\">https:\/\/blogs.technet.microsoft.com\/umairkhan\/2019\/05\/11\/demystifying-the-native-sql-replication-co-existence-issues-with-resident-configmgr-drs\/<\/a><\/p>\n<p>Blog post with additional information around the not for replication column property<\/p>\n<p><a href=\"https:\/\/www.mssqltips.com\/sqlservertip\/1274\/change-not-for-replication-value-for-sql-server-identity-columns\/\">https:\/\/www.mssqltips.com\/sqlservertip\/1274\/change-not-for-replication-value-for-sql-server-identity-columns\/<\/a><\/p>\n<p>DAC Support for SQL Objects and Versions<\/p>\n<p><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/data-tier-applications\/dac-support-for-sql-server-objects-and-versions?view=sql-server-ver15\">https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/data-tier-applications\/dac-support-for-sql-server-objects-and-versions?view=sql-server-ver15<\/a><\/p>\n<p>SQLPackage documentation<\/p>\n<p><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/tools\/sqlpackage?view=sql-server-ver15\">https:\/\/docs.microsoft.com\/en-us\/sql\/tools\/sqlpackage?view=sql-server-ver15<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to prevent the DACPAC from rebuilding all objects that once participated in transactional replication and have no changes in development. <\/p>\n","protected":false},"author":582,"featured_media":37840,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[8,5940],"tags":[12,3],"class_list":["post-38132","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data","category-development","tag-sql-server","tag-team"],"acf":[],"blog_post_summary":"<p>Learn how to prevent the DACPAC from rebuilding all objects that once participated in transactional replication and have no changes in development. <\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/38132","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/users\/582"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/comments?post=38132"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/38132\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media\/37840"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media?parent=38132"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/categories?post=38132"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/tags?post=38132"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}