Database mirroring system setup

When starting database servers that will be participating in a mirroring system, you must specify -xp on. It is recommended that you also 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.

For information about upgrading SQL Anywhere or rebuilding a database involved in a database mirroring system, see Upgrading SQL Anywhere software and databases in a database mirroring system.

Note

When setting up a database mirroring system, in all the examples below, localhost and the port number need to be changed to the computer name and port where the corresponding database server will be running.

 Set up a mirroring system
  1. Start the database that will be mirrored on a database server with the -su and -xp on options. For example:

    dbsrv12 -n mirror_server1 -x tcpip(PORT=6871;DOBROAD=no) -su sql 
    "c:\server1\mymirrordb.db" -xp on

    See -su dbeng12/dbsrv12 server option and -xp dbsrv12 database option.

  2. Define the partner servers and arbiter server for the database by using the CREATE MIRROR SERVER statement. You must define one database server as the primary server and one database server as the mirror server, as well as an arbiter server. You must also define the primary and mirror servers as partners in the database mirroring system.

    The first statement defines the name that clients use to connect to the database server that is acting as the primary server in the database mirroring system. The second statement defines mirror_server1 as a partner server in the database mirroring system.

    CREATE MIRROR SERVER myprimary
    AS PRIMARY
    connection_string='SERVER=myprimary;HOST=localhost:6871,localhost:6872';
    
    CREATE MIRROR SERVER mirror_server1
    AS PARTNER
    connection_string='SERVER=mirror_server1;host=localhost:6871'
    state_file='c:\\server1\\server1.state';

    The following SQL statements define the name of the database server that is acting as the mirror server in the database server, and also define the second partner server in the database mirroring system:

    CREATE MIRROR SERVER mymirror
    AS MIRROR
    connection_string='SERVER=mymirror;HOST=localhost:6871,localhost:6872';
    
    CREATE MIRROR SERVER mirror_server2
    AS PARTNER
    connection_string='SERVER=mirror_server2;host=localhost:6872'
    state_file='c:\\server2\\server2.state';
    Note

    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.

    The following SQL statement defines the arbiter server for the database mirroring system:

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

    See CREATE MIRROR SERVER statement.

  3. Set mirroring options for the mirroring system. You must specify an authentication string. For example:

    SET MIRROR OPTION authentication_string='abc';

    See SET MIRROR OPTION statement.

  4. Make a copy of the database, current transaction log, as well as any other transaction logs on the computer where the second database server will be running.

    The transaction log files on the primary server computer and the mirror server computer must be identical, including the starting offset of the current transaction log files.

    If you stop the database server running the database you want to mirror, you can copy files; otherwise, use the BACKUP DATABASE statement or the Backup utility (dbbackup). See BACKUP statement and Backup utility (dbbackup).

  5. Start the second database server in the database mirroring system:

    dbsrv12 -n mirror_server2 -x tcpip(PORT=6872;DOBROAD=no) -su sql 
    "c:\server2\mymirrordb.db" -xp on
  6. Start the arbiter server.

    dbsrv12 -n myarbiter -su sql  
    -x "TCPIP(PORT=6870;DOBROAD=no)" -xf "c:\arbiter\arbiter.state" 
    -xa "AUTH=abc;DBN=mymirrordb"

    Clients can now connect to the mirrored database.

    Tip

    You can monitor your database mirroring system by adding your system to the SQL Anywhere Monitor's resource list. See Lesson 3: Monitoring a database mirroring system.

    You can also check the status of the database servers in a database mirroring system by connecting to the primary database from Sybase Central. Database mirroring information is available on the Health and Statistics pane. See Database health and statistics.

 Configuring the connection strings between mirror servers
 See also
 Connecting to a mirrored database server

Initial primary server
Preferred database server
Configuring read-only access to a database running on the mirror server
Forcing a database server to become the primary server
Using a copy node as an arbiter
Failover on the primary server
Stopping a database server in a mirroring system
Mirror server dropping
Primary server failure
Database mirroring and MobiLink
Database mirroring and transaction log files
Database mirroring system events
Database mirroring and performance
Database mirroring and backups
Database mirroring scenarios
Database mirroring dropped connection scenarios