SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Adventures in Replication

By Chris Rock,

A few months ago my company opened a new call center. One of the issues that we knew we were going to face was keeping the data for a client current between two call centers. My boss (Andy Warren) decided to use a combination of queued updates and merge replication. This was running smoothly until last week. For some unknown reason all of the publications became invalidated. This meant that all the subscriptions had to be reinitialized and all the data had to be re-snapshot. This is no problem for some of our smaller tables but the majority of the tables had 1 million+ records. The biggest tables had 9 million+ rows in them. Yikes!!!

Andy and I came up with a plan to get this done and I was almost ready to go. I say almost because I was a little reluctant to take this on as I’m primarily a developer with a second title of Junior DBA (kind of the pseudo-DBA if you will). I'm comfortable with snapshot and transactional replication but merge is a whole new ballgame; one I didn't understand (like croquet).

A couple of other reasons I was reluctant to take this on was if I screw this up, not only do I create more work for my already overworked boss, but I potentially keep 20+ people from working. Luckily I came through and saved the day. Well not really, but you get the idea. I learned a few lessons along the way and I’m a better pseudo-DBA for it.

Here is the plan that we came up with:

  1. Rename the tables on the subscriber to keep as a backup.
  2. Reinitialize the snapshot (let the publisher re-create the table, etc)
  3. To make sure the data was consistent after the snapshot I had to:
    a) Insert rows from the backup tables that didn’t exist on the snapshotted (is that a word?) table from the publisher.
    b) Update rows on the table that was snapshotted (seriously, I don't think this is a word) where the data didn't match what was on backup table.

Simple as it looks, I learned the hard way that it wasn’t that easy.

Lesson 1
In reference to transactional / queued updates publications; tables that exist in the publication and have already been part of a subscription before a re-initialization, have to be present on the subscriber, otherwise the distribution agent fails. (See error message below). This was perplexing as I thought the publisher would create the table no matter what if the article setting was "Drop existing table and re-create it."

Lesson 2
Again, in reference to transactional / queued replication; the table has to be almost an exact copy of what’s on the publisher (not including indexes) if the table was part of the publication before a re-initialization of the subscription. To solve lesson 1 I created a table with the same name as the table on the publisher. The distribution agent failed with the same message above. I created a shell of the table without the constraints; the distribution agent failed again!!!! What the hell was going on? I was really irritated at this point. What finally worked was scripting the table from the publisher and creating it on the subscriber (constraints and all). I started the distribution agent again and I finally saw the bulk copy starting.

Lesson 3
To snapshot a lot of rows requires a lot MORE time if the database is 80 miles away. This is kind of obvious, but I was a little surprised how long it really takes. When I ran the snapshot for the 9 million row table overnight I woke up to a surprise. In 9 hours only 6.7 millions rows went over. I had to stop the distribution agent, rename the backup table and recreate the constraints. Fun, Fun, Fun… Out of sync for a few more days. I finally ran the snapshot on a Saturday and for some reason the data went over much faster. This allowed me to run the snapshot for the 14 million row table in the next day. Maybe less network traffic was the difference.

Lesson 4
The distribution agent does not always give you a status update of the bulk copy. I’m used to seeing the distribution agent give some type of status of the bulk copy with transactional replication (100000 rows copied, etc). The merge replication distribution agent does not give any type of status. This freaked me out for a while. I thought that once again something else went wrong. Finally, I discovered that if I query the table on the subscriber I could see the row count.

Lesson 5
You can keep data synced between two tables using queued updates with transactional replication. I thought the only way to keep data synced up between two tables was to use merge replication. I found out that using transactional replication with queued updates can accomplish basically the same thing. One major difference is queued updates cannot keep text and image columns synced between the two tables. I found the official explanation on the msdn site. The Subscriber cannot update or insert text or image values because they cannot be read from the inserted or deleted tables inside the trigger. Similarly, the Subscriber cannot update or insert text or image values using WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher. Instead, you could partition the text and image columns into a separate table and modify the two tables within a transaction. Use merge replication to synchronize these values. You cannot be assured there are no conflicts because the update of the text or image table can occur if the data is not well partitioned.

Lesson 6
Maybe I should read more articles on sqlservercentral.com about replication.


All in all I’m sure everything would have gone much smoother if I read about what I was doing beforehand, but I had Andy to help me along the way. I was happy with the solution. Whether you agree that it was the best solution is another matter, it worked. I hope that by reading this you understand the struggles that an Until next time...Happy databasing!

Total article views: 9648 | Views in the last 30 days: 4
Related Articles

Replication: Distribution Agent Monitoring

Learn about the distribution agent and how to identify replication bottlenecks.


Snapshot Agent in Transactional Replication

Function of Snapshot Agent in Transactional Replication


Snapshot Agent Failure

Snapshot agent is not running


Distribution agent failing

Distribution agent failing


Snapshot replication

Snapshot replication -- Trigger not firing

sql server 7