Solved

Temporarily disable SQL Replication

Posted on 2016-10-05
7
48 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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

860 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