Synchronizing Disparate Data with Master Data Services
How to implement a basic MDS system
April 25, 2011
Microsoft first introduced Master Data Services (MDS) in SQL Server 2008 R2 to solve a problem that large companies often have: Multiple systems have the same kinds of records but they contain slightly different values, making it difficult to keep the data synchronized. For example, the states in which customers live might be stored in two systems, with one system using the two-letter abbreviation (e.g., CA) and the other system spelling out the state (e.g., California). MDS helps solve this problem by allowing data mapping between systems and providing a mechanism to keep the data between these different systems in sync, even when the systems have different schemas.
To create a basic MDS implementation, you need to perform the following steps:
Install MDS on the server that will be the MDS server.
Create the database that the MDS service will use to store its information.
Create the web interface that will be used to create the model and later manage the data.
Create a data model for each system whose data you want to synchronize.
Load each system’s data into the MDS database.
Step 1: Installing MDS
Installing MDS in SQL Server 2008 R2 is easy. You simply run the installer found in the MasterDataServicesx641033_ENU folder. Before you run the installer, though, you need to make sure that Windows PowerShell 2.0 and Microsoft IIS are installed. (In addition to the core IIS configuration pieces, the ASP.NET components and the Windows Authentication component of IIS are needed.) After the installer finishes, the Master Data Services Configuration Manager launches automatically.
Installing MDS is even easier in the next release of SQL Server, which is code-named Denali. Launching the standard SQL Server installer is all it takes. When the list of services is shown, you just scroll down to the bottom of the list and select the Master Data Services check box. Like the MDS version in SQL Server 2008 R2, the MDS version in SQL Server Denali requires that PowerShell and IIS be preinstalled.
After the installer finishes, you can bring up the Master Data Services Configuration Manager by selecting Program Files on the Start menu, choosing Microsoft SQL Server Denali CTP 1, selecting Master Data Services, then clicking Configuration Manager. (Although these instructions are for the Community Technology Preview 1—CTP 1—version of SQL Server Denali, they’ll likely be similar in subsequent versions.)
From this point on, the MDS implementation steps are the same for SQL Server 2008 R2 and SQL Server Denali. So, I provide just one set of instructions in the sections that follow.
Step 2: Creating the MDS Database
To create the MDS database, select the Databases tab in the Master Data Services Configuration Manager. Click the Create Database button to launch a wizard that will walk you through configuring the database. This wizard will prompt you for a variety of information, including the name of the MDS database, the name of the SQL Server instance on which that database resides, and the authentication information needed to access that SQL Server instance. The wizard will also prompt you for the name of the Windows account that will run the MDS service and the Windows account that will be the default administrator for the MDS system.
After you provide the necessary information, the wizard will create
the MDS database on the specified instance
a database login and user for the service account
the database records that grant the permissions
Figure 1 shows the configured database for this example.
Figure 1: The configured MDS database
In true Microsoft fashion, the MDS database’s size will be around 9MB with growth in 1MB increments and the transaction log’s size will be around 2MB with a growth rate of 10 percent. In a production system, you’ll likely need to increase the size of the database and transaction log so that they’re sized appropriately for the amount of data that will be loaded into the MDS database. There’s no way to provide specific guidance here on what those files should be resized to, because every installation will be different depending on the amount of data that’s being loaded into the MDS database.
Step 3: Creating the Web Interface
After creating the database, you need to create the web interface. This interface is called the Master Data Manager Web application, which I’ll simply refer to as the web application. Begin by clicking the Web Configuration button in the Master Data Services Configuration Manager. In the Web Configuration page, you need to specify the IIS website in which you want the MDS installer to create the web application and the application pool that will contain the web application. It can be a new website or an existing website.
For this example, let’s create the web application on a new website. To do so, select Create Site and specify the settings that MDS will need to create not only the website but also the web application and application pool. For this example, you can leave the settings at their default values. However, you might need to change the TCP port if you receive an error message saying that the port is already in use.
If you want the web application on an existing website, you need to select that website, click Create Application, and specify the settings that MDS will need to create the web application and application pool. You can find more information about those settings in the SQL Server MDS team's blog article “Creating Web Sites and Applications in Master Data Services Configuration Manager”.
Next, you need to select the MDS database that you want the web application to connect to. You also need to provide the connection information for the SQL Server instance on which that database resides, which was created in the previous steps. For instructions on how to do so, see the Microsoft article “How to: Associate a Master Data Services Database and Web Application