SQL Server->> High Availability and disaster recovery (HADR) technology-AlwaysOn Availability Group setup for always on (actual combat)

For space reasons, AlwaysOn availability groups are split into two parts: theoretical and actual. The actual combat component was then disassembled for preparation and the AlwaysOn availability Group was built.

Three articles of the respective links:

SQL Server->> high Availability and disaster recovery (HADR) technology-AlwaysOn (theory)

SQL Server->> High Availability and disaster recovery (HADR) technology--AlwaysOn (actual combat) to establish Active Directory domains, DNS servers, and Windows failover clusters (prepare for work)

SQL Server->> High Availability and disaster recovery (HADR) technology-AlwaysOn Availability Group setup for always on (actual combat)

The previous two articles describe the readiness for AlwaysOn and before you build AlwaysOn. This article is also the theme of this whole series, which is to build AlwaysOn availability groups.

This article is primarily a step-by-step process from how to start AlwaysOn, configure AlwaysOn availability groups, some other Windows-level configurations (such as firewalls), and several validation examples to verify AlwaysOn functionality (failover, read-only routing, read replicas, and so on). The environment here is based on the environment of the first two articles.

Setting up environmental information

Domain Name: jerrychen.com

AlwaysOn Virtual IP Address: 192.168.2.200

WSFC Virtual IP Address: 192.168.2.201

WSFC Cluster Name: Aocluster

	Domain Controller	Primary Replica	Secondary Replica
Server Name	Dc.jerrychen.com	Main.jerrychen.com	Slave1.jerrychen.com
OS	Windows Server Data Center x64	Windows Server Data Center x64	Windows Server Data Center x64
IP Address	192.168.2.100	192.168.2.102	192.168.2.101
Gateway	192.168.2.2	192.168.2.2	192.168.2.2
SQL Server Version	-	SQL Server Enterprise x64	SQL Server Enterprise x64
Dns	127.0.0.1	192.168.2.100	192.168.2.100

Installing the SQL Server instance and configuring the SQL Server service account

The first is to install SQL Server Enterprise x64 separately in main and Slave1. Install by default in stand-alone mode, not in detail here. This keeps the default instance name.

Configure DomainAdmin as service account after installation is complete

Enable always availability groups

Then open the SQL Server service's properties interface in SQL Server Configuration management. The AlwaysOn High Availability option Check the Enable AlwaysOn availability groups option.

We use the SQL Server database is the most commonly used demo database-AdventureWorks to serve as the experimental database it. The source of the database itself Google or Baidu search AdventureWorks2012 find connection download.

Configuring firewalls

Since AlwaysOn requires nodes to communicate with each other, we need to configure the firewall on each node to ensure that the SQL Server application can communicate with the external TCP/IP

Configure the service account to ensure that it has sufficient permissions to operate

If the service account is not a sysadmin member, add it as a member of the sysadmin role

Prerequisites to meet all AlwaysOn availability Group creation

Database Full Recovery Model

Have a full backup history

Creation of an availability group

Create through Wizards

Configure the Availability group name

If you do not meet the previous conditions for creating an availability group, such as a full backup or if the database is read-only or single-user mode, the status here will prompt you. Prerequisites for meeting here.

Add the Slave1 as a secondary replica. Tick synchronous commit (synchronous commit), up to 3 means that there can be a maximum of 3 secondary copies of the synchronous-commit mode.

Whether the primary replica or the secondary replica chooses synchronous-commit mode, the readable secondary of the secondary replica is selected as Yes. Only for the later read-only secondary database preparation.

AlwaysOn and mirroring all use endpoint (endpoints) for data transfer. AlwaysOn uses endpoints to communicate with the secondary replica for log transfer and heartbeat lines.

Backup priority tick prefer secondary. This means that limited consideration is given to backup on the secondary replica. The primary replica is used only if there is no secondary replica. The secondary replica's priority level is set to 100, while the primary replica is 50.

The last page is listener (listener). Here you need to configure the virtual server name, port number, and IP address of the listener. This is what is written in the previous schema environment information.

Click Yes to go to the next page

This place is the way to choose to initialize the database. If you choose full, you need to provide a shared address, AlwaysOn automatically backs up the database and then restores it to the secondary replica of the target. Here we choose join only, so we need to back up the database and restore it to the target's secondary database beforehand.

The restore process needs to be accompanied by a with NORECOVERY, followed by no error.

Confirm that no error message appears

Successfully added main and SLAVE1 to the availability group

Verify that availability group creation is successful or not

This time you will find that the virtual server name you configured earlier appears on the DNS server

Also appears on the domain controller

Observe the operation of the copy through dashboard. Is the availability group in the healthy state? is the replica in a synchronized state?

Arbitration information

Verify that read-only access to the secondary database is successful

The SSMs Object Browser can display the availability groups we have created, including the primary and secondary replicas on the server.

This time you try to use SSMS to open a query window and add the APPLICATIONINTENT=READONLY option to connect to the SLAVE1 server before connecting. Try to access any one of the data tables. Can be read-only access.

Then switch to the virtual server name to see if it will be redirected to SLAVE1.

The result can be either open or on main.

Read-only routing feature

Actually, because we haven't configured read-only routing yet.

ALTERAvailabilityGROUP [ag_adventureworks2012]MODIFY REPLICA onN'MAIN'  with(Secondary_role (allow_connections=  All));ALTERAvailabilityGROUP [ag_adventureworks2012]MODIFY REPLICA onN'MAIN'  with(Secondary_role (Read_only_routing_url=N'tcp://main.jerrychen.com:1433'));ALTERAvailabilityGROUP [ag_adventureworks2012]MODIFY REPLICA onN'SLAVE1'  with(Secondary_role (allow_connections=read_only));ALTERAvailabilityGROUP [ag_adventureworks2012]MODIFY REPLICA onN'SLAVE1'  with(Secondary_role (Read_only_routing_url=N'tcp://slave1.jerrychen.com:1433'));ALTERAvailabilityGROUP [ag_adventureworks2012]MODIFY REPLICA onN'MAIN'  with(Primary_role (read_only_routing_list=('SLAVE1','MAIN')));ALTERAvailabilityGROUP [ag_adventureworks2012]MODIFY REPLICA onN'SLAVE1'  with(Primary_role (read_only_routing_list=('MAIN','SLAVE1')));GO

Run the following code to see if the previous configuration was successful

Select     Rp.replica_server_name,         as  readonly_replica_server_name,         rl.routing_priority   from sys.availability_read_only_routing_lists RL        joinon=  rp.replica_id        joinon= rp2.replica_id

SSMs doesn't really support this applicationintent=readonly. For testing purposes, we use SSRS reports to test. To create an SSRS report, the DataSource of the report's DataSet uses the following connection string, which is the addition of applicationintent=readonly. Then look at the server name.

A preview of what you can find is actually redirected to read-only access to the secondary replica on the database.

Verifying the data synchronization situation

Lab 1: A simple test

We turn off the network card of the secondary replica, then create a table on the primary and insert a single row of records

You can see that SLAVE1 is in a disconnected state from the primary replica dashboard. The synchronization status of the SLAVE1 is not SYNCHRONIZING.

Then we re-boot the NIC and query the SLAVE1 for the table we just created on main. The data has been synced over.

Lab 2: Concurrency test

Create a second table on main. This time, however, we open two sessions without interruption to this table inserting data. In the two sessions without interruption of data insertion, we then cut off the SLAVE1 network card.

Session 1:

Session 2

SLAVE1 the network card cut-off port we stop the database insert operation for both sessions. Then observe how many rows of records are inserted altogether.

Re-enable SLAVE1 's NIC, and see if it syncs? And the data are consistent. As you can see, the copy will automatically "keep up"-redo the end of the log-when it is restored-this is true.

Test the secondary replica failover

Since AlwaysOn's main function is failover. Here is the test under the SLAVE1 network card disconnect, main will take the read-only access to live?

When you can see that the SSRS report is refreshed again, the destination server that you access does become main.

Test the primary replica failover

In turn, it is also the most important test: the primary replica failover. Disconnect the primary replica NIC.

Refresh the report again. The server that found the target has changed.

Observing the dashboard on SLAVE1, we can see that the role of SLAVE1 is now being promoted to primary.

Of course, it is more important for us to test whether the original secondary replica can accept data writes after failover. We use AGVM this virtual server name to open a connection after you create a new table and insert a record to verify that the data can be written.

Let's restart the main NIC again. At this time, AlwaysOn will wait for main to rejoin the cluster node and then re-become a secondary replica.

Let's go back to the main database and see if the table created on SLAVE1 was synced over the time it was offline.

One thing I don't understand is that after failover, SLAVE1 's character is unknown.

Some people will ask, what if we want to switch the auxiliary copy back to the primary copy now? Then manual failover.

Main is re-made into the primary replica

Summarize:

All right. This is the end of the last article in the AlwaysOn series. Finally, here is a small summary: AlwaysOn as a major feature of SQL Server 2012, set the advantages of the previous version of the HADR technology in one, it really makes people bright. It also does not lose its nickname as the preferred technology for SQL Server 2012 after Hadr. Experience it and see the differences and similarities between failover Cluster, mirroring, and log shipping, among other things. I think it is more meaningful to the product that SQL Server HADR technology is not always a technology to meet the company's business needs, often combined with other HADR technology to achieve the best solution. and the presence of AlwaysOn today is a better solution. Provides powerful functionality when designing high-availability solutions.

Reference:

SQL Server 2012 Implementation and Management Guide

Configure read-only Routing for an availability Group (SQL Server)

AlwaysOn Client Connectivity (SQL Server)

Creation and Configuration of availability Groups (SQL Server)

Use the availability Group Wizard (SQL Server Management Studio)

AlwaysOn Architecture guide:building A high availability and disaster Recovery solution by Using Failover Cluster Instanc Es and Availability Groups

SQL Server->> High Availability and disaster recovery (HADR) technology-AlwaysOn Availability Group setup for always on (actual combat)