Installing SQL 2016 Always On with Windows Server 2016 Core


<!--[if lt IE 9]>

<![endif]-->


Comments (2)

  1. Can you do the same for Exchange 2016, SC2016, Hyper-V 2016, etc….

  2. 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 exception
    New-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 Module
    Import-Module SQLPS
    #Enable the SQL Always On Feature
    Enable-SqlAlwaysOn -Force -Path “SQLServer:\SQL\$hostname\default”
    #Configure the Mirroring Endpoint
    New-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 server
    Set-SqlHADREndpoint “SQLServer:\SQL\$hostname\default\endpoints\$endpoint” -State Started

    #Configure the service account to connect to the Endpoint
    Invoke-Sqlcmd -Query “CREATE LOGIN [$ServiceAccount] FROM WINDOWS ”
    Invoke-Sqlcmd -Query “GRANT CONNECT on ENDPOINT::SCSQLBAG TO [$ServiceAccount]”

    #Done with this node , proceed to the next
    Exit-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 exception
    New-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 Module
    Import-Module SQLPS
    #Enable the SQL Always On Feature
    Enable-SqlAlwaysOn -Force -Path “SQLServer:\SQL\$hostname\default”
    #Configure the Mirroring Endpoint
    New-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 server
    Set-SqlHADREndpoint “SQLServer:\SQL\$hostname\default\endpoints\$endpoint” -State Started

    #Configure the service account to connect to the Endpoint

    Invoke-Sqlcmd -Query “CREATE LOGIN [$ServiceAccount] FROM WINDOWS ”
    Invoke-Sqlcmd -Query “GRANT CONNECT on ENDPOINT::SCSQLBAG TO [$ServiceAccount]”

    #Done with this node , proceed to the next
    Exit-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 Module
    Import-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 databases
    New-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 listener
    New-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 replication
    New-Item -Path c:\Replica -ItemType Directory
    cmd /c “net share Replica=c:\replica /grant:Everyone,FULL”

    #exit the session
    Exit-PSSession

    #Connect to server 2 to join AG Groups
    Enter-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 Groups
    Join-SqlAvailabilityGroup -Path “SQLSERVER:\SQL\$HostName\Default” -Name $SCOMDBListenerName
    Join-SqlAvailabilityGroup -Path “SQLSERVER:\SQL\$HostName\Default” -Name $SCOMDWListenerName
    Join-SqlAvailabilityGroup -Path “SQLSERVER:\SQL\$HostName\Default” -Name $SCVMMDBListenerName

Skip to main content