Installing SQL 2016 Always On with Windows Server 2016 Core


imageimage

 

This will be a simple walk through of installing two Windows Server 2016 Core servers, then installing SQL 2016, and setting up SQL Always On replication between them.  This is meant for lab testing and getting familiar with the scenario.  This setup is incredibly simple and straightforward, and fast.  You can have this scenario up and running in just a few minutes.

 

First, deploy two VM’s.  Nothing fancy (2GB RAM, 2 vCPU’s, 1 disk) is fine for a lab deployment.

I will name mine:  SQLCORE1 and SQLCORE2.

Install Windows Server 2016, and choose the default option of Windows Server Core (no GUI):

image

 

When the install is complete, log in by creating a password.  You are now ready to begin configuration.

From the command line, run PowerShell.

We will configure static IP’s and DNS on each server.  Change these to match your lab:

New-NetIPAddress -InterfaceAlias "Ethernet" -IPAddress 10.10.10.60 -PrefixLength 24 -DefaultGateway 10.10.10.1 Set-DnsClientServerAddress -InterfaceAlias "Ethernet" -ServerAddresses 10.10.10.10,10.10.10.11

 

Next – we will join the domain and rename the computer when prompted.  Type “sconfig” and press enter.

image

 

From the menu – choose “1”.  Choose Domain, and provide your domain and domain credentials to be able to join.

When prompted, choose “Yes” to change the computer name.  Provide the new computername you want for your SQL core servers.  Mine are SQLCORE1 and SQLCORE2.

Reboot when prompted.

You must log in as the local administrator after the reboot.  Then, type “logoff” and hit enter.  Now you can log in as your domain admin account in the domain.  Hit ESC to get back to “other user” and log in as a domain account.

Add the domain group for your SQL admin’s to the local administrators group at the command prompt:

net localgroup administrators /add OPSMGR\SQLAdmins

 

At this point you can log in as one of your SQL Administrator accounts, or continue the installation as your domain admin account.

Map a drive to your SQL 2016 installation media:

Net use Y: \\server\software\sql\2016\ENT

 

Install SQL server from the command line.  There are two ways to install SQL.  From a command line with options, or from an INI file.  The INI file is much more powerful, but to keep things simple we will use a command line here.  This basic install will cover the SQL database engine, the Full-text service, and set the SQL agent service to run as automatic startup.  You will need to change your domain group for the SQL admins, and your SQL service account and password.

Setup.exe /qs /ACTION=Install /FEATURES=SQLEngine,FullText /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="OPSMGR\sqlsvc" /SQLSVCPASSWORD="password" /SQLSYSADMINACCOUNTS="OPSMGR\sqladmins" /AGTSVCACCOUNT="OPSMGR\sqlsvc" /AGTSVCPASSWORD="password" /AGTSVCSTARTUPTYPE=Automatic /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS

 

The SQL setup will begin and you will see some UI’s pop up along with progress in the command line window….  when complete you will be returned to a command prompt.

Now that SQL is installed – reboot each server.

Log back in with a domain account to continue setup and configuration.

Next, we will configure the firewall.  We will open the necessary ports for SQL and Always On, and then enable the built in group rules for remote administration.

Run PowerShell.

Copy and paste the following to configure the firewall:

New-NetFirewallRule -Group "Custom SQL" -DisplayName "SQL Default Instance" -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow New-NetFirewallRule -Group "Custom SQL" -DisplayName "SQL Admin Connection" -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow New-NetFirewallRule -Group "Custom SQL" -DisplayName "SQL Always On VNN" -Direction Inbound -Protocol TCP -LocalPort 1764 -Action allow New-NetFirewallRule -Group "Custom SQL" -DisplayName "SQL Always On AG Endpoint" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action allow Enable-NetFirewallRule -DisplayGroup "Remote Desktop" Enable-NetFirewallRule -DisplayGroup "Remote Event Log Management" Enable-NetFirewallRule -DisplayGroup "Remote Service Management" Enable-NetFirewallRule -DisplayGroup "File and Printer Sharing" Enable-NetFirewallRule -DisplayGroup "Performance Logs and Alerts" Enable-NetFirewallRule -DisplayGroup "Remote Volume Management" Enable-NetFirewallRule -DisplayGroup "Windows Firewall Remote Management"

 

Next, we will install the Windows Failover Cluster feature, a prerequisite for SQL Always On.

Install-WindowsFeature Failover-Clustering –IncludeManagementTools

image

 

Next – we will create a cluster.  You can create a simple failover cluster between two nodes in a single line of PowerShell!  You will need to change your cluster name, IP address, and node names to match your configuration.  Only run this on ONE NODE! 

(This step assumes you are running this as a domain admin, as this will create a computer account in the domain for the virtual cluster computer.  If you do not wish to run this as a domain admin, you must pre-stage that account and assign permissions.  See cluster documentation for this)

New-Cluster –Name SQLCORECL1 –StaticAddress 10.10.10.62 –Node SQLCORE1,SQLCORE2

You might see a warning at this point.  That’s fine – they are likely just because we have a single NIC in each VM, and because we didn’t configure a witness.

image

 

Next, we need to enable each server to support SQL Always On.  You will need to provide your SERVERNAME\INSTANCENAME.  If you use the default instance like we did above, input just the servername.  Do this on each node, but change the servername to match the correct node name you are running it on.

$ServerInstance = 'SQLCORE1' #this should be in format SERVERNAME\INSTANCENAME or just use servername for default instance Enable-SqlAlwaysOn -ServerInstance $ServerInstance -Force

 

Lastly – we need to configure the Always On availability group.  This is easiest done manually, via SQL management studio from a remote tools machine.

Launch SQL management studio and connect to the SQLCORE1 server:

image

 

First, we need to create a “dummy” database ONLY on SQLCORE1 which is required to configure and test Always On.  Go to Databases, right click, and choose New Database.  Name the Database “TESTDB” and click OK.

image

 

Before we can use a database in Always On, it must have at least one previous backup.  Right click TESTDB, tasks, Back Up.  Hit OK to accept defaults, and OK when backup is done.

Now expand “Always On High Availability”, and Right Click “Availability Groups” and choose “New Availability Group Wizard”

image

 

Assign an AG name.  This isn't terribly important.  I will use “SQLCOREAG1” and click Next.

image

 

Select your TestDB and click Next.

image

 

Add a replica, and choose your other server, SQLCORE2.  Check the boxes next to Auto failover and Synchronous commit on both servers.

image

 

On the Listener tab, create an Availability group listener.  I will use “SQLCOREAGL1”  We will use port 1764 (which we set up a previous Firewall rule for).  You will need to scroll down to the bottom right and click “Add” to add in an IP address, and click Next.

(This step assumes you are running this as a domain admin, as this will create a computer account in the domain for the virtual availability group listener.  If you do not wish to run this as a domain admin, you must pre-stage that account and assign permissions.  See SQL Always On documentation for this)

image

 

Next, you will choose FULL synchronization, and provide a network share where the servers have read and write access to.

image

 

This will run the tests:

image

 

Click “Finish” and you should have success!

image

 

Go into SQL Management studio and look over your configuration:

image


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