SQL Server AlwaysOn Cluster Configuration Guide

1. AlwaysOn Introduction

AlwaysOn is the new comprehensive, flexible, cost-effective, high availability and disaster recovery solution provided by SQL Server 2012. It integrates the capabilities of mirroring and clustering, based on an OS failover cluster (Windows Server FailOver Cluster), by installing a stand-alone SQL Server instance on a different node of the same wsfc, defining an AlwaysOn Group, A database can deploy a maximum of 4 mirrors. When a hot standby fails, it can manually or automatically fail over, exchanging the roles of the primary and secondary databases.

The highlight of AlwaysOn is the image is readable. For OLTP applications, it is possible to transfer operations such as reports in the read operations set to Read-only's secondary libraries, significantly reducing the primary db IO, CPU, and other resource usage. Because the secondary library is a stand-alone SQL instance, tempdb operations such as creating temporary tables are not affected.

1.1. Synchronization of Availability mode commit

Synchronous-commit mode, before the primary database transaction commits, notifies the secondary database until the secondary database commits successfully, and the primary database is successfully committed.

Pros: Data is fully protected and data inconsistencies are not present.

Cons: Transaction execution time is prolonged and efficiency is reduced.

Asynchronous commit

In asynchronous-commit mode, the primary database commits the transaction independently, without waiting for the secondary database to synchronize, while the data is written to the log, and the secondary database synchronizes the data through the transaction log.

Advantage: Transaction execution time is not affected by the secondary database and is highly efficient.

Disadvantage: There is a delay in data synchronization.

* Note: We have tested SQL The efficiency of mirroring asynchronous commit and synchronous commit, in asynchronous mode, the time of delay can basically be ignored, in the case of large transaction, the delay is only in the second level. In synchronous mode, if the secondary database is abnormal, such as network connection errors, then the primary database will hang, the impact on the system is huge.

We recommend using asynchronous commit mode, considering that the report's requirements for data real-time are completely acceptable within seconds.

1.2. Failover Mode manual transfer (no data loss)

Both the primary and secondary libraries are in synchronous-commit mode, and failover is manual and the failover command is initiated by SSMS.

Automatic transfer (no data loss)

The primary and secondary libraries are synchronous-commit modes, and failover is automatic, not artificially controlled, and automatically arbitrated by WSFC.

Forced transfer (presence of data loss)

The main library is asynchronous-commit mode, and failover is manual, and the Failover command is initiated by SSMS. For some reason, the primary and secondary library data is out of sync, and you must use forced mode for failover, where data loss can occur and is typically applied to sudden disaster recovery. When both the primary and secondary library SQL instances are restored to normal from the disaster, data movement can be used to ensure the synchronization.

Availability mode and failover Mode compatibility table:

1.3. Primary and secondary database connection mode

dotNetFramework 4. 0 later versions, in order to be compatible with the new disaster recovery AlwaysOn cluster database, an attribute applicationintent is added to the connection string to identify how the application connects to the database, Applicationintent has three options:

1) Null. does not set applicationintent, which defaults to ReadWrite and is compatible with. NET 4.0 connection strings.

2) ReadWrite.

3) ReadOnly

When an application accesses a database cluster through the DNS of an AlwaysOn cluster, it first routes to the primary database and then chooses whether to route to the Read-only Secondary library based on the mode of application connection (Null, ReadWrite, ReadOnly).

? Primary database connection mode

A) allow all connections. When we set up the primary database to allow all connections, the application can connect to the database cluster at any time.

b) Allow read/write connections. When we set the primary database to allow only read/write connections, the applicationintent= readonly application connection is blocked and throws the exception "database does not allow read-only connections".

? Whether the secondary database allows read-only

a) NO. The secondary database does not allow read operations.

b) read-intent only. The secondary database is read-only and allows only readonly connections. This option means that the database can only be connected through Sqlcmd–k ReadOnly, PowerShell, or applicationintent=readonly applications. We typically use SSMS to connect to the database that is forbidden.

c) Yes. The secondary database is read-only and is compatible with previous connection methods. This option means that you can connect to the secondary database by any connection, and the secondary database is read-only.

Typical application scenarios:

2. Installation Preparation

Install the operating system cluster and MSDTC, see the SQL2008 Cluster Configuration Guide (Windows 2008).

3. Configure AlwaysOn 3.1. Start the service

Enable AlwaysOn availability groups for SQL services, and restart the SQL service. The cluster nodes are the same.

3.2. Set the database Full recovery model

On the primary database, set the database to the full recovery model

3.3. Full BACKUP Database

Full backup database, can be placed in any directory.

3.4. Setting up a shared directory

On the primary database, set the network share directory and set the Everyone writable permission. The primary database automatically backs up the transaction log to the shared directory, and the secondary library synchronizes the data via transaction log.

3.5. Create an AlwaysOn Group

Any of the availability group names, such as U9availablegroup, are specified.

Select the databases that have been backed up, and verify that the requirements are met, and only the DB that meets the requirements can be selected.

DB1 The primary database, once failover occurs as a secondary database, we also want it to be readable and set readable secondary to Yes.

Endpoint tab, default value, do not modify.

Backup policy, secondary database first.

Create listeners, listen on port 1433, and set AlwaysOn cluster IPs.

Go back to the Copy tab and click "Add a Copy".

Connect to the secondary database.

Set up secondary database readable, readable secondary=yes. Next.

Specify the shared directory set in section 3.4. Since we are going to do a cluster of libraries that exist only on DB1, we want to automatically restore an identical library on DB2 and select full. Other two items can be selected according to different circumstances.

Verify the availability group, and if an exception occurs, you must follow the prompts to fix the exception information until it succeeds.

Click Done.

All success is complete.

3.6. Setting the connection mode

On the primary database, the right-click property of the last created availability Group, AlwaysOn high availability-> availability Group, and so on.

Set as follows, Connections in Primary role all allow all connections, readable secondary all yes.

3.7. Check Read-only Routing List

Step 1: On the primary database->master database, execute the following sql:

Select * from Sys.availability_read_only_routing_lists, view the returned results as follows:

Because our AlwaysOn cluster has two node, there should be two records in the routing list. OK, check through. Otherwise perform step 2:

Step 2: Execute the following SQL on the primary database:

ALTER Availability GROUP U9availablegroup

MODIFY REPLICA on

N ' DB1 ' with

(Secondary_role (Read_only_routing_url = N ' tcp://db1.u9erp.com:1433 '));

ALTER Availability GROUP U9availablegroup

MODIFY REPLICA on

N ' DB2 ' with

(Secondary_role (Read_only_routing_url = N ' tcp://db2.u9erp.com:1433 '));

ALTER Availability GROUP U9availablegroup

MODIFY REPLICA on

N ' DB1 ' with

(Primary_role (read_only_routing_list= (' DB2 ', ' DB1 '));

* Note: U9availablegroup the name of the availability group to create; DB1 , DB2 The primary database, the secondary database name, respectively.

Check the routing List again and you should have added two records.

3.8. Check the database synchronization situation

Step 1: Check the primary database and use SSMS to connect to the primary database.

The primary database state should be synchronized and the availability database should function correctly. See the red section of the picture.

Step 2: Check the secondary library and use SSMs to connect to the secondary library.

Because we chose asynchronous commit mode, the secondary library display is synchronizing, normal. The availability database is functioning correctly.

3.9. Test Read-only Routing

We hope that when the database cluster is connected in a readonly manner, by default, the request is forwarded to the Read-only Secondary library, which can be tested by the SQLCMD command, and the following command is executed at the command line:

Step 1:sqlcmd–s [cluster dns]–e–d [cluster library name]–k ReadOnly

* Note : note-K uppercase.

Step 2:select @ @ServerName

Step 3:go

DB2 for ReadOnly Auxiliary Library, test results returned DB2, normal.

If DB1 is returned, it indicates that the secondary seobinggo is not working, check that sections 3.6 and 3.7 are set correctly.

4. SQL Server Reportservice KB

SQL Server Reportservice runs under. NET 2.0, and then installs Microsoft patch KB2654347 after you install SQL Server 2012.

Windows R2, need to install Windows 6.1 patch, Windows SP2, need to install Windows 6.0 patch, see attachments.

5. U9 Configuration

As with SQL Server 2008 configuration, add the SQL Server cluster address in the U9 Configuration management tool to connect to the database server. Query loads such as U9 reports are automatically transferred to the slave node.

SQL Server All-on is a new solution for high availability and disaster recovery. 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. This provides the efficiency of the use of the hardware.

An availability group 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 a corresponding secondary database of one to four groups. The availability group fails over at the availability replica level. Failover is not caused by database issues such as database corruption due to loss of data files or transaction log, which makes databases a suspect database.

Each set of availability databases is hosted by an availability replica. There are two types of availability replicas: one primary replica and one to four secondary replicas. The former is used to host the primary database, which hosts a set of secondary databases and acts as a potential failover target for the availability group. The primary replica makes the primary database available to read and write connections to clients. In addition, it is used in the process called "Data Synchronization" and is synchronized at the database level. 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. 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.

Alternatively, 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. Deploying an AlwaysOn Availability group requires a Windows Server failover cluster (WSFC) cluster.

The figure shows an availability group that contains the maximum number of availability replicas, one primary replica and four secondary replicas.

From: http://msdn.microsoft.com/zh-cn/library/ff877884.aspx

While always on is WSFC-based, it does not require shared storage, so the configuration is straightforward.

Here are my installation steps:

Need at least three machines (I created three VMs, one as a dc,dns server, two Nod3)

Machine name	Role	OS	IP Address
DC	Domain Controller	Windows 2008R2	192.168.1.10
Node1	Cluster Node 1	Windows 2008R2	192.168.1.11 Public 192.168.2.1 Heart jumper
Node2	Cluster Node 2	Windows 2008R2	192.168.1.12 Public 192.168.2.2 Heart jumper Form Bottom

First configure the Windows cluster:

1. Install. NETFramework 3.5.1 Features and failover clustering

2. Install Windows KB 2494036

3. Create a new cluster

4. Select a server to join the cluster:

5. Detection configuration:

6. No need to select Detect shared disk (AlwaysOn not required)

7. Start Detection:

8. Test content (You can export the report after detection is complete):

9. After entering cluster name and IP Click Next to create success, open Server Manager after success to view the cluster configuration (you can see that there is no shared disk, and the traditional cluster is still different)

Since we only use two machines, when a machine is down, there is no quorum and cannot be transferred successfully. When using multi-node quorum, three nodes can be used, so that the other two can be arbitrated after the one is down. If two node, do not use a shared disk can use the share file, the specific configuration can refer to: http://www.sqlskills.com/blogs/jonathan/ failover-clustering-without-a-san-sql-server-2012-and-smb-for-shared-storage/(This step was not previously configured, although the AlwaysOn level can be failover , but the real one node down after the fall, thank @struggle1 pointed out the problem. ）

Now that our cluster has been configured, the next step is to install SQL Server and configure always on.

Part1 We have configured CLUSTER,PART2 we installed SQL Server 2012 Evaluation version (to use 64-bit SQL Server, X86 does not support always on) and configured Alaways on Group. 1. Install as Administrator

2. Select stand-alone installation (not cluster installation)

3.SQL Server 2012 New features, you can search for the latest patches at the time of installation, will also install patches before (this is optional)

4. Rule detection

5. Select the installation component

6. Instance Name:

7. Calculate the required disk space:

8.Service account (domain account):

9. Sorting rules (you can choose according to your needs):

10. Set permissions, database file backup address, and FILESTREAM options:

11. Reboot required after installation (can view installation log):

12. Open always onhigh availability for SQL Server in ConfigurationManager (you can automatically detect the cluster name we created earlier)

The service needs to be restarted after setting the change. Now that everything is in place, we can configure always on group.

1. Create a new availability group (the Availability Group wizard can also be selected using the following selection):

2. Enter the name of the availability group:

3. Select the database in the group:

4.Replica Select Node2 (select Automatic failover/readable database):

5. Click Next, Node1 will back up the database to share folder and then revert to Node2 for synchronization (Node1-based, Node2-assisted)

The next step is to test Node2 data readable already failover. Availability Group We've already created it, now test the Node2 for reading data and failover. 1. Data test: CREATE TABLE test insert record on Node1

Access to the test database on Node2, data can be found (in the mirror is not queried, and data synchronization will not cause the NODE2 connection to break off):

2. Failover test:

Connect to Node2:

After failover (primary has become Node2):

You can see that always on group ensures high availability, has read-only access to the synchronous database, provides hardware utilization, and is a powerful feature. For more information, refer to: MicrosoftSQL Server AlwaysOn Solutions Guide for high availability and disaster recovery:http:// Msdn.microsoft.com/en-us/library/hh781257.aspx SQL Server, AlwaysOn high availability and disaster Recovery designpatterns:http://sqlcat.com/sqlcat/b/msdnmirror/archive/2011/12/22/ Sql-server-2012-alwayson-high-availability-and-disaster-recovery-design-patterns.aspx

SQL Server AlwaysOn Cluster Configuration Guide