Solved

Temporarily Disable Replication without removing subscriptions

Posted on 2011-03-01
10
4,359 Views
Last Modified: 2012-06-21
I am looking for a way of temporarily disabling replication on a SQL 2008 server without deleting the subscriptions.
0
Comment
Question by:hplovecraft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 35015836
What about stopping the log reader agent. That should do the trick

//Marten
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35019816
Actually, I think you could just stop the SQL Server Agent, and then restart it when you are ready to resume replication.
0
 
LVL 13

Expert Comment

by:geek_vj
ID: 35024350
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
ID: 35025796
Launch Replication Monitor, right-click on replication you want and choose "Stop synchronize".
Don't stop SQL Server Agent because you'll kill a fly with a bomb and could hit some other animals too (animals = jobs).
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 400 total points
ID: 35028284
The sql agent, hmmm. Why not just stop sql server service!

Well a bit harsh, but ... The log reader agent is a sole job. Why stop the sql agent. When you could disable one job. Stop the agent and maintenance plans stop working, perhaps some db is sql agent drven and so forth. see:;
http://msdn.microsoft.com/en-us/library/ms151783.aspx
or
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/c68f562f-2a22-4873-a8d8-f988e41cd6f7/

//Marten
0
 

Author Comment

by:hplovecraft
ID: 35036706
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.
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 35037051
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.
0
 

Author Comment

by:hplovecraft
ID: 35039382
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?

0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 35039631
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
0
 

Author Comment

by:hplovecraft
ID: 35053345
Thanks!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Why I am having this error in my query? 2 32
average of calculation (TSQL) 4 37
SQL server client app 3 35
Database Mail Profiles 1 25
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question