June 18, 2008 at 1:41 pm
I am experienced with SQL Server in general, but fairly new to replication. In our environment, we have about 100 mobile users, each having approximately 100 customers for which they are resposible.
We maintain a table similar to this:
UserID
CustomerID
BeginDate
EndDate
This table is at the top of the replication "tree" when setting up the article filtering, essentially defining what customers go with each user. This is all well and good - the right data get's pulled down swiftly for each user.
The problem is making any changes to this table, which our business model must support. Customers can be reassinged to another user at any time.
Any updates such as...
update AssignmentTable
set UserID = 'U888'
where CustomerID = 'C'
...are painfully slow - on the order of 20-30 minutes in a well indexed 40gig database (performance is fine in all other aspects like bulk loading, app response, etc).
I'm not looking for an answer here so much as some thoughts or advice on how to better manage these changes in assignment. We'd like to be able to perform them as they occur and not batch them at night. I would have to think somebody out there has run into this issue before.
Thanks for reading, and I look forward to your responses.
keith
June 18, 2008 at 3:10 pm
A few thoughts for you:
1 transaction will have x replication commands. So if your update affects 10000 rows, you have 1 transaction with 10000 commands.
Hence, replication distribution to a subscriber is done row by row. Depending on your hardware and bandwidth you can run into problems like this.
In Replication Monitor, View Details for a subscriptions and look at UnCommited Commands, what do you have there during your update? and running idle. Best case is to have 0 idle of course, and have the number of uncommitted commands drop as you refresh it.
Erik
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply