Querying HA Status

Me: Have you ever started the day with a long list of to-do items, and the first one looks easy, but you work all day on it and you don't get even that one thing done? Friend: No. Me: Me neither :)

Question: How do I show the status of my SQL Anywhere 12 High Availability system?

I want to make sure the arbiter and partner servers are working properly, and I don't have Foxhound installed.

Answer: Use the SQL Anywhere Monitor:

• Create a "SQL Anywhere Database Resource" that connects to the primary server, then
  

• create a "SQL Anywhere Scale-Out Topology Widget" based on that "Resource":
  
  
  

• The Server Name column shows the real server names defined by the CREATE MIRROR SERVER ... AS PARTNER and CREATE MIRROR SERVER ... AS ARBITER statements.
  

• The Type column shows the mirror roles (primary versus mirror) as well as the partner-versus-arbiter breakdown.
  

• The State column tells you whether the server is connected or not.
  

• Sadly Interestingly, the display doesn't show the alternate server names defined by the CREATE MIRROR SERVER ... AS PRIMARY and CREATE MIRROR SERVER ... AS MIRROR statements.
  

Here's what the SQL Anywhere Monitor shows when the mirror has crashed:

Here's what failover looks like; sadly interestingly, the rows have changed order to keep the current primary at the top:

Question: The SQL Anywhere Monitor doesn't work for me.

Answer: It looks like the SQL Anywhere Monitor widget only works for declarative High Availability setups. A "declarative" setup uses the new CREATE MIRROR SERVER statement instead of the "old-school" dbsrv12 -xp command line option. The widget is (probably?) looking in the SYSMIRRORSERVER view and that will be empty in an old-school setup.

It is possible to code a generic SQL query that works with any HA setup, be it declarative or old-school:

Here's what it looks like when the secondary (mirror) goes away...

Here's what it looks like after a failover, when the other partner becomes the primary...

With this query, the output is always sorted on the real server name which corresponds to physical computers rather than logical primary and mirror roles.

Also, the status "connected" is shown as "OK" and the primary role is shown in upper case, both of which serve to highlight important distinctions.

No custom application is required to run this query, just an ISQL command file that connects to the primary server; for example:

@ECHO OFF ECHO. SETLOCAL ENABLEDELAYEDEXPANSION SETLOCAL ENABLEEXTENSIONS "%SQLANY12%\bin64\dbisql.com"^ -c "SERVER=primary_demo; UID=dba; PWD=sql; HOST=localhost:55401,localhost:55402"^ READ ENCODING Cp1252 "Query_HA_status.sql" IF ERRORLEVEL 1 ( GOTO dbisql_failure ) ELSE ( GOTO end ) :dbisql_failure ECHO ****************************** ECHO *** dbisql.com ERRORLEVEL = %ERRORLEVEL% ECHO ****************************** :end PAUSE

If you see the ERRORLEVEL message then you know that All Hope Is Lost... if you can't connect to the primary, then HA is completely down.

Here's the Query_HA_status.sql script file... living proof that simple output often requires complex code:

BEGIN

DECLARE LOCAL TEMPORARY TABLE ha_status (
   real_server_name      VARCHAR ( 128 ) NOT NULL PRIMARY KEY,
   role                  VARCHAR ( 7 ) NOT NULL,
   alternate_server_name VARCHAR ( 128 ) NOT NULL,
   state                 VARCHAR ( 100 ) NOT NULL )
   NOT TRANSACTIONAL;

DECLARE @MAX_LENGTH_real_server_name      INTEGER;
DECLARE @MAX_LENGTH_role                  INTEGER;
DECLARE @MAX_LENGTH_alternate_server_name INTEGER;
DECLARE @MAX_LENGTH_state                 INTEGER;
DECLARE @MAX_line_length                  INTEGER;

INSERT ha_status
SELECT PROPERTY ( 'ServerName' )                    AS real_server_name,
       'PRIMARY'                                    AS role,
       DB_PROPERTY ( 'AlternateServerName' )        AS alternate_server_name,
       CASE
          WHEN sa_mirror_server_status.state  = 'connected'
           AND DB_PROPERTY ( 'ReadOnly' )     = 'Off'
             THEN 'OK'           -- always expected 
          WHEN sa_mirror_server_status.state = 'disconnected'
             THEN 'disconnected' -- never expected
          ELSE STRING (          -- never expected  
             sa_mirror_server_status.state,
             ', ReadOnly=',
            DB_PROPERTY ( 'ReadOnly' ) )
       END CASE                                     AS state
  FROM sa_mirror_server_status() 
 WHERE sa_mirror_server_status.server_name = PROPERTY ( 'ServerName' )
   AND DB_PROPERTY ( 'MirrorRole' ) = 'primary' -- assertion
UNION ALL
SELECT sa_mirror_server_status.server_name          AS real_server_name, 
       'mirror',
       DB_PROPERTY ( 'AlternateMirrorServerName' )  AS alternate_server_name,
       CASE
          WHEN sa_mirror_server_status.state  = 'connected'
           AND DB_PROPERTY ( 'PartnerState' ) = 'connected'
           AND DB_PROPERTY ( 'MirrorState' )  = 'synchronized'
             THEN 'OK'
          WHEN sa_mirror_server_status.state = 'disconnected'
             THEN 'disconnected'
          ELSE STRING (  
             sa_mirror_server_status.state,
             ', ',
             DB_PROPERTY ( 'PartnerState' ),
             ', ',
             DB_PROPERTY ( 'MirrorState' ) )
       END CASE                                     AS state
  FROM sa_mirror_server_status() 
 WHERE sa_mirror_server_status.server_name <> PROPERTY ( 'ServerName' )
   AND DB_PROPERTY ( 'MirrorRole' ) = 'primary' -- assertion
UNION ALL
SELECT COALESCE ( SYSMIRRORSERVER.server_name, '' ) AS real_server_name,
       'arbiter'                                    AS role,
       ''                                           AS alternate_server_name,
       IF DB_PROPERTY ( 'ArbiterState' ) = 'connected'
          THEN 'OK'
          ELSE DB_PROPERTY ( 'ArbiterState' )
       END IF                                       AS state
  FROM DUMMY 
          LEFT OUTER JOIN 
             ( SELECT * 
                 FROM SYSMIRRORSERVER
                WHERE SYSMIRRORSERVER.server_type = 'arbiter' ) AS SYSMIRRORSERVER
          ON 1 = 1
   AND DB_PROPERTY ( 'MirrorRole' ) = 'primary' -- assertion
UNION ALL
SELECT 'This query can only be run against the current primary server.', '', '', ''
  FROM DUMMY
 WHERE DB_PROPERTY ( 'MirrorRole' ) <> 'primary';

SELECT MAX ( LENGTH ( ha_status.real_server_name ) ) + 1,
       MAX ( LENGTH ( ha_status.role ) ) + 1,
       MAX ( LENGTH ( ha_status.alternate_server_name ) ) + 1,
       MAX ( LENGTH ( ha_status.state ) ) 
  INTO @MAX_LENGTH_real_server_name,
       @MAX_LENGTH_role,
       @MAX_LENGTH_alternate_server_name,
       @MAX_LENGTH_state
  FROM ha_status;

SET @MAX_line_length 
  = @MAX_LENGTH_real_server_name 
  + @MAX_LENGTH_role 
  + @MAX_LENGTH_alternate_server_name 
  + @MAX_LENGTH_state;

SELECT LEFT ( STRING ( 
                 LEFT ( STRING ( ha_status.real_server_name, REPEAT ( ' ', 129 ) ),       
                        @MAX_LENGTH_real_server_name ), 
                 LEFT ( STRING ( ha_status.role, REPEAT ( ' ', 8   ) ),       
                        @MAX_LENGTH_role ),
                 LEFT ( STRING ( ha_status.alternate_server_name, REPEAT ( ' ', 129 ) ),       
                        @MAX_LENGTH_alternate_server_name ),
                 ha_status.state ), 
              @MAX_line_length ) AS "High Availability Status"
  FROM ha_status
 ORDER BY ha_status.real_server_name;

END;

The table defined on line 3 is filled with three rows for the arbiter, primary and mirror. For an old-school setup, the real_server_name gets the dbsrv12 -n name (except for the arbiter server where this column is empty), the role is set to 'arbiter', 'PRIMARY' or 'mirror', the alternate_server_name is filled with the dbsrv12 -sn or -sm primary or mirror server names, and the state contains 'OK' or something less friendly.

For a declarative HA setup, the table is the same except (a) the real_server_name is filled in and (b) some of the values come from CREATE MIRROR SERVER statements rather than the dbsrv12 command line.

The INSERT on line 16 is a UNION of four SELECT statements. The first fills the 'PRIMARY' row, the second SELECT fills the 'mirror' row, the third fills the 'arbiter' row and the fourth SELECT returns an error message in case your ISQL connection string isn't properly set up to connect to the primary.

The INSERT relies on information from three sources:

• Various PROPERTY() and DB_PROPERTY() calls,
  

• the sa_mirror_server_status() procedure which returns rows for both the primary and mirror servers in both old-school and declarative setups, and
  

• the SYSMIRRORSERVER view which is only populated in a declarative setup.
  

The arbiter is a special case. There's no PROPERTY() or DB_PROPERTY() call you can make on the primary to get the arbiter's real server name, and you can't connect to the arbiter in any meaningful way since it doesn't have a database. The only place the real server name for the arbiter is available is SYSMIRRORSERVER, which accounts for the funky FROM DUMMY LEFT OUTER JOIN starting on line 63; i.e., it causes the COALESCE on line 56 to return '' for an old-school setup and a non-empty SYSMIRRORSERVER.server_name value for a declarative setup.

The SELECT and SET statements on lines 75 through 89 calculate various display widths so the output can be crammed to fit in an ordinary Windows "DOS box" display window.

The final SELECT on line 91 returns the fixed-column-format output that is shown in the earlier images. The ORDER BY on line 101 guarantees that you can always see at-a-glance when the PRIMARY moves from one partner to another and so on... the real server names remain in the same order, whereas the 'PRIMARY' and 'mirror' roles move up and down.

For The Record

Here's what Foxhound shows for an HA setup (old-school or declarative) that's running OK:



Here's what Foxhound shows when the mirror crashes (the primary keeps running):



Here's what failover looks like (the primary crashed so the former mirror became the primary):

---

Friend: What makes you think anyone's going to buy Foxhound if you keep showing them how to do-it-yourself? Me: It's not do-it-yourself if someone else does it for you. Friend: [fretful sighing sound] You know what I mean... Me: Yeah... but I don't know the answer :)

---