Thus far we have taken time to install SQL Server, configure the FCI portion and now time to configure the always on availability group portion. These were done in previous posts, please see the home page if you missed any of that goodness. Once again, if any formatting issues with the script, just clean up in VSC or your code editor of choice, the cut and paste within the site editor getting a little funky.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
<# ##Net New Always On Availability Group Add ###Variables $Server1=Read-Host "Enter First Server Name" $Server2=Read-Host "Enter Second Server Name" $AGName=Read-Host "Enter Availability Group Name" $AGListenerName=Read-Host "Enter Listener Name" $SQLAGPort=Read-Host "Enter SQL AAG Port" #Variable for an array object of Availability Group replicas $replicas = @() #Variable for T-SQL command $createLogin = "CREATE LOGIN [gso\adam.wisowaty] FROM WINDOWS; " $grantConnectPermissions = “GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [gso\adam.wisowaty];” #List all of the WSFC nodes all WSFC nodes; all SQL Server instances run DEFAULT instances foreach($node in Get-ClusterNode) { # Enable SQL AAG Enable-SqlAlwaysOn -ServerInstance $Server1 -Force Enable-SqlAlwaysOn -ServerInstance $Server2 -Force # Create the Availability Group endpoints New-SqlHADREndpoint -Path "SQLSERVER:\SQL\$Server1\Default" -Name "Hadr_endpoint" -Port 5022 -EncryptionAlgorithm Aes -Encryption Required New-SqlHADREndpoint -Path "SQLSERVER:\SQL\$Server2\Default" -Name "Hadr_endpoint" -Port 5022 -EncryptionAlgorithm Aes -Encryption Required # Start the Availability Group endpoint Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\$Server1\Default\Endpoints\Hadr_endpoint" -State Started Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\$Server2\Default\Endpoints\Hadr_endpoint" -State Started # Create the Availability Group replicas as template objects $replicas += New-SqlAvailabilityReplica -Name $node -EndpointUrl "TCP://$Server1.gso.lab:5022" -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -BackupPriority 60 -AsTemplate -Version 13 } # Create the Availability Group replicas as template objects $replicas += New-SqlAvailabilityReplica -Name $node -EndpointUrl "TCP://$Server2.gso.lab:5022" -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -BackupPriority 60 -AsTemplate -Version 13 } # Create the Availability Group, replace SERVERNAME with the name of the primary replica instance New-SqlAvailabilityGroup -InputObject $Server1 -Name $AGName -AvailabilityReplica $replicas -Database @("SETUP_AG") New-SqlAvailabilityGroup -InputObject $Server2 -Name $AGName -AvailabilityReplica $replicas -Database @("SETUP_AG") # Join the secondary replicas and databases to the Availability Group Join-SqlAvailabilityGroup -Path “SQLSERVER:\SQL\$Server2\Default” -Name $AGName Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$Server2\Default\AvailabilityGroups\$AGName -Database "SETUP_AG" # Create the Availability Group listener name (on the primary replica) New-SqlAvailabilityGroupListener -Name $AGListenerName -staticIP "10.4.90.199/255.255.0.0" -Port $SQLAGPort -Path "SQLSERVER:\Sql\$Server1\DEFAULT\AvailabilityGroups\$AGName" New-SqlAvailabilityGroupListener -Name $AGListenerName -staticIP "10.4.90.199/255.255.0.0" -Port $SQLAGPort -Path "SQLSERVER:\Sql\$Server2\DEFAULT\AvailabilityGroups\$AGName" |
Leave A Reply