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)
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
The good news is Skype for Business Server 2015 comes with AlwaysOn Availability Groups:
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
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:
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://docs.microsoft.com/powershell/module/failoverclusters/test-cluster
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:
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://docs.microsoft.com/powershell/module/failoverclusters/new-cluster
The New-Cluster will generate a Create Cluster report:
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://docs.microsoft.com/powershell/module/failoverclusters/set-clusterquorum
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:
In Instance Features select at least Database Engine Services:
We can use Default instance for both servers:
Or change it to a different name. If you change it to a Named Instance, make sure both servers use the same instance name:
In Service Accounts, change the Account Name to a custom service account and use it on both SQL Servers:
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:
Select the AlwaysOn High Availability tab and tick Enable AlwaysOn Availability Groups:
For the changes to be applied, we need to restart SQL Server Service:
Select SQL Server Service, then click on the Restart service icon:
An additional step is to create a DNS A record for sqlpool.halo.lab. This is our Availability Group Listener FQDN:
In the Skype for Business Server 2015 Topology Builder, we add a new SQL Server Store with the following configuration:
Notice that we use the SQL01 server FQDN. This is normal and we will change it later on.
Now we publish the topology:
In SQL Server Management Studio, we can check that the Skype for Business Server 2015 related databases were successfully created in SQL01:
To create a new Availability Group, right click AlwaysOn High Availability and open New Availability Group Wizard…:
Fill the Availability Group name:
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:
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 *
Robocopy – Info
Go back to the wizard, click Refresh and select the databases:
On the next step, click Add Replica…:
Change the server name and connect to the second SQL Server:
Select both SQL Instances in the Replicas tab:
We also need to create a listener, thus select the Listener tab and then select Create an availability group listener:
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:
The wizard will run additional availability group validation checks:
And if everything goes okay, we get the following messages:
In AlwaysOn High Availability, we can check if the selected databases were included in the group:
Almost done. If we compare Security Logins for both servers, we can notice that some logins are missing from SQL02:
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:
In the wizard, click Next:
We need to connect to the server:
If Failover is successful, we get this:
We can see that the Primary Replica is now the second SQL Server:
Time to go back to Topology Builder, select SQL Server Store and Edit Properties… :
Change the SQL Server FQDN to the second SQL Server:
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
After completion, the necessary logins are also added to the second SQL server:
Finally, let’s change SQL Server Store in Topology Builder to the final value:
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
http://blog.chrislehr.com/2015/06/sql-2014-alwayson-deployment-for-skype.html
