Wednesday, October 30, 2013

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 :)


7 comments:

Justin Willey said...

Many thanks for this Breck - it's a very useful run through.

Maybe SAP should do a YouTube demo with anvil dropping on to the live server like Novell's famous SFT demo - more impressive than 3 servers running on one machine!

HTalstra said...

I am missing the next things to this great article.
- The commandline program to stop the Primary so the secundary becomes in charge.
- The restart of primary server via commandline

What would be the connectionstring in a C# .NET program to connect to this Fail Over server fun?

Regards,

Herman

Breck Carter said...

@HTalstra: To stop the primary, just stop it... kick the power cord out of the wall is one way, dbstop.exe is another, Task Manager - End Process is yet another.

To restart the server that used to be the primary, and will now become the secondary, use exactly the same command line that was used to start it in the first place.

The C# connection string should look pretty much just like the ones shown in the article, except using C# syntax. The only difference with HA is the two HOST addresses: HOST=localhost:55501,localhost:55502;

HTalstra said...

Hi Breck,

I have the idea this nice demo works great but I have some flaw or something.

My secondary machine shows:
PROPERTY ( 'MachineName' ) = WRK2038
PROPERTY ( 'Name' ) = TDMS_Secundary
PROPERTY ( 'ServerName' ) = TDMS_Secundary
PROPERTY ( 'TcpIpAddresses' ) = 172.16.123.108:55502
DB_PROPERTY ( 'Name' ) = TdmsCentral
DB_PROPERTY ( 'ReadOnly' ) = On
DB_PROPERTY ( 'ArbiterState' ) =
DB_PROPERTY ( 'PartnerState' ) =
DB_PROPERTY ( 'MirrorState' ) = synchronizing
DB_PROPERTY ( 'File' ) = D:\FailOver\Secundary\TdmsCentral.db
CONNECTION_PROPERTY ( 'Number' ) = 95
CONNECTION_PROPERTY ( 'Name' ) = Read-Only
CONNECTION_PROPERTY ( 'CommLink' ) = local
CONNECTION_PROPERTY ( 'CommNetworkLink' ) = TCPIP
Execution time: 0.01 seconds
Execution time: 0 seconds

I do not get an arbiter and partnerstate. But I have to connect to my physical mirror. I cannot see my Logical second server. For the primary part I can connect to both. Why I cannot see my secondaru logical server?

When 'shutting down' he primary, he secondary is still working readonly!
My secondary server does not get a .state file.

What have I setup wrong?

Regards,

Herman
ps: I have changed you code to use ASE 16

HTalstra said...

if I check
SELECT DB_PROPERTY( 'MirrorRole' )
The primary says primary, the mirror says COPY in stead of mirror.

Bruno Vianco said...
This comment has been removed by the author.
Bruno Vianco said...

Hi Breck, as allways, your blog is awesome. I've legacy applications running SQLANY11, and I've used your previous post to implement HA with those. Now I want to do the same, but with legacy applications running SQLANY12.

With SQLANY11 I've used to ran a single database per dbsrv11 instance, but with SQLANY12 I understand that is better to run all databases in one instance, so, the question is: ¿is posible enable HA capabilities (-xp) adding databases on the fly with START DATABASE?.

The idea is start the server without any database attached and add them as needed with START DATABASE and then configure HA, but I don't now if it is the best way to go.

Thanks for sharing your knowdledge.