High Availability plus Read-Only Scale-Out

The Read-Only Scale-Out feature was introduced in SQL Anywhere 12. It's a little bit like using the secondary or mirror database in a High Availability setup for read-only processing, with the following differences:

• You can have more than one Read-Only Scale-Out copy database... in fact, you can have a whole tree structure of copy databases.
  

• A copy database will never become the primary database in a High Availability setup, unlike a secondary database being used for read-only connections,
  

• which means a copy database will never suddenly allow updates, not like a secondary database which suddenly becomes the primary.
  

• You can implement copy databases without creating a High Availability setup; i.e., you can have an updatable database and a read-only copy (or multiple copies) without bothering with failover... but that's not what this article is about:
  

This article shows how to add a single Read-Only Scale-Out copy database to the High Availability setup described in High Availability Demo, Revised.

Start With The High Availability Setup

The following command file, together with the SQL script following it, takes care of creating and starting the entire setup described in High Availability Demo, Revised. The code's repeated here so you can run it all-at-once without going step-by-step through the previous article:

Command file: 01_High_Availability_Demo.bat

ECHO OFF ECHO ------------------------------ ECHO Step 1: Prepare the folders and ECHO the database file in server1. PAUSE ECHO Create the HA subfolders... MD server1 MD server2 MD arbiter ECHO 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 ECHO ------------------------------ ECHO Step 2: Start the partner1_demo ECHO server. PAUSE "%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 ECHO ------------------------------ ECHO Step 3: Wait until the partner1_demo ECHO server is REALLY up and running. PAUSE :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 ------------------------------ ECHO Step 4: Create the database objects ECHO to support HA. PAUSE "%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" ECHO ------------------------------ ECHO Step 5: Create the database file ECHO in server2. PAUSE "%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 ECHO ------------------------------ ECHO Step 6: Start the partner2_demo ECHO server. PAUSE "%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 ECHO ------------------------------ ECHO Step 7: Start the arbiter server. PAUSE "%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" ECHO ------------------------------ ECHO Step 8: Connect to the primary ECHO and secondary databases. PAUSE 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" PAUSE All done

SQL script: 04s_script_to_create_supporting_objects.sql

-- 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;

Start The Read-Only Scale-Out Copy Database

Here's the Windows command file for creating, starting and connecting to a new copy database:

REM Create the copy subfolder...

MD copy1

REM Create the supporting objects in the primary database...

"%SQLANY12%\bin32\dbisql.com"^
  -c "SERVER=primary_demo; UID=dba; PWD=sql; HOST=localhost:55501,localhost:55502;"^
  READ ENCODING Cp1252 "10s_script_to_prepare_copy.sql"

REM Copy the primary database into copy1...

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "SERVER=primary_demo; UID=dba; PWD=sql; HOST=localhost:55501,localhost:55502;"^
  -o "copy1\dbbackup_primary_demo_log.txt"^
  copy1

REM Start the database in copy1...

"%SQLANY12%\bin32\dbspawn.exe"^
  -f "%SQLANY12%\bin32\dbsrv12.exe"^
  -n copy1_demo^
  -o "copy1\dbsrv12_copy1_demo_log.txt"^
  -su sql^
  -x tcpip(port=55503;dobroadcast=no)^
  "copy1\demo.db"^
  -xp on

PAUSE Wait until the copy is up and running, then

"%SQLANY12%\bin32\dbisql.com"^
  -c "SERVER=copy1_demo; UID=dba; PWD=sql; HOST=localhost:55503; CON=COPY1_query;" 

The MD command on line 3 above creates a new subfolder for the copy database.

The dbisql command on lines 7 through 9 runs a SQL script to run a CREATE MIRROR SERVER statement on the primary database to tell it (and the secondary database) about the new copy database that's about to be created.

The dbbackup command on lines 13 through 16 creates the copy database file and transaction log in the copy1 subfolder.

The dbsrv12 command on lines 20 through 27 starts the copy database using these options:

-n copy1_demo^ the server name for the copy server -o "copy1\dbsrv12_copy1_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=55503; the port for all connections to the copy database dobroadcast=no)^ to speed up connections between HA servers "copy1\demo.db"^ the first physical database file -xp on enables mirroring and read-only scale-out

The dbisql command on lines 31 and 32 starts an ISQL session connected to the copy database.

Here's the SQL script that is used in the command file above:

CREATE MIRROR SERVER copy1_demo AS COPY
   FROM SERVER primary_demo
   connection_string = 'SERVER=copy1_demo; host=localhost:55503';

-- Disable automatic creation of copy nodes and force usage of 
-- explicit CREATE MIRROR SERVER ... AS COPY statements

SET MIRROR OPTION child_creation = 'off';

The CREATE MIRROR SERVER statement on lines 1 through 3 defines "copy1_demo" as the server name for a physical "COPY" based on the logical "primary_demo" database:

• The server name copy1_demo matches the dbsrv12 -n option that's used to actually start the database,
  

• the keyword COPY identifies this database as a physical COPY database in a read-only scale-out setup, as opposed to a physical PARTNER or a logical MIRROR in an HA setup, and
  

• the FROM SERVER clause associates the copy with the HA setup as a whole (primary_demo) rather than a specific physical partner (partner1_demo or partner2_demo).
  

The SET MIRROR OPTION statement on line 8 disables a sophisticated feature available in a read-only scale-out setup: the ability for the primary database to automatically recognize freshly-created copy databases without the need for explicit CREATE MIRROR SERVER ... AS COPY statements, a feature which is not discussed in this article.

Here's what the three ISQL sessions look like immediately after connecting to the fresh HA plus read-only scale-out setups:



As expected,

• the "Updatable" session is connected to primary_demo which is currently associated with partner1_demo,
  

• the "Read-Only" session is connected to secondary_demo, currently associated with partner2_demo, with DB_PROPERTY ( 'ReadOnly' ) set to 'On', and
  

• the "COPY1_query" session is connected to copy1_demo, with DB_PROPERTY ( 'ReadOnly' ) also set to 'On'.
  

Here's what the three ISQL sessions look like immediately after failover (the partner1_demo database has stopped, and partner2_demo has taken over the role of primary):



After the failover,

• the "Updatable" session has been reconnected to the new primary_demo, which has been "Reconnected to database" partner2_demo,
  

• the "Read-Only" session is still connected to "secondary_demo", still associated with partner2_demo even though that database is now really the primary, and DB_PROPERTY ( 'ReadOnly' ) is now set to 'Off' as warned about in the Help, and
  

• the "COPY1_query" session is still connected to copy1_demo, with DB_PROPERTY ( 'ReadOnly' ) still set to 'On'.
  

As mentioned earlier in this article, that's a significant difference between read-only connection to an HA mirror database and a read-only connection to a read-only scale-out database: the read-only scale-out connection isn't going to suddenly become updatable.

Another difference is that OLAP connections to a read-only scale-out database STAY there, they don't suddenly find themselves sharing the primary database with time-critical OLTP sessions (to be clear, it isn't the OLAP connections to the mirror database that move, it's the OLTP connections that get re-connected to the new primary... but try explaining THAT to your customers :)

---