Wish-list for SQL Server Replication

, 2008-09-07

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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads