I'm fairly new to SQL Server. I'm looking to achieve a highly available SQL Server setup where if the primary database fails, the backup database takes over. What would you guys recommend?

I'm familiar with MySQL replication, however in the case of SQL Server it doesn't seem straight forward to switch the publisher role to a subscriber.

Ideally not a high availability cluster because I'd like to avoid Active Directory. I've had a brief search on google, however I'm asking on here to get the opinion of experienced pros.

Thanks in advance.

  • Windows/SQL Server 2016 support domainless architecture for Clusters and AGs. See links here: blogs.msdn.microsoft.com/clustering/2015/08/17/… and here: blogs.technet.microsoft.com/dataplatforminsider/2015/12/15/… – Erik Darling Feb 26 '17 at 20:26
  • @sp_BlitzErik thanks for your suggestion. Which safest method is closest to MYSQL Master - Master replication? (without degrading performance) – joebegborg07 Feb 26 '17 at 20:30
  • No idea, I've never touched MySQL. Sorry! – Erik Darling Feb 26 '17 at 20:32
  • @sp_BlitzErik basically, what I'm looking for is a simple replication / mirroring of all the databases on a server. I've made some research and it seems that with replication, if the publisher fails, it's not really straightforward to replace it with a subscriber. On the other hand using mirroring (high-safety), seems to have a major impact on performance. – joebegborg07 Feb 26 '17 at 20:36
  • Cool, check out the links and see which one comes closest to your needs. There are pretty big differences between Failover Clusters and Availability Groups. – Erik Darling Feb 26 '17 at 20:39

SIOS makes an excellent product that you can use without every supported version of SQL Server and is also SANless. It does block by block transfers and David Klee is a big enough fan that he even does sessions for them, and doesn't get remuneration for it. He just loves the product that much.

I'm not sure if it meets all of your needs but it's definitely worth checking out.


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.