High Availability Demo, Revised

This article is a complete revision of Demonstrating High Availability published wayyyy back in April 2009. It has been revised

• to use SQL Anywhere 12 instead of version 11,
  

• to show all the code in the article instead of forcing you to download a file, and
  

• to use the funky new CREATE MIRROR SERVER and CREATE MIRROR OPTION statements instead of coding everything on the command line.
  

Step 1: Prepare the folders and the database file in server1.

All the databases and associated files will be stored in three subfolders immediately below the current folder: server1, server2 and arbiter.

REM Create the HA subfolders...

MD server1
MD server2
MD arbiter

REM Copy the V12 demo as a working database in server1...

PUSHD server1
COPY "C:\Documents and Settings\All Users\Documents\SQL Anywhere 12\Samples\demo.db" 
"%SQLANY12%\bin32\dblog.exe"^
  -t demo.log^
  demo.db 
"%SQLANY12%\bin32\dbspawn.exe"^
  -f "%SQLANY12%\bin32\dbeng12.exe"^
  -o "dbsrv12_demo_log.txt"^
  demo.db 
"%SQLANY12%\bin32\dbstop.exe"^
  -c "ENG=demo; UID=dba; PWD=sql"^
  -y^
  demo
POPD

The COPY command on line 10 copies the SQL Anywhere 12 demo database to the server1 subfolder.

The dblog, dbeng12 and dbstop commands on lines 11 through 21 ensure there is a functioning transaction log accompanying the database file.

Step 2: Start the partner1_demo server.

The following command starts the database in the server1 subfolder, and gives it the server name "partner1_demo":

"%SQLANY12%\bin32\dbspawn.exe"^
  -f "%SQLANY12%\bin32\dbsrv12.exe"^
  -n partner1_demo^
  -o "server1\dbsrv12_partner1_demo_log.txt"^
  -su sql^
  -x tcpip(port=55501;dobroadcast=no)^
  "server1\demo.db"^
  -xp on

Here's what the dbsrv12 command line options do:

-n partner1_demo^ the server name for the first physical server -o "server1\dbsrv12_partner1_demo_log.txt"^ where to write the diagnostic console log text file -su sql^ the DBA password for connecting via DBN=utility_db -x tcpip(port=55501; the port for direct connections to the first physical server dobroadcast=no)^ to speed up connections between HA servers "server1\demo.db"^ the first physical database file -xp on enables mirroring and read-only scale-out

Other than the -xp on option, there's nothing at all "HA" about this database yet. Those who remember the earlier way of doing things may notice that the new -xp option is quite a bit simpler than it used to be:

-xp partner=(eng=server2;links=tcpip(host=localhost;port=55502;timeout=1));mode=sync; auth=dJCnj8nUx3Lijoa8;arbiter=(eng=arbiter;links=tcpip(host=localhost;port=55500;timeout=1))

Step 3: Wait until the partner1_demo server is REALLY up and running.

All the commands in Steps 1 through 8 can be combined into one single command file, but if you do that, the commands in THIS step prevent the NEXT step from starting until the server started in the PREVIOUS step is ready to accept connections.

Other than that, this step is completely optional... go ahead, leave it out, see what happens :)

PAUSE Wait until partner1_demo is up and running...

ECHO OFF
:dbping
ECHO Waiting until partner1_demo is up and running...
"%SQLANY12%\bin32\dbping.exe"^
  -c "SERVER=partner1_demo; DBN=demo; UID=dba; PWD=sql; HOST=localhost:55501;"^
  -d
IF ERRORLEVEL 1 ( GOTO dbping ) ELSE ( GOTO continue )
:continue
ECHO ON

The dbping command on lines 6 through 8 tries connecting to the partner1_demo database, and if the connection fails the IF ERRORLEVEL 1 ( GOTO dbping ) command loops back to try again:

Press any key to continue . . . Waiting until partner1_demo is up and running... SQL Anywhere Server Ping Utility Version 12.0.1.3298 Ping database failed -- Specified database not found. Waiting until partner1_demo is up and running... SQL Anywhere Server Ping Utility Version 12.0.1.3298 Ping database failed -- Specified database not found. ... Waiting until partner1_demo is up and running... SQL Anywhere Server Ping Utility Version 12.0.1.3298 Connected to SQL Anywhere 12.0.1.3298 server "partner1_demo" and database "demo" at address 127.0.0.1. Ping database successful.

Step 4: Create the database objects to support HA.

Here's the dbisql command that runs the CREATE MIRROR SERVER and other SQL statements contained in the script that follows:

"%SQLANY12%\bin32\dbisql.com"^
  -c "SERVER=partner1_demo; DBN=demo; UID=dba; PWD=sql; HOST=localhost:55501;"^
  READ ENCODING Cp1252 "04s_script_to_create_supporting_objects.sql"

There are only three physical servers in this HA setup, but lines 5 through 31 in the script below show five separate CREATE MIRROR SERVER statements. These statements don't create actual servers (only a dbsrv12 command can do that), they just store descriptions of all the servers in the first physical database file. That physical database is the only database file that exists at this point in time, but Step 5 will copy it to create the second physical database, along with all five CREATE MIRROR SERVER definitions... so both physical databases will contain all the necessary information about each other and about the arbiter.

CREATE MIRROR SERVER partner1_demo the first "AS PARTNER" physical server, with a name matching the dbsrv12 -n option in Step 2, plus a state_file in the server1 folder partner2_demo the second "AS PARTNER" physical server, with a name matching the dbsrv12 -n option in Step 6, plus a state_file in the server2 folder primary_demo the logical "AS PRIMARY" server name for whichever partner happens to be the HA primary server at any point in time (hence the two ports). secondary_demo the logical "AS MIRROR" Server name for whichever partner happens to be the HA secondary or mirror server at any point in time (hence the two ports). arbiter_demo the physical arbiter server, which doesn't have an actual database or state file.

-----------------------------------------------------------------------------------------------
-- Describe the first of two physical mirror partners.
-- When this script is first executed, this is the only server actually running.

CREATE MIRROR SERVER partner1_demo AS PARTNER
   connection_string = 'SERVER=partner1_demo; host=localhost:55501'
   state_file        = 'server1/partner1_demo.state';

-- Describe the second of two physical mirror partners.

CREATE MIRROR SERVER partner2_demo AS PARTNER
   connection_string = 'SERVER=partner2_demo; host=localhost:55502'
   state_file        = 'server2/partner2_demo.state';

-- Describe the logical primary server.

CREATE MIRROR SERVER primary_demo AS PRIMARY
   connection_string = 'SERVER=primary_demo; host=localhost:55501,localhost:55502';

-- Describe the logical secondary (mirror) server.

CREATE MIRROR SERVER secondary_demo AS MIRROR
   connection_string = 'SERVER=secondary_demo; host=localhost:55501,localhost:55502';

-- Describe the physical arbiter server.
-- The information recorded here is only for use by the partner servers. The arbiter
-- server itself doesn't have any physical database associated with it, so all the options
-- required by the arbiter server must be specified on the arbiter server command line.

CREATE MIRROR SERVER arbiter_demo AS ARBITER
   connection_string ='SERVER=arbiter_demo; HOST=localhost:55500';

-- Define the authentication string.

SET MIRROR OPTION authentication_string = 'abc';

-- Set the HA synchronization mode to synchronous (default), asynchronous or asyncfullpage.

SET MIRROR OPTION synchronization_mode = 'synchronous';

-- Request automatic failover when synchronization_mode set to asynchronous or asyncfullpage.

SET MIRROR OPTION auto_failover = 'on';

-----------------------------------------------------------------------------------------------
CREATE FUNCTION which_database()
RETURNS LONG VARCHAR
BEGIN

   RETURN STRING (
      'PROPERTY ( ''MachineName'' )     = ', PROPERTY ( 'MachineName' ),     '\x0d\x0a',
      'PROPERTY ( ''Name'' )            = ', PROPERTY ( 'Name' ),            '\x0d\x0a',
      'PROPERTY ( ''ServerName'' )      = ', PROPERTY ( 'ServerName' ),      '\x0d\x0a',
      'PROPERTY ( ''TcpIpAddresses'' )  = ', PROPERTY ( 'TcpIpAddresses' ),  '\x0d\x0a',
      'DB_PROPERTY ( ''Name'' )         = ', DB_PROPERTY ( 'Name' ),         '\x0d\x0a',
      'DB_PROPERTY ( ''ReadOnly'' )     = ', DB_PROPERTY ( 'ReadOnly' ),     '\x0d\x0a',
      'DB_PROPERTY ( ''ArbiterState'' ) = ', DB_PROPERTY ( 'ArbiterState' ), '\x0d\x0a', 
      'DB_PROPERTY ( ''PartnerState'' ) = ', DB_PROPERTY ( 'PartnerState' ), '\x0d\x0a', 
      'DB_PROPERTY ( ''MirrorState'' )  = ', DB_PROPERTY ( 'MirrorState' ),  '\x0d\x0a', 
      'DB_PROPERTY ( ''File'' )         = ', DB_PROPERTY ( 'File' ),         '\x0d\x0a', 
      'CONNECTION_PROPERTY ( ''Number'' )          = ', CONNECTION_PROPERTY ( 'Number' ),   '\x0d\x0a',
      'CONNECTION_PROPERTY ( ''Name'' )            = ', CONNECTION_PROPERTY ( 'Name' ),     '\x0d\x0a',
      'CONNECTION_PROPERTY ( ''CommLink'' )        = ', CONNECTION_PROPERTY ( 'CommLink' ), '\x0d\x0a',
      'CONNECTION_PROPERTY ( ''CommNetworkLink'' ) = ', CONNECTION_PROPERTY ( 'CommNetworkLink' ) );

END; 

The SET MIRROR OPTION statements on lines 35 through 43 fill in some values the HA setup needs, only one of which (authentication_string) is actually required in this demo:

SET MIRROR OPTION authentication_string = 'abc'; to be used by all the servers in the HA setup synchronization_mode = 'synchronous'; the default auto_failover = 'on'; in case synchronization_mode is changed

The CREATE FUNCTION on lines 46 through 66 can be used to answer the question, "Who am I connected to?" Here's what it shows for one of the connections in Step 8:

MESSAGE which_database() TO CLIENT; PROPERTY ( 'MachineName' ) = ENVY PROPERTY ( 'Name' ) = primary_demo PROPERTY ( 'ServerName' ) = partner1_demo PROPERTY ( 'TcpIpAddresses' ) = DB_PROPERTY ( 'Name' ) = demo DB_PROPERTY ( 'ReadOnly' ) = Off DB_PROPERTY ( 'ArbiterState' ) = connected DB_PROPERTY ( 'PartnerState' ) = connected DB_PROPERTY ( 'MirrorState' ) = synchronized DB_PROPERTY ( 'File' ) = C:\$ blogs and websites\blog SQLAnywhere\20131028 PENDING - High Availability Demo, Revised\server1\demo.db CONNECTION_PROPERTY ( 'Number' ) = 87 CONNECTION_PROPERTY ( 'Name' ) = Updatable CONNECTION_PROPERTY ( 'CommLink' ) = local CONNECTION_PROPERTY ( 'CommNetworkLink' ) = TCPIP Execution time: 0.031 seconds

Step 5: Create the database file in server2.

The dbbackup utility can be used to create a copy of the first database and store the files in the server2 subfolder:

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "SERVER=partner1_demo; DBN=demo; UID=dba; PWD=sql; HOST=localhost:55501;"^
  -o "server2\dbbackup_partner1_demo_log.txt"^
  server2

Step 6: Start the partner2_demo server.

"%SQLANY12%\bin32\dbspawn.exe"^
  -f "%SQLANY12%\bin32\dbsrv12.exe"^
  -n partner2_demo^
  -o "server2\dbsrv12_partner2_demo_log.txt"^
  -su sql^
  -x tcpip(port=55502;dobroadcast=no)^
  "server2\demo.db"^
  -xp on

Except for the server name, subfolder and port number, these dbsrv12 options are the same here as in Step 2:

-n partner2_demo^ the server name for the second physical server -o "server2\dbsrv12_partner2_demo_log.txt"^ where to write the diagnostic console log text file -su sql^ the DBA password for connecting via DBN=utility_db -x tcpip(port=55502; the port for direct connections to the second physical server dobroadcast=no)^ to speed up connections between HA servers "server2\demo.db"^ the second physical database file -xp on enables mirroring and read-only scale-out

The simplicity of the new -xp on option is important to note again: All the other objects and options required by the HA setup were defined once, in the script shown in Step 4.

Step 7: Start the arbiter server.

Because there is no physical database file associated with the arbiter server, the objects and options defined by the script in Step 4 are no use when starting the arbiter. That's why the following dbsrv12 command doesn't just specify different server name, subfolder and port number values, it also includes -xf and -xa options to fill in the blanks:

"%SQLANY12%\bin32\dbspawn.exe"^
  -f "%SQLANY12%\bin32\dbsrv12.exe"^
  -n arbiter_demo^
  -o "arbiter\dbsrv12_arbiter_demo_log.txt"^
  -su sql^
  -x tcpip(port=55500;dobroadcast=no)^
  -xf "arbiter\arbiter_demo.state"^
  -xa "AUTH=abc;DBN=demo"

-n arbiter_demo^ the server name for the physical arbiter server -o "arbiter\dbsrv12_arbiter_demo_log.txt"^ where to write the diagnostic console log text file -su sql^ the DBA password for connecting via DBN=utility_db -x tcpip(port=55500; the port for direct connections to the arbiter server dobroadcast=no)^ to speed up connections between HA servers -xf "arbiter\arbiter_demo.state"^ where to store the arbiter server state file -xa "AUTH=abc; the authorization string to use for this HA setup DBN=demo" the database name to use for this HA setup

At this point, there are five servers running: three physical servers partner1_demo, partner2_demo and arbiter_demo started by the dbsrv12 commands in Steps 2, 6 and 7, and the two logical primary and secondary (mirror) servers defined by the script in Step 4: primary_demo and secondary_demo.

Step 8: Connect to the primary and secondary databases.

The following script uses the logical server names to connect to the primary and secondary (mirror) databases; the connection names are "Updatable" and "Read-Only" to indicate that changes can be made to database data only via the primary server:

PAUSE Wait until the servers are up and running, then

"%SQLANY12%\bin32\dbisql.com"^
  -c "SERVER=primary_demo; UID=dba; PWD=sql; HOST=localhost:55501,localhost:55502; CON=Updatable" 

"%SQLANY12%\bin32\dbisql.com"^
  -c "SERVER=secondary_demo; UID=dba; PWD=sql; HOST=localhost:55501,localhost:55502; CON=Read-Only" 

The connection strings don't need to specify a DBN database name value because there's only one database running on each server. Single SQL Anywhere servers can (and often do) run multiple databases, but in the world of High Availability that . . . is . . . just . . . not . . . done. Nothing in the documentation says "you can't do it", but none of the examples show how it might be done, and all of the documentation about High Availability refers to "database" in the singular.

Here's a screenshot show two dbisql sessions where a table that has been freshly created and loaded in "Updatable" dbisql session

CREATE TABLE t ( c INTEGER ); INSERT t VALUES ( 1 ); COMMIT;

is available almost immediately to be selected in the "Read-Only" session

SELECT * FROM t;

even though both sessions are connected to entirely different databases (the primary and the secondary):

What Failover Looks Like

Here are a pair of screenshots showing the "Updatable" dbisql session before and after

• the original primary server partner1_demo was abruptly stopped,
  

• the arbiter and secondary servers got together to make partner2_demo the new primary server,
  

• and the "Updatable" dbisql session automatically reconnected to the new primary server partner2_demo (as shown by the "Reconnected to database" message):
  

"Read-Only" Can Become Updatable

After the failover described above, the "Read-Only" dbisql session remained connected to what became updatable primary server; consider these interesting facts:

• PROPERTY ( 'ServerName' ) returns "partner2_demo" which is the physical server the "Read-Only" session remains connected to,
  

• PROPERTY ( 'Name' ) still returns "secondary_demo" even though the physical server "partner2_demo" has now become the logical "primary_demo" server, and
  

• DB_PROPERTY ( 'ReadOnly' ) is now set "Off" for this connection, which means the "Read-Only" session is now updatable.
  

In other words, nothing changes for the "Read-Only" session as long as the physical partner2_demo server (the original secondary, and now the new primary) remains running. Even if the partner1_demo server is restarted, it will assume the role of secondary server, and the "Read-Only" session will remain updatable.

It's a matter of opinion whether this behavior obeys the rule "Watcom Does Things The Way They Should Be Done", but the Help is clear on the question "Bug or Feature?"... it's a feature:

Configuring read-only access to a database running on the mirror server Connections to the mirror database are maintained if failover occurs and the mirror server becomes the primary server. After failover, a connection can make changes to the database. You can query the value of the ReadOnly database property to determine whether the database you are connected to is updatable: SELECT DB_PROPERTY( 'ReadOnly' );

Divine Right Of Update

There is nothing in the way of locks or blocks that a read-only connection to the secondary (mirror) server can do to prevent updates made to the primary database from being applied to the secondary... in other words, the primary database has divine right of update.

For example, consider the earlier scenario where a table is created and loaded in an "Updatable" dbisql session on the primary database

CREATE TABLE t ( c INTEGER ); INSERT t VALUES ( 1 ); COMMIT;

followed by a query in a "Read-Only" session on the secondary database, only this time with the isolation level set to 3:

SET TEMPORARY OPTION ISOLATION_LEVEL = '3'; SELECT * FROM t; c 1

If an update is then made to the same row on the primary database

UPDATE t SET c = 2; COMMIT;

it isn't blocked by the transaction started at isolation level 3 on the "Read-Only" connection. Far from being blocked, the update is not only applied to the primary database, it is also applied to the secondary database, and the next statement executed on the "Read-Only" connection fails horribly:

Could not execute statement. Rollback occurred due to deadlock during prefetch SQLCODE=-684, ODBC 3 State="40001" Line 1, column 1 SELECT * FROM dummy

Several points are worth noting here:

• The only true part of the statement "Rollback occurred due to deadlock during prefetch" is the part about the rollback.
  

• There's no "deadlock" involved, it's a straightforward bludgeoning of the secondary database by the update coming from the primary database, and
  

• there's no "prefetch" (it doesn't help to SET TEMPORARY OPTION PREFETCH = 'OFF').
  

• It doesn't matter what the next statement is on the "Read-Only" session; it's the transaction that's getting bludgeoned, and in this case it was a "SELECT * FROM dummy" that got the bad news.
  

• The bludgeoning doesn't happen until the COMMIT is executed on the primary database; until then, the secondary database doesn't see the update.
  

• If you try to catch the SQLCODE -684 in an EXCEPTION handler, you will be unsuccessful; the EXCEPTION handler will be ignored.
  

• If you try to catch the deadlock in a CREATE EVENT TYPE DEADLOCK handler, you will also be unsuccessful; it isn't a real deadlock, at least not a deadlock that will fire an EVENT.
  

Divine Right Of Update is also a feature rather than a bug, as explained in this Help topic: Mirror database queries.

Tip: If you want your read-only queries to behave in a robust manner on the secondary database, run them at isolation level 0, and issue a ROLLBACK after every SELECT to release schema locks as soon as possible. That way, updates and schema changes on the primary database won't cause (as much) grief for your read-only queries on the secondary. Why ROLLBACK? Well, you could do a COMMIT, it doesn't matter, they're read-only queries :)

---