SQL Anywhere® Server - Database Administration  > SQL Anywhere High Availability  > Introduction to database mirroring

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:

   • -x  Instructs the database server to use TCP/IP communications over port 2639. The other servers also use TCP/IP, but communicate on different ports.

   • -su  Specifies the password for the utility database.

   • -n  Names the database server arbiter.

   • -xa  Specifies the names of the database(s) being mirrored and the 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.

   • -xf  Specifies the location of the state information file for the arbiter.

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:

   • -n  Names the database server server1.

   • -x  Specifies the port on which the database server runs.

   • -xf  Specifies the location of the state information file for server1.

   • -su  Specifies the password for the utility database.

   • -sn  Specifies the alternate name for the database server. Both the primary and mirror server must have the same name so clients can connect without knowing in advance which server is the primary server, and which server is the mirror server.

   • -xp  Provides information to the server that is being started so it can connect to its partner and the arbiter server.

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:

   • -n  Names the database server server2.

   • -x  Specifies the port on which the database server runs.

   • -xf  Specifies the location of the state information file for server2.

   • -su  Specifies the password for the utility database.

   • -sn  Specifies the alternate name for the database server. Both the primary and mirror server must have the same name so clients can connect without knowing in advance which server is the primary server, and which server is the mirror server.

   • -xp  Provides information to the server that is being started so it can connect to its partner and the arbiter server.

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:

    • 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"

    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 Server Messages window for server2 displays a message indicating that it is the new primary server:

    

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.