Installing Master Data Services in an AlwaysOn environment

SQL Server Technical Article

Writer: Jim van de Erve

Technical Reviewer: Alexander Tolpin, Anand Subbaraj, Minh Pham, Bogdan Pienescu

Published: November 2012

Applies to: SQL Server 2012 SP1

Summary: This white paper describes how to set up a Microsoft SQL Server 2012 AlwaysOn environment with SQL Server Master Data Services. This requires Master Data Services in SQL Server 2012 SP1. You can set up this infrastructure with either a shared storage configuration or a nonshared storage configuration.

Copyright

This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it.

Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.

This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.

© 2012 Microsoft. All rights reserved.

Contents

Introduction 4

Master Data Services with AlwaysOn high availability in a nonshared storage configuration 4

Installing Master Data Services with AlwaysOn in a nonshared storage configuration 5

Master Data Services with AlwaysOn high availability in a shared storage configuration 6

Installing Master Data Services with AlwaysOn in a shared storage configuration 7

Conclusion 8

Introduction

This white paper describes how to install and configure Microsoft SQL Server Master Data Services in a high-availability/disaster recovery deployment that uses SQL Server 2012 with SQL Server AlwaysOn enabled. This scenario requires Master Data Services in SQL Server 2012 SP1.

Note that the steps outlined in this document are not intended for SQL Server 2008 R2 with database mirroring.

This document presents two different scenarios: one in which you install SQL Server as a nonshared storage solution using an availability group, and another in which you install SQL Server as a shared storage solution using SQL Server failover cluster instances (FCIs) and an availability group. Master Data Services is configured differently for the two cases.

Master Data Services with AlwaysOn high availability in a nonshared storage configuration

This high-availability and disaster recovery solution uses a Windows Server failover cluster and AlwaysOn availability groups as follows:

·  Set up a Windows Server Failover Cluster.

·  Nodes use dedicated, not shared, databases.

·  SQL Server Failover Clustering is not used. There are no SQL Server failover cluster instances.

·  A SQL Server AlwaysOn availability group spans the two data centers.

·  Microsoft Internet Information Services (IIS) can either be internal and dedicated to each node, or external with network load balancing. The Master Data Services web application should be configured to connect to the virtual network name (VNN) of the availability group listener.

·  Commits from the primary database to the secondary database in the same primary data center are synchronous. Commits from the primary database to the secondary database in the disaster recovery data center are asynchronous.

Installing Master Data Services with AlwaysOn in a nonshared storage configuration

Perform the following steps to set up a high-availability and disaster recovery solution that uses an availability group (not a SQL Server failover clustering) and incorporates Master Data Services. For a detailed procedure without steps specific to Master Data Services, see "Building the Availability Group Solution" in AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups (http://msdn.microsoft.com/library/jj191711.aspx). These steps refer back to the steps in that guide (as shown in parentheses).

  1. Add the Windows Failover Clustering feature to the nodes located in the primary data center. Use a static IP reservation for the WSFC name (step 1 in the AlwaysOn Architecture Guide here that documents how to use a Windows Server Failover Cluster with an availability group, but not how to use SQL Server failover cluster instances).
  2. Install a stand-alone instance of SQL Server with MDS on each node (step 7).

Note: If SQL Server is already set up, you can perform an add/remove features step to set up Master Data Services.

  1. In the SQL Server Configuration Manager, enable AlwaysOn for each SQL Server node (step 8).
  2. Create a SQL Server availability group and a SQL Server availability group listener for all the nodes (steps 10 and 11).
  3. In the Master Data Services Configuration Manager, deploy the Master Data Services database on only one node in the primary data center. When you create or select the database, set the SQL Server instance to the VNN of the availability group listener.
  4. In SQL Server Management Studio, add the database that you deployed in step 5 above to the availability group. This creates a backup of the database to restore to other nodes.
  5. In the Master Data Services Configuration Manager, deploy the Web application to the IIS server. For the database name, point to the VNN of the availability group listener. Make sure the Master Data Services Web application is accessible.

For high availability, we recommend deploying the Web application in a network load balancing configuration. However, you cannot install the network load balancing role on nodes within the Windows Failover Cluster. The alternative is to set up IIS on all nodes within the cluster, and deploy the Web application to all nodes. The MDS web configuration needs to be identical on each node, including the web application name, port number, and host header. For more information, see Overview of Network Load Balancing (http://technet.microsoft.com/en-us/library/cc725691.aspx). For more information about how to set up IIS in a Windows Failover Cluster, see Configuring IIS 7.0 World Wide Web Publishing Service in a Microsoft Windows Server 2008 failover cluster (http://support.microsoft.com/kb/970759).

  1. It is possible to configure WFC (Windows Failover Cluster) such that only one MDS Windows Service is started, while the other services in the cluster are stopped. To do so:
  2. Set all MDS services pertaining to the MDS instance to "Manual" (they install as "Automatic"). Stop the ones from the passive nodes.
  3. Open the WFC console on the active machine in the cluster and add the MDS service to the cluster, under "Generic services".
  4. In SQL Server Management Studio, perform a manual failover by navigating to the availability group, right-clicking on the availability group, clicking Failover, and walking through the wizard. This step must be done for all secondary replicas. In Master Data Services Configuration Manager, connect to the VNN of the availability group listener, and then perform a repair on all nodes. This action performs a number of re-initializations. You only need to perform this step once during installation; after it's done, and you have verified that the Web application works, failover should work.
  5. Set up the solution in the disaster recovery data center. When you set up the Availability Group, by default the Availability Mode in the Availability Replica Properties is set to Synchronous commit. For the disaster recovery data center, set the Availability Mode to Asynchronous commit, as shown in the figure above.

Master Data Services with AlwaysOn high availability in a shared storage configuration

This high-availability and disaster recovery solution uses a Windows Server failover cluster, SQL Server failover cluster instances, and SQL Server AlwaysOn availability groups as follows:

·  Set up a Windows Server Failover Cluster.

·  SQL Server Failover Clustering is used, with nodes in SQL Server failover cluster instances.

·  The SQL Server failover clusters instances use shared storage.

·  A SQL Server AlwaysOn availability group spans the two data centers and covers the failover cluster instances.

Installing Master Data Services with AlwaysOn in a shared storage configuration

Perform the following steps to set up a high availability and disaster recovery solution that uses a SQL Server failover cluster instance with shared storage, and incorporates MDS. For a full procedure, follow the steps in "Configuring the FCI+AG Solution" in AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups (http://technet.microsoft.com/library/jj215886.aspx). These steps refer back to the steps in that guide.

  1. Add the Windows Failover Clustering feature to the nodes located in the primary data center. Use a static IP reservation for the WSFC name (step 1 in the AlwaysOn Architecture Guide here that documents how to use a Windows Server failover cluster and SQL Server failover cluster instances with an availability group).
  2. Install a stand-alone failover cluster instance of SQL Server with MDS on each node (step 9). For the first node, perform the New SQL Server failover cluster installation; for the second node, perform the Add node to a SQL Server failover cluster installation.

Note: If SQL Server is already set up, you can perform an add/remove features step to set up Master Data Services.

  1. In the SQL Server Configuration Manager, enable AlwaysOn for each SQL Server failover cluster instance (step 10).
  2. Create a SQL Server availability group and a SQL Server availability group listener for all nodes (step 13 in the "Setting Up the Solution at the DR Data Center" section of the AlwaysOn Architecture Guide here).
  3. In the Master Data Services Configuration Manager, deploy the Master Data Services database to the primary cluster instance. When you create or select the database, set the SQL Server instance to the VNN of the availability group listener.
  4. In SQL Server Management Studio, add the shared database that you deployed in step 5 above to the availability group. This creates a backup of the database to restore in the other nodes.
  5. In the Master Data Services Configuration Manager, deploy the Web application to the IIS server. For the database name, point to the VNN of the availability group listener. Make sure the Web application is accessible.

For high availability, we recommend deploying the Web application in a network load balancing configuration. However, you cannot install the network load balancing role on nodes within the Windows Failover Cluster. The alternative is to set up IIS on all nodes within the cluster, and deploy the Web application to all nodes. The MDS web configuration needs to be identical on each node, including the web application name, port number, and host header. For more information, see Overview of Network Load Balancing (http://technet.microsoft.com/library/cc725691.aspx). For more information about how to set up IIS in a Windows Failover Cluster, see Configuring IIS 7.0 World Wide Web Publishing Service in a Microsoft Windows Server 2008 failover cluster (http://support.microsoft.com/kb/970759).

  1. It is possible to configure WFC (Windows Failover Cluster) such that only one MDS Windows Service is started, while the other services in the cluster are stopped. To do so:
  2. Set all MDS services pertaining to the MDS instance to "Manual" (they install as "Automatic"). Stop the ones from the passive nodes.
  3. Open the WFC console on the active machine in the cluster and add the MDS service to the cluster, under "Generic services".

9.  In SQL Server Management Studio, perform a manual failover by navigating to the availability group, right-clicking the availability group, clicking Failover, and walking through the wizard. In Master Data Services Configuration Manager, connect to the availability group, and then perform a repair on all nodes. This action performs a number of re-initializations. You only need to perform this step during installation; after it's done, failover should work.

10.  Set up the solution in the disaster recovery data center. When you set up the Availability Group for the disaster recovery data center, set the Availability Mode to Asynchronous commit.

Conclusion

In this white paper, we have seen how you can set up and configure Master Data Services in a SQL Server 2012 high-availability/disaster recovery deployment with AlwaysOn enabled. This requires Master Data Services in SQL Server 2012 SP1. You have two basic options for doing so: using non-shared storage with a Windows Server failover cluster and an AlwaysOn availability group, or using shared storage with a SQL Server failover cluster, a Windows Server failover cluster, and an AlwaysOn availability group.

For more information:

http://www.microsoft.com/sqlserver/: SQL Server Web site

http://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter

http://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter

See the following AlwaysOn architecture guides:

·  AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups

·  AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

·  Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?

·  Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

Send feedback.

8