Setting up a database mirroring system

Set up a database mirroring system by defining primary, mirror, and arbiter servers to maintain copies of the database and transaction log files.

Prerequisites

You must have MANAGE ANY MIRROR SERVER system privilege.

This task involves backing up a copy of the database. Depending upon the backup method you choose, see the appropriate privileges for that method.

Context and remarks

In a production mirroring system, the partners and arbiter servers must run on three separate computers. When setting up a database mirroring system, in all the examples below localhost and the port number must be changed to the computer name and port where the corresponding database server must run.

Task

Start the database to be mirrored on a database server with the -su and -xp on options. This server becomes one of the partners for the database, the initial primary server. The database must have a transaction log. For example:

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

Connect to the database. For example:

dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_server1"

Define the partner servers and arbiter server for the database by using the CREATE MIRROR SERVER statement. Also define the primary and mirror servers as partners in the database mirroring system.

For example:

The following two SQL statements define mirror_server1 and mirror_server2 as the 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';

The following SQL statements 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.

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

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

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

Set mirroring options for the mirroring system. You must specify an authentication string. For example:
SET MIRROR OPTION authentication_string='abc';
Create a copy of the primary database and the current transaction log, as well as any other transaction logs, onto the computer where the second partner is. For example, use the BACKUP DATABASE statement.

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.

Start the second database server in the database mirroring system:

dbsrv16 -n mirror_server2 -x tcpip(PORT=6872;DOBROAD=no) -su sql "c:\server2\mirror_demo.db" -xp on

Start the arbiter server:

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

Results

Clients can now connect to the mirrored database.

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.

Next

Monitor your database mirroring system by adding your system to the Monitor's resource list.

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.

Setting up a database mirroring system

Task

See also