How to configure and integrate SQL Server Master Data Services and Data Quality Services

By Danielle Arad - June 22, 2012

DQS and MDS are both SQL 2012 features supported by BI and Enterprise editions. Data Quality Services (DQS) can be used by business users and IT professionals to maintain the quality of their data and to ensure that the data is suited for their organization’s business usage. Master Data Services (MDS) is a SQL Server feature for master data management (MDM). MDM is used to discover non-transactional lists of data, with the goal of assembling maintainable master lists.

In this post I will guide you through the configuration process of those features and the integration of DQS and MDS. If you haven’t installed them yet use the installation CD\ISO file and add the features during SQL Server installation process.

And to save you the trouble, I’ve prepared a new version of SQL Server 2012 w/ BI Stack template with these features.

So let’s begin!

  • The DQS installation process is really easy. Click on Start -> All Programs -> SQL Server 2012 -> Data Quality Services -> Data Quality Server Installer.

This will open an installation script; all you have to do is type in a Data master key password.

When the script finishes running you can start using Data Quality client located under the same folder in the Start menu.

  • Before we start the configuration of MDS we should prepare the server.

On Windows Server 2008 / 2008 R2, open the Server Manager and add the Web Server (IIS) role and the following required role services:

Web Server

– Common HTTP Features

Static Content

Default Document

Directory Browsing

HTTP Errors

– Application Development

ASP.NET

.NET Extensibility

ISAPI Extensions

ISAPI Filters

– Health and Diagnostics

HTTP Logging

Request Monitor

– Security

Windows Authentication

Request Filtering

– Performance

Static Content Compression

– Management Tools

IIS Management Console

Add the following features:

– .NET Framework 3.0 Features

– WCF Activation

HTTP Activation

Non-HTTP Activation

– Windows Process Activation Service

Process Model

.NET Environment

Configuration APIs

 

Create Service Accounts:

Create a service account for the Master Data Manager Web application. You must specify an identity for the application pool in which the application runs.

  • Now our server is ready and we can continue with MDS configuration. Click on Start -> All Programs -> SQL Server 2012 -> Master Data Services -> Master Data Services Configuration Manager.
  • Navigate to the Database configuration tab and click on Create Database. The Create database wizard will open up.
  • Specify the SQL Server instance you want to host the MDS database in, the authentication type, and the user credentials that will be used to connect the instance. Test the connection. If the connection is successful click on next.

  • Specify the database name and the collation settings and click on next.

  • Choose an administrative account that will be granted full access to all models and click on next. Review the summary and click on next to finish the process.
  • Our next step will be creating a web application for MDS. Navigate to the Web Configuration tab and select create new web site.

  • Specify the website name, port, host header (optional) and service account credentials. The account is added to MDS_ServiceAccounts group in active directory and to the database role group in the MDS database. I would like to note that if you have other web applications running on the same server, don’t use ports that are already taken.

Click on OK and continue.

  • Our next step is to associate an application with a database. Click on Select and choose a database from the list. Perform a connection test and click OK. After you’re done click on Apply and the wizard will create the web application.

Congratulations! This is how your web application will look:

  • We are almost done! Our last step is integrating MDS and DQS.

In order to do this we need to go back to the Master Data Services Configuration Manager and navigate to the web configuration tab. Choose your web application and database, and click on the Enable integration with Data Quality Services button.

Great! You successfully integrated MDS and DQS!

And to save you the trouble I’ve prepared a new version of the SQL Server 2012 w/ BI Stack template that contains these features.