ALTER MIRROR SERVER statement

Modifies the attributes of a mirror server.

Syntax

Alter a mirror server

ALTER MIRROR SERVER mirror-server-name
[AS { PRIMARY | MIRROR | ARBITER | PARTNER}]
[ server-option = { string | NULL } [ ... ] ]

Alter a mirror server as a copy

ALTER MIRROR SERVER mirror-server-name
[AS COPY] 
[ { FROM SERVER parent-name [ OR SERVER server-name ] | USING AUTO PARENT } | ALTER PARENT FROM mirror-server-name ]
[ server-option = { string | NULL } [ ... ] ]

parent-name :
server-name | PRIMARY

server-option :
connection_string 
logfile
preferred
state_file

Parameters

AS clause
Use the AS clause to change the server-type of the mirror server from PARTNER to COPY or COPY to PARTNER. This clause is not needed and it is not recommended if you are not changing the type of mirror server.
- PARTNER
  Only a database server with the mirror server type of COPY can use this value to change its type to PARTNER. The parent definitions for the copy node are deleted.
  
  The name of the mirror server must correspond to the name of the database server, as specified by the -n server option, and must match the value of the SERVER connection string parameter specified in the connection_string mirror server option.
  
  In a database mirroring system, the partners use the connection string value to connect to each other. In a read-only scale-out system, the connection string is used by a copy-node that has this server as its parent.
- COPY
  Only a database server whose mirror server type is PARTNER can use this value to change its server type to a copy node. This partner server must also currently have the MIRROR role.
  
  In a read-only scale-out system, this value specifies that the database server is a copy node. All connections to the database on this server are read-only. The name of the mirror server must correspond to the name of the database server, as specified by the -n server option, and must match the value of the SERVER connection string parameter specified in the connection_string mirror server option.
FROM SERVER clause
This clause can only be used when AS COPY is specified. This clause constructs a tree of servers for a scale-out system and indicates which servers the copy nodes obtain transaction log pages from.

The parent can be specified using the name of the mirror server or PRIMARY. An alternate parent for the copy node can be specified using the OR SERVER clause.

In a database mirroring system that has only two levels (partner and copy nodes), the copy nodes obtain transaction log pages from the current primary or mirror server.

A copy node determines which server to connect to by using its mirror server definition that is stored in the database. From its definition, it can locate the definition of its parent, and from its parent's definition, it can obtain the connection string to connect to the parent.

You do not have to explicitly define copy nodes for the scale-out system: you can choose to have the root node define the copy nodes when they connect.
USING AUTO PARENT clause
This clause can only be used when AS COPY is specified. This clause causes the primary server to assign a parent for this server.
ALTER PARENT FROM clause
This clause can only be used when AS COPY is specified. This clause changes the parent for this mirror server, and assigns all its siblings to be its children. The server name specified by the ALTER PARENT FROM clause is used to verify that the current parent for this server matches the value specified. This is used to ensure that only one of a collection of siblings is able to replace its parent if they all request the change simultaneously.
server-option clause
You can specify a variable name for server-option.

The following options are also supported:
- connection_string server option
  Specifies the connection string to be used to connect to the server. The connection string for a mirror server should not include a user ID or password because they are not used when one mirror server connects to another mirror server.
- logfile server option
  Specifies the location of the file that contains one line per request that is sent between mirror servers if database mirroring is used. This file is used only for debugging.
- preferred server option
  Specifies whether the server is the preferred server in the mirroring system. You can specify either YES or NO. The preferred server assumes the role of primary server whenever possible. You specify this option when defining PARTNER servers.
- state_file server option
  Specifies the location of the file used for maintaining state information about the mirroring system. This option is required for database mirroring. In a mirroring system, a state file must be specified for servers with type PARTNER. For arbiter servers, the location is specified as part of the command to start the server.

Remarks

Read-only scale-out and database mirroring each require a separate license.

In a database mirroring system, the mirror server type can be PRIMARY, MIRROR, ARBITER, or PARTNER.

In a read-only scale-out system, the mirror server type can be PRIMARY, PARTNER, or COPY.

You can only change the mirror server type from COPY to PARTNER or from PARTNER to COPY. To change to or from a PRIMARY, MIRROR, or ARBITER server type, you must drop the mirror server definition and recreate it.

Mirror server names for servers of type PARTNER and COPY must match the names of the database servers that will be part of the mirroring system (the name used with the -n server option). This requirement allows each database server to find its own definition and that of its parent. mirror-server-name, parent-name, and server-name above must contain only 7-bit ASCII characters.

When you convert from a copy node to a partner, the parent definitions are deleted from the mirror server definition.

To replace a mirror server definition, use the CREATE MIRROR SERVER statement with the OR REPLACE clause.

Note For parameters that accept variable names, an error is returned if one of the following conditions is true:

The variable does not exist
The contents of the variable are NULL
The variable exceeds the length allowed by the parameter
The data type of the variable does not match that required by the parameter

Privileges

You must have the MANAGE ANY MIRROR SERVER system privilege.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following example does the following:

Creates a primary server called scaleout_primary1.
Creates a copy node, scaleout_child1, for scaleout_primary1.
Creates a mirror server, scaleout_mirror1.

Using the ALTER MIRROR SERVER statement, reassigns scaleout_child1 to be a copy node of scaleout_mirror1.

CREATE MIRROR SERVER "scaleout_primary1"
   AS PRIMARY
   connection_string = 'server=scaleout_primary1;host=winxp-2:6871,winxp-3:6872';

CREATE MIRROR SERVER "scaleout_child1"
   AS COPY FROM SERVER  "scaleout_primary1"
   connection_string = 'server=scaleout_child1;host=winxp-2:6878';

CREATE MIRROR SERVER "scaleout_mirror1"
   AS MIRROR 
   connection_string = 'server=scaleout_mirror1;host=winxp-2:6871,winxp-3:6872';

ALTER MIRROR SERVER "scaleout_child1"
   FROM SERVER "scaleout_mirror1"
   connection_string = 'server=scaleout_child1;host=winxp-2:6878';

The following example alters a mirror server using a variable for the connection_string parameter.
1. The following statement creates a variable for a connection string:
```
CREATE VARIABLE @connstr_value LONG VARCHAR
SET @connstr_value = ' server=new_scaleout_primary;host=winxp-2:6878 ';
```
2. The following statement alters the mirror server new_scaleout_primary using the variable @connstr_value for the connection_string parameter:
```
ALTER MIRROR SERVER new_scaleout_primary AS PRIMARY
connection_string = @connstr_value = @connstr_value;
```