SQL Multi Site Failover High Availability Suggestions
-
Hello All,
I am looking for a solution to have a geo location High availability for our Production SQL Database. Currently we have two datacenter A and B with site to site VPN. Datacenter A is having an active/active production SQL Cluster. Our requirement is to have a replication of SQL Database in site A and Site B. Currently we are using SQL standard edition. SQL Alwayson is an option but we need to upgrade the SQL to enterprise edition. Currently evaluating methods are- SQL Alwayson
- Storage replication
- SQL Mirroring
- Log Shipping
I am looking for the available methods to achieve Multisite High availability for SQL Server. Any solution is there than SQL Alwayson. Any third party application is there to achieve this?
Requirement is that if our Datacenter A SQL cluster is having issue or maintenance , need to failover to Datacenter B SQL with minimal impact.
-
I'm assuming that calling it SQL you mean MS SQL Server? SQL itself can refer to a lot of things and none properly except the language family.
-
@scottalanmiller said in SQL Multi Site Failover High Availability Suggestions:
SQL itself can refer to a lot of things a
Sorry, I mean MS SQL Server 2014
-
@sreekumarpg said in SQL Multi Site Failover High Availability Suggestions:
@scottalanmiller said in SQL Multi Site Failover High Availability Suggestions:
SQL itself can refer to a lot of things a
Sorry, I mean MS SQL Server 2014
Does Replication or Mirroring not work? Those are already there and available for use.
Not sure how they apply to a cluster, but for a single server they work fine.
-
I don't know your existing infrastructure, budget, requirements, etc...
But here are two solutions that are designed to work with MS SQL, though, they require Windows Server 2016 Datacenter.
Storage Spaces Direct (S2D): https://sqlperformance.com/2015/11/io-subsystem/storage-spaces-direct-for-sql-server
Storage Replica: https://technet.microsoft.com/en-us/windows-server-docs/storage/storage-replica/storage-replica-overview
Of course, there's always WSFC with SQL: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server
-
Have you looked at BigCouch? Just asking given your requirements.
I have not used Azure SQL but from why I read I would think this would suit.
What is the application? Internal business app or scale out service?
-
@Tim_G said in SQL Multi Site Failover High Availability Suggestions:
I don't know your existing infrastructure, budget, requirements, etc...
But here are two solutions that are designed to work with MS SQL, though, they require Windows Server 2016 Datacenter.
Storage Spaces Direct (S2D): https://sqlperformance.com/2015/11/io-subsystem/storage-spaces-direct-for-sql-server
Storage Replica: https://technet.microsoft.com/en-us/windows-server-docs/storage/storage-replica/storage-replica-overview
Of course, there's always WSFC with SQL: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server
Why are you taking it out of the application layer?
-
@JaredBusch said in SQL Multi Site Failover High Availability Suggestions:
@Tim_G said in SQL Multi Site Failover High Availability Suggestions:
I don't know your existing infrastructure, budget, requirements, etc...
But here are two solutions that are designed to work with MS SQL, though, they require Windows Server 2016 Datacenter.
Storage Spaces Direct (S2D): https://sqlperformance.com/2015/11/io-subsystem/storage-spaces-direct-for-sql-server
Storage Replica: https://technet.microsoft.com/en-us/windows-server-docs/storage/storage-replica/storage-replica-overview
Of course, there's always WSFC with SQL: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server
Why are you taking it out of the application layer?
He specifically asked for something outside of MS SQL:
@sreekumarpg said in SQL Multi Site Failover High Availability Suggestions:
Any third party application is there to achieve this?
-
@Tim_G said in SQL Multi Site Failover High Availability Suggestions:
@JaredBusch said in SQL Multi Site Failover High Availability Suggestions:
@Tim_G said in SQL Multi Site Failover High Availability Suggestions:
I don't know your existing infrastructure, budget, requirements, etc...
But here are two solutions that are designed to work with MS SQL, though, they require Windows Server 2016 Datacenter.
Storage Spaces Direct (S2D): https://sqlperformance.com/2015/11/io-subsystem/storage-spaces-direct-for-sql-server
Storage Replica: https://technet.microsoft.com/en-us/windows-server-docs/storage/storage-replica/storage-replica-overview
Of course, there's always WSFC with SQL: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server
Why are you taking it out of the application layer?
He specifically asked for something outside of MS SQL:
@sreekumarpg said in SQL Multi Site Failover High Availability Suggestions:
Any third party application is there to achieve this?
Missed that.
-
StarWind Virtual SAN is something that would allow achieving your goal. We can synchronously replicate the storage across 2 sites thus provide you with the ability to build geo-cluster. Then instead of upgrading your SQL to the enterprise edition, you could simply go with the FCI approach that is available in the standard edition.
-
Thanks All for the valid comments.
Management is looking for a low cost solution , Currently we are having a two Physical Windows Server 2012 R2 standard Cluster with Microsoft SQL 2014 standard edition. Storage is Dell compellent. This setup is done in Datacenter A and In datacenter B we are having two Physical Windows Server 2012 R2 standard Cluster with Microsoft SQL 2014 standard edition and storage is Dell equallogic.
Now the application is configured to send data to both SQL cluster and in outage or maintenance we are reconnecting all services to Datacenter B.
Requirement is to reduce the manual process and only doing a DNS change or Load balance change should move service from Datacenter A to Datacenter B and SQL data should be the same. SQL alwayson seems to be the exact solution but not in budget.
-
@sreekumarpg said in SQL Multi Site Failover High Availability Suggestions:
Thanks All for the valid comments.
Management is looking for a low cost solution , Currently we are having a two Physical Windows Server 2012 R2 standard Cluster with Microsoft SQL 2014 standard edition. Storage is Dell compellent. This setup is done in Datacenter A and In datacenter B we are having two Physical Windows Server 2012 R2 standard Cluster with Microsoft SQL 2014 standard edition and storage is Dell equallogic.
Now the application is configured to send data to both SQL cluster and in outage or maintenance we are reconnecting all services to Datacenter B.
Requirement is to reduce the manual process and only doing a DNS change or Load balance change should move service from Datacenter A to Datacenter B and SQL data should be the same. SQL alwayson seems to be the exact solution but not in budget.
So why are you even worrying about this any more. Management said it's to expensive, so you or your boss should tell them to stand by that decision.
Edit: Easier said than done I know, but the decision is out of your hands.
