Solved

Temporarily disable SQL Replication

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

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 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