Importance of Setting the Correct SQL Server Collation

This was a really interesting issue that I ran into at a customer recently.  The goal was to install the Monitoring Server role so that they could report on adoption and usage of Lync 2010.  They already had an existing Lync Server 2010 environment up and running, so we were just adding the Monitoring Server role to the topology.  However when publishing the topology we encountered the following error:

Viewing the deployment log showed the following error message:

Error: Script failed (code "ERROR_EXECUTE_BATCH") when installing "MonitoringStore" on "TEST-SQL.test.deitterick.com". For details, see the following log file: "C:\Users\Administrator.TEST\AppData\Local\Temp\Create-MonitoringStore-TEST-SQL.test.deitterick.com-[2013_06_24][11_54_11].log"

Looking at the Create-MonitoringStore log file showed the following:

Error executing batch DbRtcCdr.sql on LcsCDR

For some reason there appears to be a problem creating the database, so we went to the SQL Server to see if we could see any issues.  On the SQL Server, in SQL Server Management Studio, I noticed a couple of things.   First, the LcsCDR database did get created, but it was still in the Restricted User state.  That ruled out any kind of firewall or permissions issue.  Secondly, the Address Book databases database names didn't look correct:

The Address Book database names should be rtcab and rtcab1.  They don't get created with capital letters, which means that someone changed them for some reason.  We decided to rename them back to the default values.  Once we did that, we started seeing the following error message on the Front End Server:

Also, on the SQL Server, we started seeing the following message in the Event Log:

The Front End Server is trying to connect to the RtcAb database, but the SQL Server is saying that it can't open that database.  However when we change the Address Book database names back to having capital letters, the errors go away and everything is fine.  So it appears the issue is that the SQL Server can't find the database if the database name in the connection string from the Front End Server isn't the same case.  Case sensitivity is a collation setting on the SQL Server instance.  You can find more information about SQL Server collations in the SQL Server Collation Fundamentals TechNet article.  Looking at the SQL instance properties in SQL Server Studio Manager shows us the root of the problem:

The Server Collation for this instance is set to Latin1_General_BIN, not the default of Latin1_General_CP1_CI_AS.  When you install a SQL instance, there are options for changing the collation settings:

The solution was to go through the process of changing the Server Collation for this instance.  Once the Server Collation was set to the default of Latin1_General_CP1_CI_AS, we were able to complete the creating of the Monitoring Server databases.