Deploying SQL Server AlwaysOn Availability Group for Skype for Business Server 2015

In Lync Server 2013, there were requests regarding an alternative to SQL Mirroring for SQL Server High Availability. This was related to the fact that SQL Mirroring was marked as a feature to be removed in future SQL Server versions:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.
in SQL Server 2014 - Database Mirroring (SQL Server) - https://msdn.microsoft.com/en-us/library/ms189852.aspx

In Lync Server 2013, it was common to have SQL Server High Availability using SQL Mirroring. The reason for this was that Topology Builder did all the hard work for us. Another supported scenario was to use SQL failover clustering, but in this case we need to manually deploy it:

Database software support in Lync Server 2013
https://technet.microsoft.com/en-us/library/gg398990.aspx

The good news is Skype for Business Server 2015 comes with AlwaysOn Availability Groups:

skype4b-aoag01

Note:  AlwaysOn Availability Groups requires SQL Server 2012/2014 Enterprise Edition.

For other supported scenarios, check the following:

Back End Server high availability in Skype for Business Server
https://technet.microsoft.com/en-us/library/jj205248.aspx

To deploy AlwaysOn Availability Groups for Skype for Business Server 2015, we need to follow specific steps. In this tutorial, we consider a lab environment with one Front End server and two SQL Server 2014 Enterprise Edition servers, which is a new environment without any previous Lync Server/OCS deployments.

Let's start by installing and configuring the clustering service on both SQL Servers (SQL01 and SQL02). We can add new features by using the following PowerShell cmdlet:

Add-WindowsFeature Net-Framework-Core, Failover-Clustering, RSAT-Clustering-Mgmt,RSAT-Clustering-PowerShell -Source d:\sources\sxs
Note: The reason to use the source switch is that Windows Server 2012 R2 doesn’t install the source files. So, if your server doesn’t have internet access, you need to specify the path. In this case, the DVD is D:

skype4b-aoag02

Now that we have both servers with the necessary Windows Features, we can create the cluster. Before creating the cluster, we should test the configuration:

Test-Cluster -Node sql01,sql02
https://technet.microsoft.com/en-us/library/hh847274.aspx

skype4b-aoag03

In a lab environment, these warnings can be ignored, but in a production environment we need to check them before continue.
The Test-Cluster cmdlet will generate a Failover Cluster Validation Report:

skype4b-aoag04

After the test, we can create the cluster. For that, we can also use a PowerShell cmdlet. Since we don’t have DHCP in our lab subnet, we need a valid IP Address in the SQL Servers subnet:

New-Cluster -Name sqlcluster -Node sql01,sql02 -NoStorage -StaticAddress 172.20.15.8
https://technet.microsoft.com/en-us/library/hh847246.aspx

skype4b-aoag05

The New-Cluster will generate a Create Cluster report:

skype4b-aoag06

Before installing SQL Server we also need to configure the Cluster Quorum, we can use a File Share Witness:

Set-ClusterQuorum -Cluster sqlcluster -NodeAndFileShareMajority "\\dc01.gears.lab\SQLClusterWitness"
https://technet.microsoft.com/en-us/library/ee461013.aspx

skype4b-aoag06a

Note: For additional information please go to Configure and Manage the Quorum in a Windows Server 2012 Failover Cluster

Now that we have the cluster with basic configuration, we can proceed and install SQL Server 2014 on both servers:

skype4b-aoag07

In Instance Features select at least Database Engine Services:

skype4b-aoag08

We can use Default instance for both servers:

skype4b-aoag09

Or change it to a different name. If you change it to a Named Instance,  make sure both servers use the same instance name:

skype4b-aoag10

In Service Accounts, change the Account Name to a custom service account and use it on both SQL Servers:

skype4b-aoag11

After completing the installation, we need to enable AlwaysOn Availability Groups. On each server, we need to open SQL Server Configuration Manager, then right click on SQL Server Service and open Properties:

skype4b-aoag12

Select the AlwaysOn High Availability tab and tick Enable AlwaysOn Availability Groups:

skype4b-aoag13

For the changes to be applied, we need to restart SQL Server Service:

skype4b-aoag14

Select SQL Server Service, then click on the Restart service icon:

skype4b-aoag15

An additional step is to create a DNS A record for sqlpool.halo.lab. This is our Availability Group Listener FQDN:

skype4b-aoag16

In the Skype for Business Server 2015 Topology Builder, we add a new SQL Server Store with the following configuration:

skype4b-aoag17

Notice that we use the SQL01 server FQDN. This is normal and we will change it later on.

Now we publish the topology:

skype4b-aoag18

In SQL Server Management Studio, we can check that the Skype for Business Server 2015 related databases were successfully created in SQL01:

skype4b-aoag19

To create a new Availability Group, right click AlwaysOn High Availability and open New Availability Group Wizard… :

skype4b-aoag20

Fill the Availability Group name:

skype4b-aoag21

The wizard will check for prerequisites and will let us know that, before we proceed, the database recovery needs to be changed to full and also perform a full backup:

skype4b-aoag22

To make things easier, we can use the following PowerShell SQL cmdlets:

Back End databases:

Invoke-Sqlcmd -Query "ALTER DATABASE [cpsdyn] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"
Invoke-Sqlcmd -Query "ALTER DATABASE [rgsconfig] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"
Invoke-Sqlcmd -Query "ALTER DATABASE [rgsdyn] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"
Invoke-Sqlcmd -Query "ALTER DATABASE [rtcab] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"
Invoke-Sqlcmd -Query "ALTER DATABASE [rtcshared] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"
Invoke-Sqlcmd -Query "ALTER DATABASE [rtcxds] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"

Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database cpsdyn
Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database rgsconfig
Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database rgsdyn
Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database rtcab
Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database rtcshared
Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database rtcxds

CMS Databases:

Invoke-Sqlcmd -Query "ALTER DATABASE [xds] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"
Invoke-Sqlcmd -Query "ALTER DATABASE [lis] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"

Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database xds
Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database lis

Monitoring Databases:

Invoke-Sqlcmd -Query "ALTER DATABASE [LcsCDR] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"
Invoke-Sqlcmd -Query "ALTER DATABASE [QoEMetrics] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"

Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database LcsCDR
Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database QoEMetrics

Archiving Database:

Invoke-Sqlcmd -Query "ALTER DATABASE [LcsLog] SET RECOVERY FULL WITH NO_WAIT;" -ServerInstance "SQL01\S4B_BackEnd"

Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database LcsLog

Another requirement is that we copy the directory structure to the second SQL server:

robocopy C:\CsData \\SQL02\C$\CsData /e /xf *
https://technet.microsoft.com/en-GB/library/cc733145.aspx

skype4b-aoag00

Go back to the wizard, click Refresh and select the databases:

skype4b-aoag23

On the next step, click Add Replica… :

skype4b-aoag24

Change the server name and connect to the second SQL Server:

skype4b-aoag25

Select both SQL Instances in the Replicas tab:

skype4b-aoag26

We also need to create a listener, thus select the Listener tab and then select Create an availability group listener:

skype4b-aoag27

Note:  As mentioned before, we don’t have DHCP on this Lab subnet, so we use a static address (different from the cluster).

Click Next and specify a temporary file share:

skype4b-aoag28

The wizard will run additional availability group validation checks:

skype4b-aoag29

And if everything goes okay, we get the following messages:

skype4b-aoag30

In AlwaysOn High Availability, we can check if the selected databases were included in the group:

skype4b-aoag31

Almost done. If we compare Security Logins for both servers, we can notice that some logins are missing from SQL02:

skype4b-aoag32

To add all the necessary permissions, we need to change the Primary Replica to the second SQL Server, right click on Availability Group and select Failover:

skype4b-aoag33

In the wizard, click Next:

skype4b-aoag34

We need to connect to the server:

skype4b-aoag35

If Failover is successful, we get this:

skype4b-aoag36

We can see that the Primary Replica is now the second SQL Server:

skype4b-aoag37

Time to go back to Topology Builder, select SQL Server Store and Edit Properties...  :

skype4b-aoag39

Change the SQL Server FQDN to the second SQL Server:

skype4b-aoag40

Publish the topology.

In the Skype for Business Server 2015 server, open the PowerShell and run:

Install-CsDatabase -Update -ConfiguredDatabases -SqlServerFqdn sqlpool.halo.lab -Verbose

skype4b-aoag41

After completion, the necessary logins are also added to the second SQL server:

skype4b-aoag42

Finally, let's change SQL Server Store in Topology Builder to the final value:

skype4b-aoag43

After publishing the topology, we now have Skype for Business Server 2015 with an AlwaysOn Availability Group configured.

Additional resource:

Chris Lehr experienced a few errors during the deployment and published the notes on his blog:

Chris and Robin's Technology blog - SQL 2014 AlwaysOn Deployment for Skype for Business Server 2015 https://blog.chrislehr.com/2015/06/sql-2014-alwayson-deployment-for-skype.html