CREATE MIRROR SERVER statement

Creates or replaces a mirror server that is being used for database mirroring or read-only scale-out.

Syntax

Create a mirror server

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

Create a mirror server as a copy

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

Parameters

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
You can specify a variable name for server-option.

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.
- 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

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, and you cannot have more than two partner servers.

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.

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

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

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

Example

The following example creates a mirror server using a variable for the connection_string parameter.

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:6871,winxp-3:6872 ' ;

The following statement creates a mirror server using the variable @connstr_value for the connection_string parameter:

CREATE MIRROR SERVER new_scaleout_primary AS PRIMARY
connection_string = @connstr_value ;