Lesson 1: Creating a database mirroring system with three databases and one arbiter server

Create a database mirroring system with three mirrored databases that all use the same arbiter server.

Prérequis

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

Task

Create the following directories:
- c:\server1
- c:\server2
- c:\arbiter
Create copies of the SQL Anywhere 16 sample database (demo.db) using newdemo.bat. Run the following command:
newdemo.bat c:\server1\one.db
Run the following command:
newdemo.bat c:\server1\two.db
Run the following command from the c:\server1 directory:
newdemo.bat c:\server1\three.db

Start the database server named server1:

dbsrv16 -n server1 -x tcpip(PORT=6871) -su sql c:\server1\one.db -xp on c:\server1\two.db -xp on c:\server1\three.db -xp on

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.

Connect to database one from Interactive SQL and define the required mirroring objects:

dbisql -c "UID=DBA;PWD=sql;SERVER=server1;DBN=one"

Define server1 as a partner server for database one:

CREATE MIRROR SERVER server1
AS PARTNER
connection_string='SERVER=server1;host=localhost:6871'
state_file='c:\\server1\\server1state.txt';

Define the database server primary_one as the logical primary server for database one:

CREATE MIRROR SERVER primary_one
AS PRIMARY
connection_string='SERVER=primary_one;host=localhost:6871,localhost:6872';

Define server2 as a partner server for database one:

CREATE MIRROR SERVER server2
AS PARTNER
connection_string='SERVER=server2;host=localhost:6872'
state_file='c:\\server2\\server2state.txt';

Define the database server mirror_one as the logical mirror server for database one:

CREATE MIRROR SERVER mirror_one
AS MIRROR
connection_string='SERVER=mirror_one;host=localhost:6871,localhost:6872';

Define the arbiter server:

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

Set the mirroring options for the database mirroring system:

SET MIRROR OPTION authentication_string='abc';

Disconnect from Interactive SQL.

Make a backup copy of the database in the c:\server2 directory:

dbbackup -c "UID=DBA;PWD=sql;SERVER=server1;DBN=one" c:\server2

Connect to database two from Interactive SQL and define the required mirroring objects:

dbisql -c "UID=DBA;PWD=sql;SERVER=server1;DBN=two"

Define server1 as a partner server for database two:

CREATE MIRROR SERVER server1
AS PARTNER
connection_string='SERVER=server1;host=localhost:6871'
state_file='c:\\server1\\server1state.txt';

Define the database server primary_two as the logical primary server for database two:

CREATE MIRROR SERVER primary_two
AS PRIMARY
connection_string='SERVER=primary_two;host=localhost:6871,localhost:6872';

Define server2 as a partner server for database two:

CREATE MIRROR SERVER server2
AS PARTNER
connection_string='SERVER=server2;host=localhost:6872'
state_file='c:\\server2\\server2state.txt';

Define the database server mirror_two as the logical mirror server for database two:

CREATE MIRROR SERVER mirror_two
AS MIRROR
connection_string='SERVER=mirror_two;host=localhost:6871,localhost:6872';

Define the arbiter server:

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

Set the mirroring options for the database mirroring system:

SET MIRROR OPTION authentication_string='def';

Disconnect from Interactive SQL.

Make a backup copy of the database in the c:\server2 directory:

dbbackup -c "UID=DBA;PWD=sql;SERVER=server1;DBN=two" c:\server2

Connect to database three from Interactive SQL and define the required mirroring objects:

dbisql -c "UID=DBA;PWD=sql;SERVER=server1;DBN=three"

Define server1 as a partner server for database three:

CREATE MIRROR SERVER server1
AS PARTNER
connection_string='SERVER=server1;host=localhost:6871'
state_file='c:\\server1\\server1state.txt';

Define the database server primary_three as the logical primary server for database three:

CREATE MIRROR SERVER primary_three
AS PRIMARY
connection_string='SERVER=primary_three;host=localhost:6871,localhost:6872';

Define server2 as a partner server for database three:

CREATE MIRROR SERVER server2
AS PARTNER
connection_string='SERVER=server2;host=localhost:6872'
state_file='c:\\server2\\server2state.txt';

Define the database server mirror_three as the logical mirror server for database three:

CREATE MIRROR SERVER mirror_three
AS MIRROR
connection_string='SERVER=mirror_three;host=localhost:6871,localhost:6872';

Define the arbiter server:

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

Set the mirroring options for the database mirroring system:

SET MIRROR OPTION authentication_string='ghi';

Disconnect from Interactive SQL.

Make a backup copy of the database in the c:\server2 directory:

dbbackup -c "UID=DBA;PWD=sql;SERVER=server1;DBN=three" c:\server2

Start the database server named server2:

dbsrv16 -n server2 -x tcpip(PORT=6872) -su sql c:\server2\one.db -xp on c:\server2\two.db -xp on c:\server2\three.db -xp on

Start the arbiter server.

dbsrv16 -n arbiter -su sql -x tcpip(port=6870) -xf c:\arbiter\arbiterstate.txt -xa "AUTH=abc,def,ghi;DBN=one,two,three"

Résultat

After starting server2, the server1 database server messages window shows that server1 is the primary server in the mirroring system for databases one, two, and three. The messages window also indicates that the mirror databases for one, two, and three (partners) are connected to server1.

The arbiter database server messages window shows that both server1 and server2 are connected.

Suivant

Proceed to Lesson 2: Using and testing the database mirroring system.

Discuter à propos de cette page dans DocCommentXchange.