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.
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.