Solved

Temporarily disable SQL Replication

Posted on 2016-10-05
7
21 Views
Last Modified: 2016-11-17
Hello All,

I am fairly new to using SQL Replication.  I am wanting to put replication on a database that is in use constantly.  Occasionally I have to do an upgrade to the application that uses this database which may also involve making table changes to this database. What is the preferred method to disabling replication when doing an upgrade?  Do you have to totally dismantle replication?  Once the upgrade is done do you have to "Reinitialize the replication"?  Any help would greatly be appreciated.
0
Comment
Question by:UncleT
  • 2
  • 2
7 Comments
 
LVL 42

Expert Comment

by:EugeneZ
ID: 41830549
are you talking about transactional replication?
as the main idea -- you should keep your replication up and running
all changes will be replicated... it can handle in most cases ..
but if you really-really need to "pause" it
you can stop  Replication jobs -- > see sql agent jobs category REPL_-Distribution REPL- Logreader..
you can stop these jobs from replication manager-as well (see agents tab ..)

you can script replication -> drop replication -> do what you need to do -> create replication from script ..

it depends on your setup -environment, etc
if you replicate a very large data sets - it can take time and extended downtime for the depended apps ..
0
 

Author Comment

by:UncleT
ID: 41830557
Yes I am talking about Transactional Replication.  It is a fairly large replication set and I was wanting to get away from having to reinitialize it because it is so large.  Seems like the last time I did an upgrade it failed in the middle when it started doing the database updates with a message like database is in use.  This particular time, I didn't have time to research it so I totally took replication down and reran the upgrade and it worked.  I just trying to figure out now if there is a way to do upgrades without deleting replication and having to add that additional time for the snapshot.
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 41830972
test the upgrade script on some test box where you can set replication (you need just publication)
see what this code needs: e.g. if you drop published article (table) or PK -- you will speed up processes by  removing just 1 -2 affected tables from replication and let replication run during upgrade  without stopping and full reinit and add just 1-2 of these affected tables afterward
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831284
If the upgrade means changing a replicated object schema then you need to drop the replication and recreate it after. Best way to do this is by scripting the replication before dropping it. Of course the snapshot will take time when you reinitiate the replication.
0
 

Author Comment

by:UncleT
ID: 41831627
Thanks for all the responses!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now