Many people get caught up in the excitement of deploying a new SfB environment, and hit Next…Next…Finish. This however can lead to some unexpected consequences.
When publishing the topology via Topology Builder, there is an oft-missed or simply overlooked option when creating databases. The option to choose the Database File Location!
Publishing Topology – Select Databases
As you can see in the screenshots above, there are actually 3 options presented if you select the database and click on Advanced. The default option being “Automatically determine database file location.”
- “Automatically determine database file location. This option will determine the database file location based on the available physical drives on the target SQL Server and distribute the database and log files for optimal performance.”
- “Use SQL Server instance defaults – This option will place the database and log files based on target SQL server instance settings. If you choose this option, read the product documentation on how to determine the best paths for optimal performance and use SQL Manager to change the paths appropriately.”
- “Use these paths on target SQL Server – Path to log files. Path to Database files”
If you leave the default option selected, Topology Builder will use a built-in algorithm to choose the storage location for the database and log files. Full disclosure…I’ve looked for details on how the built-in algorithm chooses it’s file location but haven’t found anything authoritative from Microsoft. If anyone has something, please pass it along and I’ll reference it.
Alternatively, here’s an interesting read showing the built-in algorithm in action while deploying the Front End databases. http://howdouc.blogspot.com/2012/11/exploring-default-installation-and-sql.html.
As this is routinely overlooked, I’d like to review 2 scenarios and see how this could impact your deployment.
First though, a quick review of the SQL Instances used in a SFB deployment
Back End – In a Standard Edition Pool, the “Back End” databases are installed locally on SQL Express. In an Enterprise Edition Pool, they are deployed on a dedicated SQL server.
- RTC – Commonly referred to as the “RTC Instance”, not to be confused with the rtc database. This instance contains the CMS Master, Response Group, LIS, and can contain the P-Chat, Archiving and Monitoring databases.
Front End – These are the instances that are installed on all non-backend SfB servers: Front End, Mediation, Edge, Director and P-Chat.
- RTCLocal – Stores local copy of the master CMS database and other database used for some user information
- LyncLocal – The Lync Storage Service (LYSS) is used to maintain HA within a FE pool
Scenario 1: Databases Installed by the Topology Builder
(EE Back End, Monitoring, Archiving, P-Chat)
An EE pool Back End, Monitoring, Archiving and P-Chat all require a dedicated SQL server to be deployed. This could be a single server, a Mirrored Pair, a Cluster, or an Availability Group. Which means someone (most likely a DBA) has to go through the trouble of building and deploying the SQL server. When building a SQL server, it is best practice to separate Data and Log files onto separate logical drives. This is done for performance and I/O optimization.
In these scenarios, the databases associated with the “RTC Instance” are installed via the Topology Builder. The screen shots above reflect the options that will be presented during the Topology Builder Wizard for creating the databases.
When the default option (Automatic) is left selected, the Topology Builder uses the built-in algorithm to programmatically select the location for the data and log file paths. This results in SQL Database and Log files being deployed in what may seem to be random locations on C: D: E: F: or whatever drives have been provisioned. This tends to frustrate DBA’s, especially after you’ve just spent an hour convincing them to give you Sysadmin rights to the Instance because you need it in order to deploy the databases.
When the second option (Use SQL Server instance defaults) is selected, the Topology Builder uses the SQL Server instance settings to determine the database and log location. If the DBA or SQL admin has done their job, the folder structure for the data and log file paths have already been created, and the SQL Instance settings have already been adjusted accordingly. *In my findings, this is the rule and not the exception. 99% of the time, this is the option I select.
The third option (manual) gives you the discretion of hard-coding the database and log paths.
Keep in mind that behind the scenes, the Topology Builder is just using PowerShell cmdlets to execute the work. Yes, all of this can also be accomplished with the Install-CsDatabase cmdlet. Also note, that the automatic option using the built-in algorithm only works for stand-alone SQL servers, and will not work with a SQL Server cluster.
NOTE: This built-in algorithm works with a stand-alone SQL Server, it will not work with a SQL Server cluster. To install a database on a SQL Server cluster your command must include either the DatabasePath or the UseDefaultSqlPath parameter
Scenario 2: Databases installed by the Deployment Wizard
(SE Back End, Front End, Mediation, Edge, and Directors)
Databases installed by the Deployment Wizard using SQL Express, do not give you the option of selecting a deployment location. These will be installed using the “Automatic” built-in algorithm.
It is possible to pre-deploy SQL Express and define the default database and log locations. http://guybachar.net/2014/03/29/how-to-install-lync-sql-express-to-a-non-system-drive/
In a Standard Edition (SE) deployment, the “RTC Instance” (Back End) is installed as part of the “Prepare First Standard Edition Server” in the Deployment Wizard, and not as part of the Topology Builder. This step installs a SQL Express Instance locally on the SE and uses the built-in algorithm for database and log locations.
Two (2) additional SQL Express instances will be installed on all SfB servers, such as Front End Servers, Mediation Servers, Edge Servers and Directors.
- The RTCLocal instance will be installed during Step 1 of the Deployment wizard “Install Local Configuration Store.”
- The LyncLocal instance is installed during Step 2 of the Deployment Wizard “ Setup or Remove Lync Server Components”.
Again, if you review this article on how the built-in algorithm places the databases, you may see a similar behavior in your environment depending on the drives you have provisioned on the server. http://howdouc.blogspot.com/2012/11/exploring-default-installation-and-sql.html.
What to do when your databases are in the wrong place?
Well, as usual it depends!
If this is a new deployment, and these are all new databases, you could use SQL Management Studio to connect to the SQL instance and delete the databases. Once the databases are deleted, you can use the Topology Builder to Create the databases again. This time, slowing down long enough to change the default selection to either honor the SQL Instance settings, or manually specify the paths.
If it’s one of the SQL Express instances installed via the Deployment Wizard, you could completely uninstall SQL from Programs and Features. Then pre-install the SQL Express instances to the drives and folder paths you prefer. http://guybachar.net/2014/03/29/how-to-install-lync-sql-express-to-a-non-system-drive/. Re-running Steps 1 and 2 after SQL Express has been installed.
You could move the databases from within SQL Management Studio.
In the end, this article isn’t intended to detail how to fix it. Instead its intent is to inform you of what choices you have when initially deploying. How you fix your environment is entirely up to you. Keep in mind, Google is your friend!