ALTER MIRROR SERVER statement

ALTER MIRROR SERVER mirror-server-name
AS { PRIMARY | MIRROR | ARBITER | PARTNER | 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

AS clause You can specify one of the following server types:
- PRIMARY The mirror server with type PRIMARY defines a virtual or logical server, rather than an actual database server. The name of this server is the alternate server name for the database. The alternate server name can be used by applications to connect to the server currently acting as the primary server. The server marked as PRIMARY also defines the connection string used by mirror servers to connect to the server currently acting as primary, and it defines how new copy nodes initially connect to the root server in a scale-out system. There can be only one PRIMARY server for a database.
- MIRROR The mirror server with type MIRROR defines a virtual or logical server, rather than an actual database server. The name of this server is the alternate mirror server name for the database. The alternate mirror server name can be used by applications to connect to the server currently acting as the read-only mirror. There can be only one MIRROR server for a database.
- ARBITER In a database mirroring system, the arbiter server assists in determining which of the PARTNER servers takes ownership of the database. The arbiter server must be defined with a connection string that can be used by the partner servers to connect to the arbiter. There can be only one ARBITER server for a database.
- PARTNER In a database mirroring system, servers defined as PARTNER are eligible to become the primary server and take ownership of the database. You must define two PARTNER servers for database mirroring, and both must have a connection string and a state file. 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 read-only scale-out system, you must define one PARTNER server. This server is the root server, and runs the only copy of the database that allows both read and write operations.
- COPY 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. 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. See Adding copy nodes.
FROM SERVER clause You can only specify this clause for mirror servers of type COPY. This clause constructs a tree of servers for a mirroring or scale-out system and indicates which servers the non-participating 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 (participating and non-participating nodes), the non-participating 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. See SYSMIRRORSERVER system view.

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. See Adding copy nodes.
USING AUTO PARENT clause This clause causes the primary server to assign a parent for this server. See Automatically assign the parent of a copy node.
ALTER PARENT FROM clause 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 The following options are supported:
- connection_string Specifies the connection string to be used to connect to the server. A user ID and password are not required. 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.
  For a complete list of connection parameters, see Connection parameters.
- logfile 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 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. See Specifying a preferred database server.
- state_file Specifies the location of the file used for maintaining state information about the mirroring system. This option is required for database mirroring. 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. See State information files.

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.

Mirror server names for servers of type PARTNER, ARBITER, or 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 allows each database server to find its own definition and that of its parent.

Must have DBA authority.

Automatic commit.

SQL/2008 Vendor extension.

The following example changes the parent of the scaleout_child copy node and assigns all its siblingsg to be its children:

ALTER MIRROR SERVER "scaleout_child"
AS COPY
ALTER PARENT FROM scaleout_mirror 
connection_string = 'server=scaleout_child;links=tcpip(host=winxp-2:6878';

ALTER MIRROR SERVER statement

Separately licensed component required

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example