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:

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:

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

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://technet.microsoft.com/en-us/library/hh847246.aspx

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://technet.microsoft.com/en-us/library/ee461013.aspx

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 *
https://technet.microsoft.com/en-GB/library/cc733145.aspx

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 https://blog.chrislehr.com/2015/06/sql-2014-alwayson-deployment-for-skype.html

Comments

• Anonymous
  May 21, 2015
  The comment has been removed
• Anonymous
  May 21, 2015
  You need to use a different free address, in our lab we have this:
  
  172.20.15.5 - Listener IP
  
  172.20.15.6 - SQL01
  172.20.15.7 - SQL02
  
  172.20.15.8 - Cluster IP
  
  David
• Anonymous
  May 27, 2015
  The comment has been removed
• Anonymous
  May 28, 2015
  The comment has been removed
• Anonymous
  June 03, 2015
  If you have issues with the Always On listener erroring with "The WSFC cluster could not bring the Network Name resource online" - you need to change permissions to allow your SQL CNO to create a computer account, or you need to pre-stage the object. See options here.
• Anonymous
  June 03, 2015
  Final note - When you run the install-CsDatabase -upgrade command, make sure you ran S4B PowerShell as Admin. Running in non-admin mode it didn't seem to work for me. (permissions not added)
• Anonymous
  June 05, 2015
  Thanks Chris, I included a link to your post.
• Anonymous
  June 05, 2015
  The comment has been removed
• Anonymous
  June 06, 2015
  Did you configured in Topology Builder the sqlpool.halo.lab as SQL Listener and SQL server as your second SQL server FQDN?
• Anonymous
  June 06, 2015
  Oh I figured it out, looks like instead of sqlpool.halo.lab I just put sqlpool. It seemed to work now but will that be a problem?
• Anonymous
  June 08, 2015
  You should FQDN, in this case sqlpool.halo.lab - replace halo.lab by your internal domain.
• Anonymous
  June 08, 2015
  When I try to go back and edit it (the SQL Server Availability Group Listener FQDN) it's grayed out.
  All I can change is the SQL Server FQDN (which I already have the complete name).
  
• Anonymous
  July 11, 2015
  Thanks for the post. It is super helpful as I'm following it. Couple of questions:
  
  - I noticed you didn't setup a witness SQL Server. Isn't this required for SQL AlwaysOn?
  
  - do you not need a separate network (i.e. replication network) between the 2 SQL Servers in the cluster? This network is for replication between the 2 SQL Servers in the cluster. See:http://careexchange.in/installingconfiguring-sql-2014-always-on-cluster-on-windows-2012-r2-recommended-way/
• Anonymous
  July 13, 2015

1. SQL witness is just to SQL Mirroring:
   "The quorum for AlwaysOn Availability Groups is based on all nodes in the WSFC cluster regardless of whether a given cluster node hosts any availability replicas. In contrast to database mirroring, there is no witness role in AlwaysOn Availability Groups."
   in https://msdn.microsoft.com/en-us/library/ff929171%28v=sql.120%29.aspx
   
   2) SQL AlwaysOn uses the Windows Failover Cluster but the recommendation is to keep it in the same network:
   "We strongly recommend that you use the same network links for communications between WSFC cluster members and communications between availability replicas. Using separate network links can cause unexpected behaviors if some of links fail (even intermittently). "
   in https://msdn.microsoft.com/en-us/library/ff878487%28v=sql.120%29.aspx#NetworkConnect
   

• Anonymous
  August 13, 2015
  I noticed that after I moved CMS from a pool with a single backend database to a pool with an Always On backend, my remote powershell sessions stopped working correctly. I can still connect, but any command I run returns:
  
  =====
  
  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was
  not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: NamedPipes Provider, error: 40 - Could not open a connection to SQL Server)
  
  =====
  
  Taking a step back, I also recall that after I had set up Always On, when I went back later and attempted to install the new database in preparation for moving CMS to the new front end I was unable to point the install command at the availability group FQDN, I had to point it at the actual database name. I did verify that I took the step to change the SQL server FQDN to one of the DB servers prior to doing that. Any thoughts on what's going on there?
• Anonymous
  September 29, 2015
  Hi, Do we need File share for AlwaysOn feature like we were requiring for SQL Mirroring , may be just during first time setup.Thanks
• Anonymous
  October 05, 2015
  The Persistent Chat database is not listed in the PowerShell commands in the article. The necessary commands are:Invoke-Sqlcmd -Query “ALTER DATABASE [mgc] SET RECOVERY FULL WITH NO_WAIT;” -ServerInstance “SQL01\S4B_BackEnd”Backup-SqlDatabase -ServerInstance SQL01\S4B_BackEnd -Database mgcAlso, once AOAG is up and running as described here, attempting to install more databases, such as for added roles, will timeout due to it not able to connect to the relevant paths using the listener name. I changed the SQL Server FQDN back to a single node, published, ran the above PowerShell, then added the database to the AOAG. Then, change the SQL Server FQDN back to match the listener name & publish again.Also note that once you've setup the AOAG, and the replication is up, the temp files created in the temp share can be removed.
• Anonymous
  November 29, 2016
  Hi, i have a question, Is SQL alwaysOn availability group supported for Persistent chat??And can we keep separate instances for Archiving and Monitoring for alwaysOn availability group?
  • Anonymous
    November 30, 2016
    Hi,Using SQL AlwaysOn Availability Groups is not supported with Persistent Chat Server databases. https://technet.microsoft.com/en-gb/library/dn951400.aspxRegarding the Archiving and Monitor they can be installed on different instances.David
• Anonymous
  August 07, 2017
  The comment has been removed
  • Anonymous
    August 09, 2017
    Yes it's supported to have SQL servers in a different subnet, however, they should be in the same Datacenter. Regarding the port, Front End will connect to the AOAG on port 1433. Front End will always be the one initiating the communication.
• Anonymous
  September 06, 2017
  The comment has been removed
  • Anonymous
    September 06, 2017
    Sorry forgot to add, in our Topology I have 1 central site called DCE and I have another central site called DCS, the plan was to use SQL AlwaysOn for HA and use pool pairing. I have setup all the databases on both SQL servers and followed your steps and everything is good. I am just not sure the SQL server store setup with two different sites. Thank youl
  • Anonymous
    September 06, 2017
    The comment has been removed
    • Anonymous
      September 07, 2017
      Ok that makes a lot of sense now. Once I have setup each pool/site with its own listener would I be able to implement pool paring across both data centers? We are trying to provide HA so if we loose one data center everyone is still running on another. Is this an overkill? Thanks for your info David.
      • Anonymous
        September 07, 2017
        We recommend to have different data centers for Pool Pairing and in case of a "disaster" you can failover users to the backup pool. I cannot comment about the design since it will depend on the your requirements.
• Anonymous
  May 20, 2018
  I've a query ..we are planning to migrate to S4B and my design is to have EEpools with two Central sites and each site will be paired each other. Now each site will be having it's own SQL servers/cluster incase if one of the site goes down and if we failed over the users to other Pool then how we can ensure the SQL data is synced between two SQL clusters across paired pools?
  • Anonymous
    May 21, 2018
    The Backup Service will make sure that the user and conference data is synced between the paired pools. You can check the status with:Get-CsBackupServiceStatus