Solved

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

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Measure time after installing Antivirus 8 60
how to make geography query faster?  SQL 7 47
getting error while running below query  in sql 2 16
Related to SQL Query 5 21
Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
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 the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

730 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