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 » Database mirroring » Tutorial: Creating a database mirroring system

 

Lesson 1: Creating a database mirroring system

Create a mirroring system.

Prérequis

This lesson assumes that you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Creating a database mirroring system with multiple databases that share an arbiter server.

 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
    • -n   Names the database server.

    • -su   Specifies the password for the utility database. It is recommended that you include the -su option to specify the password for the utility database, so that you can connect to the utility database to shut down the database server, if necessary.See Stopping a database server in a mirroring system and Troubleshooting: The primary server cannot be restarted.

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

    • -xp on   Indicates that the database server is available to participate in a database mirroring system.

  4. Connect to the database from Interactive SQL. Run the following command:

    dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_server1"
  5. Define the partner servers and arbiter server for the database by using the CREATE MIRROR SERVER statement.

    Execute the following statements to define mirror_server1 and mirror_server2 as partner servers in the database mirroring system:

    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';

    Execute the following statements to define

    • mirror_demo_primary as the alternate server name for mirror_server1. mirror_demo_primary is the name that clients use to connect to the database server that is acting as the primary server.

    • mirror_demo_mirror as the alternate server name for mirror_server2. mirror_demo_mirror is the name that clients use to connect to the database server that acts as the mirror server.

    The roles of primary and mirror are necessary for configuring the database servers in the system: the names that you give these servers are used as alternate server names when clients connect to the database servers. Either partner server can act as the primary or mirror server.

    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_mirror;HOST=localhost:6871,localhost:6872';

    Execute following SQL statement to define the arbiter server for the database mirroring system:

    CREATE MIRROR SERVER demo_arbiter
    AS ARBITER
    connection_string ='SERVER=demo_arbiter;HOST=localhost:6870';
  6. Set the authentication string for the database. Execute the following statement:

    SET MIRROR OPTION authentication_string='abc';
  7. Use the Backup utility (dbbackup) to copy the database file and transaction log in c:\server1 to c:\server2. Run the following command:

    dbbackup -c "UID=DBA;PWD=sql;SERVER=mirror_server1;DBN=mirror_demo" c:\server2
  8. Start the second partner server. Run the following command:

    dbsrv16 -n mirror_server2 -x "tcpip(PORT=6872;DOBROAD=no)" -su sql "c:\server2\mirror_demo.db" -xp on
  9. Start the arbiter server. Run the following command:

    dbsrv16 -n demo_arbiter -su sql -x "tcpip(PORT=6870;DOBROAD=no)" -xf "c:\arbiter\arbiter.state" -xa "AUTH=abc;DBN=mirror_demo"
    • -xf   Specifies the location of the state information file for the 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.

  10. Start Interactive SQL and connect to the primary server using the alternate server name for the primary server. Run following command:

    dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_demo_primary;HOST=localhost:6871,localhost:6872"
  11. Add data to the database. Execute the following statements:

    CREATE TABLE test (col1 INTEGER, col2 CHAR(32));
    INSERT INTO test VALUES(1, 'Hello from server1');
    COMMIT;
  12. (Optional) To determine the name of database server that you are connected to you, use the ServerName database server property. Execute the following statement:

    SELECT PROPERTY( 'ServerName' );
  13. Close all Interactive SQL windows.

Résultat

The database mirroring system is running.

 See also