Tutorial: Using database mirroring with multiple databases sharing an arbiter server

Note

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.

There is a sample in %SQLANYSAMP12%\SQLAnywhere\DBMirror that uses database mirroring with a scale-out system. The sample can be run on one computer, or on multiple computers.

In this configuration the primary and mirror servers each host three individual databases participating in mirroring systems. All three mirroring systems communicate with the same arbiter server. Each mirroring system uses unique alternate server names that are specified using the PRIMARY and MIRROR clauses of the CREATE MIRROR SERVER statement. With this type of configuration, the primary, mirror, and arbiter servers can all run on separate computers.

Diagram showing the mirroring system.

If the primary server becomes unavailable, then a role switch occurs and the mirror server takes ownership of the databases. The mirror server becomes the primary server. The client must re-establish a connection to the primary server. The alternate server name is all that needs to be specified to re-establish the connection to the primary server. This configuration also has the ability to protect against failure of a single database. If a database running on the primary server becomes unavailable, then a role switch occurs and the mirror server takes ownership of the failed database. The mirror server becomes the primary server for only this database. The client must re-establish a connection to the primary server for this database using the alternate server name.

 Set up a mirroring system with three databases and one arbiter server

  1. Create the following directories:

    • c:\server1
    • c:\server2
    • c:\arbiter

  2. Run the following command:

    newdemo c:\server1\one.db

  3. Run the following command:

    newdemo c:\server1\two.db

  4. Run the following command from the c:\server1 directory: 

    newdemo c:\server1\three.db

  5. Start the database server named server1:

    dbsrv12 -n server1 -x tcpip(PORT=6871) -su sql c:\server1\one.db -xp on c:\server1\two.db -xp on c:\server1\three.db -xp on

    It is recommended that you include the -su option to specify the password for the utility database. Then, you can use the utility database to shut down the database server, or force the mirror server to become the primary server if necessary. See Stopping a database server in a mirroring system and Forcing a database server to become the primary server.

  6. Connect to database one from Interactive SQL and define the required mirroring objects:

    dbisql -c "UID=DBA;PWD=sql;SERVER=server1;DBN=one"

    1. Define server1 as a partner server for database one:

      CREATE MIRROR SERVER server1
AS PARTNER
connection_string='SERVER=server1;host=localhost:6871'
state_file='c:\\server1\\server1state.txt';

    2. Define the database server primary_one as the logical primary server for database one:

      CREATE MIRROR SERVER primary_one
AS PRIMARY
connection_string='SERVER=primary_one;host=localhost:6871,localhost:6872';

    3. Define server2 as a partner server for database one:

      CREATE MIRROR SERVER server2
AS PARTNER
connection_string='SERVER=server2;host=localhost:6872'
state_file='c:\\server2\\server2state.txt';

    4. Define the database server mirror_one as the logical mirror server for database one:

      CREATE MIRROR SERVER mirror_one
AS MIRROR
connection_string='SERVER=mirror_one;host=localhost:6871,localhost:6872';

    5. Define the arbiter server:

      CREATE MIRROR SERVER arbiter
AS ARBITER
connection_string='SERVER=arbiter;HOST=localhost:6870';

    6. Set the mirroring options for the database mirroring system:

      SET MIRROR OPTION authentication_string='abc';

    7. Disconnect from Interactive SQL.

    8. Make a backup copy of the database in the c:\server2 directory: 

      dbbackup -c "UID=DBA;PWD=sql;SERVER=server1;DBN=one" c:\server2

  7. Connect to database two from Interactive SQL and define the required mirroring objects:

    dbisql -c "UID=DBA;PWD=sql;SERVER=server1;DBN=two"

    1. Define server1 as a partner server for database two:

      CREATE MIRROR SERVER server1
AS PARTNER
connection_string='SERVER=server1;host=localhost:6871'
state_file='c:\\server1\\server1state.txt';

    2. Define the database server primary_two as the logical primary server for database two:

      CREATE MIRROR SERVER primary_two
AS PRIMARY
connection_string='SERVER=primary_two;host=localhost:6871,localhost:6872';

    3. Define server2 as a partner server for database two:

      CREATE MIRROR SERVER server2
AS PARTNER
connection_string='SERVER=server2;host=localhost:6872'
state_file='c:\\server2\\server2state.txt';

    4. Define the database server mirror_two as the logical mirror server for database two:

      CREATE MIRROR SERVER mirror_two
AS MIRROR
connection_string='SERVER=mirror_two;host=localhost:6871,localhost:6872';

    5. Define the arbiter server:

      CREATE MIRROR SERVER arbiter
AS ARBITER
connection_string='SERVER=arbiter;HOST=localhost:6870';

    6. Set the mirroring options for the database mirroring system:

      SET MIRROR OPTION authentication_string='def';

    7. Disconnect from Interactive SQL.

    8. Make a backup copy of the database in the c:\server2 directory: 

      dbbackup -c "UID=DBA;PWD=sql;SERVER=server1;DBN=two" c:\server2

  8. Connect to database three from Interactive SQL and define the required mirroring objects:

    dbisql -c "UID=DBA;PWD=sql;SERVER=server1;DBN=three"

    1. Define server1 as a partner server for database three:

      CREATE MIRROR SERVER server1
AS PARTNER
connection_string='SERVER=server1;host=localhost:6871'
state_file='c:\\server1\\server1state.txt';

    2. Define the database server primary_three as the logical primary server for database three:

      CREATE MIRROR SERVER primary_three
AS PRIMARY
connection_string='SERVER=primary_three;host=localhost:6871,localhost:6872';

    3. Define server2 as a partner server for database three:

      CREATE MIRROR SERVER server2
AS PARTNER
connection_string='SERVER=server2;host=localhost:6872'
state_file='c:\\server2\\server2state.txt';

    4. Define the database server mirror_three as the logical mirror server for database three:

      CREATE MIRROR SERVER mirror_three
AS MIRROR
connection_string='SERVER=mirror_three;host=localhost:6871,localhost:6872';

    5. Define the arbiter server:

      CREATE MIRROR SERVER arbiter
AS ARBITER
connection_string='SERVER=arbiter;HOST=localhost:6870';

    6. Set the mirroring options for the database mirroring system:

      SET MIRROR OPTION authentication_string='ghi';

    7. Disconnect from Interactive SQL.

    8. Make a backup copy of the database in the c:\server2 directory: 

      dbbackup -c "UID=DBA;PWD=sql;SERVER=server1;DBN=three" c:\server2

  9. Start the database server named server2:

    dbsrv12 -n server2 -x tcpip(PORT=6872) -su sql c:\server2\one.db -xp on c:\server2\two.db -xp on c:\server2\three.db -xp on

  10. Start the arbiter server.

    dbsrv12 -n arbiter -su sql -x tcpip(port=6870) -xf c:\arbiter\arbiterstate.txt -xa "AUTH=abc,def,ghi;DBN=one,two,three"

    After starting server2, the server1 database server messages window shows that server1 is the primary server in the mirroring system for databases one, two, and three. The messages also indicate that the mirror databases for one, two, and three (partners) are connected to server1.

    The arbiter messages show that both server1 and server2 are connected.

  11. Run the following command to start Interactive SQL and connect to database one on the primary server:

    dbisql -c "UID=DBA;PWD=sql;Server=primary_one;LINKS=TCPIP"

  12. Add sample data to the SQL Anywhere sample database by executing the following statements:

    CREATE TABLE test (col1 INTEGER, col2 CHAR(32));
INSERT INTO test VALUES(1, 'Hello from server1');
COMMIT;

  13. Determine which database server you are connected to by executing the following statement:

    SELECT PROPERTY( 'ServerName' );

    The name of the primary server appears.

  14. Disconnect from Interactive SQL.

  15. Initiate failover. You can do this by stopping the primary server in one of the following ways:

    If a warning message appears indicating that the database server still has one connection, click Yes to shut it down.

    The arbiter database server messages window displays a message indicating that the primary server is disconnected.

    The arbiter database server messages window showing that server1 is disconnected.

    The database server messages window for server2 displays a message indicating that it is the new primary server:

    server2 database server messages window.

  16. Restart Interactive SQL by running the following command:

    dbisql -c "UID=DBA;PWD=sql;Server=primary_one;LINKS=tcpip"

  17. Execute the following statement to see that you are now connected to server2:

    SELECT PROPERTY ( 'ServerName' );

  18. Execute the following statement to verify that all transactions were mirrored to the mirror database:

    SELECT * FROM test;

  19. Disconnect from Interactive SQL, and then click Shut Down on the database server messages window for the arbiter and server2 database servers.

  20. (optional) Delete the c:\server1, c:\server2, and c:\arbiter directories.

Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1