Introduction

In any distributed environment, database synchronization between two different locations or different servers’ is very essential for mission critical applications. For any reason, if the database server failed, these mission critical applications should point to the database as quickly as possible without loss of committed data. In real time situation, users should not experience any down time. There are two solutions that meet this requirement and one of them is Database Mirroring and the other is Clustering.

Database Mirroring is a new feature introduced in SQL Server 2005.  Database Mirroring as a High Availability option is not available in SQL Server 2000 and prior versions of SQL Server Editions.  Database Mirroring transfers transaction log record from source server to a destination server and quickly fail over to a destination server if any failure occurs in the source server database. Basically, main purpose of Database Mirroring is, if this feature is enabled, whatever the updates made on the principal server database will be transferred to the mirror server database immediately.

Application code can be written and configured in the application to point to the destination (which we call standby or mirror server) server database and can establish the connection to the standby database server in the event of hardware failure. This technology is also used as a Disaster Recovery Solution for any company.  Database mirroring is implemented on database level. Database mirroring is cost-effective solution for high availability; no special hardware is needed and ensures transactional consistency.  In real time, if the primary server fails, mirror server takes over the responsibility of the primary live server and the mirror server becomes the live server and all the applications are reconnected to the mirror server.

Database Mirroring works only with databases that are set to FULL RECOVERY mode. Like in Log Shipping Bulk-Logged recovery model is not supported in Database Mirroring.

Database Mirroring Components and concepts

Principal Server:  Server that houses the database that is used in the mirror session.

Mirror Server: Server that receives transaction log record transferred from the principle.

Witness Server: Server that watches the activity of principal and mirror servers. Using the witness server, when the principal server becomes unavailable, it sets the mirror server as the principal server automatically. Witness server is used for automatic failover.

Database Mirroring Operating Modes

There are three modes in which database mirroring will operate and they are:

• High-Availability:
  • This mode provides synchronous transfer of data between principal and mirror database.
  • Provides automatic failure detection between principal and mirror.
  • Supports automatic failover operation.
  • For this mode, all three components should be present: Principal, Mirror, and Witness.
  • Witness server initiates the failover in the event of failure detection.
  • SQL Server writes transaction to the buffer cache, after being written to the buffer this data will be written to the transaction log.
  • When SQL Server writes record to the transaction log, database mirroring will be initiated.
  • When the principal server fails over to mirror server, mirror server becomes principal, the role switches over between these two servers.
  • There will not be data loss in this operation because of being automatic failover.
• High-Performance:
  • In this mode, principal and mirror servers are involved, but witness server is not required since there will not be automatic failover using this mode.
  • Transaction between principal and mirror database will be asynchronous.
  • Being the data transfer asynchronous, there will not be performance impact on applications.
  • There can be chances of losing the data when manually failing over the principal server to the mirror server.
• High-Safety:
  • High-Safety is also referred to as High-Protection Mode.
  • This operating mode transfers data synchronously
  • It does not have witness server though and prevents from automatic failover.
  • Synchronous transactions confirm that data transferred to the mirrors is committed at the mirror and sends acknowledgement to the principal and then writes to the log file.
  • Manual failover is required to switch over the mirroring roles in this operating mode.

 SQL Server Editions used in Database Mirroring

Database mirroring supports

• Standard Edition,
• Enterprise Edition

Principal and mirror instance should be one of these editions in order to set up database mirroring. Witness Instance can be of any editions.

Database Mirroring Set up Steps

To initialize the mirror database

• Perform full back up in primary database.
• Perform Transaction Log backup on principal server database.
• Restore this full backup on mirror database WITH NORECOVERY option.
• Restore the transaction log back up on the mirror server WITH NORECOVERY option.
• Restore any other transaction log backup taken before initiating the mirror database.

I have following instances for this database mirroring demo set up.

Linux-PC: Principal Instance

Linux-PC\SQLMIRROR: Mirror Instance

Linux-PC\SQLWITNESS: Witness Instance

Perform full DBMirroring database backup on the Principal Instance, using following script.

BACKUP DATABASE dbmirroring

TO DISK = N’E:\Linux-PC Mirror\Backups\DBMirroring_db.bak’

Perform Transaction log back up on DBMirroring database using the following script.

BACKUP LOG DBMirroring

TO  DISK = N’E:\Linux-PC Mirror\Backups\DBMirroring_TL.trn’

Note: Give the location where you want to keep your backup file.

 Database and Log Restoration

Use the following script to restore full database backup and log backup restore. Restore on the Mirror Instance: Linux-PC\SQLMIRROR

RESTORE DATABASE DBMirroring

FROM  DISK = N’E:\Linux-PC Mirror\Backups\DBMirroring_db.bak’

WITH NORECOVERY

 RESTORE LOG DBMirroring

FROM  DISK = N’E:\Linux-PC Mirror\Backups\DBMirroring_TL.trn’

WITH NORECOVERY

Screen shot to show after the mirror database is initialized. DBMirroring database is in restore mode:-

Now it is time to set up the mirroring process.

Step 1: Set up

• Right click on DBMirroring on Linux-PC Instance and then on Tasks, and on Mirror  option from the context menu as show below:-

Following screen comes after clicking on Mirror…

Configuring Security for Witness Server

• Click on Configure security

Screen to include Witness Instance is shown below

This is a place where you want to add Witness server for automatic failover. Please check Yes  and then Click Next button shown in red.

After you click next, comes the next window where you can select Witness Server as shown below and then click next, as shown below:-

Once security configuration is saved for witness server, we can configure Principal, Mirror, and Witness Instances.

Principal Instance configuration

In the principal server instance configuration window, give the endpoint name and listener port. Default port is 5022. Since am using the same machine with different instance for the mirror set up therefore, listener port should be different to communicate with each other. Click next after giving the endpoint name as Mirroring-Principal. Also select Encrypt Data option.  By selecting this option, data will be sent through this port. It should be selected by default.  Screen shot is given below:- 

Mirror Instance Configuration

• When Next button is clicked, Mirror Server Instance configuration comes as shown below:-

Since principal instance and mirror instance cannot be the same so have to connect to the mirrors, so click Connect button as shown in the figure above.

Once connect button is clicked, following window comes where you can select Mirror Instance, Linux-PC\SQLMIRROR and then Click connect as shown below  in red

• Port number is different since it in the same server but different instance name. Click next to go the Witness Instance configuration window.

Witness Instance Configuration

Clicks connect as shown below

• When Connect Button is clicked, following window comes where Witness Instance, Linux-PC\SQLWITNESS is selected from the Server Name dropdown menu, and then click Connect button  as shown below

 Once you click connect, following screen comes up with Mirror Instance details:-

Port number is different since it in the same server but different instance name. Click next to go the Witness Instance configuration window.

Witness Instance Configuration

Click connect.

The following window then appears, where Witness Instance, Linux-PC\SQLWITNESS is selected from the Server Name dropdown menu, and then click Connect button  as shown below:-

After clicking the connect button, the Port Witness Instance details screen is shown. Click next after giving the name of the endpoint for witness instance:-

Service Accounts

When Next Button is clicked as shown above, Service Accounts window comes where service account for each instance should be added. If the servers are in the same domain or trusted domain which has SQL Login accounts, enter the service account. Since my machine is a local so do not need the service account and not going to use the service account and it is empty as shown below. Click next after entering the service account in the following window:-

Database Mirroring Complete then appears. Click Finish button to complete the Mirror Initiation.

Then the Endpoint Configuration window is shown as below:-

After closing this window, another window with database mirroring configuration comes where all the details are showed.

Database mirroring can be start now or later by selecting Start Mirroring or Do Not Start Mirroring, shown below:-

I am going to start the mirroring now and it is an automatic failover with synchronous transfer.

Now I have started the mirroring by clicking the Start Mirroring Button and has the following screen shot:-

Mirroring has started and Start Mirroring Button is disabled. Mirroring session can be Paused by clicking on Pause button, mirroring can be removed, can be failed over to Mirror Instance by clicking Failover, and click OK to close this window,  as shown in the figure above in red.  

 Failing Over Primary Instance Database to Mirror

Database mirroring failover can be of three different types and they are automatic, manual and forced. Automatic failover occurs in a situation when the principal Instance lost the communication with the witness server. In this case, principal instance and mirror instance changes the mirroring role. Principal instance becomes the mirror and mirror instance become principal. Manual failover occurs when there is change control for applying patches or upgrade or any other changes that affect the principal server that needs the database engine to stopped and restarted or if the server needs reboot. Force failover is a perfect scenario for disaster recovers. This failover occurs only when the power fails or database server went down, than this failover occurs to bring the system up and running.

Demo to Failover Database Mirroring

Go to LINUX-PC instance and the go to the DBMirroring database and then right click on the database and select mirror as shown below:-

 After clicking Mirror option, following window comes, click on Fail Over button to failover the Principal Instance LINUX-PC to LINUX-PC\SQLMIRROR Instance. Once you click this, Principal Instance become mirror and mirror instance become principal.

Once failover button is clicked, the next window prompt us to confirm if we want to switch the Instance. Click Yes.

Now the partner roles are changed. Go to principal instance LINUX-PC now and check the database and the DBMirroring is in Restoring mode and LINUX-PC\SQLMIRROR has become the principal. We can switch back again if needed.

Monitoring Database Mirroring

 To monitor database mirroring is an essential step to make sure that the database mirroring is working fine. Stored procedure, catalog views and GUI are used to monitor database mirroring. I am going to use GUI to do database mirroring.

Go to the DBMirroring database from Linux-PC\SQLMIRROR, Right click on the Database, Tasks and then click on Launch Database Mirroring Monitor, as shown below:-

Database Mirroring Monitor window comes as shown below where we can see the status and warning for the database mirroring. In the status tab, service instance, Current role, Mirroring state, witness connection and mirroring history are shown. In warning tab, thresholds warning information are given as shown below:-

Conclusion:

Database mirroring is one of the options for high availability in SQL Server 2008. This is how we can set up database mirroring between two instances and how we can failover to another instance. It really depends on the need of the business which high-availability solutions can be implemented.