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

Windows 2012 always on read-write separation cluster configuration.

Introduction to Always on

AlwaysOn availability Group (SQL Server)

The AlwaysOn availability group feature is a high availability and disaster recovery solution that provides an enterprise-class alternative to database mirroring. The AlwaysOn availability group feature, introduced in SQL Server 2012, maximizes the availability of a set of user databases to the enterprise. Availability groups support a failover environment for a discrete set of user databases called availability databases that work together for failover. An availability group supports a set of reads and writes to the primary database and one to eight corresponding secondary databases. (optional) enables the secondary database to have read-only access and / or some backup operations.

Availability groups fail over at the availability replica level. Failover is not caused by database problems such as making the database suspicious due to missing data files, deleting the database, or corrupting the transaction log.

Advantages of Always on:

The AlwaysOn availability group provides a rich set of options to improve database availability and resource usage. The main components are as follows:

Up to nine availability copies are supported. An availability copy is an instantiation of an availability group hosted by a specific SQL Server instance that maintains a local copy of each availability database that belongs to this availability group. Each availability group supports one primary replica and up to eight secondary replicas

Support alternative availability modes:

Asynchronous commit mode. This availability model is a disaster recovery solution that is suitable for situations where availability replicas are distributed over long distances.

Synchronous submission mode. This availability model places more emphasis on high availability and data protection than performance, at the cost of increased transaction latency. A given availability group can support up to three simultaneous commit availability copies (including the current master copy).

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

Several forms of availability group failover are supported: automatic failover, planned manual failover (usually referred to as "manual failover"), and forced manual failover (usually referred to as "forced failover"). For more information, see failover and failover modes (AlwaysOn availability groups).

Enables you to configure a given availability copy to support one or both of the following active accessibility features:

O with read-only connection access, a read-only connection to a replica can access and read its database while the replica is running as a secondary copy. For more information, see active Secondary copy: readable Secondary copy (AlwaysOn availability Group).

O when the replica runs as a secondary copy, perform a backup operation on the database of the replica. For more information, see active secondary copy: secondary copy backup (AlwaysOn availability group).

By using activity aids, you can make better use of auxiliary hardware resources, thereby improving IT efficiency and reducing costs. In addition, it helps to improve performance for the primary copy by moving read-intended applications and backup jobs to the secondary copy.

Availability group listeners that support each availability group. The availability Group listener is a server name to which clients can connect to access databases in the primary or secondary copy of the AlwaysOn availability group. The availability group listener directs incoming connections to either the primary copy or the read-only secondary copy. Listeners provide fast application failover after availability group failover. For more information, see availability Group listeners, client connections, and Application failover (SQL Server).

Support flexible failover policies to better control availability group failover.

Automatic page repair is supported to avoid page corruption.

Supports encryption and compression, which provides a secure and high-performance mode of transmission.

Provides a set of integrated tools to simplify the deployment and management of availability groups, including:

O Transact-SQL DDL statements used to create and manage availability groups. For more information, see Overview of Transact-SQL statements for AlwaysOn availability groups (SQL Server).

The o SQL Server Management Studio tool, as follows:

§the New availability Group wizard creates and configures availability groups. In some environments, this wizard can also automatically prepare secondary databases and start data synchronization for each database. For more information, see using the New availability Group dialog box (SQL Server Management Studio).

§the add Database to availability Group wizard adds one or more primary databases to an existing availability group. In some environments, this wizard can also automatically prepare secondary databases and start data synchronization for each database. For more information, see using the add Database to availability Group wizard (SQL Server).

§add replicas to an availability group wizard adds one or more secondary replicas to an existing availability group. In some environments, this wizard can also automatically prepare secondary databases and start data synchronization for each database. For more information, see using the add replica to availability Group Wizard (SQL Server Management Studio).

§the failover availability group wizard initiates a manual failover to the availability group. Depending on the configuration and status of the secondary copy that you specify as the failover destination, the wizard can specify scheduled manual failover or force manual failover. "for more information, see using the failover availability Group Wizard (SQL Server Management Studio)."

O the AlwaysOn panel monitors AlwaysOn availability groups, availability replicas, and availability databases, and evaluates the results of AlwaysOn policies. For more information, see working with AlwaysOn panels (SQL Server Management Studio).

O the object Explorer details pane displays basic information about existing availability groups. For more information, see Monitoring availability using object Explorer details (SQLServer Management Studio).

O PowerShell cmdlet . "for more information, see AlwaysOn availability Group PowerShell Cmdlet Overview (SQL Server)."

The source of the document can be found at: https://msdn.microsoft.com/zhcn/library/hh610230(v=sql.120).aspx

Test environment:

4 Windows 2012 R2 servers.

Server function

Server IP

System

Service

AD node

10.92.37.2

Windows 2012 R2

AD

Sql 2012 nod

10.92.37.3

Windows 2012 R2

Failover + sql2012

Sql 2012 nod

10.92.37.4

Windows 2012 R2

Failover + sql2012

Sql 2012 nod

10.92.37.5

Windows 2012 R2

Failover + sql2012

I. installation failover

Always on clustering requires failover clustering support. First of all, you need to set up a failover cluster.

Install the domain control at 10.92.37.2. Open the server manager, open-manage-add roles and functions.

Check the Active Directory domain service.

Wait for all installations to complete.

Configure the domain control server. Click the button shown in the figure below.

Select "add New Forest" and enter the name of your domain in the root domain.

Enter the restore password.

Next step

Wait for the system to assign a NetBIOS name. And move on to the next step. Wait for completion and restart the server. When the restart is complete, you need to log in with a domain account. For example, jianjian\ administrator

Installation

Note: after the domain control is configured, the network card dns will be automatically changed to 127.0.0.1, or before you install the domain control, the dns of the 127.0.0.1 SQL node needs to be changed to the address of the domain control: 10.92.37.2

Second, Sql node server joins the domain.

Configure the network card of the sql node server (in the same section as the domain control) DNS as the address of the domain control. Make sure that the domain control name can be resolved.

Start my computer right-click property à change setting à à select domain and fill in the domain control name. OK, enter the administrator account and password of the domain control to join the domain. Restart the server.

After restarting, log in to the server through the domain user. For example: jianjian\ administrator

Install the failover role.

Install the failover role service on all servers of the sql node.

The installation method is all the same, and the next step is fine.

Failover disk configuration. A complete failover cluster requires arbitration disk and data disk, MSDTC disk. When the transfer occurs, the disk will also be transferred as a whole so as to play the role of data synchronization between the two sides. You can refer to the relevant documents for details.

This time we are a 3-node (odd) cluster and there is no need to create an arbitration.

Fourth, establish a failover cluster.

Next step

Next step

Next step

If you don't choose the role, go straight to the next step.

Select function-failover Cluster.

The next step goes directly to the end of the installation.

Browse to find the nodes you need to add to the cluster, and it is recommended to change the computer name for better identification.

Next step

Next step

Next step

Next step

Next step

Install sql 2012.

Sql2012 can be installed normally. It is important to note that the domain account is used when the service account is configured (see figure 1). Install sql 2012 on 3 nodes. After the installation is complete, (figure 2) opens the sql server configuration Manager-SQL Server (MSSQLSERVER)-- right-click properties. Enable always on availability groups. Restart the database service.

Figure (1)

Figure (2)

6. Configure read and write clusters.

First import your database on the master node of your choice. Reading and writing the cluster configuration requires a full backup of the database. Select the database, right-click Properties-options-adjust the recovery type to be complete.

Make a full backup of this data.

Always on cluster configuration.

Enter a name. Just do what you want.

Click add copy and use the domain account to connect to the other 2 sql nodes. Check (automatic failover) and (synchronous submission)

The secondary copy "readable secondary copy" is selected as a read-only intention. Backup preferences are fine by default.

Add a listener. You can also add it after you create it. Select a subnet and enter the specified IP address. Note that the listening port sql server is 1433

"A shared network location accessible to all copies." Set up a shared folder at any node and mount it in the other 2 (pay attention to boot mount). That's it.

All the verification results are successful. The next step is to wait for the creation to complete. After completion, the results are as follows. The word "main" can be seen on the main node. At this point, the data synchronization of three servers can be achieved (synchronization has a 1-2s delay) and failover can be achieved. When the master node fails, other nodes can automatically become the master node to take over the work.

At this time, the database cluster can not achieve the separation of read and write. Due to the limitations of always on, it is necessary to configure the read-only routing table manually, so the routing table structure is roughly the main node-corresponding to two read-only nodes. And read-only nodes have priority, and the front node is superior to the latter. Unable to implement 2 or more read-only nodes to achieve load. Actually, it's just for hot backup. The read-only routing table is implemented in the T-sql language, as follows. Changes to the relevant parameters are saved as a .sql file and executed by the sqlcmd command. Sqlcmd-S server name-U user-P password-I script

GO

ALTER AVAILABILITY GROUP [weitui_sql_wr]

MODIFY REPLICA ON Numwyn WITH 2N0I1AI1BHC'

(

SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)

);

GO

ALTER AVAILABILITY GROUP [weitui_sql_wr]

MODIFY REPLICA ON Numwyn WITH 2N0I1AI1BHC'

(

SECONDARY_ROLE (READ_ONLY_ROUTING_URL = Nipple TCPRUGUGUR 10.92.37.3 VOL1433')

);

GO

ALTER AVAILABILITY GROUP [weitui_sql_wr]

MODIFY REPLICA ON Numwyn K0VKE108NM8 'WITH

(

SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)

);

GO

ALTER AVAILABILITY GROUP [weitui_sql_wr]

MODIFY REPLICA ON Numwyn K0VKE108NM8 'WITH

(

SECONDARY_ROLE (READ_ONLY_ROUTING_URL = Nipple TCPRUGUGUR 10.92.37.6 VOL1433')

);

GO

ALTER AVAILABILITY GROUP [weitui_sql_wr]

MODIFY REPLICA ON Numwin: LF5RK32CE87' WITH

(

SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)

);

GO

ALTER AVAILABILITY GROUP [weitui_sql_wr]

MODIFY REPLICA ON Numwin: LF5RK32CE87' WITH

(

SECONDARY_ROLE (READ_ONLY_ROUTING_URL = Nipple TCPRUGUGUR 10.92.37.5 VOL1433')

);

GO

ALTER AVAILABILITY GROUP [weitui_sql_wr]

MODIFY REPLICA ON Numwyn WITH 2N0I1AI1BHC'

(

PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= (Native WINmurk K0VKE108NM8)

);

GO

ALTER AVAILABILITY GROUP [weitui_sql_wr]

MODIFY REPLICA ON Numwyn K0VKE108NM8 'WITH

(

PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= (Numwink LF5RK32CE87'))

);

GO

ALTER AVAILABILITY GROUP [weitui_sql_wr]

MODIFY REPLICA ON Numwin: LF5RK32CE87' WITH

(

PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= (Numbwink K0VKE108NM8))

);

GO

Verify that the routing table is read and written. Use the same command to verify.

Select a.corrective cc. Isoblocalrec. Rolebrush local.rolefugitive pr. Endpointencoding url. Readreadable onlyordered routingcalendar url.

From sys.dm_hadr_availability_replica_cluster_nodes a

Join sys.dm_hadr_availability_replica_cluster_states b

On a.replica_server_name = b.replica_server_name

Join sys.dm_hadr_availability_replica_states c

On b.replica_id = c.replica_id

Join sys.availability_replicas d

On c.replica_id = d.replica_id

Test whether the read-only route works. Parameters such as ApplicationIntent = ReadOnly need to be passed during the Always on cluster web link. The test method is as follows.

Click the link after completing the above operations. If you can connect to a read-only node and conform to the order of the read-only routing table, the configuration is complete.

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.