Introduction
Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012. Master Data Services is an extensible Master Data Management (MDM) platform, which is built on top of the SQL Server database engine and Windows Communication Foundation (WCF).
MDS allows you to create a centralized hub for creating and managing enterprise master data and provides many functionalities, some of them are:
- Transaction logging – MDS does transaction logging for all the changes made to the master data by each user; for example what changes were done, who made the changes, when the changes were made, what the value was before the change and what the value is after the change. This transaction logging feature allows users to revert to old values if the changes are no longer required. To learn more about it see A First Look at SQL Server 2008 R2 Master Data Services.
- Versioning – MDS also allows you to define multiple versions of your master data. For example, you can have one committed version that will be available for consumption by downstream applications and at the same time, you can have an open version of the same master data to make changes. This ensures that downstream applications get consistent data because no changes are permitted on the committed version of master data and at the same time, you are able to do changes to the open version of the master data. A version could be either in open (available for changes), locked (locked for validating master data against defined business rules) and committed (no changes are permitted and hence this version is available for downstream applications for consumption) version; to learn about how versioning works, see MDS Versioning, Permission and Security.
- Role-based security – MDS has fine grained Active Directory (AD) role based security model to secure the functional area (the kind of operation a user can perform), model’s entities and columns (object level security) and hierarchy (row or data member level security); to learn more about Permission and Security in MDS see MDS Versioning, Permission and Security.
- Subscription views – MDS allows creating subscription views to consume master data from the MDS database repository. Reporting, analytical and other applications can consume these subscription views to get access to the master data from the central master data hub.
In this article, I will be talking about installing, configuring Master Data Services in 2012 and a way to deploy sample models.
To learn more about Master Data, Master Data Issues, Master Data Management, Master Data Services and its architecture see A First Look at SQL Server 2008 R2 Master Data Services.
Installation of Master Data Services in SQL Server 2012
Unlike MDS in SQL Server 2008 R2 where we had separate installer (msi) file for Master Data Services installation, in SQL Server 2012 the installation is built in with the main installation wizard of SQL Server 2012, as you can notice below under the Shared features node. Please note, unlike SQL Server database engine, MDS is a shared feature and can be installed only once, though you can create or configure multiple sites/services for MDS from single installed MDS bits:
Feature Selection
Configuration of Master Data Services in SQL Server 2012
Configuration of MDS in SQL Server 2012 is not much different from the configuration in SQL Server 2008 R2; you need to launch Master Data Services Configuration Manager from the Programs menu as shown below:
Master Data Services Configuration Manager
From Master Data Services Configuration Manager, the first thing that you need to do is to create an MDS database or select one if already available. (You can also upgrade MDS database from earlier version from this screen):
Upgrade MDS database
On the second screen of the wizard, you create a MDS website and specify the database (that we created above) to store the data for that MDS application. You can also notice an option of enabling integration with Data Quality Service; another new feature in SQL Server 2012 for data cleansing and data matching:
Web Configuration
To learn more about configuring Master Data Services and its pre-requisites see Getting started with SQL Server 2008 R2’s Master Data Services.
On successful configuration you will see a site similar to that shown below. Again, notice the interface is similar to what we have with SQL Server 2008 R2; the difference on the main screen is the link to install MDS Add-in for Microsoft excel. To learn more about these functional areas, see MDS Versioning, Permission and Security.
Master Data Manager
Deploying Sample Models in Master Data Services in SQL Server 2012
MDS allows you to create deployment packages for exporting and importing models onto another environment (for example moving a model from development to test to production environment).
The way we do deployment in MDS in SQL Server 2012 is different from the way we do the deployment in MDS in SQL Server 2008 R2. In MDS of SQL Server 2008 R2 we have the Model Deployment Wizard, which allows you to create / deploy packages with data as well, but in MDS of SQL Server 2012 a package can only be deployed with the Model Deployment Wizard if the package does not contain data; if the package contains data we need to use the MDSModelDeploy utility from the command prompt; to learn more about deploying model with this utility see Deploy a Model Deployment Package by Using MDSModelDeploy.
Model Deployment Wizard
The MDSModelDeploy utility is available in following folder:
<Installation Drive>:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration
Launch the command prompt with administrator elevation and then go to the above folder and execute this command to list all the MDS services installed on the local machine:
MDSModelDeploy.exe istservices
MDSModelDeploy.exe listservices
By default MDS has 3 different sample models that ship with \SQL Server and these model files are available at this location:
<Installation Drive>:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages
To deploy a product sample file to the MDS1 service, you can execute this command:
MDSModelDeploy.exe deploynew -package "<Installation Drive>:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages\product_en.pkg" -model Products -service MDS1
MDSModelDeploy.exe deploynew -package
After deploying a model, it will not appear immediately in Master Data Manager, click on the “Refresh cached information” link available on the right side of it and it will appear in the model list:
Refresh Cache
Getting Started with Master Data Services in SQL Server 2012
There are several significant enhancements in MDS of SQL Server 2012 and the first thing that you will notice is the Silverlight based user interface on the Master Data Manager tool. The other important enhancement is MDS Add-in for Microsoft Excel; now you can create entities and load data in bulk from Excel itself or without doing it one by one in the web user interface of Master Data Manager.
Select the product model (which we deployed above) and click on explorer link:
Click on the Explorer link
And this is what you will see; a Silverlight based interface that is more intuitive and more responsive:
Silverlight based interface
Conclusion
Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012. In this article, I talked about installing, configuring Master Data Services in 2012, a way to deploy sample models and how to get started working with it.
Resources
Master Data Services in SQL Server 2012
Data Quality Services in SQL Server 2012
A First Look at SQL Server 2008 R2’s Master Data Services
Getting started with SQL Server 2008 R2’s Master Data Services
