Demystifying Skype for Business with SQL AlwaysOn

Hello Readers

In the world of Microsoft technologies there is always one thing that seems to be a dark art and SQL AlwaysOn can definitely be added to this list, we all need our magic wands and psychic powers. I have designed this post to be a one stop shop for all things Skype for Business and SQL AlwaysOn. Talking about the supported SQL methods to date for Skype for Business, design considerations and how to deploy AlwaysOn to a customer environment, so hold on to your seats this post is going to be a long one.

Please note: The below contains information extracted from Technet as well as my own thoughts and opinions.

[toc]

Design Considerations

Supported SQL Methods

Microsoft support of number of methods when its comes to High Availability or Disaster Recovery

SQL Mirroring It’s supported however this feature will be depreciated in the next release of Skype for Business
SQL Clustering Its supported however SQL Clustering is configured on the SQL Server. Make sure you have an active/passive configuration for SQL Clustering, as this is the supported method. Don’t share the passive node with any other SQL instance.

Microsoft recommendation for Two-node configuration – SQL Clustering:

  • Microsoft SQL Server 2014 Standard (64-bit edition), and we recommend running with the latest service pack.
  • Microsoft SQL Server 2012 Standard (64-bit edition), and we recommend running with the latest service pack.
  • Microsoft SQL Server 2008 R2 Standard (64-bit edition), and we recommend running with the latest service pack.

Microsoft recommendation for Sixteen-node configuration – SQL Clustering:

  • Microsoft SQL Server 2014 Enterprise (64-bit edition), and we recommend running with the latest service pack.
  • Microsoft SQL Server 2012 Enterprise (64-bit edition), and we recommend running with the latest service pack.
  • Microsoft SQL Server 2008 R2 Enterprise (64-bit edition), and we recommend running with the latest service pack.
SQL AlwaysOn

SQL AlwaysOn comes in two different flavours;

  • SQL Always Availability Groups
  • SQL Always Failover Cluster Instances

There are only two SQL servers options that are supported in a SQL AlwaysOn scenario. You must use either SQL Server 2012 and SQL Server 2014 (Enterprise) Edition

importantImportant:
SQL Server 2012 and SQL Server 2014 Standard Edition are not supported. AlwaysOn is an enterprise feature.

Skype for Business 2015 only supports SQL AlwaysOn in an Active/Passive configuration.

Active/Active nodes are not supported

Recommended Hardware for Skype for Business AlwaysOn – Backend Database Server

If you are an Consultant working with Skype for Business the table below should be common knowledge. As the recommended Hardware is the same for Front End Servers, Back End Servers, Standard Edition Servers and Persistent Chat Servers.

Hardware component Recommended
CPU 64-bit dual processor, hex-core, 2.26 gigahertz (GHz) or higher.Intel Itanium processors are not supported for Skype for Business Server 2015 roles.
Memory 32 gigabytes (GB).
Disk EITHER:
• 8 or more 10000 RPM hard disk drives with at least 72 GB free disk space (two of the disks using RAID 1 and 6 using RAID 10).OR• Solid state drives (SSDs) able to provide the same free space and similar performance to 8 10000 RPM mechanical disk drives.
Network 1 dual-port network adapter, 1 Gbps or higher (2 network adapters can be used, but they need to be teamed with a single MAC address and a single IP address).Dual or multi-homed configurations are not supported for Front End Servers, Back End Servers, Standard Edition servers, and Persistent Chat Servers.As long as they are not exposed to the operating system and are being used to monitor and manage server hardware, you can have out of band management systems, such as DRAC or ILO. This scenario doesn’t constitute a multi-homed server, and it is supported.

Supported Skype for Business Databases with SQL AlwaysOn

The table below outlines all the currently supported Skype for Business Databases for an AlwaysOn Availability Group.

Database Name Database Function
cpsdyn Maintains the dynamic information database for Call Park application.
LcsCDR Maintains the call detail recording data.
LcsLog Maintains data file for the retention of instant messaging and conferencing data on an Archiving Server.
lis Store location information (network subnets, ports, Switches…etc.) that is used by Lync Location services for E9-1-1 feature.
QoEMetrics Maintains the QoE data to provide best experience to SfB users
rgsconfig Maintains the response group configuration service data file.
rgsdyn Maintains the runtime data for response group configuration service data file.
rtcab Maintain the address book service information
rtcshared Hosts the conferencing directory
rtcxds Maintains the backup for user data
xds Main database of SfB Server and part of central management store which maintain the topology information, polices, configuration etc. and replicate a read only copy to each SfB server.

Not Supported Skype for Business Databases with SQL AlwaysOn

Originally there was no supporting statement from Microsoft regarding Persistent Chat Databases and support within SQL AlwaysOn deployment. Microsoft have released an update to TechNet on or around the 12th May 2016 stating mgc and mgccomp are not supported in a SQL AlwaysOn scenario. I would like like to call out that ReportServer DBs are not supported for SQL AlwaysOn. It is also worth noting that SQL 2014 and above is not supported for Persistent Chat databases.

Database Name Database Function
mgc Maintains user persistent chat data.
mgccomp Maintains compliance data persistent chat.
ReportServer$<Instance Name>
ReportServer$<Instance Name>TempDB

You can read more about it in the Back End Server high availability in Skype for Business Server 2015 topic.

Windows Failover Clustering

Like any Microsoft product there is always prerequisites you need to consider before implementing a new product. So here is some of the key things to consider with Failover Clustering as its a requirement for deploying SQL AlwaysOn.

  • Make sure that all servers that you want to add as cluster nodes are running the same version of Windows Server. You can use the Failover Clustering feature on all editions of Windows Server 2012 R2 and Windows Server 2012. This includes Server Core installations. Please note: It probably not a good idea to use Core since we will need SQL Server Management Studio GUI but adding the option in for completeness.
  • Review the hardware requirements to make sure that your configuration is supported. For more information you can compare the Backend Server requirements against the Failover Clustering Hardware Requirements and Storage Options.
  • Ensure that all servers that you want to add as cluster nodes are joined to the same Active Directory domain.
  • (Advisory) Ensure that all cluster traffic uses a separate network – Please note: This is best practice with any Failover Clustering deployment but you can use the same VLAN as the Server traffic if the customer has good network links. In my deployment for customers I have managed to use the Server VLAN and not a separate network
  • (Optional) Create an organizational unit (OU) and move the computer accounts for the servers that you want to add as cluster nodes into the OU. As a best practice, we recommend that you place failover clusters in their own OU in AD DS. This can help you better control which Group Policy settings or security template settings affect the cluster nodes. By isolating clusters in their own OU, it also helps prevent against accidental deletion of cluster computer objects.

Additionally, verify the following account requirements:

  • Make sure that the account you want to use to create the cluster is a domain user who has administrator rights on all servers that you want to add as cluster nodes.

Restriction on using Failover Cluster Manager with Availability Groups

Do not use the Failover Cluster Manager to manipulate availability groups, for example:

  • Do not add or remove resources in the clustered service (resource group) for the availability group.
  • Do not change any availability group properties, such as the possible owners and preferred owners. These properties are set automatically by the availability group.
  • Do not use the Failover Cluster Manager to move availability groups to different nodes or to fail over availability groups. The Failover Cluster Manager is not aware of the synchronization status of the availability replicas, and doing so can lead to extended downtime. You must use Transact-SQL or SQL Server Management Studio.

IP Addressing and DNS

So how do we decide on IP Addressing and DNS for a new SQL AlwaysOn Deployment? First thing we need to do is ensure that our SQL nodes are configured on the customer LAN and domain joined.

Windows Failover Clustering

Deciding the IP Addressing for Windows Failover Clustering, you have two options

  1. Use the Server VLAN addressing for the Failover Cluster Traffic
  2. Implement a Private VLAN addressing on a separate NIC for the Failover Traffic

Next up will be deciding the DNS Requirements for Windows Failover Clustering the most important part of this section will be defining the Cluster Name. For example in my lab I have used sqlclusag.lyncme.local. Obviously this DNS name will need to be unique and if you pre-stage the Active Directory Object you will need to visit this blog post as you will run into the error described in this post. http://blogabout.cloud/skype-for-business-2015/skype-for-business-always-on-the-attempt-to-create-the-network-name-and-ip-address-for-the-listener-failed/

Check List
IP Addresses for SQL Failover Clustering (Based on 2 Nodes) x.x.x.x
x.x.x.x
IP Address for SQL Cluster Name x.x.x.x
SQL Failover Clustering Name sqlclusag.lyncme.local

SQL AlwaysOn

Deciding the IP Addressing for SQL AlwaysOn Availability Group  you will need 1x IP Address for the SQL AOAG Listener and 1x DNS Name for the SQL AOAG Listener.

Check List
IP Addresses for SQL AOAG Listener x.x.x.x
DNS Name for SQL AOAG Listener sfbaoag.lyncme.local

Service Accounts

It is always best practice to use Active Directory Service Account where possible. This limits the possible issues you may encounter

Deployment

In this section will now for through the deployment process step by step making sure all areas are covered when deploying SQL AlwaysOn. As this is a Lab environment it will not match the Microsoft recommendations for machine spec but will give you all the information required for successfully being an AlwaysOn Expert.

Details about Lab Environment

Server Name IP Address Role
DC01 10.10.10.1 Primary Domain Controller and DNS Server
DB01 10.10.10.5 Primary SQL AlwaysOn Node
DB02 10.10.10.6 Secondary SQL AlwaysOn Node
FE01 10.10.10.11 Skype for Business Server 2015 Front End
FE02 10.10.10.12 Skype for Business Server 2015 Front End
FE03 10.10.10.13 Skype for Business Server 2015 Front End
SQLCLUSAG 10.10.10.4 Windows Failover Clustering IP Address
SFBAOAGL 10.10.10.7 SQL Server AlwaysOn Availability Listener

Windows Failover Clustering

Its time to install the Windows Failover Clustering, this can be done in two way. Either using the Server Manager or Powershell but for the purpose of this post I will be using Powershell.

Install-WindowsFeature Failover-Clustering

image

Once installed launch the Failover Cluster Manager

image

You will now need to create the Failover Cluster. This is done by selecting Create Cluster under the Action field

image

Press Next to the first prompt entitled Create Cluster Wizard, this is just a default message from Microsoft. Now that you are moved on to the next step, please the following process

  1. Select Browse
  2. Enter your SQL Server NetBios names
  3. Press Check Names
  4. Press OK
  5. Press Next

image

Select No and Press Next

image

You will now need to enter your desired Cluster Name, this doesnt need to be a fully qualifying domain name as shown before. Some IT Professional do like to pre-stage Computer Accounts in Active Directory so they pick up the correct Group Policies, if you decide to pre-stage the Cluster Name you many need to go to the following url

http://blogabout.cloud/skype-for-business-2015/skype-for-business-always-on-the-attempt-to-create-the-network-name-and-ip-address-for-the-listener-failed/

As you may encounter this issue if you dont correct set permissions on the OU where the SQL Cluster Name is hosted.

 

As you can see from below I have selected the 10.10.10.4 as my cluster IP Address. Again you can separate the traffic from the Server VLAN if you chose but for this scenario I am using the Server VLAN as I have no concerns over latency on this VLAN.

image

Make sure you untick Add all eligible storage to the cluster and press Next to continue

image

Your WSFC will now be created

image

Once you the Cluster has been created you may encounter a error message regarding “a quorum witness”. This message can be ignored as we are yet to define a quorum witness for this deployment

image

Select More Actions –> Configure Cluster Quorum Settings…

image

Press Next

image

Press Select the quorum witness –> Press Next

image

Select Configure a file share witness –> Press Next

image

Specify File Share Path –> Press Next

image

Press Next

image

Press Finish

image

This now completed the installation and configuration of the Windows Failover Cluster. In the next section we will now run through the installation and configuration of SQL Server 2014 Enterprise, this section will come as no surprise to all Skype for Business Consultants as the initial installation is pretty straight forward. The configuration for SQL will be the most interesting part as we will cover the configuration for AlwaysOn.

SQL Server Installation

Launch the SQL Server installation Media, select Installation and New SQL Server stand-alone installation

image

Press Next

SNAGHTML14e5e92

Tick I accept the license terms –> Press Next

image

Press Next –> Next –> Next

image

Select SQL Server Feature Installation –> Press Next

image

Specify all the required SQL Instance Features

  • Database Engine Services
    • SQL Server Replication
    • Data Quality Service
  • Reporting Service  – Native
  • Client Tool Connectivity
  • Management Tools – Basic
    • Management Tools – Complete

Press Next

image

image

Specify Named Instance, it is best practice to name each SQL Instance. Please try and follow this best practise where possible.

image

Specify Service Accounts for each

  • SQL Server Agent
  • SQL Server Database Engine
  • SQL Server Reporting Services

Change Startup Type to Automatic

  • SQL Server Agent

Press Next

image

Specify your SQL Server Administrators for this instance, I will be using the following Service Accounts

  • svc.sql – SQL Server Service Account
  • svc.lync – Lync/Skype for Business Service Account

Press Next

image

Select Install and configure –> Press Next

image

Press Install

image

Once the installation is completed, we will now need to configure this SQL Server for AlwaysOn. The first step is to enable AlwaysOn High Availability.

Launch SQL Server Configuration Manager

Select SQL Server Services

Right Click SQL Server (Instance Name) –> Select Properties

image

Select AlwaysOn High Availability Tab

Tick Enable AlwaysON Availability Groups

Press Apply

image

You will now be prompted with a message to restart the SQL Server Service

image

Press OK  –> Press OK

Right Click SQL Server (Instance Name) –> Select Restart

image

Repeat this process for the Secondary SQL Node.

Skype for Business Deployment

We now move on to the deployment process for the Skype for Business Back End Database, at this stage of the SQL AlwaysOn deployment I will expect the following to be completed.

  • All Front End Servers have been built with prerequisites
  • Skype for Business Management Tools are installed
  • Skype for Business Topology has not yet to be defined.

Once you have started building your Skype for Business Enterprise Pool Topology by adding in Site Name, Front End Servers FQDN. I would expect you to be at the below window

image

Press New

You will now need to enter the following information

  • Fully Qualifying Domain Name of the SQL AlwaysOn Availability Group Listener under SQL Server FQDN
    • Its important to remember the SQL AOAGL as you will need this name later in this post
  • The name of your Named Instance

image

  • Tick High Availability Settings

You will now see that SQL Server FQDN Field has now changed to SQL Server Availability Group Listener FQDN

  • Now specify the Primary SQL Server within the SQL Server FQDN Field.

Please note: We will be revisiting the SQL Server Store later in this post as we need to make a number of changes.

image

Press OK –> Press Next

Continue defining your Skype for Business topology and once complete

Publish your Skype for Business topology in accordance to your requirements.

image

Configuring Skype for Business Databases for SQL AlwaysOn Availability Group

At this stage of the SQL AlwaysOn deployment you are required to configure the Skype for Business Database into the required model in order to add them into the SQL AlwaysOn Availability Group that are now going to create. Each Skype for Business database is required to converted from a Single to Full recovery model and then backed up using the Task option on each Databases. Without performing this action you will be unable to add the Skype for Business databases to the AOAG, the next steps in this post outline the process for converting all Skype for Business Databases in the Full Recovery Model and perform the backup action.

Good News:
I have created a Powershell script that is available on the Technet Gallery which performs all the steps detailed within the “Configuring Skype for Business Database for SQL AlwaysOn Availability Group” section https://gallery.technet.microsoft.com/Alter-Your-Skype-for-4d3d91f9Details on running the script can be found http://blogabout.cloud/skype-for-business-2015/sql-alwayson-script

Launch the Microsoft SQL Server Management Studio –> Connect to your Named Instance

image

Browse to your Skype for Business Databases

image_thumb[6]

Right Click your first Skype for Business Database in my case this will be cpsdyn

Select Properties

image

Select Options –> Select Full from Recovery Model

Press OK

image

Right click cpsdyn database –> Select Tasks –> Select Back Up..

image

Press OK

image

Press OK

image

You will now need to repeat this process for the following databases

  • rgsconfig
  • rgsdyn
  • rtcab
  • rtcshared
  • rtcxds
  • xds
  • lis
  • LcsCDR
  • QoEMetrics
  • LcsLog

Now we have completed converting the Database to Full Recovery mode and backed up.

Database Drive Folder Structure

You will need to copy the following folder structures from Primary SQL Server to Secondary SQL Server. Please note: The folder structure below maybe different compared to your Skype for Business deployment, as I always ensure that all LogPath folders are located on the Log drive.

Database Folders

SNAGHTML44270689

  • <Drive Letter>:\ABSStore\<Instance Name>\
  • <Drive Letter>:\ApplicationStore\<Instance Name>\
  • <Drive Letter>:\ArchivingStore\<Instance Name>\
  • <Drive Letter>:\BackendStore\<Instance Name>\
  • <Drive Letter>:\MonitoringStore\<Instance Name>\

Log Folders

image

  • <Drive Letter>:\ABSStore\<Instance Name>\
  • <Drive Letter>:\ApplicationStore\<Instance Name>\
  • <Drive Letter>:\ArchivingStore\<Instance Name>\
  • <Drive Letter>:\BackendStore\<Instance Name>\
  • <Drive Letter>:\CentralMgmtStore\<Instance Name>\
  • <Drive Letter>:\MonitoringStore\<Instance Name>\

Backup Folder

image

  • <Drive Letter>:\CentralMgmtStore\<Instance Name>\

Right Click AlwaysOn High Availability –> Click New Availability Group Wizard

image

Press Next

image

Specify your Availability Group Name –> Press Next

image

Select all your Skype for Business Databases –> Press Next

image

At this point it is important to note the following

Automatic Failover off the Skype for Business can only happen between two SQL Server Nodes but you can Sychronous Commit data upto three SQL Server Nodes. In my case I only have two SQL Servers within this lab build.

 

Press Add Replica…

image

Specify your Server Name –> Press Connect

image

Tick Automatic Failover and Synchronous Commit –> Click Listener Tab

image

Tick create an availability group listener

Enter your DNS Name for the SQL AOAG Listener, you dont need the fully qualifying domain name.

  • sfbaoagl

Enter your required port number,

  • 1433 (Default SQL Port)

Press Add

image

Add your IP Address for the SQL AlwaysOn Availability Group Listener –> Press OK

image

Press Next

image

At this step you are given 3 options to choice from;

  1. A Full Data Synchronisation of the full databases and log backups to a specified shared network location that is accessible by all replicas
  2. Data Synchronisation where you have already restored database and log backups to secondary server.
  3. Skip Data Synchronisation and perform your own database and log backups of each primary database.

For this blog I will be using Full Data Synchronisation to a shared network location

Press Next

image

Your Validation of the AOAG Listener should now pass with success –> Press Next

image

Press Finish

image

If you receive the following error message

image

Follow this link http://blogabout.cloud/skype-for-business-2015/skype-for-business-always-on-the-attempt-to-create-the-network-name-and-ip-address-for-the-listener-failed/ for resolution.

image

If you receive a success message, press Close.

This completes the build of the SQL AlwaysOn Availability Group. We now need to replicate the Skype for Business RBAC roles onto the secondary node and this is completed by following the below process.

From the SQL Management Studio –> Browse to the Availability Group you have created –> Right Click –> Press Failover

image

Press Next

image

Press Next

image

Press Connect –> Press Connect

image

Press Next

image

Press Finish

image

Press Close

image

You can now see that the Primary SQL Server has now become the Secondary Server.

 

image

 

We now need to log back onto Skype for Business Front End and launch Skype for Business Topology Builder.

Browse to your SQL Server Stores –> Right Click your SQL Store –> Edit Properties

image

Change the SQL Server FQDN from your one SQL Server to the other in my case this is

db01.lyncme.local change to db02.lyncme.local

image

Press OK

You now need to Publish the topology

image

Connect back your SQL Server and launch SQL Management Studio

Connect to your new Primary Server and check the security groups. If they dont match against your first SQL Server, ensure that you add all missing security groups as shown below.

image

Now switch back to your Skype for Business Front End and launch Skype for Business Topology Builder.

Browse to your SQL Server Stores –> Right Click your SQL Store –> Edit Properties

Change the SQL Server FQDN to the SQL AlwaysOn Listener DNS name.

Press OK

image

Publish your Skype for Business topology.

Failback your SQL Server AlwaysOn Availability Group and this completes the design, build and deployment of SQL AlwaysOn for Skype for Business databases.

 

If there anything else you would like adding to this post, please ping me an email at andrew.price@lyncme.co.uk. I have been working with Geomant/UC Analytics deployments recently and some knowledge of working with them in an AlwaysOn scenario.

Regards

Andrew J Price

2 thoughts on “Demystifying Skype for Business with SQL AlwaysOn”

Leave a Reply

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