IgnoreNotForReplication not being ignored

Question

We currently have a database solution that contains 30+ database projects.  We have one database that is a "reference" database so we use transactional replication to replicate the tables from the "reference" database to the "consumer" databases.  Our solution to work around keeping DDL of the replicated tables out of the "consumer" databases is to use partial projects.

Most of our replicated tables have an identity column in them as the primary key.  All of the DDL for the replicated tables in the "reference" database has the Not For Replication property set in the table definition (as it should be).

Example. 

We have table ItemMaster (ItemID int identity(1,1) not for replication not null) in database A.  We want to replicate this table to database B via transactional replication.  In database B, the table definition should be ItemMaster (ItemID int not null) after a snapshot has occurred, and it is.  The problem we have is that TFS sees ItemMaster in database B as different than what's in TFS because, via the partial project, it thinks it should be ItemMaster (ItemID int identity(1,1) not for replication), so it tries to rebuild the ItemMaster table in database B.

In my research I found the /p:IgnoreNotForReplication parameter and thought we had solved the issue.  I modified our build definition template to include this parameter and it still tries to rebuild the ItemMaster table in database B.

Any thoughts on this would be greatly appreciated.  Are we going about this the wrong way?  Is there a different option we should be using on our build/deploy?  There is very little about the parameter mentioned on the web, except for the definition, so am I reading the defintion of this parameter incorrectly?

Answer 1

Hello Angela,

As far as I know that the IgnoreNotForReplication option is used to specify whether differences in the NOT FOR REPLICATION setting should be ignored or updated when you deploy updates to a database. You mentioned above that you are using the /p:IgnoreNotForReplication parameter, do you mean you deploy your database project via vsdbcmd.exe command line? If so I am sorry that /p:IgnoreNotForReplication is not one deployment property of the vsdbcmd command.

Instead, you can set the IgnoreNotForReplication option in the Database.sqldeployment file. And on the Advanced options table, select the Database.sqldeployment checkbox.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Vicky,

Thanks for the clarification, just color me stupid on that one.  I was trying to use it as a parm to vsdbcmd.  I did go back and edit our database.sqldeployment file and checked the IgnoreNotForReplication box in the advanced options table.

We have multiple database.sqldeployment files per database project, based on environment.  One for Local, one for Dev, one for QA one for UAT and one for Prod.  I did make the change in all the deployment files for every project, however, I am still having the issue where the deployment wants to deploy the table with the identity column. 

We use build definitions to control our build/deploy process and we use a custom build process template that has lots of variables that control different things.  I will start looking in our template to see if I can narrow it down, but if you have any ideas in the mean time, I'd love to hear them. 

Thanks again for your assistance.

-A.

More info after researching our custom build process template.

I checked our deployment manifest to ensure that the correct sqldeployment file was being included.  It was being included and the IgnoreNotForReplication=True is indeed present.

Not sure what else to check.

Answer 3

Hello Angela,

I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 4

You could use schema compare to see what the detected differences are. I’m guessing it’s rebuilding because the IDENTITY property is missing in the target. Could you add that to the target?

---

Cheeeeerrzzzz!
S@kthi
http://blogs.msdn.com/sqlsakthi | http://twitter.com/PowerSakthi

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties and confers no rights.