16.2 Adding nodes and databases

16.2 Adding nodes and databases

16.2.1 adding nodes

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/6B/wKiom1V7rE6wAehJAARwYiEfMtg437.jpg "style=" float: none; "title=" Add replica 1.png "alt=" Wkiom1v7re6waehjaarwyiefmtg437.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/6B/wKiom1V7rE6yBUESAAI-zExe0rE933.jpg "style=" float: none; "title=" Add replica 2.png "alt=" Wkiom1v7re6ybuesaai-zexe0re933.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/67/wKioL1V7rfiDlZroAAHRRA1ZYds433.jpg "style=" float: none; "title=" Add replica 3.png "alt=" Wkiol1v7rfidlzroaahrra1zyds433.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/67/wKioL1V7rfnCxyV9AAKcNaCVHzg811.jpg "style=" float: none; "title=" Add replica 4.png "alt=" Wkiol1v7rfncxyv9aakcnacvhzg811.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/6B/wKiom1V7rFDgRwoJAAKDtUVkUZw742.jpg "style=" float: none; "title=" Add replica 5.png "alt=" Wkiom1v7rfdgrwojaakdtuvkuzw742.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/6B/wKiom1V7rFCRtI5cAALCruNbSgA412.jpg "style=" float: none; "title=" Add replica 6.png "alt=" Wkiom1v7rfcrti5caalcrunbsga412.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/67/wKioL1V7rfrBUPjcAAJTnBtze0w483.jpg "style=" float: none; "title=" Add replica 7.png "alt=" Wkiol1v7rfrbupjcaajtnbtze0w483.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/67/wKioL1V7rfuxxt6pAAL0F1LlN_o938.jpg "style=" float: none; "title=" Add replica 8.png "alt=" Wkiol1v7rfuxxt6paal0f1lln_o938.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/6B/wKiom1V7rFKyRVCHAAL3x2DmbYQ802.jpg "style=" float: none; "title=" Add replica 9.png "alt=" Wkiom1v7rfkyrvchaal3x2dmbyq802.jpg "/>

To do this, the script is:

---You must EXECUTE the following SCRIPT in SQLCMD MODE. : Connect SQLSVR3 Use [master] GO CREATE LOGIN [Localdomain\sqlhauser] from WINDOWS GO : Connect SQLSVR1 Use [master] GO GRANT CONNECT on Endpoint::[hadr_endpoint] to [Localdomain\sqlhauser] GO Use [master] GO GRANT CONNECT on Endpoint::[hadr_endpoint] to [Localdomain\administrator] GO : Connect SQLSVR2 Use [master] GO GRANT CONNECT on Endpoint::[hadr_endpoint] to [Localdomain\sqlhauser] GO Use [master] GO GRANT CONNECT on Endpoint::[hadr_endpoint] to [Localdomain\administrator] GO : Connect SQLSVR3 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 SQLSVR3 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 ALTER availability GROUP [HAGROUP01] ADD REPLICA on N ' SQLSVR3 ' with (Endpoint_url = N ' Tcp://sqlsvr3. localdomain.local:5022 ', Failover_mode = MANUAL, Availability_mode = asynchronous_commit, backup_priority = 50, Secondary_role (allow_connections = NO)); GO : Connect SQLSVR3 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 SQLSVR3 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_20150613040532.trn ' with Noformat, Noinit, Noskip, REWIND, NOU Nload, COMPRESSION, STATS = 5 GO : Connect SQLSVR3 RESTORE LOG [SQLDB01] from DISK = N ' \\SQLSVR1\HAGroup\SQLDB01_20150613040532.trn ' with NORECOVERY, nounload, STATS = 5 GO : Connect SQLSVR3 --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.2.2 Adding a database

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/67/wKioL1V7r9-juipnAASCPUR8zRY693.jpg "style=" float: none; "title=" Add Database 1.png "alt=" Wkiol1v7r9-juipnaascpur8zry693.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/6B/wKiom1V7rjehGVV-AAJexe9urYY787.jpg "style=" float: none; "title=" Add Database 2.png "alt=" Wkiom1v7rjehgvv-aajexe9uryy787.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/6B/wKiom1V7rjjzc_5FAAHGTMK9d24617.jpg "style=" float: none; "title=" Add Database 3.png "alt=" Wkiom1v7rjjzc_5faahgtmk9d24617.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/67/wKioL1V7r-LyeQLNAAKIqi7cA3M586.jpg "style=" float: none; "title=" Add Database 4.png "alt=" Wkiol1v7r-lyeqlnaakiqi7ca3m586.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/67/wKioL1V7r-Ox862IAAKIqi7cA3M893.jpg "style=" float: none; "title=" Add Database 5.png "alt=" Wkiol1v7r-ox862iaakiqi7ca3m893.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/6B/wKiom1V7rjuzZ78DAAPTNXoyV3o009.jpg "style=" float: none; "title=" Add Database 6.png "alt=" Wkiom1v7rjuzz78daaptnxoyv3o009.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/6B/wKiom1V7rjywO21jAAGuG8dqLzw144.jpg "style=" float: none; "title=" Add Database 7.png "alt=" Wkiom1v7rjywo21jaagug8dqlzw144.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/67/wKioL1V7r-awy06GAAL-pPyuJd4668.jpg "style=" float: none; "title=" Add Database 8.png "alt=" Wkiol1v7r-awy06gaal-ppyujd4668.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/67/wKioL1V7r-eQSoeQAALw2y7hTV0338.jpg "style=" float: none; "title=" Add Database 9.png "alt=" Wkiol1v7r-eqsoeqaalw2y7htv0338.jpg "/>

To do this, the script is:

---You must EXECUTE the following SCRIPT in SQLCMD MODE. : Connect SQLSVR1 Use [master] GO ALTER availability GROUP [HAGROUP01] ADD DATABASE [SQLDB02]; GO : Connect SQLSVR1 BACKUP DATABASE [SQLDB02] to DISK = N ' \\SQLSVR1\HAGroup\SQLDB02.bak ' with Copy_only, FORMAT, INIT, SKIP, REWIND, Nounloa D, COMPRESSION, STATS = 5 GO : Connect SQLSVR2 RESTORE DATABASE [SQLDB02] from DISK = N ' \\SQLSVR1\HAGroup\SQLDB02.bak ' with NORECOVERY, nounload, STATS = 5 GO : Connect SQLSVR3 RESTORE DATABASE [SQLDB02] from DISK = N ' \\SQLSVR1\HAGroup\SQLDB02.bak ' with NORECOVERY, nounload, STATS = 5 GO : Connect SQLSVR1 BACKUP LOG [SQLDB02] to DISK = N ' \\SQLSVR1\HAGroup\SQLDB02_20150613041128.trn ' with Noformat, Noinit, Noskip, REWIND, NOU Nload, COMPRESSION, STATS = 5 GO : Connect SQLSVR2 RESTORE LOG [SQLDB02] from DISK = N ' \\SQLSVR1\HAGroup\SQLDB02_20150613041128.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 [SQLDB02] SET HADR availability GROUP = [HAGROUP01]; GO : Connect SQLSVR3 RESTORE LOG [SQLDB02] from DISK = N ' \\SQLSVR1\HAGroup\SQLDB02_20150613041128.trn ' with NORECOVERY, nounload, STATS = 5 GO : Connect SQLSVR3 --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 [SQLDB02] SET HADR availability GROUP = [HAGROUP01]; GO GO

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

16.2 Adding nodes and databases