Configuring SQL 2014 with Always On for your Skype for Business Deployment

Hello Readers

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.

Capture

Once you have logged in open Failover Cluster Manager

Capture1

Click Create Cluster

Capture2

Click Next

Capture3

Add your SQL Servers and Press Next

Capture4

Press Next

Capture5

Press Next

Capture6

Press Next and this should take a couple of minutes to complete

Capture7

Press Finish

Capture8

Give your Cluster a name and IP Address then Press Next

Capture9

Make sure you “UNTICK” Add Eligible Storage to the Cluster and Press Next

Capture10

Press Finish

Capture11

You should now be back at the main Failover Cluster Manager window

Capture12

We need to add a File Witness and to do this we need to click More Actions –> Configure Cluster Quorum Settings..

Capture13

Press Next

Capture14

Tick Select the Quorum Witness

Capture15

Tick Configure a file share witness and Press Next

Capture16

You now need to enter your File Share location for the Quorum.

Capture17

Press Next

Capture18

Press Next

Capture19

Press Finish

Capture20

We now need to perform this task on all of the SQL Server 2014

Launch SQL Server 2014 Configuration Manager

Capture21

Click SQL Server Services –> Right Click SQL Server (Your Instance) –> Properties –> AlwaysOn High Availability Tab

Capture22

Tick Enable AlwaysOn Availability Group –> Press OK

Capture23

Right Click SQL Server (Your Instance) –> Click Restart

Capture24

Open Microsoft SQL Server Management Studio –> Right Click AlwaysOn High Availability –> Click New Availability Group Wizard

Capture25

Press Next

Capture26

Specify your Availability Group Name –> Press Next

Capture27

Select Test DB –> Press Next

Please

Capture

Click Add Replica

Capture33

Enter the Server Name of the Secondary Server and Press Connect

Capture34

Tick Automatic Failover and Synchronous Commit on both SQL Servers

Capture35

Tick Create an availability group listener now then enter

  • Listener DNS Name
  • Port: (Default is 1433)
  • Static IP Address is preferred

Press Next

Capture

Select Full –> Specify Network Location accessible by all replicas –> Press Next

Capture

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..

Capture40

Enter the FQDN of the Listener we created earlier and the SQL Instance name –> Tick High Availability and Enter the Primary SQL Server FQDN.

Capture39

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

Capture41

Select Options –> Change Recovery Model from Simple to Full

Capture42

Repeat the process QoEMetrics

We now need to backup the databases within SQL Management Console. Right Click LcsCDR –> Tasks –> Back Up..

Capture44

Press OK

Capture45

Press OK

Capture46

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

CMS Databases:

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

Monitoring Databases:

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

Archiving Database:

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

Capture

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..

Capture43

Tick the Monitoring Databases –> Press Next

Capture48

Select your Shared Network Location –> Press Next

Capture49

Press Connect –> Press Connect –> Press Next

Capture50

All test should validate successfully –> Press Next

Capture51

Press Finish

Capture52

Press Close

Capture53

As you can see from the below image we now have replicated Databases across both SQL Servers.

Capture54

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.

Capture55

Right Click Availability Group –> Click Failover

Capture56

Press Next

Capture57

Press Next

Capture58

Press Connect –> Press Connect –> Press Next

Capture50

Press Finish

Capture59

Press Close

Capture60

We now need to make a change to Skype for Business topology, edit Properties.. on the <sql server\instance>

Capture61

Change the SQL Server to the new Primary Server –> Press OK and Publish the topology

Capture62

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

Capture65

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.

Capture66

And republish your topology

 

This completes the configuration for Always On and Skype for Business.

Regards

Andrew J. Price

5 thoughts on “Configuring SQL 2014 with Always On for your Skype for Business Deployment”

  1. there’s a small typo when installing the database:

    you missed one D in configureDatabases,

    install-csdatabase –update –configuredDatabases –sqlserverfqdn –verbose

    Without it, you get:
    + CategoryInfo : InvalidArgument: (:) [Install-CsDatabase], ParameterBindingException
    + FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.Rtc.Management.Deployment.InstallDatabaseCmdlet

Leave a Reply

Your email address will not be published. Required fields are marked *