In the first two parts of this blog series we spent time talking about master data, master data management (MDM) and the architectural patterns that are prevalent in MDM solutions. In this post we will start narrowing our focus to Master Data Services (MDS) in SQL Server 2012 by starting with the installation and set-up process.
Part 1 – Understanding Master Data
Part 2 – Master Data Management Architectures
Before we get started there are a couple of pre-requisites to be aware of. The first and one which will cause you problems in numerous places but will not stop the installation process is that the server you are installing this on needs to be a member of a domain. There are workarounds for this but I’d advise against them.
Next, in order to install and work with MDS, you will need to set-up the Web Server (IIS) server role. The .Net 4.0 framework and PowerShell 2.0 are also required although if you installed the full version of SQL Server 2012, both of these requirements should be met. Silverlight 5 is the final requirement and one that is only needed on machines which will access the web client. You don?t need to worry about tracking this down as you will be prompted to install the component the first time you access the client if it is not available.
Master Data Services installs as a feature for SQL Server 2012. If you by chance selected All Features, All Defaults in the installation process, MDS is already installed. If not you will need to run the Installation Center with the installation media to add the Master Data Services feature to the SQL Server instance.
It’s worth noting while we talking about installation that MDS is not supported in a clustered environment. If you attempt to set it up in a cluster environment it will only be accessible on a single node.
Configuring Master Data Service
Once the MDS feature has been installed, we are ready to configure it. The configuration process can be split into two distinct parts: setting up the Master Data Services database and configuring the Master Data Manager web application. Both parts of the configuration process are handled by the Master Data Management Configuration utility. To launch this utility, click the Start Menu -> Microsoft SQL Server 2012 -> Master Data Services -> Master Data Services Configuration.
When the configuration utility launches, you will notice that it confirms the PowerShell and IIS requirements. On the left-hand side of the utility you have options for both database and web configuration. These are the jumping off points to handle the remainder of the MDS configuration.
Setting up the MDS Database
When you start the database configuration you are presented with a database configuration page that provide high-level information about the SQL Server instance, the MDS database and database version. Additionally, the database configuration page is used to set configuration parameters which we will review in a bit.
To start the install process, click the Create Database button to launch the Create Database Wizard. The Create Database Wizard consist of three primary steps. The first step you specify and SQL Server instance, select an authentication method and supply credentials if required. The second step is where we define the database name and choose either the default collation or configure the database collation based on your own requirements. The final step is where you define a domain account as an administrator. This account is given full access to all models in the database.
Create DB First Step
DB Server Instance and Credentials
Supply MDS DB Info
Specify Administrator Account
Create DB Summary
Create DB Processing Progress
Once you configure the instance and database you have are given a summary prior to the actual database set-up. Once you finish the database installation you are taken back to the database configuration page where the Current Instance section should reflect the information you just configured.
Database Configuration with Default System Settings
We will circle back and discuss the System Settings shortly.
Configuring the Master Data Manager Web Application
The Master Data Manager installs a web site and application pool to the web server (IIS) host. You start by selecting one of several options. The first is to use the default website. This option creates a new web application under the default website that is created when the web server role is set-up. The second is to create a totally new website. Finally, you have the option of selecting a website which is already set-up. The first two options are the most common with the third option being available so that you can manage the website once you have completed the set-up process.
At this point, I would generally recommend that you establish a new website unless you are working on a box that is 100% dedicated to MDS or have some other requirement that would prevent access to the server on a port other than port 80. Start by selecting the Create a new website option.
When you select the create new option, you are presented with a dialog which collects the various settings required to establish a new website. If you are familiar with IIS there?s nothing new here. You can define the protocol, IP address, port, any host headers as required and an identity for the application pool. For demo purposes, take the defaults with the exception of the port, which I generally set to 8081 in demo situations such as this. You will also need to enter a valid user and password for the website to work properly. Once you click OK, you will be prompted with a warning about HTTPS and SSL, if you require any of these features they must be configured through IIS. Click OK to disregard the warning and continue.
Create Website Dialog
HTTPS/SSL Binding Warning
With the web application created, you now must associate the database with the website. Click the Select? button under the Associate Application with Database section to launch the prompt. If you are running everything on a single server, the instance name will be populated for you, otherwise make sure the instance which host the MDS database is specifies, valid credential have been supplied and then click Connect. Once connected, select the MDS database and then click OK. You have successfully updated the Master Data Manager Web Application configuration file to point to the MDS database.
Connect to a MDS Database
The final task is to enable integration with Data Quality Services (DQS). This task requires only a click of a button with a caveat. In order to enable integration DQS and MDS must be running on the same server. There currently is no support to separate the two. With that the basic set-up is complete.
Enable DQS in MDS
MDS System Settings
The final area to explore is the system settings. These system settings are where high-level configuration occurs. This is the place where you configure things like database and session timeouts. We originally saw the system setting during the database configuration step. While a setting by setting description is beyond a scope of this blog there are a few settings which are worth pointing out. If you interested in more detail, I will refer you to the MSDN documentation here: http://msdn.microsoft.com/en-us/library/ff487028.aspx.
|Number of rows per batch||Controls the number of records retrieved by each batch of the web service. If you are working with a fair amount of data, you want to consider increasing the batch size to reduce the number of trips that will be required to retrieve the data.|
|Staging batch interval||This setting does exactly what it says, it?s the lag between when you click Start on a batch and the time MDS actually begins processing the batch.|
|Number of members in the hierarchy by default||The number of members that are displayed before a More option is presented in the user interface.|
|Member Security Processing Interval||The frequency with with User/Group security changes are applied. The default value for this setting is 3600 seconds or 1 hour. I like something in the neighborhood of 300 seconds or 5 minutes in a demo environment.|
The final settings to discuss are best discussed as a group. They are the notification settings. Notification in MDS can occur for a number of different reasons. Business rule validation errors and new members being created being two of the most common. On the back end, MDS uses database mail to send notifications. The system settings is where you configure the database mail profile and other general notification settings such as frequency and notification format. We will spend a whole blog post on notifications in MDS so we will stop at just pointing the relevant settings out for now.
With that, we have run through the installation and set-up process for Master Data Services. We walked through installing the MDS feature in SQL Server 2012, set-up the Master Data Services database and created the Master Data Management web application. We also briefly explored some of the system settings. In the next blog post, we will start exploring some of the important concepts in MDS. We will look at the MDS model, define attributes and explore both hierarchies and business rules.
Till next time!!