Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Swimmer

I started out as a software developer back in 1996 in Denver, CO, doing Client/Server development in PowerBuilder. I am now a Data Architect, living in High Point, NC and I love what I do. I’ve worked with all versions of SQL Server since the infamous split from the Sybase code (a.k.a. version 4.21a). I’ve worn all the hats that come with dealing with SQL Server, developer to data architect and everything in between. Twitter handle: @SQLSwimmer

Replication and TFS

 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.

  1. Create a partial project in the publication database project containing all the tables and their respective attributes that you want to replicate.
  2. Add a reference to the subscriber database project by importing the partial project created in step one.
  3. 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. 
  4. 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.
  5. 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. 


Comments

Leave a comment on the original post [sqlswimmer.wordpress.com, opens in a new window]

Loading comments...