• ESXTOP
  • About
  • Guides
    • SQL Server 2016 Scripted Powershell Installation
    • Powershell Failover Cluster Install – SQL Server AAG or WSFC
    • AAG Add – Powershell
  • Travel Tips

AAG Add – Powershell

October 5, 2019

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.

PowerShell
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"

Share

Microsoft  / Powershell  / SQL Server  / Uncategorized

Leave A Reply


Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *




© Copyright LetsBlog Theme Demo - Theme by ThemeGoods