replication time just doubled (even with no table changes)
bgraves
Posts: 19
In the past week I've notice that the replication time (when not timing out) has increased significantly. Our set of SQL tables replicated in under 11 minutes last week. Currently, it's taking 20-25 minutes on a regular basis. Is there a reason this is happening (even when no new rows are being added to the table)? This is very difficult to troubleshoot since the code is hidden in the redgate DLLs. Could upgrading the libraries help (currently we're using version 6...not version 8)? I'm afraid to upgrade them now since it sounds like the SQL commandTimout in the redgate libraries has been decreased to 10 minutes.
Comments
I've just had a quick look in the code for the block executor and it looks like the commandtimeout is 6000 seconds, which would be 100 minutes? So I'm not sure that's entirely your problem.
Either way, it doesn't explain why it's now slower than it was. Did it change suddenly, or has it got slower over time?
Assuming nothing else has altered, has something happened to your network connection that's maybe slowing it down? Any other services added, or some other problem?
Redgate Software
When it doesn't time out, the replication time varies as well: between 730 seconds (122166 rows replicated) to 1822(984 rows replicated) seconds. Thankfully, today we're experiencing the lower part of this range (unlike earlier this week). The same set of tables is used in each case so there doesn't seem to be any rhyme or reason why the time varies. The only reason I can think of is the fact that since there was so many timeout issues over the weekend with the redgate replication, it caused subsequent extended time once SQL server was restarted. Would you agree?
Is this a live production system? I can only think that varying loads on it are accounting for the differences.
Redgate Software
Yes, this is a live production system. Today, we had a record breaking 35 minutes to replicate 21 tables. Something is definately wrong.
Well, as a general rule the process will take "as long as it takes".
You can speed it up by only including a subset of tables you're interested in (this will help the comparison phase) but for the actual sync it's basically executing an update/insert/delete script against the target server.
This is subject to what else the server is doing at the time, and other network traffic and so on.
I noticed you mentioned replication a lot in your messages. I'm not sure if this is exactly what you're using it for, but SQL Data Compare isn't really intended as a replacement for the full transactional replication offered within SQL Server itself. Whilst you can use it to move data from one db to another on a regular basis, it's only connecting to the server in the same way as any other client application and has to take its turn for resources like anything else.
Redgate Software
Basically if the performance varies then there's unlikely to be much you can do to speed it up. If it had always been slow, then it could be worth looking in your code to see if improvements could be made (perhaps run our performance profiler against it) but if the only real variable is the time it happens to run at I don't think much can be done. Sorry.
Redgate Software