Solved

Temporarily disable SQL Replication

Posted on 2016-10-05
7
63 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
[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
  • 2
  • 2
7 Comments
 
LVL 43

Expert Comment

by:Eugene Z
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 43

Accepted Solution

by:
Eugene Z 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 51

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 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