I made a comparison of Sql Server disaster recovery and high availability schemes.


The database version is:

Microsoft SQL Server 2017 (RTM) – 2017 Microsoft Corporation Standard Edition (64-bit)


A simple comparison:

HA (High-Availability)
DR(disaster recovery)
Release subscription
Mirror
AlwaysOn
strategy
DR
HA
DRHA
Whether this technology is available for the current database version
available
Yes, only full safety mode is supported
Available, requires Enterprise EDITION Sql Server.
For the Standard version (BI database version), only one database is supported to do AlwaysOn
Whether to support IAAS – > PAAS
support
IAAS -> IAAS is not supported
IAAS -> IAAS is not supported


Ii. Depth comparison:

In order to make it easier for everyone to understand, I designed the following scene:
The existing production database of BI is MASTER. How does the Slave work when the MASTER goes down?
After the MASTER recovers, how are the identities of the MASTER and Slave adjusted?
Release subscription
Mirror
AlwaysOn
The MASTER is offline and the Slave is switched
The program needs to modify DBconnectionString
The program needs to modify DBconnectionString
The program does not need to modify DBconnectionString. AlwaysOn has virtual mapping on the front end
After the MASTER is fixed, the MASTER can switch back to the original
In no other. The irreversible
You can manually switch MASTER
MASTER can be toggled manually or not. Because the front-end code drifts the database.
Check whether there is a data Gap between the MASTER and Slave after the MASTER is repaired
There is. The MASTER will miss data. Recovery is complicated, and the database is hung and unavailable during data retrieval.
Hardly any. There is almost no data gap between MASTER and Slave.
Hardly any. There is almost no data gap between MASTER and Slave


Three summary

AlwaysOn:

Advantages:
1 MASTER is offline, and then Setup. There is almost no data gap between MASTER and Slave.
2 set up virtual mapping, front-end procedures do not need to modify any code, can drift database.
3 For the Standard version (BI database version), only one database can do AlwaysOn
From the monitoring point of view, THERE is just one BI hotspot library: EDC
Disadvantages:
1 IAAS ->IAAS
2 Standard Version AlwaysON can be used only for basic availability groups, but cannot be backed up or read

Mirror:

Disadvantages:
1 Since our database version is Sql Server 2017 Standrad Edition, we can only support full Safety Mode.
What is full safety Mode?
After the MASTER submits transaction A, the Slave must also submit transaction A. MASTER can process transaction B.
In the case of a morning BI batch run, this is a significant performance drain and execution will be slower than it is now.
2 Mirror technology may be removed in future versions
3 IAAS -> IAAS

Publish and subscribe:

Advantages:
1 IAAS -> PAAS
2 If the business accepts to restore T-1(to restore the data of the previous day), and accepts to establish a publish subscription takes longer and longer. That’s ok.
Disadvantages:
1 Because once the MASTER goes offline, the publish subscription “chain” is lost.
After the MASTER resetup, the lost data must be recovered through transaction log restore. The database is unavailable during data retrieval.
3. It will take longer and longer for the MASTER to set up a publish subscription again because of the increasing amount of data.