An Overview of SQL Server Master Data Services

As enterprises add systems and acquire companies, they will most likely find themselves faced with a growing inconsistency amongst core data utilized by their key systems. This leads to an inability to provide quality data to management. Master Data Services for SQL Server provides a means to centrally manage and serve up this critical enterprise data.

As enterprises grow and change over time, they add systems
and acquire companies. They will most likely find themselves faced with a
growing inconsistency amongst core data utilized by their key systems. This
leads to an inability to provide quality data to management for an overarching
view of the business for proper decision making. Master Data Services for SQL
Server provides a means to centrally manage and serve up this critical
enterprise data.

Master Data Services

Master Data Service’s helps businesses set up and manage
critical, cross-system data (such as customer lists) in a single location in
order to provide one organization-wide authoritative source. Companies can be
leaner and more agile when one authoritative source supplies key data to all
its mission-critical systems. One source means systems can be integrated
faster, data can be rolled-up cleaner, and business intelligence can provide a
more enterprise-wide view.

Hub Architecture

Master Data Services provides for Master Data Management
(MDM). With MDM, there are three types of hub architectures for managing
master data: repository, registry, and hybrid. The repository model is used
when all attributes needed for all systems regarding the master data entities
are stored in the master data database. With registry architecture, only key
values from the systems of record are stored in the master data database and
additional information about that entity has to be looked up in that system.
Hybrid is a mixed approach to storing all attributes of an entity or storing
just key values.

Master Data Models

Data is setup in Master Data Services using models. Models
are used to organize the entities, attributes, hierarchies and collections. Models
should contain entities that can be logically grouped together such as person
with its related address and phone entities.

Entities are the objects being stored in Master Data
Services. The members of these entities are the master data that is managed
centrally (such as customers). Members can be thought of as rows in a table.
Entities are described by attributes, which can be thought of as columns in a

Attributes can be one of three types: free-form,
domain-based attribute (DBA), or file attribute. Free-form attributes are
numbers, strings, dates, and links. File attributes, as you can guess, store
files associated to the entity such as documents or images. Domain-based
attributes are attributes associated with the entity, which are entities
themselves. Take for instance the manufacturer of a vehicle. The vehicle is
an entity and the manufacturer describes the vehicle. Manufacturer is itself
an entity with its own attributes. Two attributes are automatically assigned
to every entity: name and code. These default attributes are used for the
domain-based attribute of the containing entity.

Within a model, you can create hierarchies to group members
within and across entities. A hierarchy defined within a single entity is an
explicit hierarchy. One defined across entities with existing relationships is
a derived hierarchy.

Collections can also be created to group members of a single
entity. Collections can contain other collections and explicit hierarchies.
This allows for the creation of taxonomies such as grouping your customers by
type and also by region.

Master Data Services Tools

Master Data Services has a tool for setting up master data
databases and web sites. This tool is called Configuration Manager, which is
targeted at administrators of Master Data Services.

The master data is managed through the Master Data Services
Manager web application. This user interface provides access to create, view,
review, approve and modify master data. Business users could be granted
access to this application to remove IT from stewardship over the master data
if so desired.

Additionally there is a Master Data Services Web Service
that developers can use to access or extend Master Data Services.

Security and Auditing

Fine-grain, role-based access is available to manage
security to master data and its models. Administrators can grant access down
to the attribute level for any user or group. In Master Data Manager, access
is granted to the functional areas: Explorer, Version Management, Integration
Management, System Administration, and User and Group Permissions.

The who, what, and when of every change to master data is
tracked in the transaction log. In addition to providing a much needed audit
trail, the transaction log serves as means to rollback any given master data
change should that data’s steward deem the rollback necessary.

Data Quality

Because master data is critical to your organization and
your systems, you want to be extra certain that the data captured is of the
highest quality. With Master Data Services, you can define business rules
around your master data to ensure the data is passing the test. The business
rules can be defined by either IT or business resources.

Workflow for data change approval can also facilitate data
quality efforts. Notifications can be configured to alert interested parties
of unsuccessful data changes. If necessary, rollback of unwanted modifications
can take place using the transaction log.

Cleansing all the master data can be quite a chore. Master
Data Services provides a matching mechanism to help get the data clean from the
get go. The business rules and workflow take over from there to help keep the
data high quality.


Administrators of Master Data Services can lock down and
version the master data models allowing consuming applications the ability to
target a specific version of the model. All entities and hierarchies are
versioned and new versions can be created as necessary.


Business intelligence and integration efforts have reduced
complexity and increased quality when core data is shared from a single
authoritative source. Master Data Services in SQL Server gives companies the
capability to have one source of the truth for critical data and therefore
provide consistency in this data across systems.

For More Information

Master Data Services

SQL Server R2 Master Data Services

Master Data Management Hub Architecture

Master Data

Introducing SQL Server 2008 R2 Master Data Services


See All Articles by Columnist

Deanna Dicken

Deanna Dicken
Deanna Dicken
Deanna Dicken is an architect with approximately 20 years of IT experience. In that time she has worked on many large-scale, mission-critical applications and has been involved at all phases of the lifecycle. She has also contributed to three SQL Server MCSE books, co-authored Learn SQL in a Weekend, and tech edited many other titles. She lives on the outskirts of Indianapolis, Indiana with her husband, Curtis, and children, Kylee and Zach.

Latest Articles