Creating a SQL Server 2012 playground (part XII)

Introduction

With Master Data Services of Microsoft, you can create a centralized data source and keep it up to date and reduce redundancies accross applications. Also, Master data is a concept than can easily be translated to dimensions (as in starschemas and facts). Therefore it is a concept that is interesting to understand.

This blogpost covers the installation and configuring Master Data Services. Mainly, the installation steps are :

1. Installation or verification of the installation of Master Data Services
2. Configuration of MDS :  Create the Master Data Services Database
3. Configuration of MDS :  Configuration of the MDS Website
4. Installation  Microsoft SQL Server 2012 Master Data Services Add-in For Microsoft Excel

MDS is supported on x64 operating systems only.

This blogpost is one in a series of blogposts:

• Creating a VM environment with virtualbox (part I).

• Configuration of the domain controller (part II).

• Creating AD users and installing SQL Server 2012 (part III).

• Installation of Sharepoint (part IV).

• Adding the tabular mode instance to the SQL Server installation (part V).

• Adding the powerpivot mode instance to the SQL Server installation (part VI).

• Configuring SharePoint Central Administration (part VII).

• Installing Reporting Services Sharepoint mode as Single Server Farm (part VIII).

• Installing MS SQL Server Powerpivot for Excel 2010 (part IX).
• Installation of SSDT and the SSDT Power tools (part X).
• Installation of Contoso and AdventureWorks databases (part XI).
• Installation of Master Data Services (part XII).
• Installation of Data Quality Services (part XIII).
• etc.

Verify installation

If you haven't done, install the Master Data Services component with the SQL Server 2012 installation disc. I've already installed MDS during the standard installation of SQL Server 2012.

Master Data Services has been installed!

Configuration of MDS

Master Data Service isn't ready for normal usage yet and therefore we need to configure MDS  with the configuration manager. There are two steps to follow: Create the Master Data Services database (step 2) and the configuration of the MDS Website (step 3). Step 1 was the verification of the installation of MDS.

2. Create the Master Data Services Database

The Master Data Services Configuration tool can be found in the Start menu.

Review the information that is shown in the Server configuration window and ensure that all the required software has been installed.

Click on “Databases” in the left pane and click the “Create Database” button to start the Create Database wizard. Follow the steps in the wizard and supply the required information to create a new MDS database.

The wizard is start up.

Enter the SQL Server instance name in the window.

Enter the databasename at "Database name".

Enter the administrator account at "User name".

And the creation of the database is in progress.

After the database is created, system settings can be adjusted.

3. Configuration of the MDS Website
The next step is to configure the MDS Website. Click “Web Configuration” on the left part of the window to configure a new IIS web application for MDS. The MDS web application can be created as its own web site (root application) or within another web site. The simplest configuration is to create the MDS web application within the existing Default Web Site.
 
Select “Default Web Site” in the Web Site drop down list and Click the "Create Application…" button to create a new MDS web application. Accept the defaults for web application Alias and Application Pool Name (for a default installation offcourse). Type the User name and Password. This should be the same account as provided in the Service Account step of the Create Database wizard. And, click the OK button to create the new web application.

Click a couple of times on the OK button and leave the default settings as proposed bij Microsoft. The Web Configuration should look similar to that shown below

And then choose the web application you will be taken to the MDS getting started page.

MDS Add in for Excel

The "Microsoft SQL Server 2012 Master Data Services Add-in For Microsoft Excel" gives multiple users the ability to update Master data with Excel. Download the .msi from Microsoft. Double click on the .msi and press Next, Next, install and finish.

Conclusion

In this blogpost i've described the installation of MDS.


Greetz,
Hennie