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

SQL Anywhere 10.0.1 » SQL Anywhere Server - Database Administration » SQL Anywhere High Availability » Introduction to database mirroring

State information files Next Page

Tutorial: Using database mirroring


This tutorial shows you how to set up a database mirroring system and what happens when failover occurs. For the purposes of this tutorial, all of the database servers are running on the same computer. However in a real mirroring system, you would likely run the database servers on separate computers.

To simulate failover in a database mirroring system
  1. Create the following directories: c:\server1, c:\server2, and c:\arbiter.

  2. Make a copy the sample database located in samples-dir\demo.db, and add it to c:\server1.

    For information about samples-dir, see Samples directory.

  3. Create a transaction log for the database located in c:\server1 by executing the following command:

    dbping -d -c UID=DBA;PWD=sql;DBF=c:\server1\demo.db
  4. Make copies of the database file and transaction log in c:\server1, and add them to c:\server2.

  5. Start the arbiter server by executing the following command at a command prompt:

    dbsrv10 -x tcpip(PORT=2639) -su sql -n arbiter -xa auth=abc;DBN=demo -xf c:\arbiter\arbiterstate.txt

    This command line specifies the following dbsrv10 options:

  6. Start server1 by executing the following command (entered all on one line) at a command prompt:

    dbsrv10 -n server1 -x tcpip(PORT=2638) -xf c:\server1\server1state.txt -su sql 
    c:\server1\demo.db -sn mirrordemo 
    -xp partner=(ENG=server2;LINKS=tcpip(PORT=2637;TIMEOUT=1));auth=abc;
    arbiter=(ENG=arbiter;LINKS=tcpip(PORT=2639;TIMEOUT=1));mode=sync

    This command line specifies the following dbsrv10 options:

  7. Start server2 by executing the following command (entered all on one line) at a command prompt:

    dbsrv10 -n server2 -x tcpip(PORT=2637) -xf c:\server2\server2state.txt -su sql 
    c:\server2\demo.db -sn mirrordemo 
    -xp partner=(ENG=server1;LINKS=tcpip(PORT=2638;TIMEOUT=1));auth=abc;
    arbiter=(ENG=arbiter;LINKS=tcpip(PORT=2639;TIMEOUT=1));mode=sync

    This command line specifies the following dbsrv10 options:

  8. Start Interactive SQL and connect to the primary server by running the following command:

    dbisql -c "UID=DBA;PWD=sql;ENG=mirrordemo;LINKS=tcpip"
  9. 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;
  10. Determine which database server you are connected to by executing the following statement:

    SELECT PROPERTY( 'ServerName' );

    The name of the primary server appears.

  11. Initiate failover. You can do this by stopping the primary server identified in Step 10 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 Server Messages window displays a message indicating that the primary server is disconnected.

    The arbiter Server Messages window showing that server1 is disconnected.

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

    server2 Server Messages window.
  12. Close Interactive SQL. Click OK if you receive an error message.

  13. Restart Interactive SQL by running the following command:

    dbisql -c "UID=DBA;PWD=sql;ENG=mirrordemo;LINKS=tcpip"
  14. Execute the following statement to see that you are now connected to the mirror server:

    SELECT PROPERTY ( 'ServerName' );
  15. Execute the following statement to verify that all transactions were mirrored to the mirror database:

    SELECT * FROM test;
  16. Disconnect from Interactive SQL, and then click Shut Down on the Server Messages window for the arbiter, server1, and server2 database servers.