Using DQS with SQL Server AlwaysOn AG

  • Article

Hi everyone,

A couple of weeks ago I presented a session on cloud IT Pro Airlift: Infrastructure & Data Platform event in Portugal and did a demonstration about Enterprise Information Management (EIM) session using SQL Server 2012 AlwaysOn feature.

 

If you search for the text “Building an AlwaysOn HADR solution for DQS” , you will get a result for a post from Kumar Vivek @ https://blogs.msdn.com/b/kvivek/archive/2013/01/10/whitepaper-on-building-an-alwayson-hadr-solution-for-dqs-using-availability-groups.aspx.

This post contains a link for a whitepaper document that will provide you a step-by-step guide on how to configure SQL Server AlwaysOn HA Availability Groups (AG) for DQS databases.

I followed the instructions in the paper but after setting up my environment with three SQL Server servers I found a problem with multiple AG. If you pay a closer look at page 10, you will find that when a failover (manual/automatic) happens you will need to reconfigure access to DQS databases and need to run a couple of T-SQL commands that can be automated using a SQL Server Job. In page 11 you also find a script that can be automated using a SQL Server Agent Job and looks for the SQL Server instance
that is currently participating in the primary role AlwaysOn AG.

In my case I had two AG: one for MDS and another for DQS. Because of that I need to change the script to filter the DQS AG name:

With this simple trick you can now setup you SQL Server Agent Job and run it periodically in your AlwaysOn AG for DQS.

Please note that you can also find a similar whitepaper for MDS using AlwaysOn AG @ https://msdn.microsoft.com/en-us/library/jj884069.aspx.

Hope it helps,

Alexandre Mendeiros

 

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.