0

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.
  • 1
    To my knowledge there is no automatic way to failover with that setup. The connection strings for the applications have to be repointed to the subscriber, now the new publisher; assuming all the logins and security has been already conducted. Unlike mirroring, where the connection string can specify the mirror when the principal goes down, no such thing to my knowledge exists. As for switching replication roles, you would have to rebuild it with the servers reversed. Then build the subscription with the no_sync option. – Queue Mann Jun 12 at 17:07
1

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.

0

To answer your questions:

Can someone be generous and direct me with steps on how to do the failover to the replicated database server.

"Failover" doesn't exist for replication because it is not a DR technology. You have to redirect your applications to point to your replica server to "failover". You could also use a CNAME. Point your servers at a CNAME in DNS that points to your primary server, then to failover you change the CNAME to point to your replica server.

You need to set a low TTL for the CNAME so client machines pick this change up as soon as possible. Also, you will need to stop and disable the replication agent jobs to prevent data faults while you're running on the other side.

Once i failover ,how do i manage the new transactions/changes back to the primary production database server.

You can't. There is no 'reversal' in SQL Transactional Replication, so you would have to take an outage, backup the database, restore the DB on the primary and redirect your apps back to the primary.

Now to clarify some things:

These suggestions I've provided are not good ones. Replication is not a DR technology, it is not designed nor suited for DR and you need to abandon that as a solution. If you're concerned about complexity, replication is more complex than the simple DR options, like log shipping. If you're concerned about cost, later versions of SQL Server have Basic Availability Groups available in Standard Edition, and prior to 2016 you had Database Mirroring in Standard Edition - and of course log shipping in all editions with an Agent.

Check out this Docs article on Log Shipping. It is your best low cost, low complexity solution and offers you things that Replication does not, such as transactional consistency.

Advantages over Replication:

  • Transactionally consistent.
  • Automatically includes the entire database, all tables, views, procs etc.
  • Low cost and easy to implement.
  • Available in Standard Edition

Check out this Docs article on Always On Availability Groups, which is one of the better HADR solutions for SQL Server, and some additional information on Basic AGs.

Advantages over Replication:

  • Transactionally consistent
  • Automatically includes the entire database, all tables, views, procs etc.
  • Automatic redirect of clients through the use of a listener.
  • Depending on version\edition\licensing, can implement multiple replicas to achieve HADR solution.
  • Basic AGs available in Standard Edition.
  • Automatic Reversal of data following failover
  • No Agent jobs required to perform data movement

Lastly, take a look at this Docs article on Database Mirroring if you're running versions or editions that don't support AGs or Basic AGs.

Advantages over Replication:

  • Transactionally consistent
  • Automatically includes the entire database, all tables, views, procs etc.
  • Available in Standard Edition
  • Automatic Reversal of data following failover
  • No Agent jobs required to perform data movement
  • For AlwaysOn ,the servers should be setup in Windows server cluster service mode ryt.Also how about the storage part,as i understand that it needs storage in a network or SAn etc.Currently i have two separate boxes with storage separately in two boxes.Can i still achieve Always on in that set up.? – user9516827 Jun 13 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.