CREATE MIRROR SERVER statement

CREATE [ OR REPLACE ] MIRROR SERVER mirror-server-name
AS { PRIMARY | MIRROR | ARBITER | PARTNER }
[ server-option = string [ ... ] ]

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

server-option :
connection_string 
logfile
preferred
state_file

parent-name :
server-name | PRIMARY

OR REPLACE clause CREATE MIRROR SERVER creates the mirror server. An error is returned if a mirror server with the specified name already exists in the database.
Specifying OR REPLACE creates a mirror server if the server does not already exist in the database, and replaces it if it does exist.
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 connection string for the server marked as PRIMARY
  - Defines the connection string used by copy nodes to connect to the root node or PRIMARY parent.
  - Defines the connection string used by the connection parameter NodeType PRIMARY value.
  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. The server marked as MIRROR also defines the connection string used by the NodeType connection parameter MIRROR value. 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 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.
  - Mirroring or mirroring with read-only scale-out You must define two PARTNER servers for database mirroring, and both must have a connection string and a state file.
    In a database mirroring system, servers defined as PARTNER are eligible to become the primary server and take ownership of the database.
  - Read-only scale-out without mirroring You must define one PARTNER server for read-only scale-out, and it must have a connection string and no state file. 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.
  When AS COPY is specified, then you must also specify either the FROM SERVER or USING AUTO PARENT clause.
  
  The connection string is used by the NodeType connection parameter COPY value and it is also used by other copy nodes that have this server as their parent.
  
  When adding copy nodes to a read-only scale-out system, you can either execute a CREATE MIRROR SERVER statement for the copy node, or have the root server define the mirror server automatically.
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.
OR SERVER clause Use the OR SERVER clause to specify an alternate parent for the copy node.
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. When you use this clause to replace an existing copy node server, the definitions for the parent and alternate parent for the copy node do not change.
server-option clause The following options are 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.
  For a list of connection parameters, see Connection parameters.
- 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.

This statement creates or replaces a mirror server definition; it does not change a mirror server definition. To change a mirror server definition, use the ALTER MIRROR SERVER statement.

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 or COPY must match the names of the database servers that are 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. Also, all copy node servers must have unique server names. mirror-server-name, parent-name, and server-name above must be 7-bit ASCII characters.

To use a copy node as the arbiter for the database it is copying in a database mirroring system, create the arbiter server with a name that does not match the server name of any of the database servers in the high availability system. In this configuration, the name of the arbiter is used as a placeholder in the mirror server definition to hold the connection string for the arbiter.

You must have the MANAGE ANY MIRROR SERVER system privilege.

Automatic commit.

SQL/2008 Vendor extension

The following statement creates a mirror server that can be used as the primary server in a database mirroring system:

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

The following statement creates a mirror server that can be used as the mirror server in a database mirroring system:

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

The following statement creates a mirror server that can be used as the arbiter in a database mirroring system:

CREATE MIRROR SERVER "scaleout_arbiter"
AS ARBITER 
connection_string = 'server=scaleout_arbiter;host=winxp-4:6870';

The following statement creates two mirror servers that can be used as partners server in a database mirroring system:

CREATE MIRROR SERVER "scaleout_server1"
AS PARTNER
connection_string = 'server=scaleout_server1;HOST=winxp-2:6871'
state_file = 'c:\\server1\\server1.state';

    
CREATE MIRROR SERVER "scaleout_server2"
AS PARTNER
connection_string = 'server=scaleout_server2;HOST=winxp-3:6872'
state_file = 'c:\\server2\\server2.state';

The following statement creates a copy node that can act as the arbiter in a database mirroring system:

CREATE MIRROR SERVER "scaleout_child"
AS COPY FROM SERVER  "scaleout_primary"
connection_string = 'server=scaleout_child;host=winxp-5:6878';

The following statement defines a copy node as the arbiter for a different database mirroring system:

CREATE MIRROR SERVER "The Arbiter"
AS ARBITER
connection_string = 'server=scaleout_child;host=winxp-5:6878';

The following statement preserves the current parent if server-name already exists. However, it does not auto-generate a new parent.

CREATE OR REPLACE MIRROR SERVER "server-name" AS COPY USING AUTO PARENT;

CREATE MIRROR SERVER statement

Note

Syntax 1

Syntax 2

Parameters

Remarks

Privileges

Side effects

See also

Standards and compatibility

Example