hplovecraft
asked on
Temporarily Disable Replication without removing subscriptions
I am looking for a way of temporarily disabling replication on a SQL 2008 server without deleting the subscriptions.
Actually, I think you could just stop the SQL Server Agent, and then restart it when you are ready to resume replication.
Is there any specific purpose behind doing this? If yes, kindly let us know as we can suggest the best way for doing it.
If it is just disabling temporarily without making any configuration changes, then you can stop the log reader agent.
If it is just disabling temporarily without making any configuration changes, then you can stop the log reader agent.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We are in the middle of testing out replication for a data warehouse project. The reason for the question is we have had a few instances where replication had to be "stopped" because of a stored procedure update on the production SQL server. (multiple stored procedures had to be updated.)
From what I have been told, in order to update a stored procedure, you have to stop replication, then restart replication. The person that updated the stored procedure actually completelly disabled the publication, (which had to be recreated) so I'm looking for a way of ensuring that the publication stays in place the next time we need to update stored procedures on the production SQL 2008 server. (This is the publisher)
I'm relatively new to SQL (only worked with it for about 3 years, but not on a daily basis) , but I've read that replication can also be scripted so if this happens again it can be reloaded fairly quick, but I'm still learning about that.
From what I have been told, in order to update a stored procedure, you have to stop replication, then restart replication. The person that updated the stored procedure actually completelly disabled the publication, (which had to be recreated) so I'm looking for a way of ensuring that the publication stays in place the next time we need to update stored procedures on the production SQL 2008 server. (This is the publisher)
I'm relatively new to SQL (only worked with it for about 3 years, but not on a daily basis) , but I've read that replication can also be scripted so if this happens again it can be reloaded fairly quick, but I'm still learning about that.
The only time you need to stop replication it's when you need to make modifications in an object that is replicated. Modificating others objects can be done with replication working.
ASKER
We are running SQL 2008 SP1
The object was being replicated from what I have been told.
Vmontalvao, I looked for the "stop synchronize" option in Repl mon and didn't see it. I right clicked on the publisher, is that correct or am I looking in the wrong place?
Martenrune, is stopping the log job the only job I should stop or should I stop the snapshot job also?
Last couple of questions: If we stop replication through either of these methods, and it's down for a short period of time, say 1-2 hours should we recreate the snapshot if a modification is made to a replicated object(s)?
If we stop replication and down for a longer period of time, say a few days...is it easier to recreate the snapshot, or let replication continue as normal? I'm sure that the amount of data to be replicated will determine this, but how does someone make that determination?
The object was being replicated from what I have been told.
Vmontalvao, I looked for the "stop synchronize" option in Repl mon and didn't see it. I right clicked on the publisher, is that correct or am I looking in the wrong place?
Martenrune, is stopping the log job the only job I should stop or should I stop the snapshot job also?
Last couple of questions: If we stop replication through either of these methods, and it's down for a short period of time, say 1-2 hours should we recreate the snapshot if a modification is made to a replicated object(s)?
If we stop replication and down for a longer period of time, say a few days...is it easier to recreate the snapshot, or let replication continue as normal? I'm sure that the amount of data to be replicated will determine this, but how does someone make that determination?
Log job is sufficient
To determine if you will benefit from a new snapshot initialization, compare the log sizes (total) with the size of the database. If log files is lesser then let replication catch up, if the database is lesser, recreate a snapshot will be faster.
//Marten
To determine if you will benefit from a new snapshot initialization, compare the log sizes (total) with the size of the database. If log files is lesser then let replication catch up, if the database is lesser, recreate a snapshot will be faster.
//Marten
ASKER
Thanks!
//Marten