AlwaysOn Availability Group Scenarios

AlwaysOn Availability Group Scenarios

1. Availability Groups

An "availability Group" (Availability Group, or AG) supports a failover environment for a discrete set of user databases (known as "Availability Databases", which collectively implement failover). An availability group supports a set of primary databases and multiple sets of corresponding secondary databases.

Each set of availability databases is hosted by an availability replica. The following two types of availability replicas are available:

(1) A "master copy"

The primary replica is used to host the primary database. The primary replica makes a set of primary databases available to read and write connections to clients.

(2) Multiple "secondary replicas"

a secondary replica hosts a set of secondary databases and acts as a potential failover target for the availability group. The primary replica sends transaction log records for each primary database to each secondary database. Each secondary replica caches transaction log records ("hardened" logs) and then applies them to the appropriate secondary database.

You can configure one or more secondary replicas to support read-only access to the secondary database, and you can configure any secondary replicas to allow backups of the secondary database.

Displays an availability group that contains one primary replica and four secondary replicas. Supports up to eight secondary replicas, including one primary replica and two synchronous-commit secondary replicas, where 1 primary replicas and another secondary replica can be set to auto-failover.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/8B/57/wKioL1hKSkGCEATLAADZw4SIuyU256.jpg-wh_500x0-wm_3 -wmp_4-s_501837743.jpg "title=" image 1.jpg "alt=" Wkiol1hkskgceatlaadzw4siuyu256.jpg-wh_50 "/>

2. data sync and failover

The process of "data synchronization" is implemented at the database level, and the primary database synchronizes data independently with each connected secondary database. Therefore, one secondary database can hang or fail without affecting other secondary databases, and one primary database can hang or fail without affecting other primary databases.

The availability group fails over at the availability replica level. Availability replicas provide redundancy at the database level only-for that group of databases in an availability group. However, failover is not caused by database problems such as database corruption due to loss of data files or transaction logs.

The deployment of AlwaysOn availability groups requires a WSFC cluster. Each availability replica for a given availability group must be on a different node in the same WSFC. WSFC creates a resource group for each availability group, and then monitors this resource group to assess the health of the primary replica and determine when to fail over.

3. Benefits of the availability group

Availability groups have the following advantages:

(1) Availability groups are independent of shared storage compared to FCI.

(2) The number of secondary replicas can reach a maximum of 8 (SQL Server 2012 is limited to 4).

(3) Secondary replicas can provide read-only access directly.

(4) "Data synchronization" delay time has been greatly shortened, and even can be "synchronous commit." Also, the secondary replica of the availability group does not need to disconnect the client's existing connection when restoring the transaction log.

(5) Provide VNN and virtual IP addresses for transparent access by clients.

4. deficiencies in the availability group

Availability groups have the following disadvantages:

(1) SQL Server 2012 and SQL Server 2014 must be enabled in the Enterprise Edition to enable this feature, increasing user input costs. SQL Server 2016 allows the Standard Edition to enable Basic availability groups, and Enterprise Edition is required if full functionality is required.

(2) During the deployment of an availability group, most of the features and properties of log shipping, database mirroring, and FCI are centralized, increasing the complexity of deployment.

5. Interoperability of availability groups

AlwaysOn availability groups and database mirroring do not support cross-database transactions and distributed transactions. This is because the atomicity and integrity of the transaction cannot be guaranteed, and there may be a logical inconsistency.

AlwaysOn availability groups and database mirroring actually use the same data synchronization mechanism (including the use of the same endpoint, process, and so on), so these two technologies are not hybrid deployments.

included databases, database encryption, database snapshots, full-text indexes, FILESTREAM features, and so on are still available in AlwaysOn availability groups. At deployment time, you need to be aware of the access rights of each node to certain shared resources (folders, network disks, and so on) and to use the virtual network name as the computer name when considering specifying network resources.

Additional notes:

# # # Read and 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.

AlwaysOn availability groups provide read-only routing access, and AlwaysOn availability Group listeners can redirect connections to read replicas based on the order of the read-only routing table, if the client's connection string affirms read-only single.

Read-write separation is the primary load-balancing scheme that is implemented by directing some read-only access (for example, report queries, backups, and so on) to a read replica, and must consider how to allocate read-only access when designing the application.

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/57/wKioL1hKSlPjBNZWAAA4uk3hZJE285.jpg-wh_500x0-wm_3 -wmp_4-s_4255420581.jpg "title=" image 2.jpg "alt=" Wkiol1hkslpjbnzwaaa4uk3hzje285.jpg-wh_50 "/>

Depending on the latency requirements for data updates for read replicas, you can consider solutions such as synchronous commits (for example, AlwaysOn availability groups), asynchronous commits (for example, AlwaysOn availability groups, database mirroring, log shipping, replication, and so on).

# # # Distributed System CAP theory # # #

Database load Balancing is designed based on a distributed architecture, so you need to understand the CAP theory in the distributed realm.

The CAP theory proves that any distributed system can only meet the following two points at the same time, unable to balance the three.

C (consistency): Consistency is called an atomic object, and any read-write should appear to be "atomic" or serial. Write the back of the reading must be able to read the previous written content. All read-write requests appear to be sorted globally.

A (availability): Any non-failed node should be given a request response within a limited time. (Termination of the request)

P (Partition tolerance): Allows any number of messages to be lost between nodes, and messages between nodes can be completely lost when a network partition occurs.

According to CAP theory, architects should not waste their energies on how to design a perfect distributed system that satisfies the three. The strong consistency required for the ACID model of relational databases is at the expense of performance and high availability, making it difficult to partition, typically deployed as a "single live" mode, with only 1 active nodes (the "partitioning" concept in the cap theory). If strong consistency is not required, you can deploy multiple active nodes, or "live" mode.

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8B/57/wKioL1hKSl6D1hYXAABW4yupsQw273.jpg-wh_500x0-wm_3 -wmp_4-s_814207709.jpg "title=" image 3.jpg "alt=" Wkiol1hksl6d1hyxaabw4yupsqw273.jpg-wh_50 "/>

Depending on the business requirements and logical structure, load balancing schemes can be used individually or in combination to achieve satisfactory performance objectives. For example: A home appliance website, the online order module allows a small amount of data inconsistency (can be done through online queuing, etc. two times, or through the Customer Service hotline), can consider a+p mode, priority to achieve performance objectives; Online payment module must use C+a mode, ensure strong consistency, And the implementation of high-availability, message management, query statistics and other modules because do not need to respond in a timely manner, you can consider c+p mode.

# # #Alwayson优点 # #

supports up to five availability replicas. An availability replica is an instantiation of an availability group that is hosted by a specific instance of SQL Server that maintains a local copy of each availability database that belongs to this availability group.   Each availability group supports one primary replica and a maximum of four secondary replicas.

The alternative availability mode is supported, as follows: Asynchronous commit mode. This availability mode is a disaster recovery solution that is suitable for situations where the availability replicas are distributed far away. Synchronous commit mode. This availability mode emphasizes high availability and data protection in relation to performance, at the expense of increased transaction latency. A given availability group can support up to three synchronous-commit availability replicas, including the current primary replica.

Several forms of availability group failover are supported: Automatic failover, scheduled manual failover (usually referred to as "manual failover"), and forced manual failover (usually referred to as "forced failover").

With read-only connection access, a read-only connection to the replica can access and read its database while this replica is running as a secondary replica. When a replica runs as a secondary replica, a backup operation is performed on the database of the replica. By using Active Accessibility, you can better leverage your secondary hardware resources to improve IT efficiency and reduce costs. In addition, by transferring the read-intent application and the backup job to the secondary replica, you can improve performance against the primary replica.

An availability group listener that supports each availability group. An availability group listener is a server name that clients can connect to to access databases in the primary or secondary replicas of an AlwaysOn availability group. An Availability group listener directs incoming connections to the primary replica or to a read-only secondary replica. A listener provides a rapid application failover after an availability group has failed over.

About AlwaysOn-related terminology interpretation

Availability groups (availability Group): A container for a set of databases ("availability databases") that collectively implement failover.

Availability databases (availability database): Databases that belong to an availability group. For each availability database, the availability group retains a read-write copy ("primary database") and one to four read replicas ("secondary database").

Primary (primary database): read-write copy of the availability database.

Secondary (secondary database): A read-only copy of the availability database.

Availability replicas (availability replica): An instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to that availability group. There are two types of availability replicas: one primary replica and one to four secondary replicas.

Primary replica (primary replica): An availability replica makes the primary database available for read-write connections from clients, and also for sending transaction log records for each primary database to each secondary replica.

Secondary replicas (secondary replica): An availability replica that maintains a secondary replica of each availability database, acting as a potential failover target for the availability group. Alternatively, the secondary replica can support read-only access to the secondary database and support the creation of a backup of the secondary database.

Availability Group Listener (Availability Group listener): a server name that clients can connect to to access databases in the primary or secondary replicas of an AlwaysOn availability group. An Availability group listener directs incoming connections to the primary replica or to a read-only secondary replica.

Detailed reference: http://technet.microsoft.com/zh-cn/library/hh510230.aspx

AlwaysOn Failover Cluster instances

AlwaysOn failover cluster instances take advantage of the Windows server failover Clustering (WSFC) feature to provide local high availability through redundancy at the server instance level (failover cluster instance (FCI)) as part of SQL Server AlwaysOn products/Services 。 An FCI is a single instance of SQL Server installed on a Windows Server failover cluster (WSFC) node and (possibly) multiple subnets. On a network, an FCI behaves as if it were an instance of SQL Server running on a single computer, but it provides a failover from one WSFC node to another (if the current node is not available).

Detailed reference: http://technet.microsoft.com/zh-cn/library/ms189134.aspx

Overview of Alwayon availability groups

Detailed reference: http://technet.microsoft.com/zh-cn/library/ff877884.aspx

Getting started with AlwaysOn availability groups

We recommend that you configure the steps to get started when you deploy.

Detailed reference: http://technet.microsoft.com/zh-cn/library/gg509118.aspx

Reference Link: http://543925535.blog.51cto.com/639838/1341805/

This article is from the "10,000-hour Law" blog, be sure to keep this source http://daisywei.blog.51cto.com/7837970/1881161

AlwaysOn Availability Group Scenarios