Tutorial: Adding a mirroring system to a read-only scale-out system

This tutorial describes how to add a mirroring system to an existing read-only scale-out system. The root of the read-only scale-out system also becomes the mirror server.

Prérequis

This tutorial assumes that you have a running read-only scale-out system as described in Tutorial: Creating a read-only scale-out system.

Configure the root node of the read-only scale-out system to be the primary server in a mirroring system by defining mirror servers, creating an arbiter, and converting copy nodes to become the mirror partner of the mirroring system

You must have the MANAGE ANY MIRROR SERVER system privilege.

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

Contexte et remarques

In this tutorial, all the database servers are running on the same computer. However, each database server must be 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.

Task

Connect to the root node of the read-only scale-out system. Run the following command:
dbisql -c "UID=DBA;PWD=sql;SERVER=scaleout_root_demo"

Define the primary and mirror servers. Execute the following statements:

-- Alter existing primary to include both partners 
ALTER MIRROR SERVER scaleout_primary_demo 
connection_string='SERVER=scaleout_primary_demo;HOST=localhost:6871,localhost:6873';  

-- Alter existing Partner to include state file 
ALTER MIRROR SERVER scaleout_root_demo state_file='c:\\scaleoutdemo\\server1\\server1.state';

-- Define mirror 
CREATE MIRROR SERVER scaleout_mirror_demo AS MIRROR
connection_string='SERVER=scaleout_mirror_demo;HOST=localhost:6871,localhost:6873';

--Convert an existing copy node to the mirror server

ALTER MIRROR SERVER scaleout_child_demo 
connection_string='SERVER=scaleout_child_demo;HOST=localhost:6873' 
state_file='c:\\scaleoutdemo\\copynode\\server3.state';

Convert an existing copy node to the mirror server. Execute the following statement:

--Convert an existing copy node to the mirror server

ALTER MIRROR SERVER scaleout_child_demo 
connection_string='SERVER=scaleout_child_demo;HOST=localhost:6873' 
state_file='c:\\scaleoutdemo\\copynode\\server3.state';

Start a server to be the arbiter server for the mirroring system. Run the following commands:

mkdir c:\scaleoutdemo\arbiter

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

Connect to the root node and add the arbiter. Execute the following statements.

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

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

Convert an existing copy node to the mirror server. Execute the following statement:

ALTER MIRROR SERVER scaleout_child_demo AS PARTNER state_file='c:\\scaleoutdemo\\copynode\\server3.state'

Verify that scaleout_child_demo is the mirror server by inspecting the contents of the SYSMIRRORSERVER table. Run the following command:

dbisql -c "UID=DBA;PWD=sql;SERVER=scaleout_primary_demo" "SELECT  server_name, server_type FROM SYSMIRRORSERVER"

The scaleout_child_demo server is now the second PARTNER server.

Update the connection strings in your client applications to specify the addresses of both partners in the Host connection parameter. For example:

dbping -c "UID=DBA;PWD=sql;SERVER=scaleout_primary_demo;HOST=localhost:6871,localhost:6873"

(Optional) Add another copy node.

Make a backup copy of the primary database, placing it in the directory c:\scaleoutdemo\copynode2.

Execute the following statement:

BACKUP DATABASE DIRECTORY 'c:\\scaleoutdemo\\copynode2';

Start the backup copy of the database as a child (copy node) of the scaleout_root_demo database server. Run the following command:

dbsrv16 -n scaleout_child2_demo -su sql -x TCPIP(port=6874) "c:\scaleoutdemo\copynode2\scaleoutdemo.db" -xp on

Résultat

The read-only scale-out system now is also part of a mirroring system.

Tutorial: Adding a mirroring system to a read-only scale-out system

Task

See also