Tutorial: Converting a partner server to a copy node

This tutorial describes how to convert a partner server in a database mirroring system to a copy node in a read-only scale-out system without stopping the system.

Prerequisites

This tutorial relies on the database mirroring system described in Tutorial: Creating a database mirroring system.

You must have the MANAGE ANY MIRROR SERVER system privilege.

By default, to use the ALTER DATABASE...SET PARTNER FAILOVER statement you must have the SERVER OPERATOR system privilege. The required privileges can be changed by using the -gd database server option.

Context and remarks

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 partner server that you want to convert and ensure that it has the mirror role. You can only convert the partner with the mirror role. If the server you want to convert to a copy node is the primary server, you must initiate a failover so that the primary and mirror servers switch roles. For example, connect to mirror_server2:

dbisql -c "UID=DBA;PWD=sql;SERVER=mirror_server2;HOST=localhost:6872"

The database server property MirrorRole returns the current role of the server. Execute the following statement:

SELECT DB_PROPERTY( 'MirrorRole' );

If Primary is returned, then initiate a failover by executing the following statement:

ALTER DATABASE SET PARTNER FAILOVER;

The current primary database stops and restarts.

Connect to the primary server:

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

Change the definitions:

Update the mirror definition of type PRIMARY to remove the connection information of the partner that is being converted.
Delete the mirror definition of type MIRROR.
Change the partner definition for the server being converted to a copy node.

Execute the following statements:

ALTER MIRROR SERVER mirror_demo_primary
connection_string='SERVER=mirror_demo_primary;HOST=localhost:6871';
  
DROP MIRROR SERVER mirror_demo_mirror; 
 
ALTER MIRROR SERVER mirror_server2 AS COPY FROM SERVER PRIMARY;

The database on both the partner servers restart the database as part of changing these configurations, but the server itself continues running. During the database restart, connections to the database are dropped.

(Optional) If you are dismantling the mirroring system, delete the arbiter server. Execute the following statement:
DROP MIRROR SERVER demo_arbiter;

Verify that mirror_server2 has been converted to a copy node by viewing the contents of the SYSMIRRORSERVER system view. Run the following command:

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

Results

The mirror server is now a read-only scale-out copy node.

Discuss this page in DocCommentXchange.