Get started Bring yourself up to speed with our introductory content.

Getting started with Master Data Services in SQL Server 2008 R2

MDS in SQL Server 2008 R2 has gotten a lot of attention, but how well does it really work? It could all depend on how it's configured.

Editor's note: This article was written using the November Community Technical Preview (CTP) of SQL Server 2008 R2. The configuration process may change between now and when the product is released in May 2010.

Master Data Services, also known as MDS, is one of the key business intelligence features being introduced with SQL Server 2008 R2. The basic goal of Master Data Services is to provide a single authoritative source for information within your enterprise. This information can then be used by other applications and databases so that every application in you environment is always looking at a single authoritative copy of the same information.

Before you can use Master Data Services, you need to install it. This requires a 64-bit server because the MDS installer in only available in a 64-bit build. You'll also need Windows PowerShell 1.0 or higher.

You can begin the installation process by opening the SQL Server 2008 R2 DVD and navigating to the MasterDataServices\x64\1033_ENU folder. From there you can run the MasterDataServices.msi installer. The installation of the MDS software is very straight forward, with the only question being where exactly to install it.

Configurating Master Data Services

After you have installed the MDS service, you can open the Master Data Services Configuration Manager from the Start Menu under Start→All Programs→Microsoft SQL Server 2008 R2 November CTP→Master Data Services→Configuration Manager. This will allow you to configure the MDS repository and specify where to get the master data from.

To configure Master Data Services, open the MDS Configuration Manager and select the Databases option on the menu shown in Figure 1. Then click the Create Database button on the right-hand side. This will open the wizard for configuring the database that will hold the meta data for the MDS repository.

Figure 1. Master Data Services Configuration Manager

While configuring the MDS repository, you can select a local or remote database instance. After selecting the database instance, you'll need to specify service and administrator accounts to be used for administering the MDS database. If you already know which database you want to hold the MDS repository, click the Select Database button.

Once the MDS repository is configured you can then setup the web configuration. This enables a web-based configuration screen which allows you to configure where the data being managed by Master Data Services is located.

The Master Data Services website

After you configure the database it is time to configure the Internet Information Services (IIS) website. You'll need to provide the IP address, TCP port number, host header, and path within the site that will be used to access Master Data Services. Additionally, you'll need to create an application pool and specify the username and password that the pool will run under, as shown in Figure 2.


Note: While configuring Master Data Services for this article I found that the account the application pool runs under must be a local administrator on the MDS server. If the account doesn't have local admin rights, then not all the options are available via the MDS website. If you install without the application pool account having admin rights, simply add the group to the local admin group and reboot the server.

Figure 2. Creating an app pool during MDS configuration

Identifying your master data

After you have finished the web configuration for the MDS database, you can begin creating data models. These data models contain the references to what will be used as your master data. You will need to define models, entities, attributes, business rules, and derived hierarchies when modeling the data.

After connecting to the Master Data Services management tool, click on the System Administration option. This will let you create a model to hold the data. Click on the Manage option at the top and selecting Model. Then click the green plus sign to add a new model as shown in Figure 3. When creating the model, you can opt to either have an entity with the same name as the model or create one manually afterword with a different name. Click the save button when you are finished (the one with the little floppy disk without the red X).

Figure 3. Data modeling for Master Data Services

After creating the model, go back to the Explorer view and select the model you just created. This will show entities associated with the model to the right. Click on the icon to edit the entities. Note that you can also get to this screen by clicking on the Manage drop-down menu, selecting Entities, and clicking on the green plus sign. Type in a name for the entity and select if you wish to enable explicit hierarchies and collections (I elected not to when writing this article). Then click on the Save icon.

After you have created the entity, you will want to add an attribute group, also known as a "leaf group". To do this, click on the Manage drop-down menu and select Attribute Groups. Select the model and entity you wish to edit and click on Leaf Groups. The green plus sign should appear, and clicking on it will allow you to create a leaf group.

To add leaf attributes you will need to navigate back to the main tree by clicking on the Explorer menu at the top. Select the model from the model tree followed by the needed attribute tree and click the button with the pencil on it. This will show you the available leafs and allow you to add additional leafs to the leaf group. To add the leaf attribute, click on the green plus sign. This will let you select the name of the attribute, data type, and maximum size of the data.

After creating the attributes, once again click on the Manage drop-down menu at the top and select Attribute Group. Choose the model and entity that you wish to edit (in this case Adventure Works and Employee). Click the plus sign next to the leaf groups, followed by the group you wish to edit, then Attributes. Select the attributes you'd like to include in this group and click the Save button. You can also change the order of the assigned attributes.

Securing master data with MDS

After you have created your master data model, you can secure it by using Microsoft Active Directory users and groups. This is done by selecting the User and Group Permissions option from the Master Data Services home screen. You can then click on the green plus sign to add another user to MDS. This is done by simply typing in the user or group name.

After you have saved the new user or group, you can edit the user by selecting it from the user or group list and clicking the pencil icon. This will allow you to assign rights to each specific object that we just created.

In case you are wondering, yes Master Data Services is a very complex solution when it comes to maintaining master data. Now, you'll notice that this article doesn't include how to actually load data into MDS. We'll address that in a later article.

Denny Cherry has over a decade of experience managing SQL Server, including's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his blog at SQL Server with Mr. Denny.


Dig Deeper on SQL Server Business Intelligence Strategies

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.