8

Background:

We are trying to create an AG "is primary" test harness for one of our support teams. Not knowing which servers at any given time will be the primary they have been instructed to execute the TSQL against a registered server group. The registered server group consists of all servers in the AG. The goal is to only execute TSQL on the current primary server:

Current Test Harness:

IF EXISTS (SELECT *
FROM sys.dm_hadr_availability_replica_states AS HARS 
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS HACS ON HACS.replica_id = HARS.replica_id 
    WHERE (HARS.role_desc = 'PRIMARY') AND (HACS.replica_server_name LIKE @@SERVERNAME))
BEGIN
<<SOME CODE TO EXECUTE>>
END

Problem:

If the first server that responds back to the multi server query doesn't return any results, SSMS will assume the right result set is no result set, even if other servers later come back with a result set. So in this scenario, no results get returned...this is not correct and is not the expected functionality.

Can anyone think of a way, with SSMS (this is the most familiar tool to the CS team), to force execution only on current primary server?

  • Have you considered having them connect to the AG listener, thus running the query that way? It will ensure always hitting the primary. – Nic Jun 21 '17 at 15:55
  • Unfortunately, this is not possible. We have a ton of tenants with listeners named identically. Just won't work in this case. – Harry Jun 21 '17 at 20:03
10

I have run into this** before, and if I recall correctly, to ensure always getting results with multi-server queries you need to force an empty result set when no rows would otherwise be returned. Meaning, you need an ELSE branch on that IF and within the ELSE you would do something like the following:

SELECT CONVERT(DATETIME, NULL) AS [Col1name],
       CONVERT(DECIMAL(12, 5), NULL) AS [Col2name],
       ...{additional fields}...
WHERE  1 = 0;

This produces an empty result set that has the proper names and datatypes.

OR, and I have not tried this in the past (just thought of it as I was typing this), but you might be able to get away with simply pausing in that ELSE branch such that the primary / intended server is allowed to always return its result set first (which is the real issue here: the first server to respond defines the structure that all other responses must adhere to). Hence the following might work as the only thing in the ELSE:

WAITFOR DELAY '00:00:10'; -- 10 seconds (just needs to be longer than the real query takes)

But I don't remember if having other servers return no results at all caused an error message to be displayed in the "Messages" tab. If that does happen, then the empty result set is definitely the way to go. But if this does work, then this might work better in a general template (like your case seems to be) as it wouldn't require adjusting the forced, empty result set each time it is used.

UPDATE:

The O.P. verified that:

  • the WAITFOR DELAY did indeed work, and
  • the replicas did report the error message, but it did not present a problem for the O.P.'s usage

** The situation I ran into was similar, but had nothing to do with either Availability Groups or wanting results from only one server. Our situation was that we had 18 servers of same schema different data and needed to do various maintenance tasks, aggregations across all 18 nodes. There were some stored procedures that, for whatever reason, occasionally didn't return any result set, and whatever that reason was it couldn't be fixed within the stored procedure. So, depending on which node returned first, most of the time everything was fine, but every once in a while the node that sometimes returned no result set returned first. So, I had to do something like dump the results into a temp table and if @@ROWCOUNT of that INSERT...EXEC was 0, then I would select the forced, empty result set.

  • 2
    Would of never thought of that!! The wait for delay works like a champ! Definitely saves from building the empty result set out. You do man @srutzky Thanks – Harry Jun 21 '17 at 16:11
  • @Harry You are welcome, and thank you :-). I just updated my answer to be a little clearer about the benefit of the WAITFOR DELAY approach and that you confirmed it did work, so thanks for that! – Solomon Rutzky Jun 21 '17 at 16:27
  • 1
    That is the coolest thing I've seen this week. – Brent Ozar Jun 21 '17 at 16:46
  • It definitely throws an error on the replicas, however in this case this is completely fine. – Harry Jun 21 '17 at 20:37
  • @Harry Thanks again for providing that feedback. I have added it to my answer, along with an explanation at the bottom of the situation I encountered. – Solomon Rutzky Jun 21 '17 at 20:42
0

I've always used a temp table to return results to get consistent responses from all the servers in a group. Multi server queries are one of my favourite things about SSMS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.