I have been recently working on a project where we wanted to utilize Always On with their Skype for Business Enterprise deployment so I thought I would share, what is actually required to deploy a successful SQL 2014 Always On Cluster.
So some of the key things before we start.
- DC01 – Domain Controller with File Share
- DB02 – SQL 2014
- DB03 – SQL 2014
- FE01 – Skype for Business – Standard Edition
The reason I am only using a Standard Edition is just a proof of concept on how to setup and configure your SQL/Skype for Business for Always On.
Please Note: In this post I will expect you to have already
- Installed SQL 2014 on both Servers
- You have defined an SQL Instance on both Servers (Instance Name must match across all Servers)
- Configure a File Share for Quorum Witness
- Configure a File Share for SQL
- I have created a Test Database for this post to setup the Always On Availability Group first, you dont need to do this but I have included it in to show the setup of Always On without the Skype for Business Databases being present. You can configure your topology and publish to the Primary SQL Server in your Always On Pool so when you are selecting the databases you want to add to Availability Group it will be the Skype for Business databases and not the test db.
Now for the next bit, we need to install Failover Clustering and this can be done by good old Powershell
Install-WindowsFeature – Name Failover-Clustering and as you can see below I have already installed this feature on my DB servers. You will need reboot the Server and log back in.
Once you have logged in open Failover Cluster Manager
Click Create Cluster
Add your SQL Servers and Press Next
Press Next and this should take a couple of minutes to complete
Give your Cluster a name and IP Address then Press Next
Make sure you “UNTICK” Add Eligible Storage to the Cluster and Press Next
You should now be back at the main Failover Cluster Manager window
We need to add a File Witness and to do this we need to click More Actions –> Configure Cluster Quorum Settings..
Tick Select the Quorum Witness
Tick Configure a file share witness and Press Next
You now need to enter your File Share location for the Quorum.
We now need to perform this task on all of the SQL Server 2014
Launch SQL Server 2014 Configuration Manager
Click SQL Server Services –> Right Click SQL Server (Your Instance) –> Properties –> AlwaysOn High Availability Tab
Tick Enable AlwaysOn Availability Group –> Press OK
Right Click SQL Server (Your Instance) –> Click Restart
Open Microsoft SQL Server Management Studio –> Right Click AlwaysOn High Availability –> Click New Availability Group Wizard
Specify your Availability Group Name –> Press Next
Select Test DB –> Press Next
Click Add Replica
Enter the Server Name of the Secondary Server and Press Connect
Tick Automatic Failover and Synchronous Commit on both SQL Servers
Tick Create an availability group listener now then enter
- Listener DNS Name
- Port: (Default is 1433)
- Static IP Address is preferred
Select Full –> Specify Network Location accessible by all replicas –> Press Next
Press Next –> Press Finish
We now need to add the new SQL Server Always Listener to the Skype for Business topology and this post I am only going to enable Monitoring.
Launch your Skype for Business topology to make the necessary changes. As you can see from below I have edited my Front End Server and ticked “Monitoring” then Press New..
Enter the FQDN of the Listener we created earlier and the SQL Instance name –> Tick High Availability and Enter the Primary SQL Server FQDN.
And publish your topology as normal, once the topology has published we will need to move back the SQL Server (i.e. DB02) as we need to add the Databases manually to the Availability Group. Once on your SQL Server open the SQL Management Studio –> Expand Databases –> Right Click LcsCDR –> Properties
Select Options –> Change Recovery Model from Simple to Full
Repeat the process QoEMetrics
We now need to backup the databases within SQL Management Console. Right Click LcsCDR –> Tasks –> Back Up..
Repeat the process QoEMetrics
For Reference, you can use Powershell SQL to complete the steps above
Back End databases:
Invoke-Sqlcmd -Query “ALTER DATABASE [cpsdyn] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Invoke-Sqlcmd -Query “ALTER DATABASE [rgsconfig] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Invoke-Sqlcmd -Query “ALTER DATABASE [rgsdyn] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Invoke-Sqlcmd -Query “ALTER DATABASE [rtcab] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Invoke-Sqlcmd -Query “ALTER DATABASE [rtcshared] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Invoke-Sqlcmd -Query “ALTER DATABASE [rtcxds] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database cpsdyn
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database rgsconfig
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database rgsdyn
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database rtcab
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database rtcshared
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database rtcxds
Invoke-Sqlcmd -Query “ALTER DATABASE [xds] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Invoke-Sqlcmd -Query “ALTER DATABASE [lis] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database xds
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database lis
Invoke-Sqlcmd -Query “ALTER DATABASE [LcsCDR] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Invoke-Sqlcmd -Query “ALTER DATABASE [QoEMetrics] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database LcsCDR
Backup-SqlDatabase -ServerInstance DB02\SfB_BackEnd -Database QoEMetrics
Invoke-Sqlcmd -Query “ALTER DATABASE [LcsLog] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “DB02\SfB”
Backup-SqlDatabase -ServerInstance DB02\SfB -Database LcsLog
Another requirement is that we copy the directory structure to the second SQL server:
Xcopy C:\CsData \\DB03\C$\CsData /T /E
We now need to add the databases to the Availability Group we created earlier, drill down into AlwaysOn High Availability –> Availability Groups –> Your Listener –> Add Database..
Tick the Monitoring Databases –> Press Next
Select your Shared Network Location –> Press Next
Press Connect –> Press Connect –> Press Next
All test should validate successfully –> Press Next
As you can see from the below image we now have replicated Databases across both SQL Servers.
But the fun doesnt end there, we dont have replicated Security Groups across both SQL Servers so in order to replicate them correctly we need to Failover the Availability Group and make some changes to the Skype for Business Topology.
Right Click Availability Group –> Click Failover
Press Connect –> Press Connect –> Press Next
We now need to make a change to Skype for Business topology, edit Properties.. on the <sql server\instance>
Change the SQL Server to the new Primary Server –> Press OK and Publish the topology
Once you have successfully published the topology open the Skype for Business Management Shell and enter
install-csdatabase –update –configureddatabases –sqlserverfqdn <your SQL Listener> –verbose
The Security Logins will now be replicated across both SQL Servers.
We now need to change the SQL Server again to the SQL Listener for your Always On pool.
And republish your topology
This completes the configuration for Always On and Skype for Business.
Andrew J. Price