Configuring SQL Server based on Windows 2012 AlwaysOn

SQL Server AlwaysOn enhanced the original database mirroring functionality, making the previous single database failover into a group (multiple data) failover. It can support up to 9 replication partners, readability and other features such as a secondary replica server. For a group-based database, the main problem is to resolve the dependencies that exist between the applications for multiple databases, thus making a global transfer. Second, you can transfer those reports or read-only requirements to a read-only secondary replica, significantly reducing the load on the primary replica, making the primary replica more extensible, better supporting production load, and providing faster response to requests.
This document describes the Windows + SQL Server AlwaysOn configuration process based on virtual environments.

I. Configuration Environment description

Based on VMware workstation 12, 4 virtual machines, including one domain controller, 3 SQL nodes, use NAT mode, disable DHCP
To simplify the demonstration here, always install 2 nodes AlwaysOn, namely SQLnode1, SQLnode2
Machine name IP GateWay DNS
SQLDC 192.168.171.20 192.168.171.2 127.0.0.1
SQLnode1 (Master) 192.168.171.21 192.168.171.2 192.168.171.20
SQLnode2 (secondary) 192.168.171.22 192.168.171.2 192.168.171.20
SQLnode3 (secondary) 192.168.171.23 192.168.171.2 192.168.171.20
AlwaysOnWSFC 192.168.171.18

Second, configure AlwaysOn Prerequisites

[Install Windows 2012 Domain Control (For SQLServer 2014 AlwaysOn)] (http://blog.csdn.net/leshami/article/details/51180359)
   [Windows 2012 Configuration Failover (For SQLServer 2014 AlwaysOn)] (http://blog.csdn.net/leshami/article/details/51218021)
   [Install SQLserver 2014 (For AlwaysOn)] (http://blog.csdn.net/leshami/article/details/51224954)

Third, open AlwaysOn

1) Use with account to start SQL Server (2 node)
Ensure that 2 nodes are started using a domain user account

2) Enable AlwaysOn availability groups (2 nodes)
Open SQL Server Configuration Manager, locate the SQL Server service, tick enable AlwaysOn availability groups

3) Restarting SQL Server makes AlwaysOn effective (2 nodes)

4) Verify AlwaysOn on (2 node)
Right-click SQL Server Select Properties and you can see that enable Hadr is true, which indicates that AlwaysOn is in effect

5) Create a demo library and table on the master node

Here we demonstrate that we are based on SQLnode1 as the master copy
     CREATE DATABASE AlwaysonDB1;
     GO
     CREATE DATABASE AlwaysonDB2;
     GO
     USE AlwaysonDB1;
     GO
     CREATE TABLE t1 (id INT, dbname VARCHAR (20));
     INSERT INTO t1 VALUES (1, ‘AlwaysonDB1’);
     USE AlwaysonDB2;
     CREATE TABLE t2 (id INT, dbname VARCHAR (20));
     INSERT INTO t2 VALUES (1, ‘AlwaysonDB2’); 

6) Create a shared folder for backup and secondary node read backup
For example, create a shared folder

7) Assign the appropriate permissions to the shared folder

8. Backing Up the database
This step can be omitted, can be initialized at the time of SQL Server self-backup and restore
Backup database AlwaysonDB1 to disk= ' C:\AlwaysonBAK\AlwaysonDB1.BAK ';
Backup database AlwaysonDB2 to disk= ' C:\AlwaysonBAK\AlwaysonDB2.BAK ';

Iv. Configuring AlwaysOn Availability Groups

1) Use the Availability Group wizard, such as

2) Specify the availability group name

3) Select a database for the availability group, that is, which database is the current availability group

4) Add a secondary node

5) configuration transfer mode and commit mode, readable etc.
Related options items are described and can be consulted. such as replica mode, etc.

6) Endpoint configuration, leave the default
The endpoint configuration here is equivalent to the endpoint at the time of database mirroring

7) Backup Preferences
Primarily used to set the permission for that replica to have a priority backup
The configuration of the listener is ignored here and can be configured later

8) Select the data synchronization method
Full synchronization is selected here, where SQL Server automatically backs up and restores on the secondary replica

9) Verifying the configuration

10) Summary Information

11) Start building AlwaysOn available Groups

12) Build Complete

13) Verifying Availability Groups
Node 1 AS

Node 2 For example, 2 databases are displayed as synchronized

14) Availability Group Management panel
Right-click AlwaysOn High Availability, select Display panel

V. Test AlwaysOn failover

1) Select the Availability group, right click, select Failover

2) Fail-over wizard

3) Select a new primary replica

4) Connect to Replica

5) Summary information of the transfer

6) Start the failover

7) Verify the transfer results

8) Test automatic failover
The current read-write node is SQLNODE2 and the SQLNODE2 SQL Server service is restarted directly, and the available groups automatically drift to SQLNODE1. Slightly.

Configuring SQL Server based on Windows 2012 AlwaysOn