One of the major differences in deploying a Standard Edition server in Skype for Business as opposed to an Enterprise Edition server is that the Standard Edition Front End server utilizes SQL Express instead of a full SQL Server installation. This means that by default there is no simple way to view or manage the SQL Express databases out of the box. Luckily it is relatively simple to install the missing SQL management tools directly on the Standard Edition Front End server.
This article shows how to install SQL management tools on a Standard Edition Skype for Business Server. Take note that this is unsupported and not typically performed in Skype for Business deployments. Usage of this tool throughout this and any future article is strictly for educational purposes.
In an Enterprise Edition server deployment a separate backend SQL Server is utilized which already includes these management tools. Also understand that in many environments the SQL servers are already deployed and managed by separate resources and so the Skype for Business administrators may not even have that level of database access to the SQL servers.
In a Standard Edition model though the SQL databases are all stored locally on the Front End server and thus a time may come when the databases stored on the local SQL Express instances may need to be viewed, queried or even modified directly (recommended only by experts or under the guidance of Microsoft support in specific cases). But for test environments or when attempting to simply learn more about the Skye for Business platform based on a Standard Edition server deployment there is no way by default to get access to the SQL databases.
Installation
The trickiest part is finding the correct software package to download as there are multiple different versions of SQL Server and SQL Server Express for both the servers and management tools.
- On the Front End server the Programs and Features control panel can be used to confirm the currently installed version of SQL Server (Express), which should be 2014.
- Go to the Microsoft SQL Server 2014 Express download page t and then select the Download button.
- Select MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe from the list of available packages to choose from.
- Once the package has completed downloading expand the contents and then run Setup.exe to start the installation wizard.
- From the main Installation menu select the option for New SQL Server stand-alone or add features to an existing installation.
- On the Installation Type page select Add features to an existing instance of SQL Server 2014. The default selection of an existing instance is sufficient as the management studio only needs to be installed for once instance to be made available to connect to any on the server. The RTC instance was left as the choice in this example.
- On the Feature Selection page click Management Tools – Complete.
- Complete the installation wizard and close it when the requested features are finished installing.
Configuration
The first time this new application is launched it can be configured to connect to multiple database instances.
- Search Windows for “SQL Server Management Studio” to find the newly installed application.
- Launch the application and the local SQL Server instances should be displayed in the Server Name drop-down menu. Select Connect to open up the LYNCLOCAL database instance
Once open the Object Explorer tree will show the instance and databases as a child item. The Connect button can be used to add the other SQL instances to the explorer view, as shown below. This is the complete list of default databases which are installed on a Standard Edition Skype for Business 2015 Front End server (the other objects have been edited out for easier reading).
As seen above there are three different SQL instances which were created during the initial server deployment. The first and most important is the RTC instance which stores the primary databases in Skype for Business Server. The xds database is the most critical as it stores the Topology data defines the entire environment. Other key database are stored here like the Address Book database (rtcab), conferencing information (rtcshared), or services like location information (lis). In an Enterprise Edition pool deployment this instance and its databases would be stored only on the backend SQL database servers, not locally on the Front End server.
The RTCLOCAL instance stores local copies of the critical xds, rtc, and rtcdyn databases. In an Enterprise Edition pool deployment these are the only databases which would be stored locally on the Front End server. Yet as the Standard Edition pool model does not use separate SQL servers then all databases and instances are hosted locally.
Finally the LYNCLOCAL instance holds only the Lync Storage Service (lyss) database which is a storage engine used for a variety of tasks.
As additional roles and features are deployed their associated databases will be created and synchronized between one or more of these various instances. During deployment of those features this topic will be revisited to validate where those individual databases are stored.
Very Well explained. Thanks.
Very nice explained. Thanks.
do they all HAVE to be in a seperate Instance or is this just for aesthetics? We have Enterprise but it is all in its own instance installed by a consultant. i ask as we are moving the databases to a new SQL 2014 Server and are curios why its in its own instance.
You don’t have control over the different database instances configuration on Front End servers, this is the hardcoded server behavior.
Thanks for this article.
Just a little question about SQL server Express on Skype for Business Standard Edition, do we need to install sql server update (service pack and last cumulative update) ?
Thank you
No SQL Express is automatically installed as part of the SfB Server installation. You should not install it separately.
Yes don’t install it separately but you’ll need to keep it up to date. I apply the latest SP and CU immediately after installing Lync/Skype on a server. (Install Lync/Skype, install latest Lync/Skype CU, install latest SQL SP, install latest SQL CU.)
The most common miss I’ve seen on customer sites is not patching SQL Express on Skype/Lync Servers. So either ensure the patching regime includes it, or keep it up to date manually, for example when applying a Lync/Skype Cumulative Update, also patch SQL in the same maintenance window.
While we may let SfB install SQL Express itself, should we not keep SQL patched on the servers going forward?
Windows Update should take care of that.
The most common miss I’ve seen on customer sites is not patching SQL Express on Skype/Lync Servers. So either ensure the patching regime includes it, or keep it up to date manually, for example when applying a Lync/Skype Cumulative Update, also patch SQL in the same maintenance window.
Can we install archiving and monitoring database in sql express on separate server
No, SQL Express cannot be used to store the Monitoring databases (see: http://windowspbx.blogspot.com/2015/06/installing-skype-for-business-server.html)
Hi, How can we get the IP addresses of SfB Clients connected to Server?
Can some post the er diagram of the skype back end database?
HI
if I lose my backend server from my pool, what is the user impact given that my pool no longer accesses SQL databases,
Skype for Business employs a “lazy-write” approach to committing changes to the back-end database, so as long as the front-end pool is running a partial copy of the database is locally available to retain functionality. Certain changes will not be possible or saved until the back-end database is restored.