Replication and TFS

~ sqlswimmer ~ Leave a comment

 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. 

Simply NomCom

~ sqlswimmer ~ Leave a comment

So there seems to be some confusion as to what the NomCom’s job is.  I have to admit I was confused about its job at one time as well.  It took a couple of hours of sifting through blog posts, forums and documentation on the PASS site to finally figure it out.  And it’s actually very simple.

The NomCom’s job is to take the criterion that is set forth by the PASS Board of Directors and make sure that everyone that applies for the Board of Directors positions meets those criteria, then present that list to the Board of Directors.  Simple, isn’t it?

Please don’t mistake my simplified explanation of the NomCom’s job as being unimportant.  The NomCom’s job is very important and it’s a huge commitment.  So read about the candidates before you vote then vote.  It’s your community.

NomCom, what it’s all about

~ sqlswimmer ~ 2 Comments

I submitted my application for the NomCom last week and the polls opened up yesterday.  I’ve seen an amazing amount of support not only from friends and co-workers, but also members of the community that I’ve never met.  That is what PASS is all about.  A community that encourages and helps others without ever having met someone and asking nothing in return.  I know I made the right decision to run for NomCom.  Have you voted yet?  If not, get out there and vote, your vote does count.  Read through the candidates qualifications and their applications and make your decision based on the that.  Good luck to all the candidates!

Just another SQL blog

~ sqlswimmer ~ 1 Comment

I started out as a software developer back in 1996 in Denver, CO, doing Client/Server development in PowerBuilder.  The internet was just starting to blossom and my knowledge base was non-existent.  Over the past 16 years I’ve seen how the IT world has changed and grown, which in turn has increased my knowledge base.  My goal for this blog is to share some of the knowledge that I’ve accumulated over the years with others.

I am now a Data Architect, living in High Point, NC and I love what I do.  My focus is SQL Server.  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, administrator and now data architect.  I think this gives me a unique perspective on things and a well rounded knowledge base.

I hope you find what I post helpful, if it not interesting.

-SQL Swimmer

SQL Swimmer, you ask?  I am also an active US Masters Swimmer.