Solved

Could i Reverse the MSSQL Transactional Replication from Secondary DB to Primary?

Posted on 2013-01-11
2
261 Views
Last Modified: 2013-02-22
Background:

I set up a MSSQL Transactional Replication DB is replicated from
-Primary Server instance1 (MSSQL2008 R2) to Secondary Server instance1 (MSSQL2008 R2)

Transaction Replication is working good for Pri to Sec.
I would like to simulate the case of changing over using Secondary Server instead of primary Server.

Then,
1. i cleaned up any Replication on both server.
2. Use Sec Server instance1 as Publication & Distribution & Pri Server Instance1 as Subscribe.

-It is noticed that there is error after reverse the transactional replication using the original DBs.
-If i completely delete the Pri Server instance1 & Let the Sec Server instance1 create a "new DB instance2" on the Pri Side, the transactional replication started ok.

MY Question is
Could i use the original DBs (Pri Server Instance1 & Sec Server instance1) when i reverse the direction of the replication.
If it is possible-> how do i solve the issue.

Expert please help.
0
Comment
Question by:Gordon Tin
2 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 38769389
it is possible: after dropping your original replication and making sure you set the publishing db for replication: e.g. set PK
note: it depends on what you wish to have: with drop tables on subscriber with reinit - it can be easy to set subscriber..

for another cases you may need to consider to use  another Replication topologies : for example P-2-P. merge,...
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38769544
Yes you can use Merge Snap shot for REplication. In that both server instance are ready to use and updated.
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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
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…

861 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