Solved

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

Posted on 2013-01-11
2
255 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

863 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