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

By:   |   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.

    SQL Server 2014 Setup for the Master Data Services


  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):

SQL Server Master Data Services Configuration Utility

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:":

    SQL Server Master Data Services Create Database


  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. Connect to the SQL Server Instance for Master Data Services


  4. Specify the Database name on the next screen:

    Specify Database Name for SQL Server Master Data Services


  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.


    6. SQL Server Master Data Services Administrator Account

    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. Create SQL Server Master Data Services Database Summary


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


    8. SQL Server Master Data Services Progress and Finish


  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:

Create Test Database for SQL Server Master Data Services

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:

    Start Web Configuration for SQL Server Master Data Services


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

    Start Web Configuration for SQL Server Master Data Services


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

    HTTP Binding Information for SQL Server Master Data Services


  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:

    Associate Application with Database for SQL Server Master Data Services


  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:

    Connect to Database for SQL Server Master Data Services


  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:

    SQL Server Master Data Services WebConfigfuration Apply

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:

SQL Server Master Data Services Test Environment Web Configfuration

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Svetlana Golovko 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


Comments For This Article




Tuesday, June 13, 2017 - 11:09:00 PM - Svetlana Golovko Back To Top (57383)

Hi,

You will need to configure Windows Cluster or Network Load Balancing for IIS. I am not the IIS expert, but just having a second standalone IIS server doesn't seam right.

Here is a white paper from Microsoft https://msdn.microsoft.com/en-us/library/jj884069.aspx "Installing Master Data Services in an AlwaysOn Environment". I think this white paper will be a good start. 

 

I hope this will help a little bit.

Svetlana

Tuesday, June 13, 2017 - 12:36:00 AM - Spiralcb Back To Top (57293)

Hello,

I'm investigating about high availability solution for MDS. 

I would create a second mds web site on a second IIS server and link it to the same MDS database. 

Is it a good solution for HA ? 

Thanks a lot. 

 

Thursday, July 14, 2016 - 8:19:09 AM - ajay Back To Top (41884)

it halped me lot !

 














get free sql tips
agree to terms