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:

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:

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.

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.

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,SQL 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,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 } (

)
FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (

)

::=
AS TCP (
LISTENER_PORT = listenerPort
[ [ , ] LISTENER_IP = ALL | ( 4-part-ip ) | ( "ip_address_v6" ) ]

)

::=
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=

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 caus ethe 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 nonusable state.The mirror database needs to have the same name as the principal database.

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

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

4.Create the mirror database. Refer step 2 in the “Preparing for Mirroring” block.

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

ALTER DATABASE
SET PARTNER =

GO

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

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

<> 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 AdventureWorks
SET PARTNER =
'TCP://PARTNERHOST1.COM:7022'
GO

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

ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://PARTNERHOST5.COM:7022'
GO

7.On the principal server, set the witness

ALTER DATABASE AdventureWorks
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 AdventureWorks SET PARTNER FAILOVER

3.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 commited 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 AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Conclusion

Thus we had a look at the Database mirroring feature and how this feature can be explored for maintaining high availability of the databases.