1

We are deploying SQL servers at two geographically distant sites. The bandwidth between two sites is 10Mbps. The requirement is to have both sites writable/readable. Both sites have redundancy requirement within site and at the same time they sync with one another. However, the two sites will not be writing to the same row of a table. What is the best approach here to achieve maximum availability and minimizing any potential data loss? We are thinking about using SQL server 2016 and try to avoid the use of deprecated technologies such as data mirroring.

MidTierDeveloper
  • 49
  • 1
  • 5

2 Answers2

3

I would agree with @Sean however with a 10Mbps pipe, if you are using synchronous or asynchronous writes to the other site you could flood the pipe very easy.

Synchronous writes all changes to each to database in real time, transaction is not completed until both writes are done. Needs a lot of bandwidth if you database is change intensive. Not recommended for a DR sight.

Asynchronous writes build up in a queue until the secondary server can write, ideal for DR however with you 10M pipe that could be really slow.

If you want to have both sites readable and writable you really need more bandwidth, SQL Server AlwaysOn Availability Groups is one reader/writer one reader, so both sites are not write able at the same time, if your quorum is not setup properly you could end up with split brain.

Also you sound like you might be confusing HA (highly available) and DR (disaster recovery). Build out your HA first, is usually a lot cheaper, if you do not trust the data center you are in, move it to one that is better or go to the cloud.

Anthony Fornito
  • 9,546
  • 1
  • 34
  • 124
  • We are not setting up cluster cross site. To sync between the two sites we will be using transactional replication, either bidirectional replications or peer to peer replications. – MidTierDeveloper Apr 04 '18 at 19:09
  • Log shipping? that would work however you will not achieve anywhere close to what you are looking for. – Anthony Fornito Apr 04 '18 at 19:11
  • probably gonna end up using p2p transactional replication among all servers instances and have application logic to determine which server to talk to. – MidTierDeveloper Apr 05 '18 at 19:43
0

The main HA feature in SQL Server today is called AlwaysOn Availability Groups: Overview of Always On Availability Groups (SQL Server)

I think what you're after with geo-redundant HA/DR is this: SQL Server Multi-subnet clustering (SQL Server)

Ref: https://social.msdn.microsoft.com/Forums/en-US/34bc95e8-5cce-4586-b82c-d6225e172f93/best-approach-to-achieve-high-availability-and-disaster-recovery?forum=sqldisasterrecovery

Sean
  • 1