Patching SQL Server
I recently installed my first deployment of Master Data Services (MDS). I installed the SQL Server 2014 (Enterprise Edition) MDS front-end on a new server with the intent to use an existing SQL Server 2014 (Enterprise Edition) database engine for the back-end. Unsurprisingly, I ran into some issues, and as luck would have it, they were not all well documented. I found some help online in places like Microsoft Connect and the TechNet Forums, but much of what I found was only partially complete.
So why am I talking about installation issues in a blog post on patching MDS? As Einstein would say, it’s all relative. Many of the issues I encountered will reoccur if I am not careful when I do patching. The lessons learned during install will save me a lot of time later, especially if someone else does patching incorrectly, and I get to deal with issues that arise. Because of this exercise, I know what to check if certain errors are raised by the MDS front-end.
Some Installation Tips
Before I start in on how to patch MDS, I want to recommend some tips for installing MDS.
Make sure all required Windows features and roles are installed
MDS requires a lot of Windows features on the server hosting the MDS front-end and many of the child features are not enabled by default when you enable the parent feature. You have to drill-down and ensure that every feature is enabled. You can find a list of the features to install in Books Online here: Web Application Requirements (Master Data Services).
This is for the front-end only. If you are installing the front-end on a separate server from the back-end like I did, you do not need to enable any additional Windows features or roles on the database engine server.
Use the most current build of SQL Server, but at least Cumulative Update 4 (CU4)
There have been a lot of fixes for MDS in the post-RTM CUs for SQL Server 2014. There is a specific issue with using a remote back-end that was first fixed in CU3. This issue reoccurred in CU3 for some users, and was again patched in CU4. If you are not on at least CU4 on BOTH the front-end and back-end, you may receive an error in the MDS site that says the “current SQL Server edition is not supported by MDS“.
This error is meant to be reporting that you are not using a supported edition of SQL Server for the back-end. MDS is an Enterprise or BI Edition only feature. You can not use Standard or Express Edition for the MDS back-end. To fix this error, update the patch level of both the front-end and back-end to the newest build (at least CU4) and that both are the SAME patch level.
After completing the upgrade, log in to Master Data Services Configuration Manager, select the Database Configuration tab, select the database, and look to see if it needs to upgrade the database. If so, click the Upgrade Database button. More on this later.
An interesting thing to note about installing a CU on the remote front-end server with no components other than MDS installed, the CU update package does not report that it is patching MDS. If you update it on an instance that has other components installed, it does report that ti updated MDS.
Make sure the patch levels on the front-end and back-end are identical
This is an issue you are bound to hit eventually if you are using a remote front-end for MDS because they would be patched separately. If one of the servers is at a lower patch level than CU4, you may get the “edition not supported” error. Otherwise, when you open the MDS site, you will receive an error that the client version is incompatible with the database version like below.
MDS Client Version Error
click to enlarge
The text of the error is “The client version is incompatible with the database version. Ask your administrator to upgrade the client components, the database components, or both. Run the Master Data Services Configuration Manager on the server for more information.“.
If you receive this error, you need to update one of the components to the higher level of the other server. Or both to a suitably higher patch level. After patching, you will once again need to open Master Data Services Configuration Manager and check to see if the database needs to be upgraded.
Database Compatibility Level
For reasons that I have not been able to answer, the MDS database is created in compatibility mode 100 (SQL Server 2008). I changed the compatibility mode after discovering this, before I performed the CU upgrade on the remote MDS front-end. After running the database upgrade, I noticed that the compatibility level was reset back to SQL Server 2008. I reset it back to the proper level as a best practice, but I don’t expect it to stay there.
Carefully Select an Administrator Account
When you create the MDS database, it asks you to specify an account to make the administrator. If someone else is going to administer the MDS application, your inclination is to make them the administrator. You need to make yourself the administrator at this point. Once MDS is set up and running, this will be the only person who is able to log in to the application. This means you cannot even check it to make sure it is working. Set your account as the administrator and then go into the application and add users and other administrators.
If you make this mistake like I did, there is no supported way to modify the admin user except to drop and recreate the MDS database.
Set httpGetEnabled = “True” in web.config
If you get an access denied error when you try to access the MDS website, you may need to set httpGetEnabled = “True” in the web.config config file for the site. Modifying web.config will cause the application to restart and should take effect almost immediately.
If you are configuring the site to use SSL, you will also need to enable httpsGetEnabled.
Process for Patching a Remote MDS Front-end
If an update does not require any fixes for MDS, there probably will be no requirement to install the same CU on the front-end. I have not been able to test this yet because all of the CUs I have installed for SQL Server 2014 thus far have had fixes for MDS (currently at CU6). Even if not required by the application, I would recommend to keep them at the same build level as a general best practice. This is the process I recommend for patching MDS.
- Install the patch on the database engine server
- If the MDS front-end on the same server as the back-end, the patch will update the shared services for that version of SQL Server
- If the MDS front-end is on a remote server, install the patch on the front-end server
- Check to see if the database needs to be upgraded
- Open Master Data Services Configuration Manager on the front-end server
- Switch to the Database Configuration tab
- Click Select Database
- Connect to the back-end server and click Connect
- Make sure the correct database is selected and click OK
- If an error is reported that the database needs to be upgraded, click Upgrade Database
- Close Master Data Services Configuration Manager
I did encounter an issue one time where the MDS website continued to display the version error after the database upgrade. I rebooted the server, and the site was working with no errors. I have not been able to reproduce this error so I don’t expect it to occur regularly, but in case you continue to receive the error in the site, you should try restarting the MDS service or failing that, rebooting the server.
Database Upgrade needed message:
Database Upgrade Error — click to enlarge
Reposted from www.sqlsoldier.com.
