Tutorial: Using database mirroring

This tutorial provides instructions for setting up database mirroring and how to respond to a failover. For this tutorial, all the database servers are running on the same computer. However, each database server is typically installed on a separate computer in a production environment.

If this tutorial is used with database servers running on different computers, references to localhost in the connection strings must be changed to the actual computer names.

To simulate failover in a database mirroring system

Create the following directories: c:\server1, c:\server2, and c:\arbiter.
Run the following command to create a database named mirror_demo.db that contains data from the sample database:
newdemo c:\server1\mirror_demo.db

Run the following command to start the first database server:

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

-n Names the database server mirror_server1.
-x Specifies the port on which the database server runs.
-su Specifies the password for the utility database.
-xp on Indicates that the database server is available to participate in a database mirroring system.

Connect to the database from Interactive SQL:

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

Run the following statements to define the primary and mirror servers:

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

Run the following statements to define the mirror_server1 and mirror_server2 as partners in the 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';

Run the following statement to define the arbiter server:

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

Run the following statement to set the authentication string for the database:
SET MIRROR OPTION authentication_string='abc';
Disconnect from Interactive SQL.

Make copies of the database file and transaction log in c:\server1, and add them to c:\server2 by running the following command:

dbbackup -c "UID=DBA;PWD=sql;SERVER=mirror_server1;DBN=mirror_demo" c:\server2

Run the following command to start the second database server:

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

This command line specifies the following dbsrv12 options:

-n Names the database server mirror_server2.
-x Specifies the port on which the database server runs.
-su Specifies the password for the utility database.
-xp on Indicates that the database server is available to participate in a database mirroring system.

Run the following command to start the arbiter database server:

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

-n Names the database server demo_arbiter.
-su Specifies the password for the utility database.
-x Instructs the database server to use TCP/IP communications over port 6870. The other servers also use TCP/IP, but communicate on different ports.
-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.

Start Interactive SQL and connect to the primary server by running the following command:

dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_demo_primary;HOST=localhost:6871,localhost:6872"

Add data to the database by executing the following statements:

CREATE TABLE test (col1 INTEGER, col2 CHAR(32));
INSERT INTO test VALUES(1, 'Hello from server1');
COMMIT;

Determine which database server you are connected to by executing the following statement:
SELECT PROPERTY( 'ServerName' );
The name of the primary server appears.

Initiate failover. You can do this by stopping the primary server identified in the previous step in one of the following ways:

Click Shut Down in the database server messages window.
Use the Windows Task Manager to end its task.

Issue the following command:

dbstop -y -c "UID=DBA;PWD=sql;SERVER=mirror_demo_primary"

If a warning message appears indicating that the database server still has one connection, click Yes to shut it down.

The arbiter database server messages window displays a message indicating that the primary server is disconnected.

The arbiter database server messages window showing that server1 is disconnected.

The database server messages window for mirror_server2 displays a message indicating that it is the new primary server:

server2 database server messages window.

Close Interactive SQL. If you receive an error message, click OK.

Restart Interactive SQL by running the following command:

dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_demo_primary;HOST=localhost:6871,localhost:6872"

Execute the following statement to see that you are now connected to the server that was previously acting as the mirror server:
SELECT PROPERTY ( 'ServerName' );
Execute the following statement to verify that all transactions were mirrored to the mirror database:
SELECT * FROM test;
Disconnect from Interactive SQL, and then click Shut Down on the database server messages window for the arbiter and server2 database servers.
(optional) Delete the c:\server1, c:\server2, and c:\arbiter directories.

Discuss this page in DocCommentXchange.