Blog Post

Wish-list for SQL Server Replication

,

I have been working on sql server replication (on sql server 2K5) for more than 1 year now, starting from designing a replication deployment framework that will take care of multiple publishers to one subscriber and one publisher to multiple subscribers. Now reflecting on the work I have done, I put the following wish-list that I hope MS can address in future

1. Replication Monitor:  I wish I can set up groups in the monitor so I can group diffent publications into different groups for easy management, this is similar to the "Server Group" in the SSMS

2. Replication Verification: 

(1)  sp_article_validation does not have any output parameter, and this makes the auto replication validation harder as BOL seems to ask DBA to do manual check because it says "Check the agent output for the result of the validation", the output is a message like "table 'xxxx' might be out of synchronization. RowCounts (Actual:nnnnnnn, Expected: mmmmmm)...."

I wish sp_artilce_validation has the same output parameters as sp_table_validation does, plus two more parameters @Actual_RowCount and @Actual_CheckSum

(2) The validation is only based on: "One subscriber has one and ONLY one publisher" assumption. In my case when one Subscriber has multiple Publishers, the publication validation is always "out-of-synchronization" 

I wish MS can come up with a solution for this multiple-publisher to one subscriber validation

3. Optimize the system-generated update trigger on updatable-susscription table.

We have a case where we have one transactional replication with updatable subscription, and in the publication, we have one article whose columns are not entirely published, actually there are four columns we do not publish in this article. Since this is an updatable subscription, we can see some automatically-generated triggers on the subscription table. The problem with the update trigger is when we explicitly update the columns that are not involved in the replication at subscriber side, the update trigger is fired and  updates the publication table on the publisher side with the existing values.

An example is: Say table T has column C1, C2 and C2 is not involved in the replication, when update T set C2='something' at the subscriber side, the update trigger will issue the following sql to the publication table (since this is an updatable subscription)

begin distributed tran

update T set C1 = inserted.C1

commit tran

Functionally, this does not hurt anything, but performance-wise, it is a waste of time. 

What I wish is when I update explicitly the columns not involved in the replication on the subscriber side, the updatable trigger can recognize this column is not involved in the replication and thus simply returns without going to do a distributed transaction on the publisher side.

I have another wishlist item regarding the replication with updatable subscription, but this is more complex, and actually is a bug to me, and I will blog it in different post later.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating