Database Mirroring in SQL Server - Post Mirroring Configuration, Monitoring & Troubleshooting

This post is the sequel of my post Database Mirroring in SQL Server - Configuration. In this post, we'll see about what to do after Database Mirroring is configured, how to monitor a mirroring session and troubleshooting the mirroring configuration.

Post Mirroring Configuration

After Database Mirroring is configured, the following steps have to be taken to ensure a correct service to clients,

• The logins, jobs, SSIS packages etc that are created in Principal Instance must be created in Mirror Instance manually as they are not automatically replicated when mirroring is configured.
• Any new changes made on Principal such as adding new logins, mapping new users to databases should be reflected manually in the Mirror instance too.

Monitoring the Mirroring Session

After mirroring is configured, it can be checked using the following query,

SELECT * FROM sys.database_mirroring

Run the above query in Principal and Mirror to know the status of instance, role etc. The first column of this view is database_id. For first records the values of this view is NULL because database_ids 1,2,3 and 4 are of system databases Master, Model, Msdb and Tempdb respectively. System databases cannot be mirrored, so they have NULL values.

For more information about this view, go here,

http://msdn.microsoft.com/en-IN/library/ms178655.aspx

Database Mirroring Session can be monitored by using a built-in tool named Database Mirroring Monitor. 

Go to Object Explorer, right click the Principal or Mirror Database. Select Tasks and then click Launch Database Mirroring Monitor.

The databases that are configured for Mirroring are registered automatically with this tool. If not, they can be registered manually by clicking Register Mirrored Database on Home screen. From there, Principal and Mirror can be connected and registered. After registering, Database Mirroring Monitor shows the status of Principal and Mirror and their connection status with Witness. It is refreshed automatically for every 30 seconds and shows any unsent log including its size, estimated time to send it from Principal to Mirror etc. There lies a button History on clicking which the synchronization history appears. The history results can be filtered according to the given options.

In the Warnings tab, warnings can be configured by setting desired threshold values for different kinds of warnings. When a configured threshold is exceeded, an event is logged to the Application Even Log. This can be configured as an email alert.

Removing Database Mirroring

Configuring something may be difficult sometimes but removing something is very easy. So is the case with Mirroring. Mirroring can be removed with just one mouse click.

In Object Explorer, right click on the Principal database and click Properties. Under Mirroring tab, click on Remove Mirroring button. You will be asked for a confirmation. Confirm if mirroring has to be removed and it will be removed. Now you've removed Mirroring but you're not done with it completely. In the same Database Properties window, you can still see a Server Network Address for your principal instance. This is because there are endpoints configured and they have to be removed. For this, connect to Principal, Mirror and Witness (if any) and run the following SQL statement.

DROP ENDPOINT <Endpoint-Name>;

With this command, mirroring is removed completely including its endpoints. The Mirror database remains in a RESTORING state as it was created WITH NO RECOVERY option. To bring it into normal state, restore its backup WITH RECOVERY option or just run the following command,

RESTORE DATABASE <Database_Name> WITH RECOVERY

Troubleshooting Database Mirroring Configuration

While configuring Database Mirroring on a single computer with three instances, an error rises with Error Message 1418. This message indicates that the server network address cannot be reached or does not exist. It asks you to verify the server network address and re-issue the command.

For this, there is a simple solution. Go to SQL Server Configuration Manager and right click on SQL Server service and click on Properties. Under Log On tab, select This Account. In the Account Name field, provide your local system Account Name or click on Browse and search your account name by using Check Name button. Provide and confirm your account password and click Apply. It asks for restarting the service, click Yes and the service will be restarted. Do the same procedure for Principal, Mirror and Witness instances.

Now go to Database Properties window and click on Start Mirroring button which starts your mirroring session.

Database Mirroring in SQL Server - Configuration

Configuring Database Mirroring becomes easier when one understands completely about what is mirroring. Please read my previous posts about introduction of Database Mirroring here,

Database Mirroring in SQL Server - Introduction (Part - 1)

Database Mirroring in SQL Server - Introduction (Part - 2)

Mirroring can be configured by using SQL Server Management Studio or Transact - SQL statements. Before configuring mirroring session, ensure the following conditions are met,

• The recovery model of Principal database is FULL.
• Create a mirror database on Mirror server by restoring the latest full backup of Principal Database WITH NO RECOVERY which allows inserting log records into it.
• Take a log backup of Principal database and restore it on Mirror database WITH NO RECOVERY option.
• To setup Database Mirroring, the used login should be of sysadmin fixed role.

The communication between instances that are participated in Mirroring is performed over TCP endpoints. Each instance should have its own Endpoint that listens over a unique TCP/IP port.

Configuring Database Mirroring using SQL Server Management Studio

In the Object Explorer, right click on the Principal Database and select Properties.

Go to Mirroring Page and click the Configure Security button which launches Configure Database Mirroring Wizard. Click Next.

Then comes Include Witness Server page which asks whether you need a Witness instance to be configured. If you want you Mirroring Session, select NO otherwise YES. Right now, I'm selecting YES.

Click Next which takes you to Choose Servers to Configure. Ensure that Witness Server Instance is checked to configure Witness.

Click Next to go to Principal Server Instance configuration. There the Principal Server Instance is already selected. As said earlier in this post, communication is done over TCP Endpoints. Each endpoint has its own TCP/IP port. By default there is a port 5022. If you're setting up Mirroring in your production environment, use another port for security reasons. The name of endpoint is Mirroring by default. You can rename it. There is also one more option for encrypting data that is sent through that endpoint, checked by default. If you want your data not to be encrypted, uncheck that check box.

Click Next to configure Mirror Server Instance. Select an instance that has to be used as Mirror and connect to it. The above specified things imply here also. Make sure that the listener port of Mirror should not be same if both Principal and Mirror are two separate instances on a single machine.

Click Next to configure Witness Server Instance. Even here, the above specified conditions imply.

Click Next to go to Service Accounts page. If the three instances belong to same domain account then no need to specify service accounts, leave the text boxes empty. If not, specify service accounts. This creates logins for each account and grants CONNECT permission on the endpoints. If the three instances are in a Workgroup then also no need to specify any Service Account.

Click Next which takes you to Complete the Wizard page. Verify all the options selected till now and click Finish if everything is OK. Now the endpoints are created for each instance.

Click Close button which takes you to Database Properties dialog box. In this dialog box, the properties of Principal, Mirror and Witness are displayed. SQL Server asks whether to start mirroring or not. If you want it to start mirroring right now, click on Start Mirroring. To start the session later, select Do Not Start Mirroring.

If you click Start Mirroring, it takes a few seconds to start the session and gives you the following screen.

At the bottom of above screen, you can see the current status of your Mirroring Session. You have options to Pause or Remove Mirroring and Failover (this is for Manual Failover).

You can also see your databases in Object Explorer by connecting to your Principal and Mirror Instances.

Now Principal and Mirror are synchronized with each other. The Mirror database will be in Restoring state as it was created WITH NO RECOVERY option. It cannot be connected now. Only Principal can be connected.

To test synchronizing, do some transactions on Principal and refresh the Object Explorer. You can see Principal and Mirror getting synchronized.

To test Automatic Failover, go to SQL Server Configuration Manager and shutdown Principal instance. Refresh the object explorer and you can find Mirror has become Principal. This is done by Witness.

Database Mirroring in SQL Server - Introduction (Part - 2)

In my previous post, I've introduced Database Mirroring with its requirements and operating modes. As said, in this post I'll discuss about the importance and impact of configuring Witness in Mirroring.

Impact of Witness in Database Mirroring

The main job of Witness is monitoring the partners that are participating in Mirroring session. It checks frequently whether both Principal and Mirror are in working state. A single witness can be used to monitor more than one mirroring session.

In High Safety Mode

If a Witness is configured in a Mirroring Session then Quorum is needed. Quorum is a relationship that exists when two or more instances in a Database Mirroring Session are connected to each other. If any instance is not connected then that particular instance loses quorum. If no instance is connected then the whole session loses quorum hence the databases become unavailable.

There are three types of Quorum,

Full Quorum - Consists of both the partners and witness.

Witness to Partner Quorum - Consists of any one partner and Witness.

Partner to Partner Quorum - Consists of only partners.

These quorums are supported in High Safety Mode. When there is a Full Quorum, all the instances act according to their roles until a manual failover is performed.

Suppose Principal is down and loses connection with Witness and Mirror and if Witness and Mirror remain connected then they both form a quorum and an automatic failover occurs. Now Mirror becomes Principal and starts servicing the clients.

Suppose Witness becomes unavailable and steps out of Quorum, the partners remain connected to each other, this is called Partner to Partner Quorum. In this case, a manual failover is possible.

If all the server instances become disconnected from each other, the session is said to have lost quorum. As server instances reconnect to each other, they regain quorum with each other.

• If the principal server reconnects with either of the other server instances, the database becomes available.
• If the principal server remains disconnected, but the mirror and witness reconnect to each other, automatic failover cannot occur because data loss might occur. Therefore, the database remains unavailable, until the principal server rejoins the session.
• When all three server instances have reconnected, full quorum is regained, and the session resumes its regular operation.

To have a detailed explanation of how a Quorum affects database availability, follow the official documentation of Microsoft,

http://msdn.microsoft.com/en-IN/library/ms189902.aspx

In High Performance Mode

While using High Performance Mode, it is recommended to set the Witness to OFF because a witness can be set in this mode but it serves no purpose. If a witness is configured in this mode, it requires a quorum which should consist of at least two instances connected to each other.

If the witness is disconnected from the quorum when any of the partners goes down then the database becomes unavailable. When mirror goes down, the principal should be connected to witness otherwise the principal's database goes offline until mirror or witness rejoins the session. As said earlier, in this mode only forced failover is possible. When a witness is configured, if principal is down then it requires mirror and witness to be connected to each other to perform a forced failover.

For a detailed explanation of Database Mirroring Operating Modes, read Microsoft's documentation,

http://msdn.microsoft.com/en-us/library/dd207006.aspx

How a Witness performs Automatic Failover

When the partners get disconnected from each other, witness ensures whether one of them is available for the clients. In High Safety Mode, if a Mirror loses the connection with Principal after getting synchronized with it completely but still remains connected with Witness then no failover occurs and Principal keeps on serving its database to clients. In this case, the Principal keeps on accumulating log records that have to be sent to mirror once it joins mirroring session.

If Principal gets disconnected from Witness then Mirror server knows that principal is down and immediately initiates an automatic failover. If mirror is disconnected from principal and witness then no failover occurs and the state of Principal is not considered in this case.

To know more about a Witness, follow the following link,

http://msdn.microsoft.com/en-us/library/ms175191.aspx

Database Mirroring in SQL Server - Introduction (Part - 1)

The most important feature for any application is Availability. An end user gets satisfied completely when the application is available at any time. This availability is achieved from the database itself. SQL Server offers many such availability mechanisms. One of such mechanisms is Database Mirroring. I'm going to produce a series of articles on Database Mirroring.

Database Mirroring was first introduced in SQL Server 2005 and it is available in Enterprise, Developer, Business Intelligence and Standard Editions. The main concept of Database Mirroring is maintaining a standby copy for your database and keeping both the databases in a sync so that the copy database serves the clients when the main database faces any kind of problem.

There are three instances participated in Database Mirroring,

Principal - This is where the main copy of database resides.

Mirror - This is where the standby copy of database resides.

Witness - This is an optional instance which initiates automatic failover from Principal to Mirror and is set only in High Safety Operating Mode which I'll discuss later in this post.

When database mirroring is set up, Principal sends the transaction log records to the Mirror over network and Mirrors gets synchronized with Principal. When Principal becomes unavailable due to any problem, Mirror becomes the new Principal and serves the clients. This process is called Failover. When a Witness is configured, it initiates this failover automatically.

In SQL Server, Database Mirroring allows only one mirror for one principal. To have more than one mirror, another availability solution namely AlwaysOn High Availability should be used which supports up to four replicas for one principal. In SQL Server 2012 Books Online it is specified that Database Mirroring will be deprecated. This means in the next release (SQL Server 2014), it is supported but will not be available in a later release of SQL Server. So, Microsoft recommends to use AlwaysOn High Availability.

Requirements for Database Mirroring

Before setting up database mirroring, the following conditions should be satisfied:

• The Principal and Mirror (collectively called Partners) should be of same version and edition of SQL Server.
• The Witness should be of same version and can be of any edition. Even an SQL Express can serve as Witness as it is just a monitoring one.
• Principal, Mirror and Witness should be configured on three different instances.
• The partner databases should be in Full Recovery Model.
• There should be enough disk space on Mirror server for Mirror database.
• The latest backup of Principal database should be restored on Mirror Server WITH NO RECOVERY because this allows inserting transaction log records into the database.
• The log backup of Principal database should be taken and restored to the newly restored Mirror database WITH NO RECOVERY.
• The Principal and Mirror databases should be with same name.

Failovers in Database Mirroring

There are three types of failovers in Mirroring,

Automatic - When Principal is down, the mirror becomes Principal automatically. This is done by Witness.

Manual - When Witness is not configured and Principal is down, Database Administrator has to make the mirror as Principal manually.

Forced - When Principal is down, the mirror is forcibly made the Principal with some data loss.

Automatic and Manual Failovers do not incur data loss and are supported in Synchronous Database Mirroring whereas Forced Failover causes data loss and is supported in Asynchronous Database Mirroring.

Operating Modes of Database Mirroring

There are two operating modes of Mirroring which impacts the application safety and performance. 

• High Safety Mode (Synchronous Database Mirroring)
• High Performance Mode (Asynchronous Database Mirroring)

Let us know what these modes are:

High Safety Mode (Synchronous Database Mirroring)

As said earlier, Principal sends transaction log records to the Mirror constantly. In High Safety Mode, when a transaction is made on Principal, it doesn't commit the transaction immediately. It writes the log to its disk and sends it to the mirror database. Now principal waits until mirror writes the log to its disk and sends an acknowledgement to Principal. After receiving the acknowledgement, principal commits the transaction to the client.

This mode assures full safety for data and keeps both principal and mirror databases in a synchronized state but increases transaction time. This mode requires an optional instance configured, called Witness. When witness is configured, it performs automatic failover from principal to mirror in case of any failure in principal. Even manual failure can be done when witness is present. If a witness is not configured then the database administrator has to perform manual failover in case of any failure in principal.

If Witness is not configured and Principal is down then the Mirror is suspended. In such cases, a Forced Failover can be done to the Mirror with some data loss.

High Performance Mode (Asynchronous Database Mirroring)

In High Performance Mode, when a transaction is made on Principal, it sends the log record to Mirror and commits it immediately without waiting for any acknowledgment from Mirror. There may exist a lag between principal and mirror in writing log records to their disks. When there are a lot of transactions made on principal over a time, it may increase traffic over the netwrok between principal and mirror.

When there is any failure in principal, a forced manual failover should be done to mirror. As said above, if there is a delay in mirror writing all the log records received from principal then failover incurs the loss of that data and brings the mirror in available state. In this mode, Witness should not be configured as there will be issues regarding quorum. 

I'll write about impact of witness in both the modes in later posts of this series.

For introduction about Database Mirroring, read the official documentation of Microsoft,

http://msdn.microsoft.com/en-us/library/ms189852.aspx

The next post of this series is available here,

http://techie-friendly.blogspot.in/2014/08/database-mirroring-in-sql-server_17.html