Share via

Installing SQL 2016 Always On with Windows Server 2016 Core

  • Article

<!--[if lt IE 9]>

<![endif]-->

Comments

  • Anonymous
    May 30, 2017
    Can you do the same for Exchange 2016, SC2016, Hyper-V 2016, etc....
  • Anonymous
    May 31, 2017
    Hi Kevin , I have written a similar procedure to set up System Center through Always on BAG with SQL 2016 if you want you may review it and use it anywhere you want. It has been a while since I have used it so you may need to review it #Connect to server node 1 to prepare the instance--------------------------------------------------------------------------------------------------------#Enter-PSSession "iQL-SCSQL1"$ServiceAccount="iQLab\SCSQLSVC"#Construct the full Server Name as well as the FQDN$HostName=$env:computername$HostNameFQDN=$ENV:ComputerName+"."+$ENV:USERDNSDOMAIN#Create SQL Firewall exceptionNew-NetFirewallRule -Name "SQLPorts" -DisplayName "SQL Ports" -Enabled True -Direction Inbound -Action Allow -Protocol "TCP" -LocalPort "1433"New-NetFirewallRule -Name "SQLAGListener" -DisplayName "SQLAGListener" -Enabled True -Direction Inbound -Action Allow -Protocol "TCP" -LocalPort "5022"#Import the SQL Powershell ModuleImport-Module SQLPS#Enable the SQL Always On FeatureEnable-SqlAlwaysOn -Force -Path "SQLServer:\SQL$hostname\default" #Configure the Mirroring EndpointNew-SqlHADREndpoint -Name "SCSQLBAG" -Port 5022 -Path "SQLServer:\SQL$hostname\default"#Find the endpoint in this server$Endpoint=Get-ChildItem -Path "SQLServer:\SQL$hostname\default\endpoints" | Select-Object Name -ExpandProperty Name#Enable the endpoint in this serverSet-SqlHADREndpoint "SQLServer:\SQL$hostname\default\endpoints$endpoint" -State Started#Configure the service account to connect to the EndpointInvoke-Sqlcmd -Query "CREATE LOGIN [$ServiceAccount] FROM WINDOWS "Invoke-Sqlcmd -Query "GRANT CONNECT on ENDPOINT::SCSQLBAG TO [$ServiceAccount]"#Done with this node , proceed to the nextExit-PSSession#Connect to server node 2 to prepare the instance--------------------------------------------------------------------------------------------------------#Enter-PSSession "iQL-SCSQL2"$ServiceAccount="iQLab\SCSQLSVC"#Construct the full Server Name as well as the FQDN$HostName=$env:computername$HostNameFQDN=$ENV:ComputerName+"."+$ENV:USERDNSDOMAIN#Create SQL Firewall exceptionNew-NetFirewallRule -Name "SQLPorts" -DisplayName "SQL Ports" -Enabled True -Direction Inbound -Action Allow -Protocol "TCP" -LocalPort "1433"New-NetFirewallRule -Name "SQLAGListener" -DisplayName "SQLAGListener" -Enabled True -Direction Inbound -Action Allow -Protocol "TCP" -LocalPort "5022"#Import the SQL Powershell ModuleImport-Module SQLPS#Enable the SQL Always On FeatureEnable-SqlAlwaysOn -Force -Path "SQLServer:\SQL$hostname\default" #Configure the Mirroring EndpointNew-SqlHADREndpoint -Name "SCSQLBAG" -Port 5022 -Path "SQLServer:\SQL$hostname\default"#Find the endpoint in this server$Endpoint=Get-ChildItem -Path "SQLServer:\SQL$hostname\default\endpoints" | Select-Object Name -ExpandProperty Name#Enable the endpoint in this serverSet-SqlHADREndpoint "SQLServer:\SQL$hostname\default\endpoints$endpoint" -State Started#Configure the service account to connect to the EndpointInvoke-Sqlcmd -Query "CREATE LOGIN [$ServiceAccount] FROM WINDOWS "Invoke-Sqlcmd -Query "GRANT CONNECT on ENDPOINT::SCSQLBAG TO [$ServiceAccount]" #Done with this node , proceed to the nextExit-PSSession#Connect to server node 1 and create the actual replica--------------------------------------------------------------------------------------------------------#Enter-PSSession "iQL-SCSQL1"#Arm the names and IP variables for the SQL AG Listeners.Availability groups will be named after their respective listener$PartnerServerName="iQL-SCSQL2"$SCOMDBListenerName="SCOMDB"$SCOMDWListenerName="SCOMDW"$SCVMMDBListenerName="SCVMMDB"$SCOMDBListenerIP="10.255.81.21/255.255.255.0"$SCOMDWListenerIP="10.255.81.22/255.255.255.0"$SCVMMDBListenerIP="10.255.81.23/255.255.255.0"#This is all auto :)---------------------------------------------------------------------------------------------------------------------------##Import the SQL Powershell ModuleImport-Module SQLPS#Find the current server version and full computer name$HostName=$env:computername$HostNamefqdn=$env:computername+"."+$ENV:USERDNSDOMAIN$ServerVersion = Get-Item "SQLServer:\SQL$hostname\default"#Construct the endpoint URL's$EndPointURLNode1="TCP://"+$HostNamefqdn+":5022"#Construct the second server FQDN$EndPointURLNode2="TCP://"+$PartnerServerName+"."+$ENV:USERDNSDOMAIN+":5022"#Create a template for the Availability replica$PrimarySQL=New-SqlAvailabilityReplica -AvailabilityMode SynchronousCommit -FailoverMode Automatic -EndpointUrl $EndPointURLNode1 -AsTemplate -Name $HostName -Version $ServerVersion.Version $SecondarySQL=New-SqlAvailabilityReplica -AvailabilityMode SynchronousCommit -FailoverMode Automatic -EndpointUrl $EndPointURLNode2 -AsTemplate -Name $PartnerServerName -Version $ServerVersion.Version #Create the actual AG Group for the databasesNew-SqlAvailabilityGroup -BasicAvailabilityGroup -Name $SCOMDBListenerName -AvailabilityReplica ($PrimarySQL,$SecondarySQL) -Path "SQLServer:\SQL$hostname\default" New-SqlAvailabilityGroup -BasicAvailabilityGroup -Name $SCOMDWListenerName -AvailabilityReplica ($PrimarySQL,$SecondarySQL) -Path "SQLServer:\SQL$hostname\default" New-SqlAvailabilityGroup -BasicAvailabilityGroup -Name $SCVMMDBListenerName -AvailabilityReplica ($PrimarySQL,$SecondarySQL) -Path "SQLServer:\SQL$hostname\default" #Create the listenerNew-SqlAvailabilityGroupListener -StaticIp $SCOMDBListenerIP -Name $SCOMDBListenerName -Path "SQLServer:\SQL$hostname\default\AvailabilityGroups$SCOMDBListenerName" New-SqlAvailabilityGroupListener -StaticIp $SCOMDWListenerIP -Name $SCOMDWListenerName -Path "SQLServer:\SQL$hostname\default\AvailabilityGroups$SCOMDWListenerName" New-SqlAvailabilityGroupListener -StaticIp $SCVMMDBListenerIP -Name $SCVMMDBListenerName -Path "SQLServer:\SQL$hostname\default\AvailabilityGroups$SCVMMDBListenerName" #Create a new share for DB replicationNew-Item -Path c:\Replica -ItemType Directorycmd /c "net share Replica=c:\replica /grant:Everyone,FULL"#exit the sessionExit-PSSession#Connect to server 2 to join AG GroupsEnter-PSSession -VMName "iQL-SCSQL2"$HostName=$env:computername#Enter the names of the AG Groups$SCOMDBListenerName="SCOMDB"$SCOMDWListenerName="SCOMDW"$SCVMMDBListenerName="SCVMMDB"#Join this server to the AG GroupsJoin-SqlAvailabilityGroup -Path "SQLSERVER:\SQL$HostName\Default" -Name $SCOMDBListenerNameJoin-SqlAvailabilityGroup -Path "SQLSERVER:\SQL$HostName\Default" -Name $SCOMDWListenerNameJoin-SqlAvailabilityGroup -Path "SQLSERVER:\SQL$HostName\Default" -Name $SCVMMDBListenerName