Switch to standard view 
  Sybase logo
 
 
 



Database Mirroring

This sample demonstrates how to set up and use database mirroring. You use the demo database to demonstrate how to set up and run the primary, mirror, and arbiter servers, and see how failover occurs.

For more information about database mirroring, refer to the online documentation: SQL Anywhere Database Administration Guide | High Availability | Understanding database mirroring.

Requirements

You can run this sample under Windows or Linux.

Walkthrough

  1. Shut down any SQL Anywhere databases that are running on your computer.
     
  2. Create a directory called "HA10sample" to store the files you will create in this sample. Change of this directory.
     
  3. Copy the sample database demo.db from your SQL Anywhere 10 installation directory to the HA10sample directory.
     
  4. Start and stop the demo database so that a log file is created. Run the following commands:
     
    dbeng10 demo.db
    dbstop -y -c "uid=dba;pwd=sql;eng=demo"

     
  5. Create a directory called "mirror" to store the mirror database. Copy the files demo.db and demo.log to the mirror directory.
     
  6. Start the arbiter  server by running the following command:
     
    dbsrv10 -x tcpip{port=2639} -n arbiter -xa auth=abc;dbn=demo -xf .\mirror\arbiterstate.txt
     
    Notice the new options -xa and -xf. The -xa option is used to specify a list of database names (in this case 'demo') and authentication string (in this case 'abc') for the arbiter server. This authentication string must be used amongst all the servers (arbiter, primary, and mirror) in a database mirroring system.
     
    The -xf option specifies the location of the file used for maintaining state information about the database mirroring system.
     
    The -x tcpip{port=2639} option instructs the server to use TCP/IP communications over port 2639. The other servers will also use TCP/IP, but will be communicating on different ports.
     
    Note: you can start the servers in any order. You do not have to start with the arbiter.
     
  7. Start the primary server by running the following command:
     
    dbsrv10 -n server1 -x tcpip{port=2638;dobroadcast=no} -xf .\mirror\server1state.txt demo.db -sn mirrordemo -xp partner={eng=server2;links=tcpip{port=2637;timeout=1}};mode=sync;auth=abc;arbiter={eng=arbiter;links=tcpip{port=2639;timeout=1}}
     
    Notice the new options -sn and -xp. The -sn option is used to provide an alternate server name, which must be specified when using database mirroring. This is done so that client applications can connect to the current primary server without knowing in advance which server is the primary server and which is the mirror server. Both primary and mirror servers must use the same alternate server name.
     
    The -xp option provides information to an operational (primary or mirror) server so that it is able to connect to its partner and to the arbiter server. In this example, the partner is a database server called 'server2', and it communicates via TCP/IP on port 2637, and the arbiter is a database server called 'arbiter', communicating via TCP/IP on port 2639. A timeout value of 1 is used to reduce failover time. The synchronization mode used for database mirroring is synchronous (sync), but could also be asynchronous (async), or asyncfullpage (page). Finally, notice that the authentication string 'abc' is used as explained in Step 6.
     
    Note: The primary and mirror servers are started using the option "dobroadcast=no". This is done so that the servers start up faster in a testing environment. In production environments, the use of this parameter is not generally recommended.
     
  8. Start the mirror server by running the following command:
     
    dbsrv10 -n server2 -x tcpip{port=2637;dobroadcast=no} -xf .\mirror\server2state.txt .\mirror\demo.db -sn mirrordemo -xp partner={eng=server1;links=tcpip{port=2638;timeout=1}};mode=sync;auth=abc;arbiter={eng=arbiter;links=tcpip{port=2639;timeout=1}}
     
    Notice the -sn and -xp options: they include information about the arbiter server and the partner (primary) server.
     
    Once all the servers have started, you can examine the messages in the various server windows, as well as the contents of the state files (\Mirror\*.txt) to determine which server is the primary and which is the backup.
     
    Note: Under Windows, you open the Server Messages window by double-clicking the SQL Anywhere server icon in the system tray.
     
     
     
     
  9. Start Interactive SQL and connect to the primary server by running the following command:
     
    dbisql -c "uid=dba;pwd=sql;eng=mirrordemo;links=tcpip"
     
  10. Execute (F5) the following statement to determine which server (server1 or server2) you are connected to:
     
    SELECT PROPERTY('ServerName') FROM DUMMY;
     
    The name of the primary server appears in the result set.
     
     
  11. Add sample data to the demo database by executing the following statements:
     
    CREATE TABLE test (col1 INTEGER, col2 CHAR(32));
    INSERT INTO test VALUES(1, 'Hello from server1');
    COMMIT;
     
  12. Initiate failover. You can do this by stopping the primary server identified in Step 7 in one of the following ways:
    • Click Shut down in the Server Messages window
    • Use the Windows Task Manager to end its task
    • Issue the following command: dbstop -y -c "uid=dba;pwd=sql;eng=mirrordemo"
  13. If a warning message appears indicating that the server still has one connection, choose to shut down anyway (click Yes).
     
    Notice that the arbiter server window displays a message informing you that server1 is disconnected.
     

  14. The Interactive SQL session is disconnected. Shut down Interactive SQL and restart it by running the following command:
     
    dbisql -c "uid=dba;pwd=sql;eng=mirrordemo;links=tcpip"
      
  15. Execute the following statement to see that you are now connected to the mirror server:
     
    SELECT PROPERTY('ServerName') FROM DUMMY;
     
     
  16. Execute the following statement to verify that all transactions had been mirrored to the mirror database:
     
    SELECT * FROM test;
     
     
  17. Add more sample data to the demo database by executing the following statements:
     
    INSERT INTO test VALUES(2, 'Hello from server2');
    COMMIT;
     
  18. Start the server that was shut down in Step 12 by running the following command:
     
    dbsrv10 -n server1 -x tcpip{port=2638;dobroadcast=no} -xf .\mirror\server1state.txt demo.db -sn mirrordemo -xp partner={eng=server2;links=tcpip{port=2637;timeout=1}};mode=sync;auth=abc;arbiter={eng=arbiter;links=tcpip{port=2639;timeout=1}}
     
  19. Now initiate failover again by shutting down the primary server (repeat Step 12).
     
  20. The Interactive SQL session is disconnected. Shut down Interactive SQL and restart it by running the following command:
     
    dbisql -c "uid=dba;pwd=sql;eng=mirrordemo;links=tcpip"
     
  21. Execute the following statement to verify that all transactions were committed:
     
    SELECT * FROM test;
     
     
  22. This concludes the demonstration. Shut down Interactive SQL.
     
  23. Shut down all running servers in one of the following ways:
    • Click Shut down in each Server Messages window
    • Issue the following commands:
       
      dbstop -y -c "uid=dba;pwd=sql;eng=mirrordemo"
      dbstop -y -c "dbn=utility_db;uid=dba;pwd=sql;eng=arbiter"
       
      Make sure that the password (pwd=sql) matches the one provided in the file util_db.ini (found in the same directory where dbsrv10 is located).

Clean-up

Remove the temporary directory HA10sample (created in Step 2) and all its contents.



Back to Top
© Copyright 2010, Sybase Inc.