Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Database Administration » High availability and read-only scale-out systems » SQL Anywhere read-only scale-out

 

Tutorial: Using one server as both a copy node and an arbiter

Set up a server to run as both the arbiter server and as a copy node. This tutorial describes how to set up a mirroring system that is also involved in read-only scale-out.

Prerequisites

You must have MANAGE ANY MIRROR SERVER system privilege.

This task involved backing up a copy of the database. Depending upon the backup method you choose, see the appropriate privileges for that method.

Context and remarks

Although it is recommended that the arbiter server in a mirroring system run on a physically separate computer from the other servers, it is not always feasible.

In this tutorial, all the database servers are running on the same computer. However, each database server must be installed on a separate computer in a production environment.

If this tutorial is used with database servers running on different computers, references to localhost in the connection strings must be changed to the actual computer names.

 Task
  1. Create the following directories: c:\server1, c:\server2, and c:\arbiter.

  2. Create a database named mirror_demo.db that contains data from the sample database and has a transaction log. You cannot start a database that doesn't have a transaction log in mirroring mode. Run the following command:

    newdemo c:\server1\mirror_demo.db
  3. Start the first database server. Run the following command:

    dbsrv16 -n mirror_server1 -x "tcpip(PORT=6871)" -su sql "c:\server1\mirror_demo.db" -xp on
  4. Connect to the mirror_demo database. For example, run the following command:

    dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_server1" 
  5. Create the mirror server definitions. For example, execute the following SQL statements:

    CREATE MIRROR SERVER mirror_demo_primary AS PRIMARY connection_string='SERVER=mirror_demo_primary;HOST=localhost:6871,localhost:6872';
    
    CREATE MIRROR SERVER mirror_demo_mirror AS MIRROR connection_string='SERVER=mirror_demo_primary;HOST=localhost:6871,localhost:6872';
        
    CREATE MIRROR SERVER mirror_server1 AS PARTNER connection_string='SERVER=mirror_server1;HOST=localhost:6871' state_file='c:\server1\\server1.state';
       
    CREATE MIRROR SERVER mirror_server2 AS PARTNER connection_string='SERVER=mirror_server2;HOST=localhost:6872' state_file='c:\server2\\server2.state';
  6. Create the arbiter server definition with the CREATE MIRROR SERVER statement. The mirror-server-name for the arbiter must be different from the server name specified in the connection string.

    CREATE MIRROR SERVER demo_arbiter AS ARBITER
     connection_string='SERVER=demo_server3;HOST=localhost:6870';
  7. Set mirroring options for the mirroring system. You must specify an authentication string and set the auto_add_server option to the name of the primary server. Execute the following statements:

    SET MIRROR OPTION auto_add_server='mirror_demo_primary'; 
    SET MIRROR OPTION authentication_string='abc';

    In the arbiter server definition, the arbiter-server-name must be different from the actual server name for the arbiter. The client connection string for the arbiter must include the ServerName [SERVER] connection parameter with the actual server name.

  8. Create the mirror server.

    1. Make copies of the database file and transaction log in c:\server1, and add them to c:\server2 and c:\arbiter. Run the following command:

      dbbackup -c "UID=DBA;PWD=sql;SERVER=mirror_server1;DBN=mirror_demo" server2
      dbbackup -c "UID=DBA;PWD=sql;SERVER=mirror_server1;DBN=mirror_demo" arbiter
    2. Start the mirror server:

      dbsrv16 -n mirror_server2 -x "tcpip(port=6872)" server2\mirror_demo.db -su sql -xp on
  9. Start the arbiter server as a copy node and as an arbiter by specifying the -xa, -xp, and -xf server options

    dbsrv16 -n demo_server3 -x "tcpip(port=6870)" arbiter\mirror_demo.db -xp on -xf arbiter\arbiter.state -xa "AUTH=abc;DBN=mirror_demo" -su sql
  10. Use the dbping utility to determine when the HA configuration is ready:

     dbping -c "UID=DBA;PWD=sql;SERVER=mirror_demo_primary"  -pd Mirrorstate,PartnerState,ArbiterState
     SQL Anywhere Server Ping Utility Version 16.0.4157 
    Type       Property                  Value 
    ---------  ----------------          ------------------------------ 
    Database   MirrorState               synchronized 
    Database   PartnerState              connected 
    Database   ArbiterState              connected 
    Ping database successful.   
  11. Use the following query to shows the nodes being mirrored in this HA system:

    dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_server1" "SELECT server_name,state FROM sa_mirror_server_status()"
  12. Verify that demo_server3 is a COPY node and that the arbiter is connected by inspecting the contents of the SYSMIRRORSERVER table:

    dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_demo_primary" "SELECT  server_name, server_type FROM SYSMIRRORSERVER" 

Results

The server that runs the arbiter also runs a copy node.