I have a production database server and a failover database server set up.
All the transactions from the primary production db server is being replicated(transactional replication with initialize from backup) to the failover database server and it is working fine.
No activities(no reporting) are being done in failover server ,it just gets all the changes from primary production database.
Even the reporting(reporting is minimum) is done from the primary production database server.
The goal is to use it as a failover server and i know that transactional replication is not a good idea for DR and there are other recommended solutions.
But this is the way it was setup by the previous DBA and it sounded okay during that time,i heard.
So if i have to failover,i have to do it manually as i understand by researching on this topic.
There are 3 application servers(app1,app2,app3) managed using the Load Balancer.
One database server(currently primary production server)
I just have a vague idea on how to do failover, by doing my own research.
I have two questions listed below:
- Can someone be generous and direct me with steps on how to do the failover to the replicated database server.
- Once i failover ,how do i manage the new transactions/changes back to the primary production database server.
Best Answer
The Transactional replication is not a "DR Solution" you may face a lot of issues with this, (Not all tables replicated, Different data in each DB) even that it worked in the past, There's no warranty it will work in the present. The suggestion will be using some supported tool like log shipping or always on.
Even with that if you decided to go with this, as @Queue Mann said, you will need to point everything to the new server, There's no automated option on this, (but there IS in Always on) For your questions: A) There's no "official" fail over, you just use the replicated DB and point all your applications to the DB.
B) In the case of fail over you will need to set up everything again (In Always on , you won't as long it was done in Synchronous mode).
To be honest I don't know why keep using replication instead of the official DR solutions, you will save a lot of configuration (and troubles) going in the right way.