While I was attending the recent “Manchester Microsoft Teams and Skype for Business User Group” led by Martin Boam (www.ucmart.uk) earlier in January 2019, Dave Murphy from Microsoft gave a presentation on Upgrading to Skype for business 2019 “on a shoestring” & then add resiliency!
While talking about adding resiliency we touched on SQL AlwaysOn. SQL Mirroring is deprecated in Skype for Business 2019 so what options are we left with? The traditional “Always On Failover Clusters” and “AlwaysOn Availability Groups”. The most popular option is “AlwaysOn Availability Groups”. Depending what edition of SQL you have would depend on what option you go for. For example, SQL 2016 Standard edition does not have the option to create availability groups where Enterprise edition does.
An in-depth overview of Always On Availability Groups can be found here.
I decided to give it a go! For this post I am using two SQL Server 2016 Enterprise Edition servers (Evaluation) and Skype for Business 2015 on Windows Server 2016. I found an old MS post online for configuring SQL 2014, there are some differences but only subtle to the configuration.
Lets get started!
Pre-Requisites and Failover Clustering
To get started we need to install the Windows Server Pre-Requisites and restart the server.
Add-WindowsFeature Net-Framework-Core, Failover-Clustering, RSAT-Clustering-Mgmt,RSAT-Clustering-PowerShell -Source d:\sources\sxs
Once the servers have been rebooted we can then go ahead and create the Failover cluster that will underpin our Availability Group.
New-Cluster -Name SQLCluster -Node GBSQL01, GBSQL02 -NoStorage -StaticAddress 172.16.0.25
Set-ClusterQuorum -Cluster SQLCluster -NodeAndFileShareMajority “\\gboos01.exchdemo.com\SQLClusterWitness”
Once these have completed we can proceed with the installation of SQL.
Select “New SQL Server Stand-alone installation or add features to an existing installation”.
Proceed through the first couple of screen accepting the defaults.
On the features screen select “Database Engine Services”.
On the “Instance Configuration” you could use the Default Instance, but I have changed it for the purpose of keeping it unique. The instances must be called the same on both servers.
On the “Server Configuration” page change the credentials to an admin account, for my lab I have a created a service account called srv_sql, I also changed the startup type to automatic.
Under “Database Engine Configuration” add the admin account and press next to start the installation.
Once the installation has completed, open the SQL Server Configuration Manager, right click SQL Server and select properies. “On the AlwaysOn High Availability” Tab and tick the box “Enable AlwaysOn Availability Groups”, do this for both SQL servers in our group.
Next we need to create a DNS A record for the AlwaysOn Listener, note this is different to the cluster IP address we used above.
Configure Skype for Business and Availability Group
Open the Skype Topology Builder and load your deploynent or during the initial deployment “Define New SQL Server Store”.
In the SQL Server Availability Group Listener FQDN box enter the DNS address for the AOL that you created in the previous step..
Enter the name of the instance, ensure that “High Availability Settings:” tick box is selected and change the option to “SQL AlwaysOn Availability Groups”.
In the SQL Server FQDN enter the name of one of the SQL Nodes, GBSQL01.exchdemo.com. We will modify this later.
Once configure, publish the topology.
In the SQL Server Management Studio confirm the databases have been created.
Creating the Availability Group
Now we need to create the availablilty group, to do this select “New Availability Group Wizard..”.
Specify a group name and press next.
SQL Will then check for databases that can be added to the AG, You will notice that the Skype DBs are greyed out and cannot be selected, the status also says “Full recovery mode is required”.
In order to progress this we need to changethe recovery mode and perform a database backup. This can be done using Powershell:
Invoke-Sqlcmd -Query “ALTER DATABASE [cpsdyn] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “GBSQL01\GBSKYPE”
Invoke-Sqlcmd -Query “ALTER DATABASE [rgsconfig] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “GBSQL01\GBSKYPE”
Invoke-Sqlcmd -Query “ALTER DATABASE [rgsdyn] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “GBSQL01\GBSKYPE”
Invoke-Sqlcmd -Query “ALTER DATABASE [rtcab] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “GBSQL01\GBSKYPE”
Invoke-Sqlcmd -Query “ALTER DATABASE [rtcshared] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “GBSQL01\GBSKYPE”
Invoke-Sqlcmd -Query “ALTER DATABASE [rtcxds] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “GBSQL01\GBSKYPE”
Backup-SqlDatabase -ServerInstance GBSQL01\GBSKYPE -Database cpsdyn
Backup-SqlDatabase -ServerInstance GBSQL01\GBSKYPE -Database rgsconfig
Backup-SqlDatabase -ServerInstance GBSQL01\GBSKYPE -Database rgsdyn
Backup-SqlDatabase -ServerInstance GBSQL01\GBSKYPE -Database rtcab
Backup-SqlDatabase -ServerInstance GBSQL01\GBSKYPE -Database rtcshared
Backup-SqlDatabase -ServerInstance GBSQL01\GBSKYPE -Database rtcxds
Invoke-Sqlcmd -Query “ALTER DATABASE [xds] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “GBSQL01\GBSKYPE”
Invoke-Sqlcmd -Query “ALTER DATABASE [lis] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “GBSQL01\GBSKYPE”
Backup-SqlDatabase -ServerInstance GBSQL01\GBSKYPE -Database xds
Backup-SqlDatabase -ServerInstance GBSQL01\GBSKYPE -Database lis
Note, The backups will be stored in the default location.
Press refresh and you will notice that the Status of the database has changed and that they can be selected.
On the next screen you can specify another SQL Server to host the replicas, press add replica and the connect to server popup screen will appear. Enter the details of your other node.
Tick “Automatic Failover” for both instances.
On the “Listener” tab enter the details of the DNS record we created earlier. select Static IP and add the IP address and subnet.
Leave Automatic Seeding selected and press next.
SQL will complete some validation checks.
Press Next and and the AG will be configured.
Log onto the second node and confirm the databases are listed, below you can see that they are still syncronising.
After a perioud of time you will see that the database have been synchronised and under Availability Groups you will see the newly created AG and the replicas.
If you expand the logins option you will notice that the Skype created logins are missing from the secondary node. To create these we need to failover the replicas and re-publish the Skype Topology.
To Failover, right click the AG and select “Failover…”
Select the Node/Server Instance you want to make primary and press next.
You will be prompted to connect to the server.
Once connected, press next and allow the failover to finish.
Go back to the Skype Topology Builder and modify the SQL Server store properties, Change the “SQL Server FQDN” property to the name of the node now hosting the primary replica, in this instance its GBSQL02.exchdemo.com. Publish the topology.
Go back to the SQL Management Studio, expand logins and you will notice that all the logins are now created.
The final Step now is to re-publish the topology but with the SQL AOL listener FQDN in the “SQL Server FQDN” box.
Once this has been published we have successfully configured SQL Server 2016 AlwaysOn for Skype For Business 2015.
These instructions should be the same for Skype for Business 2019.