Shulou(Shulou.com)06/01 Report--

A brief introduction to AlwaysOn

The AlwaysOn availability group is a new feature provided in SQL Server 2012 that ensures the availability of application data and enables zero data loss. AlwaysOn availability group technology combines the advantages of database clustering and database mirroring. one of the benefits of this technology is to provide non-shared storage, which can avoid the failure of the whole availability scheme caused by a single point of failure of storage.

AlwaysOn availability groups are based on the database (group) level and delimit a set of user databases (which can be one or more) into a group. Each set of availability databases is hosted by an availability copy. Availability copies include one primary copy and one to four secondary copies. The primary replica is used to host the primary database, and the secondary replica hosts a set of secondary databases and serves as a potential failover target for the availability group. The master copy makes the master database available for the read-write connection of the client, realizing the change operation to the database. Synchronization is also performed at the database level. The primary copy sends the transaction log records for each primary database to each secondary database. Each secondary copy caches transaction log records and then restores them to the corresponding secondary database. The primary database is independently synchronized with the secondary database of each connection. Therefore, a secondary database can hang or fail without affecting other secondary databases, and a primary database can hang or fail without affecting other primary databases.

A Windows Server failover cluster (WSFC) cluster is required to deploy AlwaysOn availability groups. Each availability copy of a given availability group must be on a different node of the same WSFC cluster. When you deploy AlwaysOn availability groups, one WSFC resource group is created for each availability group. The WSFC cluster monitors this resource group to determine the status between nodes in order to evaluate the health of the master replica. Fail-over occurs when a failure occurs, and arbitration for AlwaysOn availability groups is based on all nodes in the WSFC cluster, regardless of whether a given cluster node hosts any availability copies.

Users can provide client connections to the master copy of a given availability group by creating an availability group listener. The availability Group listener connects the resources of a given availability group with a DNS name to direct client connections to the appropriate availability copy.

For each available copy, the transaction commit mode supported by AlwaysOn is divided into synchronous commit mode or asynchronous commit mode. In asynchronous commit mode, the primary replica can commit the transaction without waiting for confirmation that the asynchronous commit secondary copy has been forced to write to the log. Asynchronous commit mode minimizes transaction latency on the secondary database, but allows them to lag behind the primary database, so some data may be lost. This availability mode is a disaster recovery solution that is suitable for situations where available replicas are distributed over a long distance; the synchronous commit mode means that before committing a transaction, the synchronous commit primary replica waits for the synchronous commit secondary copy to confirm that it has completed forced write to the log. Synchronous commit mode ensures that committed transactions are fully protected when a given secondary database is synchronized with the primary database. The price of this protection is to extend the transaction latency. This availability mode places more emphasis on high availability and data protection than performance, and can be used when the primary and secondary copies are close to each other to solve the problem of constant synchronization.

When the Windows cluster triggers a failover, the failover target (the original secondary replica) can take over the role of the primary replica and restore the databases it manages as the new primary database. If the original primary copy is still available after a failover, it becomes a secondary copy, and the database above it becomes a secondary database.

The form of failover is determined by the mode of both the master copy and the failover target. For automatic failover to occur between two replicas, both need to be configured in synchronous commit mode + automatic failover mode. If one of the two is configured with manual failover, automatic failover cannot occur. If one of the two is configured with asynchronous commit mode, only forced failover can occur between them.

Forced failover may result in data loss. Automatic and manual failover will ensure the security of the data. To prevent data loss, both automatic and manual failover require that the failover target is a secondary copy that uses synchronous commit mode and is in the SYNCHRONIZED state at the time. If a secondary copy that is already in the SYNCHRONIZED state issues a forced failover command, it behaves the same as when you fail over manually. For a secondary copy of asynchronous commit mode, it will always be in the SYNCHRONIZING state regardless of whether the data has been synchronized or not, so it can only support forced failover.

One of the important advantages of AlwaysOn over database mirroring is that the secondary database can be configured in readable mode, which greatly enhances the overall scalability of the database. By offloading read-only requests to the secondary database, the workload of the primary copy is reduced, the conflict between read and write can be alleviated, and the hardware resources of the secondary copy can be utilized. At the same time, through the "read-only routing" function of AlwaysOn, read-only operations can be dynamically transferred to the secondary copy. To a certain extent, transparency to end users can be achieved. With this feature, SQL Server can implement the Scale-out of the workload (multiple SQL Server respond to the workload sent by the client at the same time). When the client connection accesses the SQL Server instance using the name of Listener, the read-only routing function can automatically redirect the read-only request from the client from the primary copy to the readable secondary copy. The client application only needs to make sure that the server name of the connection is the name of Listener, regardless of which copy is responding to the request. This feature automatically offloads part of the master copy, making the master copy have more resources to handle other read and write requests.

For read-only operations to be automatically transferred "transparently" to the secondary copy, the following three issues must be solved:

The client should indicate that the operation it sent is a "read-only" operation. This decision is specified by the program developer through the ApplicationIntent keyword when writing the program, ApplicationIntent=ReadOnly,ApplicationIntent=ReadWrite

The secondary database should be configured in readable mode.

The client connection should be able to be redirected to a readable secondary copy. AlwaysOn is implemented using the read-only routing mechanism.

The original origin of this article: Jiang Jianlong's technology blog http://jiangjianlong.blog.51cto.com/3735273/1791763

II. Preparation of deployment environment

1. Deployment environment

2. Create a failover cluster and use the shared disk as the arbitration disk. If you have more than three nodes, you don't have to use the arbitration disk, but it is still the recommended way to ensure the good operation of the cluster.

3. Install SQL2012SP1 independently on both nodes, not on the cluster node.

4. Enable SQL Server Always On availability groups on both nodes

5. Create a TestDB on SQL2012-01 and set the recovery model to complete

6. Make a full backup for TestDB

7. Share the directory where the backup package is stored and set the appropriate permissions

Configure Always On availability groups

1. Use the wizard to create an availability group

2. Specify the availability group name

3. Select the database to add to the availability group

Select the databases you want to add to the available groups. These databases will fail over together as a whole. You can add up to 100 databases in a usability group. To the right of the database name, the status of the database is displayed. If the state of the database does not meet the requirements of the available groups, the database cannot be checked. The requirements to be met by the database include:

It needs to be a user database, and the system database cannot join the availability group.

The database can be read and written. Read-only databases are not allowed to join availability groups.

The database should be in multi-user mode.

The database does not use AUTO_CLOSE.

The recovery model of the database is full recovery.

The database has been fully backed up.

Does not belong to any other availability group.

Database mirroring is not configured on the database.

4. Specify a copy

Click add replica, add the second node SQL2012-02 (there can be up to 5 available replicas), and specify the mode for each available replica:

Synchronous commit mode: this mode determines whether full synchronization should be maintained between the primary and secondary copies. There can be up to 3 synchronous submitted copies.

Automatic failover mode: this mode determines whether the availability group is transferred to the specified secondary copy when the primary replica fails. Up to two available copies can be configured for automatic failover.

Readable secondary copy: this mode determines whether the copy is readable as a secondary copy.

5. Configure the endpoint

Endpoints are an important part of AlwaysOn availability groups, and they serve two main roles:

(1) Log blocks and messages are transmitted between the primary copy and the secondary copy through the endpoint to synchronize the data.

(2) the primary replica and each secondary replica determine whether they are connected to each other by sending ping to each other by the endpoint.

The configuration of the endpoint can be done by default.

6. Specify backup preferences and priorities

Preferred secondary copy: if any secondary copy is available, the backup should be performed on the secondary copy. If the master copy is the only copy that is still online, the backup is performed on the master copy.

Replica backup priority: 1 for the lowest priority and 100 for the highest priority. By default, all secondary copies have the same backup priority, and if this setting is maintained, Always On must use other factors to determine which copy to perform the backup.

7. Create a listener

Only one listener can be created for each availability group through SQL Server. Typically, only one listener is required for each availability group. The listener is a virtual network name, through which the availability group can be accessed, regardless of which node is connected, it automatically forwards the request to the primary node, when the primary node fails, the secondary node becomes the primary node, and the listener automatically listens to the primary node.

To create a listener, provide:

(1) IP address. Static IP address is recommended

(2) Network name (DNS name). Make sure the name is unique on the Internet. This name is the interface that the application uses to connect to the master copy, and it is different from the server name of any copy.

(3) Port number. You need to assign an unused port on the server. In this way, the replica instance can successfully bind and listen to this port.

8. Select initial data synchronization

Other copies get a backup of the database through this shared directory and restore it on their respective instances. This is very similar to the initialization step of log shipping. Make sure that the service account of each replica instance has appropriate read and write permissions to both the shared directory and the local directory. Also note that initializing in this way ensures that the path where the primary database file is stored on the primary copy also exists on the secondary copy.

9. Verify availability group

10. Availability group summary

11. Complete the creation of the availability group

Fourth, check after the creation of the availability group

1. Confirm the availability group in the failover cluster

The cluster resource group AlwaysOnGrp01 has been created and hosted on the SQL2012-01 node.

2. Log in to SQL2012-01 to confirm that it is the master copy.

3. Log in to SQL2012-02 to confirm that it is an auxiliary copy.

4. Log in with the listener name to confirm that you are logged in to the master copy

Read-write shunt configuration and testing

By offloading read-only requests to the secondary database, the workload of the primary copy is reduced, the conflict between read and write can be alleviated, and the hardware resources of the secondary copy can be utilized. At the same time, through the "read-only routing" function of Always On, read-only operations can be dynamically transferred to the secondary copy. To a certain extent, transparency to end users can be achieved. With this feature, SQL Server can implement the Scale-out of the workload (multiple SQL Server respond to the workload sent by the client at the same time). When the client connection accesses the SQL Server instance using the listener's name, the read-only routing function automatically redirects read-only requests from the client from the primary copy to the readable secondary copy. The client application only needs to make sure that the name of the connected server is the name of the listener, regardless of which copy is responding to the request. This feature automatically offloads part of the master copy, making the master copy have more resources to handle other read and write requests.

1. Establish the read pointer

The SQL statement is as follows:

ALTER AVAILABILITY GROUP [AlwaysOnGrp01]

MODIFY REPLICA ON

N'SQL2012-01' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://SQL2012-01.labmursql.compur1433')

ALTER AVAILABILITY GROUP [AlwaysOnGrp01]

MODIFY REPLICA ON

N'SQL2012-02' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://SQL2012-02.labmursql.complex 1433'))

2. Establish the relationship between primary and read db ur list.

Set up a corresponding readonly url list (with priority concept) for each primary on the current primary, and establish a corresponding read-only list for each server that may become a primary role. Since the following code is readonly server to each other, the priority is 1. SQL statements are as follows:

ALTER AVAILABILITY GROUP [AlwaysOnGrp01]

MODIFY REPLICA ON

N'SQL2012-02' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= ('SQL2012-01')

ALTER AVAILABILITY GROUP [AlwaysOnGrp01]

MODIFY REPLICA ON

N'SQL2012-01' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= ('SQL2012-02')

3. View relationships and priorities

The SQL statement is as follows:

Select ar.replica_server_name, rl.routing_priority

(select ar2.replica_server_name

From sys.availability_read_only_routing_lists rl2

Join sys.availability_replicas AS ar2 ON rl2.read_only_replica_id = ar2.replica_id

Where rl.replica_id=rl2.replica_id and rl.routing_priority = rl2.routing_priority

And rl.read_only_replica_id=rl2.read_only_replica_id) as' read_only_replica_server_name'

From sys.availability_read_only_routing_lists rl join sys.availability_replicas AS ar ON rl.replica_id = ar.replica_id

4. Test the connection to the secondary copy

(1) use the listener name to connect to the database

(2) specify that the database to be connected is TestDB

(3) indicate that the operation sent by the client is a "read-only" operation through the ApplicationIntent keyword ApplicationIntent=ReadOnly.

(4) successfully connect to the secondary copy

VI. Failover testing of availability groups

1. Disconnect the network card of SQL2012-01 and simulate the failure of the master copy.

2. The cluster resource group has been automatically transferred to SQL2012-02.

3. SQL2012-02 has changed from a secondary copy to a primary copy.

4. Restore the network card of SQL2012-01

5. Confirm that the SQL2012-01 warning is eliminated (still a secondary copy)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.