A business case that requires the usage of Master Data Services finally landed in my desk. As part of delivering this business case, I had to install and configure SQL Server 2014 Master Data Services in production. I had previously installed and configured SQL Server Master Data Services in a test virtual machine environment so I was certain that the production setup would go smoothley. Boy, I was wrong :(.
Everything was going fine until I had to click the Explorer link (shown below) to manage master data and hierarchy relationships.
As the page was loading, I received the Error message [HttpWebRequest_WebException_Remoteserver] Arguments: NotFound…. as shown below:
I tried to ignore this message by click the OK button but when the error message went away, I received a blank page as shown below:
I then tried to access my MDS models by using Excel but I received an error message The requested service “/service.svc/bhb” could not be activated as shown below:
With both browser and Excel add-in not allowing me to access my Master Data models, I was forced to futher investigate this issue.
So I tried following different ways to no avail:
- Verifed Minimum Master Data Services Installation Requirements
- I ensured that applicationHost.config contains a location tag element
- In Excel, I tried specifying my MDS server with and without default.aspx (i.e. http://localhost:8080/default.aspx)
- Verified that IIS Handler Mappings supports *.svc extension
Finally, I remembered that in a previous lifetime I used to masquerade as a Windows System Administrator. So, I logged onto the server hosting my MDS instance and went through the application error logs. Immediately, I noticed an error message coming from System.ServiceModel 220.127.116.11.
As I read through the details of a very-loooooong error message, I noticed an exception along the lines of “Memory gates checking failed because the free memory (1867968512 bytes) is less than 5%” as shown below:
I cleared the cache and increased total memory and bang! It worked! I could access the Explore page and successfully connect from Excel.
I haven’t yet explored Master Data Services in SQL Server 2016 but I know that Microsoft has made some improvements. I hope that one of those improvements is making sure that SQL Server developers/administrators don’t have to spend hours trying to troubleshoot an error that relates to Windows Server! The least they could do – is display a correct error message in the browser or Excel Add-In. I shouldn’t have to go through the logs to find out that this whole thing was about an insufficient memory.
I am hoping that Master Data Services in SQL Server 2016 easily returns an error message saying “Insufficient memory!”.