November 11, 2009 at 7:02 am
Hello everyone,
I have setup transactional replication (read only) and set the synchronization to happen for every minute. Past two days we have problem that our live server is slowing down. We decided to stop the replication and tested the system. System is bit better now.
However, I wanted to confirm that each time when Synchronization happens it happens from the point where it has been left last time. And also if we need replicated copy to be in sync with out any delay how do we step up replication?
Ta
November 11, 2009 at 8:01 am
sqllearner-339367 (11/11/2009)
Hello everyone,I have setup transactional replication (read only) and set the synchronization to happen for every minute. Past two days we have problem that our live server is slowing down. We decided to stop the replication and tested the system. System is bit better now.
However, I wanted to confirm that each time when Synchronization happens it happens from the point where it has been left last time. And also if we need replicated copy to be in sync with out any delay how do we step up replication?
Ta
Yes it will continue from where it was up to last time assuming you have done nothing to interrupt the normal running of the log file. When you stopped the replication was it just the log reader you stopped or did you stop the distribution agent as well?
Here is the BOL article on how it works, its fairly thorough and gives a good insight into where your problems may lie
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rp_0evalplan/html/8ec9d24e-844a-46fc-9872-9ca19110be38.htm
Generally transactional replication is seen as the least latency replication type therefore as long as you have the agents continuosly running the data should be as in time as possible. If your looking outside replication then you could look at mirroring where you can setup one of the modes so that it writes to both sides of the mirror before commiting the transaction. its the one thats the opposite of high availability but i cant remember the name right now, and no its not low availability 🙂
November 11, 2009 at 9:09 am
sqllearner-339367 (11/11/2009)
And also if we need replicated copy to be in sync with out any delay how do we step up replication?
When you first setup the subscription, you have 3 choices, something like "1 - On demand", "2 - Based on a schedule", and "3 - Continuous".
If you're running every minute, you probably chose #2. I don't know if you can modify it to #3, or if you have to drop the subscription & start over. (Been a while since I've used it)
November 11, 2009 at 9:26 am
I have just diasbled the log reader. There has been page lock on to my subscriber table... need to investiagate further.
November 11, 2009 at 9:47 am
If you're running every minute, you probably chose #2. I don't know if you can modify it to #3, or if you have to drop the subscription & start over. (Been a while since I've used it)
You can change it to/from continuous but it is quite manual. you have to first change the sql job schedule then you need to look at the "run agent" step of the job and add/remove the -continuous from the end of the job params
November 11, 2009 at 10:03 am
sqllearner-339367 (11/11/2009)
I have just diasbled the log reader. There has been page lock on to my subscriber table... need to investiagate further.
Try rebuilding the indexes on that table. Ive seen it quite a few times whereby the index becomes corrupt and the amount of locking grinds the system to a halt.
November 11, 2009 at 10:11 am
Where is the distributor configured, on the local distributor or remote distributor?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 12, 2009 at 2:20 am
Distributor is cofigured remotely. I have seen few locks on to my destination table and even after stopping job or disabliing the publisher PAGELOCK seems to exsits. As suggested above , I will rebuild the index on the table. Currently Synchronization is running every minute and distributor runs continously.
If there is a continous lock this might effect my publishing and affect my live server.
Ta
November 12, 2009 at 3:07 am
When I queried DBCC ShowContig , scan density was only 13.5%. Now I have reindexed the table wi the scan density being 99.67% as a result , hopefully should see some improvement.
November 13, 2009 at 3:31 am
An update...
Reindexing has helped much and there is better performance.
Thanks
November 13, 2009 at 3:37 am
Good stuff. Probably worth making sure the replication tables are included in any maintenance scripts you have running
November 13, 2009 at 6:28 am
You are correct. As we are still in development phase , I havent considered it. Did scheduled the job now that runs every night.
Thank you very much.
Ta
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply