5

I need to setup database replication (for high availability) on a database that uses FileStream. This is on SQL Server 2012 Standard Edition, so Always On or Peer-to-Peer replication is not an option. Also, database mirroring does not work with FileStream.

The only option I have left is transactional or merge replication. I have found that both replication types work fine when all machines are on, but what happens if one machine is off?

This is what I have (servers run on Microsoft Azure as virtual machines):

  • Server A is DC server
  • Server B is SQL Server 2012 Standard and Application server (IIS)
  • Server C is SQL Server 2012 Standard and Application server (IIS)

Microsoft performs maintenance at least once a month, where at most one of the servers is turned off each time. How can I set up replication with these machines so I can achieve HA when one server is down?

Improve this question
1
  • I just want to add some useful links related to SQL Failover Cluster - Good introduction to Failover - As you are working on MS Azure MS Azure Cloud Storage options Although @spaghettidba mentioned this is possible I'm not sure how to setup SAN storage on MS Azure so you have to check this by yourself.
    – khorvat
    Apr 6, 2014 at 11:51

2 Answers 2

Reset to default
2

Although replication may not be High Availability in design, it depends on your definition of HA. Certainly it has been used for HA by many people.

If replication is down long enough it can be marked as Inactive. To automatically reactivate a replication, you could try using Kin's response: SQL Server replication subscriptions marked as inactive

This runs a job that periodically checks the status of replication and reactivates it.

Improve this answer
1

Replication is a data distribution technology, it was not meant for HA.

That said, I would use a cluster. Standard edition supports 2-nodes clusters.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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