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