Tag Archives: DQS AlwaysOn SQLServer 2012

DQS with AlwaysOn automatic failover

In the last blog post I described how to set up DQS in AlwaysOn.

In this post I will setup automatic failover of the database. The actual failover is handled by SQL server

availability groups, but there are a couple of steps that has to be done manually to be able to connect successfully

to the DQS database after a failover, automatic or manual.

I’m using the document as a guide for this setup:

http://msdn.microsoft.com/en-us/library/jj874055.aspx

The problem is the mapping of SID’s don’t get aligned correctly between the different hosts in the AG.

In manual or planned failover scenario it is easy to just run the following commands database failover has completed:

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_MAIN

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

USE DQS_PROJECTS

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

In unplanned automatic failover no one is around to run the command, this can solved by creating the following solution:

Create a database on each node, DQSFailoverStatus, and then a table called replica_role

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

);

Create a SQL agent Job on all nodes in the AG:

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)
BEGIN
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_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;

Depending on your SLA requirements setup the agent job to run every 5-15 minutes.

If you have multiple AG’s on you installation the check script has to be modified slightly to recognaize the AG name where you DQS database is running, something like this:

SELECT ROLE
FROM sys.dm_hadr_availability_replica_states states
join sys.dm_hadr_name_id_map map
on states.group_id = map.ag_id
WHERE states.is_local = 1
and map.ag_name =’IT’

Advertisements

DataQualityServices setup with AlwaysON

Setting up Data Quality Service with Always on is well described in this document:

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/Using_AlwaysOn_Availability%20_Groups%20_for%20_HighAvailability_and_DisasterRecovery_of_DQS.docx.

I used that as guide when I installed DQS at a customer site today.

The procedure was very straight forward and I had no problems at all.

1. First I added DQS feature from the SQL 2012 installation media. I did not have to reboot or restart SQL server so that was easy.

2. I applied the SP1 and CU3 patches that where previously applied for the rest of the SQL server.

3. I repeated this on all the nodes where the AvailabilityGroup was setup to run.

4. After that I had to run the DQSInstaller.exe file in the …\MSSQL11.MSSQLSERVER\MSSQL\Binn folder on each node. This requires me to enter a Database Master Key, you must use the same key on all nodes and be careful not to forget it. It is need when a SQL upgrade takes place.

The DQSInstaller then created three databases.

5. Then I added a Windows account on the first node and gave it permissions to the DQS_MAIN database according to the document.

6. Then I deleted the new databases from the two other nodes in the cluster.

7. Now I could add the three databases as availability databases in the AG. And they where copied out to all nodes.

8. I then copied the Windows account to all three nodes. Keep in mind tha AlwaysOn doesn’t replicate any logins. I use a sp called dbs_CopyLogins, it can downloaded here: http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror, is very useful since it copies the password correctly..

9. Then I installed the DQS client from the SQL distribution and patches it. When I started the Client I Could just enter the AG listener name and could successfully login.

Staffan Olofsson
MCSE: Data Platform