Database Mirroring in SQL Server 2005

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)

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 implemetation instead of cluserting 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 accesible 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

a)Synchronous mode: This is also called as high safety mode.In this mode,every transaction applied to the principal will also be commited on the mirror server.The transaction on the principal will be released only when it is also commited 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:

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:

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.

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.

Preparing for mirroring: