Dropping a mirror server from a mirroring system

Drop a mirror server from a mirroring system by deleting the two mirror server definitions (CREATE MIRROR SERVER...AS PARTNER and CREATE MIRROR SERVER...AS MIRROR).

Prerequisites

It is recommended that you do not drop a mirror server that is running as part of a database mirroring system.

You can only drop the mirror server. If the server you want to drop is the primary server, you must initiate a failover so that the mirror and primary servers switch roles.

You must have the MANAGE ANY MIRROR SERVER system privilege.

Context and remarks

To keep the same server name but change its settings, you can use the CREATE OR REPLACE MIRROR SERVER statement or the ALTER MIRROR SERVER statement.

Task

Connect to the database on the primary server.
If the mirror server has any child copy nodes, then execute an ALTER MIRROR SERVER statement to re-assign any child copy nodes to a different parent.
Delete the mirror server definition by executing a DROP MIRROR SERVER statement and specifying the mirror server name:
DROP MIRROR SERVER mirror_server_name;
Delete the partner server definition by executing a DROP MIRROR SERVER statement and specifying the partner name:
DROP MIRROR SERVER partner_server_name;
(Optional) Stop the database server.

Results

The mirror server is dropped. If there are any copy nodes remaining, the database mirroring system becomes a read-only scale-out system.

Example

The partner servers of a mirroring system were created with the following statements:

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

CREATE MIRROR SERVER myprimary
AS PRIMARY
connection_string='SERVER=myprimary;HOST=localhost:6871,localhost:6872';

CREATE MIRROR SERVER mymirror
AS MIRROR
connection_string='SERVER=mymirror;HOST=localhost:6871,localhost:6872';

Execute the following statement to drop the mirror server (mirror_server2):

DROP MIRROR SERVER mirror_server2;
DROP MIRROR SERVER mymirror

Dropping a mirror server from a mirroring system

Task

See also