So, we’ve had this issue lately at work with our TFS build/deploys causing a significant amount of contention and only partially succeeding.
A little about our environment:
- SQL Server 2008 R2 Enterprise Edition, 64-bit.
- Visual Studio 2010
- Team Foundation Server (TFS)
- Using Transactional Replication
- Using partial projects for replicated objects
- 31 database projects in our solution
When we looked at the build log, we saw that several of our largest tables were being rebuilt, but not on a consistent basis. At a cursory glance we could not see why, they appeared to be the same as what was in TFS. After a couple of weeks we discovered that the tables were only being rebuilt right after a replication snapshot had been re-initialized. The tables that were being rebuilt were tables with identity columns that were being replicated. Turns out we had some mismatched configurations between TFS and replication. When using partial projects in TFS to manage replicated objects, you cannot use the NOT FOR REPLICATION attribute on your identity columns. You must also “manually” manage the identity ranges in your replication publications.
Tips/Reminders when using TFS and transactional replication.
- Create a partial project in the publication database project containing all the tables and their respective attributes that you want to replicate.
- Add a reference to the subscriber database project by importing the partial project created in step one.
- Do NOT use the NOT FOR REPLICATION attribute on the identity column in the tables that are being replicated. This is counter intuitive, but necessary if don’t want your tables being rebuilt every time between a snapshot initialization and your TFS build.
- When you create the replication publications, you will need to set the identity column management property to manual for the articles that have identity columns. Again, counter intuitive, but necessary. Another thing that we had to do was remove permissions from our users to directly update the tables in the subscriber databases. Our database supports a web based application that uses a generic service account to access the database. We had an issue when new/inexperienced support staff would directly modify data in the subscriber database.
- In your .sqldeployment file(s) check the option named IgnoreNotForReplication.
The reason for all this hoop jumping is because the tools do not support database replication, nor do they support Change Data Capture (CDC). These are great features, but when they are not supported in the tools that are available, it makes it very hard to want to use them.
