This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012. So far, we’ve covered:
- Tables and Schemas in the data warehouse
- Dimensions and Slowly Changing Dimensions
- Fact tables and measures
- Intro to columnstore indexes
- Columnstore indexes and partitioning
- Introduction to SQL Server Data Tools (SSDT)
- Connection Managers
- Control Flow Tasks and Containers
- Data Flow Source Adapters
- Data Flow Destination Adapters
- Data Flow Transformations
- Variables and Parameters
- SSIS Expressions
- Transactions and Checkpoints
- Logging in SSIS 2012
- Deploying projects in SSIS 2012
- Executing Packages in SSIS 2012
- Introduction to Master Data
Now that we know was master data is, let’s check out Microsoft’s offering in the master data management arena, Master Data Services.
Master Data Service Architecture
Master Data Services (MDS) is made up of three main components. First, there is an MDS database that resides on a SQL Server instance. Note that this instance must be a 64-bit installation, Enterprise, Developer, or Business Intelligence edition only. The MDS database stores the master data, as well as MDS system tables, stored procedures, and functions. The next component is the MDS Service, which performs business logic and data access for MDS. Finally, there is the Master Data Manager, a web app that can be used to manage the MDS solution and manually add data to your MDS database. All of these components together make up the Master Data Services solution.
Installing Master Data Services
In order to install MDS, there are some pre-installation requirements that need to be met. The first thing you’ll need to do is install some additional roles and features to the server where you’ll be installing MDS. Those features and roles are listed below.
Roles
- Web Server (IIS)
Role Services
- Common HTTP Features
- Default Document
- Directory Browsing
- HTTP Errors
- Static Content
- Application Development
- ASP.NET
- .NET Extensibility
- ISAPI Extensions
- ISAPI Filters
- Health and Diagnostics
- HTTP Logging
- Request Monitor
- Security
- Windows Authentication
- Request Filtering
- Performance
- Static Content Compression
- Management Tools
- IIS Management Console
Features
- .NET Framework 3.5 Features
- WCF Activation
- HTTP Activation
- Non-HTTP Activation
- Windows Process Activation Service
- Process Model
- .NET Environment
- Configuration APIs
If you’re running Server 2012 or Windows 8, you’ll also want to install the following feature:
- .NET Framework 4.5 Advanced Services
- WCF Services
- HTTP Activation
- WCF Services
Once the prerequisites have been installed, you’re ready to install MDS using the SQL Server Installation Center. MDS is listed under the Shared Features section of SQL Server Setup.
After Setup finishes, you need to perform some post-installation tasks to complete the MDS installation. To do this, open the Master Data Services Configuration Manager. Make sure you run it as a Windows user with sysadmin privileges in the SQL Server instance you plan to use for your MDS repository. Click on the Database Configuration tab and click the Create Database button to create a new MDS repository or click Select Database to connect to an existing MDS database. Clicking Create Database will open the Create Database Wizard which will walk you through the very easy process of creating the MDS database and assigning the MDS administrator.
Once the database is created, you then have to create the web site that will be used to manage MDS. To do this, click the Web Configuration tab, and under the Website drop-down, select Create new website. Enter the desired website name and port number, and user account credentials for the application pool. When you click OK, you’ll get a message about HTTP Binding, which you can close. To associate your new web application with the database you created in the previous step, click the Select button under Associate Application with Database, and select the instance and database.
When you’re all done, click the Apply button and wait for an Internet Explorer window to open up to the Getting Started page of your new site. (At this point you can close out of MDS Configuration Manager.) On the Getting Started page, click the Open the Master Data Manager home page link to open MDS Manager.
Additional Resources
Congratulations! You’ve successfully installed Master Data Services. Now what? Next time we’ll talk about creating a data model in MDS, but until then, check out the following resources for more information on the MDS architecture and installation.
