CREATE MIRROR SERVER statement

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

parent-name :
server-name | PRIMARY

server-option :
connection_string 
logfile
preferred
state_file

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. An error is returned if you attempt to replace a mirror server while it is in use.
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.
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 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 causes the primary server to assign a parent for this server.
server-option clause The following options are supported:
- connection_string server option 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 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. 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.

Cloud note: This statement is not supported on tenant databases in the cloud.

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 are 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. Also, all copy node servers must have unique server names.

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.

Must have DBA authority.

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 a mirror server that can be used as a partner 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 statement

Note

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example