Install and configure multiple instances of Master Data Services MDS on the same server

By: Svetlana Golovko   |   Updated: 2016-07-12   |   Comments (3)   |   Related: > Master Data Services

Problem

We would like to minimize the number of SQL Server installations to save on licenses. This will also allow us to reduce System/Database Administration overhead on managing servers and patching SQL Server instances. Can we install MDS (Master Data Services) on a server and have both Development and Test MDS instances on this server?

Solution

Yes, this configuration is supported. We will provide the steps starting with SQL Server 2014 MDS installation and ending with the Test and the Development environments setup on the same server.

If you have a dedicated server just for MDS you will need to install only the MDS feature. The MDS databases could be on a different (remote) SQL Server.

Pre-Installation Requirements

• MDS Web Application required to be on one of the following operating systems:
  • Windows Server 2008 SP2
  • Windows Server 2008 R2 SP1
  • Windows Server 2012
• Remote database has to be on one of the following SQL Server versions/editions:
  • SQL Server 2014 Business Intelligence (64-bit) x64
  • SQL Server 2014 Enterprise (64-bit) x64
  • SQL Server 2014 Developer (64-bit) x64
  • SQL Server 2012 Business Intelligence (64-bit) x64
  • SQL Server 2012 Enterprise (64-bit) x64 – Upgrade from SQL Server 2008 R2 Enterprise only
  • SQL Server 2012 Developer (64-bit) x64
  • Microsoft SQL Server 2008 R2 Enterprise (64-bit) x64
  • Microsoft SQL Server 2008 R2 Developer (64-bit) x64
• Client computer must have Silverlight version 5.
• Check other requirements here including required roles, role services, and features.

MDS Feature Installation

Here is one of the past tips with the steps for the SQL Server 2008 R2 MDS installation.

Other versions have similar steps and the installation process if very straight forward:

1. Start SQL Server 2014 installation. The SQL Server Installation Center will start.
2. Click on the Installation option on the left side menu.
3. Click on the "New SQL Server standalone installation or add features to an existing installation".
4. Review the "Install Rules" screen and if there are no issues click the "Next" button.
5. Enter the product key on the next screen and click the "Next" button.
6. Accept the license terms on the "License Terms" screen and click the "Next" button.
7. Keep the default option selected ("SQL Server Feature Installation") on the next screen and click the "Next" button.
8. On the "Feature Selection" screen scroll down and select "Master Data Services" feature. Note that prerequisites for the feature will be automatically installed from the media. Click the "Next" button.
   
   
   
   
   
   
9. Click "Install" on the last screen.

To simplify the installation process you can use command line option for the MDS installation. From the installation folder run the following command:

Setup.exe /qs /ACTION=Install /FEATURES=MDS /IACCEPTSQLSERVERLICENSETERMS /UpdateEnabled=False 
  

See complete reference from Microsoft about SQL Server installation using command line.

Make sure that you installed at least SQL Server 2014 Service Pack 1 before you start MDS configuration. Otherwise you may get this error: "Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."

MDS Configuration

Run "SQL Server 2014 Master Data Services Configuration Manager" (MDS Configuration Manager):

Database Configuration

1. Select the "Database Configuration" option on the left side menu and click the "Create Database" button next to the "SQL Server Instance:":
   
   
   
   
   
   
2. Create Database Wizard will start. Click on the "Next" button.
3. Connect to the SQL Server Instance. Specify the SQL Server instance where the MDS Development database will be created:
   
   




4. Specify the Database name on the next screen:
   
   
   



5. Specify the MDS Administrator Account. By default it will be populated with the name of the person who runs the MDS Configuration Manager, but you may need to have a separate service account created for this. Note that in SQL Server 2014 and earlier there could be only one MDS Administrator. SQL Server 2016 can now have more than one Administrator.





This tip has steps to change the MDS Administrator's account.

6. Review the Summary on the next screen and click the "Next" button:







7. Click the "Finish" button when all of the tasks are completed:







8. Review the "System Settings" when the Database Creation Wizard will close. Some of the settings that you may need to change or configure are:
   • Master Data Manager URL for notifications
   • Database Mail profile

Repeat steps 1-8 to create a database for the Test environment on the same or on a different SQL Server instance:

Web Configuration

1. After the MDS database is created and configured click on the "Web Configuration" option in the MDS Configuration Manager left side menu. Select "Create new website" under the "Website" drop down menu:
   
   
   



2. Enter the web site and application pool settings for the Development environment in the pop-up window:
   
   
   



3. Click "OK" on the HTTP Binding pop-up warning. We can configure HTTPS for the web site later:
   
   
   



4. Now when the web site is created we need to associate it with an MDS database that we have created earlier for the Development environment:
   
   
   



5. Click "Select" button. The "Connect to Database" dialog will pop-up. Enter the SQL Server instance name that is hosting the MDS Development database (specified in step 3 of the database creation section above). Click "Connect" button. The "Master Data Services database" drop-down menu will be populated with MDS databases names that were created on this SQL Server:
   
   
   



6. Select the MDS_Dev database and click "OK". Selected SQL Server instance and database are now associated with the web application. Click "Apply" to save the settings and "OK" on the next pop-up window:
   
   
   

Repeat steps 1-3 to create a web site for the Test environment. Specify a different web site name, port number (for example, 8088), application pool and the application pool's identity.

Repeat steps 4-6 of the web configuration to associate Test web site with the database created for the Test environment:

Start Internet Explorer and test created environments by opening each web site: http://mdshost:8080 and http://mdshost:8088 (where MDSHOST is the name of your SQL Server where MDS is installed and configured).

Follow steps in this article for Securing MDS with HTTPS.

Next Steps

• Check a workflow provided by Microsoft that provides step by step instruction for SQL Server 2014 MDS installation and configuration.
• Read how to install SQL Server 2016 MDS here.
• Read all MDS tips here.
• Learn about what's new in MDS 2016 here.
• Check other Microsoft resources about MDS.
• Read other tips about SQL Server installation including command line or unattended installations.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips