Solved

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

Posted on 2013-01-11
2
271 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
[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 Comments
 
LVL 43

Accepted Solution

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

628 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