Sort SSMS Multi Server Result Set

Hi,

A little known really good SSMS functionality is what is called Registered Servers, for more info on that specifically, look here

What I want to talked about today is the problem when using it against a bunch of servers and wanting to get the result set back ordered. Thing is the servers send back their respective result set when they are done and there is no way in SSMS you can sort it. My problem I had was that I wanted the result sets back in the order of the server name, I was coding a simple script to check the Always-On and Mirroring health against a whole bunch of servers, the output from them unsorted was very cluttered, I wanted the result sets sorted.

What I ended up doing, and you can see it in the script below, is that I converted the server name to UNICODE and took the first number as seconds to wait and the next 3 as milliseconds to wait before returning the result set. The result set I wanted was the health of the database synchronization, I know this should be monitored but sometimes you just want to do a quick check, and the wait part can be used for whatever you like.

It’s so not perfect and may not work in your environment, run it on some test servers first, you may have to adapt the code a bit but it’s quite easy.

A quick hack but it made my day 🙂

USE master
GO

DECLARE @name VARCHAR(20),@sum VARCHAR(5), @ss VARCHAR(2), @ms VARCHAR(3)
DECLARE @position int
DECLARE @delay VARCHAR(12) 
SET @name =  @@SERVERNAME
SET @position = 1

WHILE @position <= LEN(@name)
BEGIN
    SELECT @sum = CONCAT(@sum, UNICODE(SUBSTRING(@name, @position, 1)))
    SELECT @position = @position + 1;  
END
SET @ss = SUBSTRING(@sum,1,1)
SET @ms = SUBSTRING(@sum,2,3)
SET @delay = '00:00:'+@ss+':'+@ms
WAITFOR DELAY @delay

--PRIMARY AG
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'PRIMARY')
SELECT 'AO Primary' as HA, role_desc, operational_state_desc, connected_state_desc, recovery_health_desc, synchronization_health_desc FROM msdb.sys.dm_hadr_availability_replica_states WHERE role_desc = 'PRIMARY'

--SECONDARY AG
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'SECONDARY')
SELECT 'AO Secondary' as HA, role_desc, operational_state_desc, connected_state_desc, recovery_health_desc, synchronization_health_desc FROM msdb.sys.dm_hadr_availability_replica_states WHERE role_desc = 'SECONDARY'

--OLD SCHOOL MIRRORING
IF EXISTS(select * from sys.database_mirroring where mirroring_guid is not null)
SELECT 'Mirroring' AS HA, DB_NAME(database_id) AS 'DB name', mirroring_state_desc, mirroring_role_desc from sys.database_mirroring where mirroring_guid is not null

Leave a Reply

Your email address will not be published. Required fields are marked *