Solved

Temporarily disable SQL Replication

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

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 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