Mirroring Overview

SQL Server 2005 provides a set of high availability methods that the users can use to achieve fault tolerance and to prevent server outages and data loss. The selection of the high availability method depends on various factors.

Some DBAs need the servers to be available 24/7, while others can afford an outage of a couple of hours. Cost also plays a role in the selection.

For example, Clustering is an expensive high availability method when compared to Database Mirroring, but it allows the user to failover immediately.

The following high availability features are available with the Enterprise edition:

  • Failover Clustering
  • Multiple Instances(up to 50)
  • Log shipping
  • Database Snapshots
  • Database Mirroring

The following high availability features are available with Standard Edition:

  • Failover Clustering(maximum two nodes)
  • Multiple instances(up to 16)
  • Log shipping
  • Database Mirroring

In this article, we will be discussing about Database Mirroring high availability method.

Overview of Database Mirroring:

Database Mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis. Mirroring works only with full recovery model. Database mirroring is available in the Enterprise edition and in the Standard edition. The user can mirror only the user databases.

Mirroring allows the user to create an exact copy of a database on a different server. The mirrored database must reside on different instance of SQL Server Database engine. Microsoft fully supports database mirroring with SQL Server 2005 SP1 onwards. For the RTM release (prior to SP1), Microsoft support services will not support databases or applications that use database mirroring. The database mirroring feature should not be used in production environments.

Prior to SP1, database mirroring is disabled by default, but can be enabled for evaluation purposes by using trace flag 1400.  The following T-SQL statement can be used to achieve this:

DBCC TRACEON (1400)

Database Mirroring is only available in the Standard, Developer and Enterprise editions of SQL Server 2005. These are the required versions for both the principal and mirror instances of SQL Server. The witness server can run on any version of SQL Server. In addition, there are some other features only available in the Developer and Enterprise editions of SQL Server, but the base functionality exists in the Standard edition.

Benefits of Database Mirroring:

  1. Implementing database mirroring is relatively easy. It does not require any additional hardware in terms of clustering support. So it proves to be a cheaper implementation instead of clustering a database.
  2. Database mirroring provides complete or nearly complete redundancy of the data, depending on the operating modes.
  3. It increases the availability of the database.

Understanding Database Mirroring Concepts:

Principal: The principal server is the primary database. This acts as a starting point in a database mirroring session. Every transaction that is applied to the principal database will be transferred to the mirrored database.

Mirror: Mirror is the database that will receive the copies from the principal server. There should be consistent connection between the mirrored and the principal server.

Standby Server: In the process of database mirroring, a standby server is maintained. This is not accessible to the users. In case of the principal server failing; the users can easily switch over.

Modes of Database Mirroring: Database Mirroring can work in two ways: synchronous or asynchronous

  1. A) Synchronous mode: This is also called as high safety mode. In this mode, every transaction applied to the principal will also be committed on the mirror server. The transaction on the principal will be released only when it is also committed on the mirror. Once it receives an acknowledgement from the mirror server, the principal will notify the client that the statement has been completed. The high safety mode protects the data by requiring the data to be synchronized between the principal and the mirror server.
  1. High safety mode without automatic failover:

  PRINCIPAL SERVER —–DataFlow—-> MIRROR SERVER

Transaction Safety set to full

When the partners are connected (Principal and Mirror) and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be manually forced to the mirror server (with possible data loss).

2. High Safety mode with automatic failover:

WITNESS SERVER

          PRINCIPAL SERVER —–DataFlow—-> MIRROR SERVER

Transaction Safety set to full:

Automatic failover provides high availability by ensuring that the database is still served after the loss of one server. Automatic failover requires that the session possess a third server instance, the witness, which ideally resides on a third computer. The above figure shows the configuration of a high-safety mode session that supports automatic failover.

B) Asynchronous mode: This is also known as the high performance mode. Here performance is achieved at the cost of availability. In this mode, the principal server sends log information to the mirror server, without waiting waiting for an acknowledgement from the mirror server.

Transactions on the principal server commit without waiting for the mirror server to commit to the log file. The following figure shows the configuration of a session using high-performance mode.

                  PRINCIPAL SERVER —–DataFlow—-> MIRROR SERVER

Transaction Safety set to off

This mode allows the principal server to run with minimum transactional latency and does not allow the user to use automatic failover. Forced service is one of the possible responses to the failure of the principal server. It uses the mirror server as a warm standby server. Because data loss is possible, one should consider other alternatives before forcing service to the mirror.

 Types of Mirroring:

 To provide flexibility when dealing with different requirements, SQL Server 2005 offers three operating modes, which are determined by presence of the witness and transaction safety level, configurable on per mirroring session basis.

The safety level can be turned either on or off. With the safety level set to ON, committed transactions are guaranteed to be synchronized between mirrored partners, with the safety turned OFF, synchronization is performed on a continuous basis, but without assurance of full consistency between transaction logs of both databases.

High availability operating mode: synchronous with a witness (with transaction safety set to ON) – In this case, transactions written to the transaction log of the database on the principal are automatically transferred to the transaction log of its mirrored copy. The principal waits for the confirmation of each successful write from its mirror before committing the corresponding transaction locally, which guarantees consistency between the two (following the initial synchronization). This type of synchronous operation is the primary prerequisite for the automatic failover – the other is the presence and proper functioning of the witness server (which means that only the synchronous mode with a witness offers such capability).

Additionally, availability of the witness also impacts operations in cases when the mirror server fails. In such a scenario, if the principal can still communicate with the witness, it will continue running (once the witness detects that the mirror is back online, it will automatically trigger its resynchronization), otherwise (if both mirror and witness are not reachable from the principal), the mirrored database is placed in the OFFLINE mode.

High protection operating mode: synchronous without a witness (with transaction safety set to ON) – uses the same synchronization mechanism as the first mode, however, the lack of the witness precludes automatic failover capability. The owner of the database can perform manual failover as long as the principal is present, by running ALTER DATABASE statement with SET PARTNER FAILOVER option from the principal).

Alternately, the owner can force the service to the mirror the database by running the ALTER DATABASE statement with the SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS option from the mirror, with potential data loss (if databases are not in synchronized state). Unavailability of the mirror (due to server or network link failure) causes the primary to place the mirrored database in OFFLINE mode (in order to prevent the possibility of having two mirroring partners operating simultaneously as principals).

High performance operating mode: asynchronous without a witness (with transaction safety set to OFF) – In this case, a transaction is committed on the principal before it is sent to its partner, which means that it is not uncommon for the source database and its mirror to be out of synch. However, since the process of transferring transaction log entries to the mirror is continuous, the difference is minor. In the case of principle failure, the database owner can force service to the mirror database, resulting in the former mirror taking on the role of the principal.

Forcing the service can result in data loss (encompassing all transaction log entries that constituted the difference between the mirror and the principal at the time of its failure), so it should be used only if such impact can be tolerated. Another choice when dealing with the principal failure in this mode (which reduces possibility of data loss) is terminating the mirroring session and recovering the database on the principal. Unlike in the synchronous mode with a witness, unavailability of the mirror leaves the principal operational.

Note:

  • Database mirroring is limited to only two servers.
  • Mirroring with a Witness Server allows for High Availability and automatic fail over.
  • You can configure your DSN string to have both mirrored servers in it so that when they switch you notice nothing.
  • While mirrored, your Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode.
  • Mirroring with SQL Server 2005 standard edition is not good for load balancing

Steps in Mirroring:

 SQL Server 2005 – Mirror Server:

In this tutorial you will learn about Mirror Server in SQL Server 2005 – Preparing the Principal and Mirror Server, Establishing a Mirroring Session, Establishing a Witness Server, Executing Transactions, Simulating Principal Server Failure, Restarting the Failed Server, Terminating the Mirror Session and Configuring Database Mirroring.

Preparing the Principal and Mirror Server:

Database mirroring is easy to set up and can be made self monitoring for automatic failover in the event of the principal server being unavailable. The first step is to configure the relationship between the principal server and the mirror server. This can be a synchronous mirroring with a witness server that provides the highest availability of the database.

A drawback in this type of configuration is the need to log transactions on the mirror before such transactions being committed to the principal server may retard performance. Asynchronous mirroring with a witness server provides high availability and good performance. Transactions are committed to the principal server immediately.

This configuration is useful when there is latency or distance between the principal server and the mirror. The third type of mirroring configuration is the Synchronous mirroring without the witness server. This guarantees that data on both servers is always concurrent and data integrity is of a very high order. However, automatic failover cannot occur as there are not enough servers to form a quorum decision on which server is to take the role of the principal server and which should be the mirror server.

Establishing a Mirroring Session:

Database mirroring is done within a mirror session. A mirror session maintains information about the state of the databases, the mirroring partners and the witness server. The mirror server identifies the most recent transaction log record that has been applied to the mirror database and requests for subsequent transaction log records from the principal server. This phase is called the synchronizing phase.

Once synchronization is complete the principal server will transmit the transaction logs to the mirror server even as changes are made. The mirror database is continually rolled forward to match the principal database. The operating mode of the mirror database (synchronous or asynchronous) will determine whether the transaction log records are applied to the mirror database immediately or after the transactions have been recorded in the principal server.

The mirror session maintains information about the state of any witness servers. It ensures that the witness server is visible both to the principal and the mirror servers.

A mirroring session is terminated by a number of causes. There may be a communication or server failure. The principal server may fail and the mirror may become the principal server. This can happen automatically or manually depending on the operating mode.

The session may also be terminated by the manual intervention of the Database Administrator using the TRANSACT-SQL ALTER DATABASE command. Mirroring may be terminated or suspended in the process

Establishing a Witness Server:

A witness server is a must where the DBA wants to implement automatic failover and the configuration must be in the synchronous operating mode. The witness server is usually on a different computer from the principal and the mirror servers. However, one server can act as a witness for multiple mirror partnerships.

The ALTER Database command with the SET WITNESS clause is used on the principal server to create a witness server. The Witness server address is specified and the endpoint port is defined to act as the witness for the server_network_address parameter.

A witness server can be disabled. However, the mirroring session will continue even when the witness server is disabled. Automatic failover will no longer be possible.

Information about the witness server can be viewed in sys.database_mirroring_witnesses catalog view.

Executing Transactions:

The ALTER DATABASE command has to be run on the mirror server specifying the principal server endpoint address and then the same has to be done on the principal server so that synchronization can commence. The operating mode has to then be selected. By default the operating mode is synchronous.

This can be changed by running the ALTER DATABASE command with SET PARTNER SAFETY clause on either partner server.

The saftety_mode parameter can be either OFF or FULL. The mirror partnership information can be viewed by running a query on sys.databases catalog view.

If the transaction safety is set to full, the principal and mirror servers operate on synchronous transfer mode. The transaction logs are hardened in the principal server and transmitted to the mirror and then the principal waits for the mirror to harden its logs and send its response. When the safety is OFF the principal does not wait for the acknowledgement of the mirror. In this instance the principal and the mirror may not be synchronized at all times.

Synchronous transfer guarantees that the mirror is a faithful image of the principal database transaction log

Simulating Principal Server Failure:

A principal server failure can be simulated in test scenarios to ensure that failover is smooth. Failover implies that the mirror server takes over as the principal server and the mirror database will have to act as the principal database. The failover can be manual, automatic or forced.

Automatic failover occurs when the high availability operating mode is synchronous and the safety is FULL and a witness is part of the session. Manual occurs in high availability and high protection operating modes. Safety has to be full and the partner databases are synchronized. Forced service is used primarily in the High Performance mode with safety off.

Simulating Principal Server failure can be done by manual intervention of the DBA in an orderly way. The safety will have to be first set to FULL and the principal and the mirror databases synchronized. Manual failover can be invoked by invoking the ALTER DATABASE command on the principal server or by clicking the failover button in the Database Properties/Mirroring dialog in the Management Studio.

A manual failover causes current users to be disconnected and all unfinished transactions to roll back. These transactions will then be recovered from the redo queue. The mirror assumes the role of the principal server and the two servers will negotiate a new starting point for mirroring based on their mirroring failover LNS.

If the principal server is no longer operating, and safety is OFF, forced service can be resorted to. This service causes some data loss.

Restarting the Failed Server:

A failed server can be restarted and can be synchronized with the principal server or the mirror server as the case may be. Any suspending of transactions causes the log on the principal server to grow with the transactions being logged and stored. Once the mirror session is resumed, the principal transaction log is synchronized and written on to the mirror database log.

Terminating the Mirror Session:

A mirror session can be manually terminated and the relationship between the servers can be ended. When a session is ended, all information about the session is removed from all servers and leaves both the principal server and the independent server with an independent copy of the database. The mirror server database will remain in the restoring state until it is manually recovered or deleted.

Configuring Database Mirroring:

Configuring a mirror server includes configuring the mirror server and the database.

The server designated as the mirror must be accessible and trusted by the principal database server. Ideally both servers should belong to the same domain. The mirror server should also have sufficient memory and processing power to act as the principal server in the event of failover. It should be able to support users and applications without noticeable difference in the quality of service.

The mirror database must be created manually. The file structure must match the principal database file structure. Both databases must implement full recovery model. Once the mirror database is created, the latest full database backup of the principal database must be applied to the mirror using the RESTORE DATABASE command with the WITH NONRECOVERY clause.

The next step is to enable the communication mechanism through which the mirroring will take place. This implies creation of endpoints on both servers. The endpoint controls the Transmission Control Protocol (TCP) port on which the server listens for database mirroring messages. The endpoint also defines the role that it must perform.

A server needs to have only one configured endpoint regardless of the number of mirroring sessions it participates in. However, each instance requires a unique port on which to listen.

The next step is to establish a mirroring session. The process of establishing a mirroring session has been discussed above. It involves creating a mirroring session using the ALTER DATABASE command on the mirror server first and then on the principal server. The server_network_address parameter will have to be specified. Then a partnership will have to be created on the mirror server, the operating mode will have to be changed and so on.

Preparing for mirroring:

To prepare for database mirroring, user has to perform three configuration steps:

    1. Configuring Security and communication between instances:

To establish a database mirror connection’s Server uses endpoints to specify the connection between servers. SQL Server performs authentication over the endpoints. This can be achieved by using Windows authentication or certificate based authentication.

If witness server is also in the picture, and then we need to specify the communication and authentication between the principal and the witness and between the mirror and witness.

Here, since we will be creating the end point for database mirroring; only TCP can be used as transport protocol. Each database mirroring endpoint listens on a unique TCP port number.

The endpoints can be created with the CREATE ENDPOINT TSQL statement.

Syntax:

CREATE ENDPOINT endPointName [ AUTHORIZATION login ]
[ STATE = { STARTED | STOPPED | DISABLED } ]
AS { HTTP | TCP } (
<protocol_specific_arguments>
)
FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (
<language_specific_arguments>
)

<AS TCP_protocol_specific_arguments> ::=
AS TCP (
LISTENER_PORT = listenerPort
[ [ , ] LISTENER_IP = ALL | ( 4-part-ip ) | ( “ip_address_v6” ) ]

)

<FOR DATABASE_MIRRORING_language_specific_arguments> ::=
FOR DATABASE_MIRRORING (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate_name
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
| CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
[ [ [ , ] ] ENCRYPTION = { DISABLED | { { SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }

]
[ , ] ROLE = { WITNESS | PARTNER | ALL }
)

 

Authentication= <authentication_options>

WINDOWS [{NTLM | KERBEROS | NEGOTIATE}]
Specifies the TCP/IP authentication requirements for connections for this endpoint. The default is WINDOWS. Along with the authentication the user has to mention the authorization method (NTLM or Kerberos).By default, the NEGOTIATE option is set, which will cause the endpoint to negotiate between NTLM or Kerberos.

CERTIFICATE certificate_name
the user can also specify that the endpoint has to authenticate using a certificate. This can be done by specifying the CERTIFICATE keyword and the name of the certificate. For certificate based authentication, the endpoint must have the certificate with the matching public key

WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
Specifies that endpoint has to first try to connect by using Windows Authentication and, if that attempt fails, to then try using the specified certificate.

CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
Specifies that endpoint has to first try to connect by using the specified certificate and, if that attempt fails, to then try using Windows Authentication.

Encryption

Next, we will take a look at the encryption option. By default, database mirroring uses RC4 encryption.

ENCRYPTION = {DISABLED | SUPPORTED | REQUIRED} [ALGORITHM {RC4 | AES | AES RC4 | RC4 AES}]
Specifies whether encryption is used in the process. The default is REQUIRED.

Encryption options:

Option Description
DISABLED Specifies that data sent over a connection is not encrypted.
SUPPORTED Specifies that the data is encrypted only if the opposite endpoint specifies either SUPPORTED or REQUIRED.
REQUIRED Specifies that connections to this endpoint must use encryption. Therefore, to connect to this endpoint, another endpoint must have ENCRYPTION set to either SUPPORTED or REQUIRED

 

 

 

Encryption Algorithm.

Option Description
RC4 Specifies that the endpoint must use the RC4 algorithm. This is the default.
AES Specifies that the endpoint must use the AES algorithm.
AES RC4 Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the AES algorithm.
RC4 AES Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the RC4 algorithm.

RC4 is a relatively weak algorithm, and AES is a relatively strong algorithm. But AES is considerably slower than RC4. If security is a higher priority than speed, then AES is recommended.

Role:

We have to specify the endpoint’s role in the Database mirroring option. Role can be Partner, Witness or All. Using the ALL keyword as the role specifies that the mirroring endpoint can be used for witness as well as for a partner in the database mirroring scenario.

We can inspect the database mirroring endpoints on a server by querying the sys.database_mirroring_endpoints catalog view:
SELECT *
FROM sys.database_mirroring_endpoints;

2.Creating the Mirror Database:

To create a mirror database, we have to restore the full backup of a principal including all other types of backup (transactional logs) created on the principal before establishing a session.

The NORECOVERY option has to be used when restoring from backup so that the mirrored database will remain in non usable state. The mirror database needs to have the same name as the principal database.

    1. Establishing a mirror session:
      The next step in setting up database mirroring is to set up the mirror session on the database by identifying the mirroring partners.

We have to identify the partners involved in the mirroring process on the principal database and on the mirror database.

Let us consider an example.

We will take Adventure Works as the sample database. This database has simple recovery model by default. To use database mirroring with this database, we must alter it to use the full recovery model.

USE master;
GO
ALTER DATABASE Adventure Works
SET RECOVERY FULL;
GO

We have two server instances which act as partners (Principal and Mirror) and one server instance which acts as witness. These three instances are located on different computers.

The three server instances run the same Windows domain, but the user account is different for the example’s witness server instance.

  1. Create an endpoint on the principal server instance

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
–Partners under same domain user; login already exists in master.
–Create a login for the witness server instance,
–which is running as XYZ\witnessuser:
USE master ;
GO
CREATE LOGIN [XYZ\witnessuser] FROM WINDOWS ;
GO
— Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [XYZ\witnessuser];
GO

2.Create an endpoint on the mirror server instance

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO
–Partners under same domain user; login already exists in master.
–Create a login for the witness server instance,
–which is running as XYZ\witnessuser:
USE master ;
GO
CREATE LOGIN [XYZ\witnessuser] FROM WINDOWS ;
GO
–Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [XYZ\witnessuser];
GO

  1. Create an endpoint on the witness server instance

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO
–Create a login for the partner server instances,
–which are both running as Mydomain\dbousername:
USE master ;
GO
CREATE LOGIN [Mydomain\dbousername] FROM WINDOWS ;
GO
–Grant connect permissions on endpoint to login account of partners.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];
GO

  1. Create the mirror database. Refer step 2 in the “Preparing for Mirroring” block.
  2. Configure the principal as the partner on the mirror.

ALTER DATABASE <Database_Name>
SET PARTNER =
<server_network_address>
GO

The syntax for a server network address is of the form:
TCP: // < system-address> : < port>

Where,
< system-address> is a string that unambiguously identifies the destination computer system. Typically, the server address is a system name (if the systems are in the same domain), a fully qualified domain name, or an IP address.

< Port> is the port number used by the mirroring endpoint of the partner server instance.

A database mirroring endpoint can use any available port on the computer system. Each port number on a computer system must be associated with only one endpoint, and each endpoint is associated with a single server instance; thus, different server instances on the same server listen on different endpoints with different ports. In the server network address of a server instance, only the number of the port associated with its mirroring endpoint distinguishes that instance from any other instances on the computer.

Example:

ALTER DATABASE Adventure Works
SET PARTNER =
‘TCP: //PARTNERHOST1.COM:7022’
GO

  1. Configure the mirror as the partner on the principal.

    ALTER DATABASE Adventure Works
    SET PARTNER = ‘TCP: //PARTNERHOST5.COM:7022’
    GO

  2. On the principal server, set the witness

ALTER DATABASE Adventure Works
SET WITNESS =
‘TCP://WITNESSHOST4.COM:7022’
GO

Switching Roles:

When the principal server fails, we have to switch roles over to the mirror and from then on specify that the mirror should become the principal database. This concept is called role switching. The three options for role switching are:

  1. Automatic failover: – When the witness server is present in the database mirroring session, automatic failover will occur when the principal database becomes unavailable and
    when the witness server confirms this. During the automatic failover, the mirror will be automatically promoted to principal, and whenever the principal comes back on, it will automatically take the role of mirror.
  2. Manual Failover: – The user can perform manual failover only if both the principal and mirror are alive and in synchronized status. DBAs use this operation most frequently to perform maintenance tasks on the principal. The failover is initiated from the principal and later the roles are reverted after the database maintenance job is done.

The statement used to switch database roles (manual failover) is shown below:

ALTER DATABASE Adventure Works SET PARTNER FAILOVER

  1. Forced Service: – When the witness server is not used and if the principal database goes down unexpectedly, then the user has to initiate manual failover to the mirror. In asynchronous mode of operation, user does not have any idea whether the transaction that have got committed on the principal have made it to the mirror or not. In this scenario, when the user wants to switch roles, there is possibility of losing data.

To achieve this, we need to invoke an ALTER DATABASE statement as shown below:

ALTER DATABASE Adventure Works SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.