Leveraging Storage Spaces Direct for SQL Server High Availability
Thursday, July 19, 2018 - click here to learn more
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?
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.
- MDS Web Application required to be on one of the following
- Windows Server 2008 SP2
- Windows Server 2008 R2 SP1
- Windows Server 2012
- Remote database has to be on one of the following SQL Server
- 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:
- Start SQL Server 2014 installation. The SQL Server Installation Center will start.
- Click on the Installation option on the left side menu.
- Click on the "New SQL Server standalone installation or add features to an existing installation".
- Review the "Install Rules" screen and if there are no issues click the "Next" button.
- Enter the product key on the next screen and click the "Next" button.
- Accept the license terms on the "License Terms" screen and click the "Next" button.
- Keep the default option selected ("SQL Server Feature Installation") on the next screen and click the "Next" button.
- 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.
- 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=18.104.22.168, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."
Run "SQL Server 2014 Master Data Services Configuration Manager" (MDS Configuration Manager):
- Select the "Database Configuration" option on the left side menu and click the "Create Database" button next to the "SQL Server Instance:":
- Create Database Wizard will start. Click on the "Next" button.
- Connect to the SQL Server Instance. Specify the SQL Server
instance where the MDS Development database will be created:
- Specify the Database name on the next screen:
- 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.
- Review the Summary on the next screen and click the "Next" button:
- Click the "Finish" button when all of the tasks are completed:
- 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
This tip has steps to change the MDS Administrator's account.
Repeat steps 1-8 to create a database for the Test environment on the same or on a different SQL Server instance:
- 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:
- Enter the web site and application pool settings
for the Development environment in the pop-up window:
- Click "OK" on the HTTP Binding pop-up warning. We can configure HTTPS for
the web site later:
- 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:
- 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:
- 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.
- 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.
Last Update: 2016-07-12
About the author
View all my tips