16.1 Implementation of AlwaysOn availability groups

16.1 Implementation of AlwaysOn availability groups

The following experiment illustrates the process of configuring AlwaysOn availability groups.

The existing environment is as follows:

Server name	Role
SQLSVR1	WSFC node, default instance
SQLSVR2	WSFC node, default instance
SQLSVR3	WSFC node, default instance

Databases that need to be added to AlwaysOn availability groups are SQLDB01 and SQLDB02.

16.1.1 Prerequisites

The AlwaysOn availability groups are supported for SQL Server 2012 and subsequent products, and this feature is available only for Enterprise editions.

Node computers that join AlwaysOn availability groups must join WSFC (Windows Server Failover Cluster).

Because you need to use transaction logs to update data between nodes, the database that joins AlwaysOn availability groups must be the full recovery model.

A database initialization operation similar to log shipping, if an instance of another node does not have a database initialized, the Configuration Wizard allows a full backup of the primary database and the backup file to be placed in the shared folder. In this case, it is recommended that an instance of another node computer be started with a domain account to access the shared folder.

16.1.2 Enable HADR

Open SSCM (SQL Server Configuration Manager) and edit the properties of the instance of the database engine. On the AlwaysOn High Availability tab, display the WSFC name that the current node computer has joined, and tick the "Enable AlwaysOn availability groups" option below to allow this instance of SQL Server to be used for high-use and disaster recovery (availability and disaster Recovery, or HADR) solutions use AlwaysOn availability groups.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6E/66/wKioL1V7l53y9-QEAAEoikDQzzE912.jpg "title=" Hadr.png "alt=" Wkiol1v7l53y9-qeaaeoikdqzze912.jpg "/>

This HADR option must be enabled for each node computer's instance of the database engine.

16.1.3 Startup Wizard

Open SSMS and connect to the master node instance (that is, the primary replica) of the AlwaysOn availability Group. In the tree-like list on the left side of Object Explorer, expand AlwaysOn High Availability, and then right-click on the availability group and select New Availability Group Wizard from the context menu.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/66/wKioL1V7mI-RO7OaAAOk_bYUqYY332.jpg "title=" wizard. png "alt=" Wkiol1v7mi-ro7oaaaok_byuqyy332.jpg "/>

In the introduction page of the New Availability Group wizard, click Next to begin configuring an availability group.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6E/66/wKioL1V7mWmQUTbqAAJn6iDbZUE503.jpg "title=" 1 Introduction. png "alt=" wkiol1v7mwmqutbqaajn6idbzue503.jpg "/>

16.1.4 Configuring Availability Groups

(1) Specify a name

Specify a name for the availability group. This name is used to create a child node under the availability Groups node in SSMS.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6E/6A/wKiom1V7mqXxkIroAAFGkCQiZsg412.jpg "title=" 2 Specifies the name. png "alt=" wkiom1v7mqxxkiroaafgkcqizsg412.jpg "/>

(2) Select database

This will list all the user databases in the master node instance. For the full recovery model and a database that has done a full backup, the status will show "Meet Prerequisites".

Select a user database to join the availability group.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6E/67/wKioL1V7pivjrgFpAAGnAAorkP8986.jpg "title=" 3 Select database. png "alt=" wkiol1v7pivjrgfpaagnaaorkp8986.jpg "/>

(3) Specify a copy

On the Specify Replicas page, the Replicas tab displays only the current instance (the primary copy) by default.

Click the Add Replica button to connect to an instance of another node computer (secondary replica) and add it to the availability group.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/66/wKioL1V7nGOTzd7pAAJ1grPCJR4542.jpg "style=" float: none; "title=" 4 specifies replica 2.png "alt=" Wkiol1v7ngotzd7paaj1grpcjr4542.jpg "/>

Go to the Endpoints tab, which displays the database mirroring endpoints that have been created by the instance of each node (replica) by default. If you have not yet created an endpoint, the wizard will automatically create an endpoint named "Hadr_endpoint" for those instances, using the TCP 5022 port by default.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/66/wKioL1V7ny2wA3kEAAKL8UVnZ5w819.jpg "style=" float: none; "Title=" 4 Specifies the replica endpoint new.png "alt=" Wkiol1v7ny2wa3keaakl8uvnz5w819.jpg "/>

Go to the Backup Preferences tab.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6E/6A/wKiom1V7pd7hCuYvAALNLWZyz-Q820.jpg "title=" 4 Specify Replica Backup. png "alt=" wkiom1v7pd7hcuyvaalnlwzyz-q820.jpg "/>

Go to the Listener tab. The listener is not created for the moment.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6E/6A/wKiom1V7pfTAXMZFAAIu12_g1FI622.jpg "title=" 4 Specify Replica Listener. png "alt=" wkiom1v7pftaxmzfaaiu12_g1fi622.jpg "/>

(4) Select initial data synchronization

On the Select Initial Data synchronization page, select an option for initializing the secondary database.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/66/wKioL1V7n07TiOpgAAJ5FGUjtm0355.jpg "style=" float: none; "title=" 5 Select data synchronization. png "alt=" wkiol1v7n07tiopgaaj5fgujtm0355.jpg "/>

(5) Verification

The validation page displays the validation results for the availability group.

If you encounter a validation item that has the result displayed as failed, you can check the cause of the failure, fix it, and then go back to this page and click the Rerun Validation button.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/66/wKioL1V7n07x1EVHAALe0D7WIoM297.jpg "style=" float: none; "Title=" 6 authentication. png "alt=" wkiol1v7n07x1evhaale0d7wiom297.jpg "/>

(6) Complete the configuration

On the summary page, display the options you just configured.

There is a "script" button at the bottom right. You can click the drop-down bar to the right of it to select either the new Query edit window or the file or clipboard.

Click the Finish button to complete the configuration options and automatically jump to the progress page.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/6A/wKiom1V7nabBTn4hAAJeDbt_48U314.jpg "style=" float: none; "title=" 7 summary. png "alt=" wkiom1v7nabbtn4haajedbt_48u314.jpg "/>

The Progress page displays the progress that is currently being configured. When you are finished, you will automatically jump to the results page.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/66/wKioL1V7n1CQZT3VAAMIWvVUwZQ049.jpg "style=" float: none; "Title=" 8 progress. png "alt=" wkiol1v7n1cqzt3vaamiwvvuwzq049.jpg "/>

On the results page, click the Close button to exit the New Availability Group wizard.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/6A/wKiom1V7naeC_KRZAANNoDraNFA655.jpg "style=" float: none; "title=" 9 results. png "alt=" wkiom1v7naec_krzaannodranfa655.jpg "/>

16.1.5 using scripts

To do this, use the following script:

---You must EXECUTE the following SCRIPT in SQLCMD MODE. : Connect SQLSVR1 Use [master] GO CREATE LOGIN [Localdomain\sqlhauser] from WINDOWS GO : Connect SQLSVR2 Use [master] GO CREATE LOGIN [Localdomain\sqlhauser] from WINDOWS GO : Connect SQLSVR1 Use [master] GO CREATE ENDPOINT [Hadr_endpoint] As TCP (Listener_port = 5022) For data_mirroring (ROLE = all, encryption = REQUIRED algorithm AES) GO IF (SELECT state from sys.endpoints WHERE name = N ' hadr_endpoint ') <> 0 BEGIN ALTER ENDPOINT [hadr_endpoint] state = STARTED END GO Use [master] GO GRANT CONNECT on Endpoint::[hadr_endpoint] to [Localdomain\sqlhauser] GO : Connect SQLSVR2 Use [master] GO CREATE ENDPOINT [Hadr_endpoint] As TCP (Listener_port = 5022) For data_mirroring (ROLE = all, encryption = REQUIRED algorithm AES) GO IF (SELECT state from sys.endpoints WHERE name = N ' hadr_endpoint ') <> 0 BEGIN ALTER ENDPOINT [hadr_endpoint] state = STARTED END GO Use [master] GO GRANT CONNECT on Endpoint::[hadr_endpoint] to [Localdomain\sqlhauser] GO : Connect SQLSVR1 IF EXISTS (SELECT * from sys.server_event_sessions WHERE name= ' alwayson_health ') BEGIN ALTER EVENT SESSION [alwayson_health] on SERVER with (Startup_state=on); END IF not EXISTS (SELECT * from sys.dm_xe_sessions WHERE name= ' alwayson_health ') BEGIN ALTER EVENT SESSION [alwayson_health] on SERVER state=start; END GO : Connect SQLSVR2 IF EXISTS (SELECT * from sys.server_event_sessions WHERE name= ' alwayson_health ') BEGIN ALTER EVENT SESSION [alwayson_health] on SERVER with (Startup_state=on); END IF not EXISTS (SELECT * from sys.dm_xe_sessions WHERE name= ' alwayson_health ') BEGIN ALTER EVENT SESSION [alwayson_health] on SERVER state=start; END GO : Connect SQLSVR1 Use [master] GO CREATE availability GROUP [HAGROUP01] With (automated_backup_preference = secondary) For DATABASE [SQLDB01] REPLICA on N ' SQLSVR1 ' with (Endpoint_url = N ' tcp://sqlsvr1. localdomain.local:5022 ', Failover_mode = MANUAL, Availability_mode = asynchronous_commit, backup_priority = 50, Secondary_role (allow_connections = NO)), N ' SQLSVR2 ' with (Endpoint_url = N ' tcp://sqlsvr2. localdomain.local:5022 ', Failover_mode = MANUAL, Availability_mode = asynchronous_commit, backup_priority = 50, Secondary_role (allow_connections = NO)); GO : Connect SQLSVR2 ALTER availability GROUP [HAGROUP01] JOIN; GO : Connect SQLSVR1 BACKUP DATABASE [SQLDB01] to DISK = N ' \\SQLSVR1\HAGroup\SQLDB01.bak ' with Copy_only, FORMAT, INIT, SKIP, REWIND, Nounloa D, COMPRESSION, STATS = 5 GO : Connect SQLSVR2 RESTORE DATABASE [SQLDB01] from DISK = N ' \\SQLSVR1\HAGroup\SQLDB01.bak ' with NORECOVERY, nounload, STATS = 5 GO : Connect SQLSVR1 BACKUP LOG [SQLDB01] to DISK = N ' \\SQLSVR1\HAGroup\SQLDB01_20150613030200.trn ' with Noformat, Noinit, Noskip, REWIND, NOU Nload, COMPRESSION, STATS = 5 GO : Connect SQLSVR2 RESTORE LOG [SQLDB01] from DISK = N ' \\SQLSVR1\HAGroup\SQLDB01_20150613030200.trn ' with NORECOVERY, nounload, STATS = 5 GO : Connect SQLSVR2 --Wait for the replica to start communicating Begin try DECLARE @conn bit DECLARE @count int DECLARE @replica_id uniqueidentifier DECLARE @group_id uniqueidentifier Set @conn = 0 Set @count =--wait for 5 minutes if (serverproperty (' ishadrenabled ') = 1) and (IsNull ((select Member_state from Master.sys.dm_hadr_cluster_members where Upper (Member_name COLLATE latin1_ GENERAL_CI_AS) = Upper (CAST (SERVERPROPERTY (' Computernamephysicalnetbios ') as nvarchar) COLLATE Latin1_general_ ci_as)), 0) <> 0) and (IsNull ((select State from Master.sys.database_mirroring_endpoints), 1) = 0) Begin Select @group_id = ags.group_id from master.sys.availability_groups as AGS where name = N ' HAGroup01 ' Select @replica_id = replicas.replica_id from Master.sys.availability_replicas as replicas where upper (Replicas.replica _server_name COLLATE latin1_general_ci_as) = Upper (@ @SERVERNAME COLLATE latin1_general_ci_as) and group_id = @group_id While @conn <> 1 and @count > 0 Begin Set @conn = IsNull ((select Connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1) If @conn = 1 Begin --Exit loop when the replica is connected, or if the query cannot find the replica status Break End WAITFOR DELAY ' 00:00:10 ' Set @count = @count-1 End End End Try Begin Catch --If The wait loop fails, do not stop execution of the ALTER DATABASE statement End Catch ALTER DATABASE [SQLDB01] SET HADR availability GROUP = [HAGROUP01]; GO GO

16.1.6 Confirmation

Connect to an instance of both node computers to view the status of the SQLDB01 database.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6E/66/wKioL1V7oBuwWT4nAAS7q9eVh8w656.jpg "title=" confirm. png "alt=" Wkiol1v7obuwwt4naas7q9evh8w656.jpg "/>

This article from "SQLServer2014 series" blog, declined reprint!

16.1 Implementation of AlwaysOn availability groups