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

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