Master Data Services (MDS) is a powerful feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012. It serves as an extensible Master Data Management (MDM) platform, built on top of the SQL Server database engine and Windows Communication Foundation (WCF). MDS allows organizations to create a centralized hub for managing enterprise master data, providing various functionalities to ensure data integrity and consistency.

Key Features of Master Data Services

Master Data Services offers several key features that make it a valuable tool for managing master data:

• Transaction Logging: MDS logs all changes made to the master data, including details such as who made the changes, when they were made, and the old and new values. This allows users to revert to previous values if needed.
• Versioning: MDS allows for the creation of multiple versions of master data. This ensures that downstream applications receive consistent data, while still allowing for changes to be made to an open version of the data.
• Role-based Security: MDS provides a fine-grained security model based on Active Directory (AD) roles. This allows for secure access control at various levels, including functional areas, entities and columns, and data member levels.
• Subscription Views: MDS allows the creation of subscription views, which can be consumed by reporting, analytical, and other applications to access master data from the central hub.

Installation and Configuration

Installing and configuring Master Data Services in SQL Server 2012 is straightforward. Unlike in SQL Server 2008 R2, where MDS had a separate installer, in SQL Server 2012, MDS is included in the main installation wizard. Once installed, you can use the Master Data Services Configuration Manager to create an MDS database and configure the MDS website.

Deploying Sample Models

MDS allows for the creation of deployment packages to export and import models between environments. In SQL Server 2012, the deployment process is different from SQL Server 2008 R2. While the Model Deployment Wizard can be used for packages without data, the MDSModelDeploy utility must be used for packages with data. This utility can be accessed through the command prompt.

Getting Started with Master Data Services

SQL Server 2012 introduces significant enhancements to MDS, including a Silverlight-based user interface in the Master Data Manager tool. Additionally, the MDS Add-in for Microsoft Excel allows for the creation of entities and bulk data loading directly from Excel.

Conclusion

Master Data Services is a powerful feature that enables organizations to create a centralized hub for managing enterprise master data. With features such as transaction logging, versioning, role-based security, and subscription views, MDS provides the necessary tools for ensuring data integrity and consistency. By understanding the installation, configuration, and deployment processes, organizations can effectively leverage MDS to streamline their master data management.

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

See all articles by Arshad Ali