




















































Master Data Services is Microsoft's Master Data Management product that ships with SQL Server 2008 R2. Much like other parts of SQL Server, such as Analysis Services (SSAS) or Reporting Services (SSRS), MDS doesn't get installed along with the database engine, but is a separate product in its own right.
Unlike SSAS or SSRS, it's worth noting that MDS is only available in the Enterprise and Data Centre Editions of SQL Server, and that the server must be 64-bit.
MDS is a product that has grown from acquisition, as it is based on the +EDM product that Microsoft obtained when they bought the Atlanta-based company called Stratature in 2007. A great deal of work has been carried out since the acquisition, including changing the user interface and putting in a web service layer.
At a high level, the new product has the following features:
Technically, Master Data Services consists of the following components:
The following diagram shows how the components interact with one another:
The MDS database uses a mix of components in order to be the master data store and to support the functionality found in the Master Data Manager, including stored procedures, views, and functions.
Separate tables are created both for entities and for their supporting objects, all of which happens on the fly when a new object gets created in Master Data Manager. The data for all entities across all subject areas are stored in the same database, meaning that the database could get quite big if several subject domains are being managed. The tables themselves are created with a code name. For example, on my local installation, the Product entity is not stored in a table called "Product" as you might expect, but in a table called "tbl_2_10_EN".
Locating entity data
The exact table that contains the data for a particular entity can be found by writing a select statement against the view called viw_SYSTEM_SCHEMA_ENTITY in the mdm schema.
As well as containing a number of standard SQL Server table-valued and scalar functions, the MDS database also contains a handful of .Net CLR (Common Language Runtime)-based functions, which can be found in the mdq schema. The functions utilize the Microsoft.MasterDataServices.DataQuality assembly and are used to assist with data quality and the merging, de-duplication, and survivorship exercises that are often required in a master data management solution.
Some of the actions in MDS, such as the e-mail alerts or loading of large amounts of data, need to happen in an asynchronous manner. Service Broker is utilized to allow users to continue to use the front-end without having to wait for long running processes to complete.
Although strictly outside the MDS database, SQL Server Database Mail, which resides in the system msdb database, is used as the mechanism to send e-mail alerts to subscribing users.
In addition to the tables that hold the master data entities, the MDS database also contains a set of staging tables that should be used when importing data into MDS. Once the staging tables have been populated correctly, the staged data can be loaded into the master data store in a single batch.
During the initial configuration of MDS, an IIS Web Application will get created within an IIS website. The name of the application that gets created is called "MDS", although this can be over-ridden if needed.
The Web Application contains a Virtual Directory that points to the physical path of <Drive>:<install location>WebApplication, where the various components of the Master Data Manager application are stored.
The MDS WCF Service called Service.svc is also located in the same directory. The service can be exposed in order to provide a unified access point to any MDS functionality (for example, creating an entity or member, retrieving all entity members) that is needed by other applications. Master Data Manager connects to the WCF service, which then connects to the database, so this is the route that should be taken by other applications, instead of connecting to the database directly.