11.6 Designing High Availability Solutions

11.6 Designing High Availability Solutions

Considerations for 11.6.1 Planning

The following two factors need to be considered when planning for high availability:

RTO (Recovery time Objective, i.e. target recovery times)

RTO indicates how much downtime the business system tolerates each time. If the business stops too long, the loss will naturally increase. For particularly important business systems, it may be necessary to use multiple technologies at the same time to ensure rapid business recovery in the event of a failure.

RPO (Recovery point Objective, target recovery points)

RPO indicates how much data loss is tolerated. Typically, you can make your data non-lost by just doing a backup. However, when a disaster occurs, recovering from a backup causes the database to be unavailable at this stage, and if the recovery time is particularly long, the loss of business downtime can be more severe than the loss of a small amount of data. Especially for databases with very large data volumes, there is a need to pre-empt the weight between recovery time and data loss to make adequate plans.

Typically, there is a conflict between RTO and RPO, which needs to be weighed against a number of factors, such as business requirements, investment size, and so on, to develop SLAs (service level agreement, or SLA).

11.6.2 Comparison of various technologies

	AlwaysOn Fail-over cluster	AlwaysOn Availability Groups	Database Mirroring	Log shipping
Number of replicas	No	Maximum of 8	1 x	Unlimited
Availability of Replicas (read-only access)	Not applicable	OK	Create a snapshot, and then access the snapshot	"Standby mode" can be accessed when
External unique IP Address	Is	Is	The principal and mirror are separate IP addresses, respectively	Stand-alone IP address
Auto Fail-Over	OK	OK	Yes (requires a witness server)	No
Fail-over Unit	Instance	A set of databases	A single database	Not applicable

11.6.3 load Sharing

While SQL Server does not support load balancing, considering that SQL Server has a lower TCO (total cost of Ownership, which is the TCO), you can allocate access to databases to multiple databases, and multiple databases can be located on different computers An instance of SQL Server.

When designing a database application, you should consider that you can implement load allocation in the future. There are two main ways of sharing: read-write separation, separation of business data.

Read/write separation

Read-write separation is not a database-owned feature because the client's connection string already specifies the target database, and the SQL Server database engine does not proactively filter which is read-only access in the client's request, and does not redirect read-only access to another instance of SQL Server.

When designing a database application, consider that read replicas can assume some read-only access, so you can direct some of these read-only access (for example, report queries, backups, and so on) to a read replica for read-write separation.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/5E/wKiom1VHYs2RWKJfAADGLdLPIW8240.jpg "title=" Readonly.png "alt=" Wkiom1vhys2rwkjfaadgldlpiw8240.jpg "/>

Depending on the latency requirements for different data updates, you might consider a synchronous commit (AlwaysOn availability Group), asynchronous commit (AlwaysOn availability Group, database mirroring, log shipping, and so on) mode.

Separation of Business data

In general, the performance of a single computer is limited, and allowing more computers to participate in it can improve performance. Consider distributing the business data on the SQL Server instance of multiple computers, which allows the application's access requests to be distributed across multiple, independently-running databases.

The following example is an OTA, an online travel agent, a company that separates a database into 5 servers when it encounters a rapidly growing database size.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/5E/wKiom1VHXyfxiXd-AAEVdbTh69Y772.jpg "title=" OTA. PNG "alt=" Wkiom1vhxyfxixd-aaevdbth69y772.jpg "/>

Based on the specific business requirements and logical structure, the above load sharing scheme can be used individually or in combination to achieve satisfactory performance objectives.

Tips:

Load allocation cannot be implemented because the data files for the AlwaysOn failover cluster are located in shared storage and do not have replicas.

This article from "SQLServer2014 series" blog, declined reprint!

11.6 Designing High Availability Solutions