Using AlwaysOn Availability Groups for High Availability and Disaster Recovery of Data Quality Services

SQL Server Technical Article

Writer: Kumar Vivek

Technical Reviewers: Gadi Brovman, Welly Lee, Jason Howell, Matt Masson

Published: December 2012

Applies to: SQL Server 2012

Summary: This white paper describes how to use the AlwaysOn Availability Groups feature in SQL Server 2012 to build a high-availability (HA) and disaster-recovery (DR) solution for Data Quality Services (DQS).

Contents

Introduction...........................................................................................................................................3

Limitations.............................................................................................................................................3

Before We Begin....................................................................................................................................3

Solution Deployment.............................................................................................................................4

Set Up the Availability Group for DQS...................................................................................................5

Step 1: Set up a Windows Failover Cluster with 3 Nodes..................................................................5

Step 2: Set Up SQL Server 2012 and DQS on Each Cluster Node.......................................................5

Step 3: Prepare DQS for Availability Group Configuration.................................................................6

Step 4: Configure the Availability Group............................................................................................6

Step 5: Test Connectivity to Data Quality Server Using the Availability Group Listener Name..........9

Handling Failover to Ensure High Availability......................................................................................10

Planned Failover..............................................................................................................................10

Unplanned Failover.........................................................................................................................11

Handling Disaster Recovery.................................................................................................................12

Upgrading DQS Databases after SQL Server Updates..........................................................................13

Step 1: Install SQL Server Updates and Upgrade the DQS Database Schema on the Primary Node 13

Step 2: Install SQL Server Updates and Delete the DQS Databases on the Secondary Node...........14

Step 3: Add Upgraded DQS Databases Back to the Availability Group on the Primary Node..........15

Feedback.............................................................................................................................................16

Introduction

The AlwaysOn Availability Groups feature in SQL Server 2012 provides a failover environment where more than one user databases can be grouped into a single unit of failover. However, in the availability group, the unit of failover is a group of user databases only. This makes it challenging for the Data Quality Services (DQS) databases because of the following 2 reasons:

The two DQS databases, DQS_MAIN and DQS_PROJECTS, contain assemblies and other complex objects, apart from just simple database objects (such as tables and stored procedures).

There are some entities outside of the DQS databases that must exist in the SQL Server instance for the DQS databases to be functional as Data Quality Server, specifically the two SQL Server logins (##MS_dqs_db_owner_login## and ##MS_dqs_service_login##), and an initialization stored procedure (DQInitDQS_MAIN) in the master database.

As a result, you must perform some additional steps when configuring AlwaysOn Availability Groups for DQS. This white paper describes how you can work around the above issues, and use Availability Groups to make the DQS databases (DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA) highly available and ready for disaster recovery.

Limitations

The AlwaysOn Availability Groups configuration for DQS databases enables knowledge base and project data to be available in case of failover. However, it does not provide automatic failover support for an active knowledge discovery process or data quality project. When a failover occurs during an active knowledge discovery, cleansing, or matching process, DQS will abort, and throw exception messages. When this happens, you must disconnect from Data Quality Server, reconnect to Data Quality Server, and then restart the knowledge discovery, cleansing, or matching process.

Before We Begin

This paper assumes an advanced knowledge of AlwaysOn Availability Groups, high-availability, and disaster-recovery concepts. We highly recommend that before reading this paper:

Read the AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups paper as this paper builds on the concepts and recommended high availability and disaster recovery (HADR) setup documented in the paper.

Read about prerequisites for using availability groups; see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) .

Read about DQS and how to install and configure it.

Solution Deployment

We will stick with the HADR setup recommended in the AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups paper with 3 Windows Server Failover Cluster (WSFC) nodes (VM-DQS1, VM-DQS2, and VM-DQS3) across the two data centers: primary and disaster recovery.

a) Quorum Model and Node Votes: For our scenario, we will set the quorum model to Node and File Share Majority. Each of the nodes in the primary data center will have 1 vote; the node in the disaster recovery data center will have 0 vote. For more information about guidelines on quorum models and node votes, see WSFC Quorum Modes and Voting Configuration.

NOTE: There is a Windows Server hotfix available (http://support.microsoft.com/kb/2494036) that allows you to assign different quorum votes (0 or 1) nodes in the failover cluster. This hotfix must be installed on each node in the cluster.

b) Availability and Failover modes: For the 2 nodes in the primary data center, we will set the availability mode to Synchronous Commit and failover mode to Automatic. For the node in the disaster recovery data center, we will set up the availability mode to asynchronous, and failover mode to Manual. For more information, see Failover and Failover Modes.

Set Up the Availability Group for DQS

Step 1: Set up a Windows Failover Cluster with 3 Nodes

Setup a Windows Failover Cluster with the 3 nodes (say VM-DQS1, VM-DQS2, and VM-DQS3). Make sure that the account you use to install and configure the Failover Cluster is a domain account, and has administrator privileges on each of the cluster nodes.

For detailed information about setting up the failover cluster nodes, quorum, and vote settings, see steps 1 -6 in the Building the Availability Group Solution section in the AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups paper .

Step 2: Set Up SQL Server 2012 and DQS on Each Cluster Node

1. On node 1, install single instance of SQL Server 2012 by selecting the New SQL Server stand-alone installation or add features to an existing installation option from the SQL Server installation wizard.

2. On the Feature Selection page, select Data Quality Services under Database Engine Services to install the Data Quality Server. If you want to install Data Quality Client as well, select it. It is recommended to install SQL Server Management Studio as well to aid you in performing additional tasks post installation.

3. After the SQL Server installation is finished successfully, run the dqsinstaller.exe file to complete the Data Quality Server installation. For more information, see Run DQSInstaller.exe to Complete Data Quality Server Installation.

4. Repeat steps 1-3 on the second and third node. Make sure you specify the same password for the database master key during the DQS installation that you specified on the first node.

IMPORTANT: The dqsinstaller.exe file must be run on the second and third nodes as well to ensure that all the required objects are created/registered along with 2 logins (##MS_dqs_db_owner_login## and ##MS_dqs_service_login##), and the initialization stored procedure (DQInitDQS_MAIN) in the master database in the target SQL Server instances.

5. On the first node, create the required SQL logins for the Windows users in your organization who will be accessing DQS, and grant them appropriate DQS roles. For more information, see Grant DQS Roles to Users.

Step 3: Prepare DQS for Availability Group Configuration

1. Enable AlwaysOn Availability Group for all the SQL Server instances on all the three nodes. For more information, see Enable and Disable AlwaysOn Availability Groups .

2. On the second and third nodes, connect to SQL Server instance using SQL Server Management Studio, and delete the DQS databases: DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA.

IMPORTANT: You delete DQS databases from the second and third node because when you set up an availability group on node 1, the three DQS databases will be restored on the second and third nodes from the first node as part of the availability group configuration.

3. On the first node, change the recovery model of all the DQS databases from Simple to Full, and perform a full backup of all the DQS databases. This is a prerequisite for setting up an availability group.

4. On the first node, script out your SQL logins that are associated with the DQS roles for connectivity to DQS, and transfer to the SQL Server instances on second and third nodes. There are several methods that can be used to transfer database objects between SQL Server instances. The Integration Services Transfer SQL Server Objects task is one such method.

IMPORTANT: You must manually keep the SQL logins associated with DQS roles in sync on all the SQL Server instances in the availability group because only database failover is supported in an availability group.

Now you are ready to configure an availability group for DQS databases.

Step 4: Configure the Availability Group

1. On the first node, start SQL Server Management Studio, expand AlwaysOn High Availability in the left pane, right-click Availability Groups, and then click New Availability Group Wizard.

2. Specify a name for the availability group (for example, DQS-AG), and click Next.

3. On the Select Databases page, select the three DQS databases, and then click Next.

NOTE: If you have your source data in a database other than the DQS_STAGING DATA database, you must select that database as well to be included in the availability group.

4. On the Specify Replicas page, add VM-DQS2 and add VM-DQS3 server instances by clicking Add Replica.

a. For the server instances in the primary data center (VM-DQS1 and VM-DQS2), set the failover mode to automatic, and commit to the synchronous mode.

b. For the server instance in the disaster recovery data center (VM-DQS3), set the failover mode to manual and commit to the asynchronous mode.

5. The Endpoints tab displays the HADR endpoints that will be created automatically. Each server instance requires a database mirroring endpoint. If a server instance that you select to host an availability replica is running under a domain user account and does not yet have a database mirroring endpoint, the New Availability Group Wizard (or Add Replica to Availability Group Wizard) can create the endpoint and grant CONNECT permission to the server instance service account. However, if the SQL Server service is running as a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication, and the wizard will be unable to create a database mirroring endpoint on the server instance. In this case, you must create the database mirroring endpoints manually before you launch the wizard. For more information, see Specify Replicas Page.

6. On the Backup Preferences tab, select the appropriate backup preference. For more information, see Specify Replicas Page.

7. On the Listener tab, create a listener for the availability group. The availability group listener is a virtual network name that is used to automatically connect to the primary replica, wherever it may be hosted at the time. Click Next. For more information, see Specify Replicas Page.

8. On the Select Data Synchronization page, select Full, and specify a shared network location accessible by all the replicas. For more information about this page, see Select Initial Data Synchronization Page. Click Next.

9. The Validation page displays the validation results of the choices made in the previous pages. Verify that all the tasks are successful, and click Next.

10. The Summary page provides a summary of the selections made by you in the wizard. Click Finish to start the availability group creation.

11. The Results page displays the status of the availability group creation. After the wizard completes successfully, click Close.

After the availability group is created, you can view the newly created availability group under the AlwaysOn Availability Groups node on any of the three nodes. DQS-AG is the newly created availability group:

You can also right-click on the availability group name, and click Show Dashboard to view detailed information about the health of your availability group. For more information, see Use the AlwaysOn Dashboard.

Step 5: Test Connectivity to Data Quality Server Using the Availability Group Listener Name

After you have set up your availability group, use Data Quality Client to log on to the Data Quality Server by specifying the availability group listener name in the Server name box. This is just to ensure that the availability group and the listener are working as expected. In the availability group setup, you connect to Data Quality Server using the listener name to ensure that your request is appropriately routed to the current primary node.

Handling Failover to Ensure High Availability

When a failover occurs (planned or unplanned), the failover target takes over the primary role, recovers its databases, and brings them online as the new primary databases. The former primary replica, when available, switches to the secondary role, and its databases become secondary databases. In case of DQS, if you try to connect to Data Quality Server using the listener name after a failover occurs, you will get the following error:

This happens because every time the DQS_MAIN gets restored to another SQL Server instance during failover, the owner security ID (SID) does not get aligned correctly. For more information about SID, see SIDs and IDs.

To fix this you must run the following SQL commands against the SQL Server instance that is now a primary instance after the failover (in this case VM-DQS2):

ALTER AUTHORIZATION ON DATABASE::[DQS_MAIN] TO [##MS_dqs_db_owner_login##]ALTER AUTHORIZATION ON DATABASE::[DQS_PROJECTS] TO [##MS_dqs_db_owner_login##]ALTER AUTHORIZATION ON DATABASE::[DQS_STAGING_DATA] TO [##MS_dqs_db_owner_login##]

USE DQS_MAINALTER USER dqs_service WITH LOGIN=[##MS_dqs_service_login##]

USE DQS_PROJECTSALTER USER dqs_service WITH LOGIN=[##MS_dqs_service_login##]

Planned Failover

A planned failover is manual failover that you force. In this case, you can select the node that you want to failover to. In our case, we will failover to the second node in the primary data center. Before doing a manual failover, ensure that no clients are connected to the Data Quality Server to avoid losing any in-process work.

1. On either VM-DQS1 or VM-DQS 2, log on to the SQL Server instance using SQL Server Management Studio.

2. Expand Always On High Availability, expand Availability Groups, right-click DQS-AG, and click Show Dashboard.

3. Use the AlwaysOn Dashboard to ensure that the state of the DQS databases on the secondary node (VM-DQS2) is fine, and it’s not reporting any data loss.

4. Right-click DQS-AG, and click Failover to begin the manual failover process.

5. A wizard appears that prompts you to select the new primary replica. Click VM-DQS2, and then click Next.

6. The next page prompts you to connect to the secondary node. Click Connect. Once connected. Click Next.

7. On the next page, click Finish to begin the manual failover process. The status is displayed in the wizard. Once the wizard completes successfully, click Close.

8. Connect to the SQL Server instance on the new primary node (VM-DQS2), and run the SQL commands that are listed earlier in the section Handling Failover to Ensure High Availability.

9. Connect to the Data Quality Server from a Data Quality Client using the availability group listener name, and continue with your work.

Unplanned Failover

Manually running the script after a failover occurs to enable connectivity to the Data Quality Server on the new primary node is not an ideal situation for the unplanned failover scenario as it hinders high-availability of the DQS application. To work around this, you can setup a SQL Server Agent job that polls the replica status, and detects when its node has become a primary node, and then automatically executes the required SQL commands against the new primary instance. To do so:

1. On the first node (VM-DQS1), ensure that SQL Server Agent is running.

2. Create a database, say DQSFailOverStatus.

3. In this database, create a table, replica_role, using the following script:

USE DQSFailoverStatus;CREATE TABLE [dbo].[replica_role](

[replica_role] [tinyint] NULL)INSERT INTO [dbo].[replica_role] ([replica_role]) ( SELECT [role] FROM sys.dm_hadr_availability_replica_states WHERE is_local = 1);

4. Create a SQL Agent Job with the following Transact-SQL as its step:

USE DQSFailoverStatus; DECLARE @last_role TINYINT;SET @last_role = ( SELECT TOP 1 [replica_role] FROM [dbo].[replica_role]); DECLARE @current_role TINYINT;SET @current_role =

( SELECT ROLE FROM sys.dm_hadr_availability_replica_states WHERE is_local = 1); -- Last time it was secondary, currently it is primary; run the commands-- to alter the authorization

IF (@last_role = 2 AND @current_role = 1) BEGINALTER AUTHORIZATION ON DATABASE::[DQS_MAIN] TO [##MS_dqs_db_owner_login##]ALTER AUTHORIZATION ON DATABASE::[DQS_PROJECTS] TO [##MS_dqs_db_owner_login##]ALTER AUTHORIZATION ON DATABASE::[DQS_STAGING_DATA] TO [##MS_dqs_db_owner_login##]

USE DQS_MAIN;ALTER USER dqs_service WITH LOGIN=[##MS_dqs_service_login##]

USE DQS_PROJECTS;ALTER USER dqs_service WITH LOGIN=[##MS_dqs_service_login##]END USE DQSFailoverStatus;UPDATE dbo.[replica_role] SET [replica_role] = @current_role;

5. Set the schedule of the job to recur after a certain amount of time. For example, after every 2 minutes.

6. Repeat steps 1-5 on the second node, VM-DQS2, in the primary data center.

Whenever an unplanned failover happens, the secondary node in the primary data center will automatically become the primary node, and the newly created SQL Server Agent will run the script (in step 4) on the new primary node instance within 2 minutes of the database failover to make it available for clients to log on to Data Quality Server.

Handling Disaster Recovery

The steps to handle disaster recovery by leveraging the third node (VM-DQS3) in the disaster recovery data center in case there is an outage in both the nodes in the primary data center are similar to what is described in the Recovering from a Disaster section in the AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups paper. However, whenever you force a failover, you must execute the following SQL commands against the target SQL Server instance to ensure that clients can connect to the Data Quality Server:

ALTER AUTHORIZATION ON DATABASE::[DQS_MAIN] TO [##MS_dqs_db_owner_login##]ALTER AUTHORIZATION ON DATABASE::[DQS_PROJECTS] TO [##MS_dqs_db_owner_login##]ALTER AUTHORIZATION ON DATABASE::[DQS_STAGING_DATA] TO [##MS_dqs_db_owner_login##]

USE DQS_MAINALTER USER dqs_service WITH LOGIN=[##MS_dqs_service_login##]

USE DQS_PROJECTS

ALTER USER dqs_service WITH LOGIN=[##MS_dqs_service_login##]

Upgrading DQS Databases after SQL Server Updates

Some SQL Server 2012 updates (cumulative updates or service packs) might require you to upgrade DQS database schemas. You upgrade the DQS database schemas by running the DQSInstaller.exe file with the -upgrade command line parameter. For more information, see Upgrade DQS Databases Schema After Installing SQL Server Update.

Since DQS databases contain assemblies and other complex objects apart from the databases objects, you must perform the following additional steps to upgrade your DQS databases schemas in an availability group.

NOTE: Upgrading DQS databases schema does not impact your existing data in the DQS databases (knowledge bases, data quality projects, and exported results in the DQS_STAGING_DATA database). However, you must back up your DQS databases before upgrading DQS databases schema to prevent any accidental data loss during the schema upgrade. For information about backing up DQS databases, see Backing Up and Restoring DQS Databases.

Step 1: Install SQL Server Updates and Upgrade the DQS Database Schema on the Primary Node

1. Ensure that no clients are connected to the Data Quality Server in the availability group.

2. Before installing SQL Server updates and upgrading the DQS database schema on the primary node, remove the DQS databases from the availability group on the primary node.

a. On the primary node in the availability group, start SQL Server Management Studio, and then connect to the SQL Server instance that has the availability group.

b. Set the database failover mode for all the nodes in the availability group to manual. On the left pane of SQL Server Management Studio, expand AlwaysOn High Availability, expand Availability Groups, right click the DQS availability group name, and then click Properties. In the properties dialog box, under the Availability Replicas area in the right pane, change the Failover Mode to Manual for all the SQL Server instances in the availability group, and then click OK. This is to avoid any undesired failovers during the upgrade process. In this case, you must change the failover mode to manual for the VM-DQS1 and VM-DQS2 nodes because the failover mode for VM-DQS3 is already set to manual.

c. Remove the DQS databases from the availability group. Expand the DQS availability group, expand Availability Databases, right-click DQS_MAIN, and then click Remove Database from Availability Group. Repeat this for the DQS_PROJECTS and DQS_STAGING_DATA databases to delete them as well.

d. Exit SQL Server Management Studio.

3. Run the SQL Server update file to update your instance of SQL Server.

4. After the SQL Server update completes successfully, connect to the SQL Server instance using SQL Server Management Studio, and run the following command so that unsafe DQS assemblies can be registered during the schema upgrade:

ALTER DATABASE [DQS_MAIN] SET TRUSTWORTHY ON

5. Run the following commands to restore the database master key:

USE DQS_MAINOPEN MASTER KEY DECRYPTION BY PASSWORD = '<DB_MASTER_KEY>'ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

In the above command, <DB_MASTER_KEY> is the database master key that you specified earlier while installing Data Quality Server by running the dqsinstaller.exe file.

6. Start command prompt, and at the command prompt, change your directory to the location where the DQSInstaller.exe file is available.

7. At the command prompt, type the following command, and press ENTER:

dqsinstaller.exe –upgrade

8. The installer prompts you for backing up the DQS databases before proceeding. Type Y or Yes, and then press ENTER to continue with the upgrade. A completion message is displayed after successful upgrade of the DQS databases schema.

Step 2: Install SQL Server Updates and Delete the DQS Databases on the Secondary Node

1. On the secondary nodes, run the SQL Server update file to update your instances of SQL Server.

2. After the SQL Server update completes successfully, connect to the SQL Server instance using SQL Server Management Studio, expand Databases, and delete DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases from the SQL Server instance.

This is because the DQS databases on the secondary nodes in the availability group are read-only copies, and hence cannot be upgraded. In the next step, we will add the upgraded DQS databases on the primary node to the existing availability group, and let the DQS databases restore from the primary replica to the secondary replicas.

Step 3: Add Upgraded DQS Databases Back to the Availability Group on the Primary Node

1. On the primary node, connect to your SQL Server instance using SQL Server Management Studio.

2. Add the upgraded DQS databases back to the availability group by expanding the DQS availability group, right-clicking Availability Databases, and clicking Add Database.

3. On the Introduction page of the add database wizard, click Next.

4. On the Select Databases page, select the three DQS databases (DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA), and click Next.

5. On the Select Data Synchronization page, ensure that Full is selected, and the same network share location is specified as the one you specified earlier in step 8 in the Step 4: Configure the Availability Group section while initially configuring the availability group. For more information about this page, see Select Initial Data Synchronization Page. Click Next.

6. On the Specify Replicas page, connect to the secondary nodes, and click Next.

7. The Validation page displays the results of each completed validation step. Ensure that all the validation steps are successful, and click Next.

8. Use the Summary page to review your choices for the new availability group, and click Finish to add the upgraded DQS databases on the primary node to the availability group.

9. The Results page displays the progress. After the wizard completes successfully, click Close.

After the DQS databases are successfully added to the availability group on the primary node, the data synchronization process automatically replicates the upgraded DQS databases from the primary node to the secondary nodes.

NOTE: After you have successfully added the DQS databases to the availability group, ensure that you change the failover mode of the appropriate nodes in the availability group to automatic to ensure high availability of DQS in case of an unplanned failover. The nodes where you set the failover mode to automatic must have the SQL Server Agent with the failover script as explained earlier in the Unplanned Failover section.

Feedback

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.