One of the major differences between SQL Server 2008 and the R2 version is Master Data Services or MDS. Microsoft has added this feature to give users the ability to centrally store and manage master data.
But what is ‘master data’ exactly?
Master data is the data that is supposed to be uniquely available within an organization. Examples are:
- Geographical information
- Product information
- Customer information
It is typically data of which you want to have just a single source of truth. In a lot of companies this information is stored in several databases and locations. This is very risky because changes to that data might not be available to all employees. However it is not only about a single source of information but also about versioning and managing this important information.
Master data services is supposed to solve this.
Microsoft didn’t build this feature from scratch but acquired Stratature in June 2007 for that. It was code named ‘Bulldog’ and the first preview with MDS was released back in November as a CTP, but the RTM was released in May this year.
Prerequisites for MDS are similar to SQL Server 2008 R2, but for MDS also the IIS role is necessary because the MDS manager is an ASP.NET application. The full prerequisites regarding IIS can be found here.
When you start the SQL Server 2008 R2 installation you won’t see anything about MDS, because it is a separate install. So first start the regular installation of SQL Server 2008 R2.
Next, you can find the MSI of MDS in a separate folder on the installation disk.
Run the MSI to install MDS. After installation the configuration is launched. This configuration checks if the prerequisites are met. If that is not the case, close the configuration and install the missing pieces. From the start menu you can launch the configuration manager again.
Next step is to create and configure the databases.
MDS requires a database to support the MDS manager web application and web services. Click ‘Create Database’ and the wizard is launched.
Specify the Database server and instance to use.
Specify the name of the database to create.
Specify the service account, which will be used by the web application and services to connect to the database.
Specify the administrator account. Take note of the small remark: “You can configure only one account with this permission and cannot change this account later”
Last screen is the summary and the creation and configuration process can start. If everything went ok then the following screen is shown.
If you click finish you’ll close the dialog and return to the main configuration screen. Before configuration the system settings were empty but they are filled in now. You can adjust the settings to your needs.
One of the options is to create a database mail profile so the system can send alerts.
Next step is the Web configuration:
With a clean install you can only select the ‘Default Website’, but if you do that the message “This Web site has no Master Data Services application” shows up. To solve this you can create an application in the default web site or create a new site. I decided to create an application.
With the web application in place, you can select the database to use. This typically is the database that you’ve created previously in the create database wizard.
With this set up, the configuration looks like this.
Click ‘Apply’ to apply the configuration and a confirmation message shows up.
After logging on the ‘Getting started’ page of the web application is displayed. This page gives the opportunity to deploy some sample models and data to be able to play a bit with MDS. The application itself looks like this.
Now MDS is installed and configured and ready to be used.