Mirroring

Database Mirroring Architecture in Sql Server

Database mirroring involves a principal server role, a mirroring server role, and an optional witness server (shown in Figure 13-10). The database on the principal server is actively used, and as transactions are applied to the principal server’s database, they are also submitted to the mirror server’s database. The mirror server database is left in a recovering state, where it receives changes made on the principal copy, but it cannot be used while the principal mirror database is still available.

Figure 13-10. Database Mirroring Basic Architecture
If an issue occurs on the principal server database, which then makes it unavailable, the mirror server can take on the role of the primary database. When the other database (the original principal) comes back online, the former primary database takes on the mirrored server database role, receiving transactions from the principal server.   

Failover from principal to mirror databases can be initiated manually or automatically, depending on the database mirroring mode (which is described in detail later in this chapter). If automatic failover is required, a third server must join the mirroring session as a witness server. The witness server monitors the principal and mirror servers. In a database mirroring session that consists of these three servers (principal, mirror, and witness), two of the three connected servers can make the decision (called a quorum) as to whether or not an automatic failover should occur.

Database mirroring sessions can run in a synchronous or asynchronous mode. When in synchronous mode, transactions written to the principal server database must also be written to the mirror server database before any containing transaction can be committed. This option guarantees data redundancy, but has a trade-off of potential performance degradation.

Asynchronous mode allows transactions to commit on the principal database mirroring session before actually writing the transaction to the mirror server database. This option allows for faster transaction completion on the principal database, but also poses the risk of lost transactions if a failure occurs on the principal server before updates can be reflected on the mirror database.       
 

With regards to application and client connectivity to the principal database, SQL Server 2005 maintains metadata that allows .NET application redirection in the event of a failover. Specifically, you can use the SQL Native Client in your .NET code to connect to the mirrored database and the code can be configured such as to be aware of the locations of the mirrored databases. With the SQL Native Client, you can designate both the principal and mirroring SQL Server instances in the connection string, allowing the application connection to be transparently redirected to the newly active principal when the primary database is unavailable.

Database Mirroring modes

Table 13-11 summarizes the different database mirroring modes and the pros/cons for each of the modes.

Mode Name	Synchronous or Async	Witness present?	Pro/Con
High- Availability	Synchronous	Yes	Supports automatic failover and is the most hardened. If mirror disappears but principal and witness are connected, operations continue. Mirror catches up when it comes back online.
High-Protection	Synchronous	No No	automatic failover and if mirror unavailable, principal database goes offline.
High- Performance	Asynchronous	Yes	Fast performance but data not guaranteed on the other side and no automatic failover. Useful for low-bandwidth connections between mirror and principal since performance is best.

Requirements

·     SQL Server 2005 with SP1 or SQL Server 2008
·    Verify that there are no differences in system collation settings between the  principal and mirror servers.
·    Verify that the local windows groups and SQL Server logins definitions are the same on both servers
·    Verify that external software components are installed on both the principal and the mirror servers.
·    Verify that the SQL Server software version is the same on both servers.

·     Database should be in FULL recovery model.

·     Service Broker should be enabled on the database.

·     Both the servers should have either Enterprise or standard editions.

·     Both the servers should have same edition.

·     Witness server can have any edition.

·     Database name should be same

·     Collation should be same

·    You can’t mirror more than 10 db in 32 bit server, because an instance can only have one end point which could be a bottle neck if there are lots of db’s in that instances.

·     You cant attach or detach

·     Mirroring Ports shuld be open and functionable

·     Service Account should be same  for sql and sql agent on Instance

·     Cross DB transactins and distributed transactions not permitted.

Multiple Mirror servers are possile in Denali version and we can read also.

Note: Only committed transactions are transferred to the mirror database

          Does not support FILESTREAM.

Warm/ Hot Standby Solution: When a database mirroring session is synchronized; database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

Advantages:

·         Hardware and software upgrades can be simplified.

·         It increase the data protection

·         Increases the Database availability.

·         Cost of the Database mirroring is less compare to clustering.

·         It is robost and efficiant than log shipping and replication.

·         It supports the full text

·         Failover is fast compare to cluster within 3 sec

·         Can use mirroring server as host database for other applications.

Disadvantages or Database mirroring has a few drawbacks:

·  It improves the availability of your databases during hardware or software upgrades.

·  By default, it does not support reporting. If you want to use the mirror database for reporting purposes, you can create a database snapshot on the mirror database and use the snapshot for reporting purposes (Denali we can use).

·  Only user databases in full recovery model can be used for database mirroring. Simple or bulk-logged recovery model cannot be used. This also means that database mirroring will break if the recovery model for a mirrored database is changed from full to simple or bulk-logged.

·  System databases (master, model, msdb, tempdb) cannot be mirrored.

· Database mirroring does not support FILESTREAM. This means that databases with a FILESTREAM filegroup cannot be mirrored, nor can you create a FILESTREAM filegroup on a principal database.

·  Database mirroring does not support cross-database transactions or distributed transactions.

·  Like log shipping, database mirroring provides redundancy at the database level and not at the entire SQL Server instance level, like SQL Server failover clustering.

· Like log shipping, database mirroring only applies to changes that are captured in the transaction log or the initial full backup of the principal database. Any database objects such as logins, jobs, maintenance plans, SSIS packages, and linked servers that reside outside the mirrored database need to be manually created on the mirror server.

·  Unlike log shipping, database mirroring does not support having multiple copies for the same principal database. This means you can only have one mirror database for each principal database(Denali it support multiple mirror server).

·  The mirror database name needs to be same as the principal database name.

·  If the mirror database fails, the transaction log space on the principal database cannot be reused even if you are taking transaction log backup. This means you need to either have enough space for the transaction log to grow, and bring the mirror database online before the log fills up the available disk space and brings the principal database to a halt, or break the database mirroring.

·  Depending on the workload, your environment, and the database mirroring configuration, database mirroring may affect application performance. It can also place a large demand on the network while the transaction log records are being sent.

· you cannot directly access the mirror database or back up the mirror database

·   Database mirroring requires that the database use the full recovery model. You can’t use the simple or bulk-logged recovery models.

·   Applications that connect to a database with ADO.NET or the SQL Native Client (SNAC) can automatically redirect connections when the database fails over to the mirror.

Database mirroring continuously transfers the transaction log records (not the transaction log backups) from the principal database and applies them to the mirror database.

Synchronous database mirroring (high-safety mode) is supported by SQL Server 2008 Enterprise, Developer, and Standard Editions. If you implement synchronous database mirroring using SQL Server 2008 Standard Edition, then you cannot create a database snapshot on the mirror database for reporting purposes, as database snapshots are not supported by SQL Server 2008 Standard Edition.

Enhancements in 2008:

·          SQL Server 2008 database mirroring has some exciting new features, one of which is called log compression (commpress ratio 12.5%). As the name suggests, this feature compresses the transaction log records on the production server before sending them to the mirror server. This feature reduces network bandwidth and increases application performance and throughput at the cost of increased CPU usage.

·         Another new feature is the capability to automatically repair a page. This means that if the production or mirror server is unable to read a data page due to certain types of errors like 823(syclic redandancy fail error) , 824(logical error), or 829(restore pending). The automatic page repair feature is not available in SQL Server 2008 Standard Edition. It is available only in SQL Server 2008 Enterprise and Developer Editions.

·         Page read ahead during undo phase

·         Licence not required for mirror server but required for principal.

·         Principal can be standard and mirror server enterprise but not vise versa.

·         Database snapshots on mirror server then we need to take licence.

·         we can establish mirroring 32 or 64 bilt.

Mirroing States:

PRINCIPAL	MIRROR	Discription
	RESTORING
	IN RECOVERY	WHILE CONFIGURING
Principal syncronized	Mirror syncronized/restoring	HIGH SAFETY WITH AUTOMATIC FAILOVER
Principal suspended	Mirror suspended/restoring	If Paused principal
Mirror syncronized/restoring	Principal syncronized	Principal Failover

Principal: In a database mirroring configuration, there are two copies of a single database, but only one copy is accessible to the clients at any given time. The copy of the database that the applications connect to is called the principal database. The server that hosts the principal database is known as the principal server.

Mirror: The mirror is the copy of the principal database. The mirror is always in a restoring state; it is not accessible to the applications. To keep this database up-to-date, the log records are transferred from the principal and applied on the mirror database. The server that hosts the mirror database is known as the mirror server.

Witness: The optional witness is an SQL Server instance in a database mirroring configuration. It is separate from the principal and mirror instances. When database mirroring is used in synchronous mode, the witness provides a mechanism for automatic failover.

Send Queue: While sending the log records from the principal to the mirror, if the log records can’t be sent at the rate at which they are generated, a queue builds up at the principal. This is known as the send queue. The send queue does not use extra storage or memory. It exists entirely in the transaction log of the principal. It refers to the part of the log that has not yet been sent to the mirror.

Redo Queue: While applying log records on the mirror, if the log records can’t be applied at the rate at which they are received, a queue builds up at the mirror. This is known as the redo queue. Like the send queue, the redo queue does not use extra storage or memory. It exists entirely in the transaction log of the mirror. It refers to the part of the hardened log that remains to be applied to the mirror database to roll it forward. 

Endpoint: An endpoint is a SQL Server object that enables SQL Server to communicate over the network. It encapsulates a transport protocol and a port number.

Database Mirroring Overview

Database mirroring was officially supported with SQL Server 2005 SP1. It is available in Enterprise and Developer Editions, and with some restrictions in Standard Edition.

In database mirroring, an originating SQL Server 2005 instance continuously sends a database's transaction log records to a copy of the database on another standby SQL Server instance. The originating database and server have the role of principal, and the receiving database and server have the role of mirror. The principal and mirror servers must be separate instances of SQL Server 2005.

In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database's log buffer in memory, and then flushed to disk (or 'hardened') as quickly as possible. In database mirroring, as the principal server writes the principal database's log buffer to disk, it simultaneously sends that block of log records to the mirror instance.

When the mirror server receives a block of log records, it places the log records first into the mirror database's log buffer and then hardens them to disk as quickly as possible. Those transaction log records are later replayed on the mirror. Because the mirror database replays the principal's transaction log records, it duplicates the database changes on the principal database.

The principal and mirror servers are each considered a partner in the database mirroring session. A database mirroring session consists of a relationship between the partner servers when they mirror a database from one partner to another. A given partner server may have the principal role for one database and a mirror role for a different database.

In addition to the two partner servers (principal and mirror) a database mirroring session may have an optional third server, called the witness. The witness server's role is to enable automatic failover. When database mirroring is used for high availability, if a principal server suddenly fails, if the mirror server has confirmation from the witness, it can automatically take on the role of principal and make its database available within a few seconds.

Some important items to note about database mirroring:

·    The principal database must be in the FULL recovery model. Log records that result from bulk-logged operations cannot be sent to the mirror database.

·  The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.

·    The mirror database must have the same name as the principal database.

·    Because the mirror database is in a recovering state, it cannot be accessed directly. You can create database snapshots on the mirror to indirectly read the mirror database at a point in time. (See 'Database Mirroring and Database Snapshots' later in this paper.)

Database Mirroring Operating Modes

Operating Mode	Transaction safety	Transfer mechanism	Quorum required	Witness server	Failover Type
High Availability	FULL	Synchronous	Y	Y	Automatic or Manual
High Protection	FULL	Synchronous	Y	N	Manual only
High Performance	OFF	Asynchronous	N	N/A	Forced only

High Availability Operating Mode

The High Availability operating mode supports maximum database availability with automatic failover to the mirror database if the principal database fails. It requires that you set safety to FULL and define a witness server as part of the database mirroring session.

The High Availability mode is best used where you have fast and very reliable communication paths between the servers and you require automatic failover for a single database. When safety is FULL, the principal server must wait briefly for responses from the mirror server, and therefore the performance of the principal server may be affected by the capability of the mirror server. Because a single database failure will cause an automatic failover, if you have multi-database applications you want to consider other operating modes. (See "Multi-Database Issues" in the Implementation section later in this paper.)

In the High Availability mode, database mirroring is self-monitoring. If the principal database suddenly becomes unavailable, or the principal's server is down, then the witness and the mirror will form a quorum of two and the mirror SQL Server will perform an automatic failover. At that point, the mirror server instance will change its role to become the new principal and recover the database. The mirror server can become available quickly because the mirror has been replaying the principal's transaction logs and its transaction log has been synchronized with the principal's.

Also, SQL Server 2005 can make a database available to users earlier in the recovery process. SQL Server database recovery consists of three phases: the analysis phase, the redo phase, and finally the undo phase. In SQL Server 2005, a newly recovered database can become available for use as soon as the redo phase is finished. Therefore if a database mirroring failover occurs, the recovered new principal database can become available for use as soon as it finishes the redo phase. Because the mirror database has been replaying transaction log records all along, all the mirror serves has to do is finish the redo process, which normally can be accomplished in seconds.

High Performance Operating Mode

In the High Performance operating mode, transactional safety is OFF, and the transfer of log records is asynchronous. The principal server does not wait for an acknowledgement from the mirror that all transaction log records have been recorded on the mirror. The mirror does its best to keep up with the principal, but it is not guaranteed at any point in time that all the most recent transactions from the principal will have been hardened in the mirror's transaction log.

Since the safety is OFF, automatic failover is not possible, because of possible data loss; therefore, a witness server is not recommended to be configured for this scenario. If the witness is set, a quorum is required. If the witness is not set, then a quorum is not required. Manual failover is not enabled for the High Performance mode. The only type of failover allowed is forced service failover, which is also a manual operation:

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable.

1.Run the following command in mirror server

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

2.Transfer the logins.

3.Make the database available to the users and applications.

High Protection Operating Mode

The High Protection operating mode also has transactional safety FULL, but has no witness server as part of the mirroring session. The principal database does not need to form a quorum to serve the database. In this mode only a manual failover is possible, because there is no witness to fill the tie-breaker role. An automatic failover is not possible, because if the principal server fails, the mirror server has no witness server with which to form a quorum.

Since there is no witness server defined, automatic failover cannot occur and a principal server which suddenly loses its quorum with the mirror does not take its database out of service.

1.Run the following commands in Mirror server

ALTER DATABASE <dbname> SET PARTNER OFF;  (To break mirroring)

2.Database comes into restoring state run the following command to take it online

RESTORE DATABASE <dbname> WITH RECOVERY

Principal Server Database States

By default, a database mirroring session is set to run in synchronous mode (SAFETY FULL) without automatic failover. To change the transaction safety level to OFF (asynchronous database mirroring), execute the following command on the principal server:

ALTER DATABASE AdventureWorks SET PARTNER SAFETY OFF;

When safety is FULL, the normal operating state for the principal database is the SYNCHRONIZED state.

When safety is OFF, the normal operating state for the principal database starts as the SYNCHRONZING state. Once the mirror has caught up, the state goes to SYNCHRONIZED and stays there regardless of how far behind it is.

A Safety FULL (Synchronous transfer) example sequence of events (synchronous database mirroring or high-safety mode)

Server A	Server B
Principal, Synchronized	Mirror, Synchronized
A multi-statement transaction begins containing data modifications
Transaction log records for the principal database are inserted into a transaction log buffer
The transaction log buffer is written to disk (hardened), the block of log records is sent to the mirror and the principal records the log block's mirroring_failover_lsn and the principal waits for confirmation from the mirror	The mirror server receives the block of log records into a transaction log buffer
	The mirror writes the log buffer to disk, records the mirroring_failover_lsn, and notifies the principal that the log block has been hardened
The principal receives notification that the block of log records was written to disk by the mirror	Mirror continues to replay transaction log records in its REDO queue
A COMMIT for the transaction is entered into the transaction log buffer
	Mirror replays transaction log records including the COMMIT, from its REDO queue, changing data pages
New transactions are written to the principal's log buffer.

·   The key point in the sequence above is that when safety is FULL, the principal server hardens its log buffer and sends a copy of the log records from the buffer to the mirror, both at the same time. It then waits for the completion of its own I/O and the I/O of the mirror before considering the transaction complete. When the principal receives its response from the mirror, the principal can then proceed to the next hardening.

·    Despite the close coordination between principal and mirror when safety is FULL, database mirroring is not a distributed transaction and does not use a two-phase commit.

·    In database mirroring, you have two transactions being played out on two servers, not one transaction distributed across them.

·     Database mirroring does not use the partner servers as resource managers in a distributed transaction

·    Database mirroring transactions do not go through prepare and commit phases.

·     Most importantly, unlike a distributed transaction, failures to commit on the mirror will not cause a transaction rollback on the principal.

·    When safety is OFF, the principal does not wait for acknowledgment from the mirror server, so the number of committed transactions on the principal can get ahead of the mirror, as shown in Table 10.

Table 10: A Safety OFF (Asynchronous transfer) example sequence of events. (asynchronous database mirroring or high-performance mode)

Server A	Server B
Principal, Synchronizing	Mirror, Synchronizing
A multi-statement transaction begins containing data modifications
Transaction log records for data modifications are written to a transaction log buffer
The transaction log buffer is flushed to disk (hardened), the block of log records is sent to the mirror and  the principal records the block's mirroring_failover_lsn	The mirror server receives the block of log records into a transaction log buffer
A COMMIT for the transaction is entered into a transaction log buffer, along with other transaction activity	The mirror writes the log buffer to disk, records the mirroring_failover_lsn, and notifies the principal that the log block has been hardened
The transaction log buffer is hardened to disk and the log block containing the COMMIT is sent to the mirror	Mirror continues to replay transaction log records in its REDO queue
The transaction is considered committed	The mirror server receives the block of log records into a transaction log buffer
	The mirror hardens the log block by writing it do disk and records the mirroring_failover_lsn  and notifies the principal that the log block has been hardened

Note: The mirroring_failover_lsn indicates the log sequence number (LSN) of the latest transaction log record that is written to disk.

Database Mirroring Role Change

Database mirroring failover is an issue that can be considered from the standpoint of the database mirroring servers or the application. From the standpoint of the database mirroring servers, failover is the conversion of the mirror server into a principal server and the use of the newly recovered database as the principal database in the session. The failover may be automatic, manual, or forced.

·      Automatic - occurs only in the High Availability mode (safety is FULL and a witness is part of the session

·      Manual - occurs in the High Availability and High Protection operating modes (safety is FULL) and the partner databases are both SYNCHRONIZED.

·      Forced service (allow data loss) - used primarily in the High Performance (safety OFF) mode to immediately and manually recover the mirror database.

When safety is FULL, the best way to reverse the roles of the servers is to use manual failover, not forced service.

Mirroring States:

SYNCHRONIZING: Indicates that the mirror database is trying to catch up with the principal database. This is typically seen when you just start database mirroring or in high-performance mode.

SUSPENDED: Indicates that the mirror database is not available. During this time the principal is referred to as running exposed, as it is processing transactions but not sending any transaction log records to the mirror.

PENDING_FAILOVER: Indicates the state that the principal goes through before transitioning to the mirror role.

DISCONNECTED: Indicates that the partners are unable to communicate with each other.

Configuring Mirroring – Steps

1.       Configuring security and communication between instances

a.       Configuring endpoint

b.       Creating logins for other servers service accounts

c.       Grant connect permission to this logins on endpoints

2.       Create mirror database

a.       Take full and T.Log backup from principle server and restore it in mirror server with

          NORECOVERY.

3.       Establish mirroring session using ALTER DATABASE command

Default port no is

5022 is default port number for mirroring in sql server

Why we are using Full Recovery Model for mirroring:

In order to use Mirroring you need to be in Full recovery mode. Once in FULL mode you have to take regular log backups in order to reuse the log space again. Truncating or backing up the log does not shrink it you need to use DBCC SHRINKFILE to shrink the log file. Since you don't need the data I suggest this. Set a SQL Agent job to backup the log file every 5 minutes and just write it to the same backup file each time but use the WITH INIT option to overwrite it. Once you have take a full backup and then at least one log backup you can shrink the log file down to a reasonable size but leave enough room for the transactions that will occur in between the 5 minute process. You can backup the log more often if the file import process is very intensive.

Steps

1.       Go to SSMS

2.       Connect 2 or 3 instances

For example

          CLASS2\sql2K8                  -        Principal

          CLASS2\FIRST                   -        Mirror

          CLASS2\THIRD                   -        Witness

3.       Note down the above instances service accounts

          a.       CLASS2\SQL2K8                 (CLASS2\KAREEM)

          b.       CLASS2\FIRST                   (CLASS2\KAREEM)

          c.       CLASS2\THIRD                   (CLASS2\SQLUSER)

4.Verify both Principal and Mirror has same editions or not i.e.  Enterprise or Standard.

  By running the following command in both the servers

  select serverproperty('edition')

5.Go to Principal server and create a sample database (In real time environment we have to use existing database) with the name OptimizeSQL

6.Create one sample table in the database with some rows.

7.Take FULL and Transaction Log Backup of OptimizeSQL database in principal server.

          usemaster

         

          backup database OptimizeSQL todisk='\\Class2\backups\OptimizeSQL.bak'

         

          backup log OptimizeSQL todisk='\\Class2\backups\OptimizeSQL.bak'

8.Go to Mirror Server and restore database by using the Recovery State WITH NORECOVERY

Specifically, monitoring a mirrored database allow us to know:

• Verify that mirroring is functioning.
• Basic status includes knowing if the two server instances are up, that the servers are connected, and that the log is being moved from the principal to the mirror.
• Determine whether the mirror database is keeping up with the principal database.
• During high-performance mode, a principal server can develop a backlog of unsent log records that still need to be sent from the principal server to the mirror server. Furthermore, in any operating mode, the mirror server can develop a backlog of unrestored log records that have been written to the log file but still need to be restored on the mirror database.
• Determine how much data was lost when the principal server instance becomes unavailable during high-performance mode.
• You can determine data loss by looking at the amount of unsent transaction log (if any) and the time interval in which the lost transactions were committed at the principal.
• Compare current performance with past performance.
• When problems are occurring, a database administrator can view a history of the mirroring performance to help in understanding the current state. Looking at the history can allow the user to detect trends in performance, identify patterns of performance problems (such as times of day when the network is slow or the number of commands entering the log is very large).
• Troubleshoot the cause of reduced data flow between mirroring partners.
• Set warning thresholds on key performance metrics.
• If a new status row contains a value that exceeds a threshold, an informational event is sent to the Windows event log. A system administrator can then manually configure alerts based on these events. For more information, see Using Warning Thresholds and Alerts on Mirroring Performance Metrics.

Using Database Mirroring Monitor

We can monitor the following features

·         Unsent Log     (at principal)

·         Un restored Log (at mirror)

·         Transaction Rate

·         Commit Overhead (Transactions applied rate at mirror)  

         

Ex:  Go to principal server and run the following query

                   use OptimizeSQL

go

declare @n int=100

while @n<=1000000

begin

insert emp values(@n,'Rajesh',60)

set @n+=1

end    

                  b. Right click on OptimizeSQL db --> Tasks -->Launch Database Mirroring Monitor

                   c. Select "Database Mirroring Monitor"

                   d. Click on Register Mirror databases

                   e. Click on Connect and select Mirror Server

                   f. Select the database OptimizeSQL --> OK

                   g. Observe the parameters by refreshing (F5) the monitor.

  

To start System Monitor in Windows

On the Start menu, point to Run, type perfmon in the Run dialog box, and then click OK.

Key principal server counters:

·      Log Bytes Sent/sec: Indicates the rate at which the principal is transferring transaction log records to the mirror.

·      Log Send Queue KB: Indicates the total number of transaction log kilobytes that have not been sent to the mirror yet.

·      Transaction Delay: Indicates the delay in milliseconds spent waiting for the commit acknowledgment from the mirror. This counter is useful for determining whether database mirroring is impacting performance on the principal server.

·      Log Compressed Bytes Sent/sec: Indicates the number of compressed bytes of transaction log sent in the last second. To find the factor by which the transaction log stream has been compressed, also referred to as log compression ratio, divide the Log Bytes Sent/sec by Log Compressed Bytes Sent/sec.

·      Log Bytes Sent from Cache/sec: Indicates how much of the transaction log bytes being sent from the principal to the mirror is being read from the principal’s in-memory transaction log cache. Key mirror server counters:

·      Redo Bytes/sec: Indicates the rate at which log bytes are being rolled forward on the mirror database.

·      Redo Queue KB: Indicates the total number of transaction log kilobytes that have not been rolled forward to the mirror database yet. To estimate the time it will take the mirror to redo the log, divide Redo Queue KB by Redo Bytes/sec.

·      Log Bytes Received/sec: Indicates the rate at which the log bytes are received from the principal. To estimate the time it will take the mirror to catch up with the principal, divide Log Send Queue KB by Log Bytes Received/sec.

·      Log Compressed Bytes Received/sec: Indicates the number of compressed transaction log bytes received in the last second.

·      Log Bytes Redone from Cache/sec: Indicates the number of redone transaction log bytes that were read from the mirror’s in-memory transaction log cache.

Mirroring states

          1.Synchronizing

          2.Synchronized

          3.Disconnected (If mirror or principal failed)

          4.Suspended (If the principal is un available or unable to send transactions to mirror)

          5.Pending Failover – If the unsent log is >0.

State ID State Name           State Description

0                  Null               Notification State unknown

1                                     Synchronized Principal with Witness

2                                     Synchronized Principal without Witness

3                                     Synchronized Mirror with Witness

4                                     Synchronized Mirror without Witness

5                                     Connection with Principal Lost

6                                     Connection with Mirror Lost

7                                     Manual Failover Manual failover started

8                                     Automatic Failover Automatic failover started

9                                     Mirroring Suspended Connection cannot be re-established

10                                    No Quorum Disconnected from partner and witness both

11                                   Synchronizing Mirror Occurs after a failover or when the mirror is not synchronized

12                                   Principal Running Exposed Principal lost connection to witness, no automatic failover

13                                    Synchronizing Principal Synchronizing Principal with Mirror

Monitor Database Mirroring status Using TSQL Statements / Queries

Viewing Mirroring Information

SQL Server 2005 provides catalog views and dynamic management views (DMVs) to view mirroring information. It also provides System Monitor counters to view database mirroring performance information.

Mirroring state, safety level, and witness

The sys.database_mirroring catalog view provides database mirroring information for each mirrored database in the SQL Server instance. Join this view to sys.databases to get the database name. The following query displays the most commonly used mirroring metadata:

SELECT d.name, d.database_id, m.mirroring_role_desc,

       m.mirroring_state_desc, m.mirroring_safety_level_desc,

       m.mirroring_partner_name, m.mirroring_partner_instance,

       m.mirroring_witness_name, m.mirroring_witness_state_desc

FROMsys.database_mirroring m JOIN sys.databases d

ON     m.database_id = d.database_id

WHERE  mirroring_state_desc IS NOT NULL

You can execute this query on either partner (the principal or the mirror), and you will get the same output.

The sys.database_mirroring_witnesses catalog view provides information on the witnesses. You can execute the following query on the witness server to list the corresponding principal and mirror server names, database name, and safety level for all the mirroring sessions for which this server is a witness.

SELECT principal_server_name, mirror_server_name,

       database_name, safety_level_desc

FROMsys.database_mirroring_witnesses

Endpoints

Query the catalog view sys.database_mirroring_endpoints to find useful information regarding the endpoints, such as the status of the endpoint, encryption settings, etc.

The port number used by the endpoint is not in sys.database_mirroring_endpoints; it is in sys.tcp_endpoints.

You can join sys.database_mirroring_endpoints and sys.tcp_endpoints to get the important metadata information regarding the endpoints:

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,

       t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,

       e.connection_auth_desc

FROMsys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t

ON     e.endpoint_id = t.endpoint_id

Monitoring database mirroring performance

To monitor the performance of database mirroring, SQL Server provides a System Monitor performance object (SQLServer:Database Mirroring) on each partner (principal and mirror). The Databases performance object provides some important information as well, such as throughput information (Transactions/sec counter). Following are the important counters to watch.

On the principal:

·  Log Bytes Sent/sec: Number of bytes of the log sent to the mirror per second.

·  Log Send Queue KB: Total kilobytes of the log that have not yet been sent to the mirror server.

· Transaction Delay: Delay (in milliseconds) in waiting for commit acknowledgement from the mirror. This counters reports the total delay for all the transactions in process at that time. To determine the average delay per transaction, divide this counter by the Transactions/sec counter. When running asynchronous mirroring this counter will always be 0.

· Transactions/sec: The transaction throughput of the database.This counter is in the Databases performance object.

·  Log Bytes Flushed/sec: The rate at which log records are written to the disk. This is the log generation rate of the application. It plays a very important role in determining database mirroring performance.This counter is in the Databases performance object.

·  Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks.

On the mirror:

· Redo Bytes/sec: Number of bytes of the transaction log applied on the mirror database per second.

· Redo Queue KB: Total kilobytes of hardened log that remain to be applied to the mirror database to roll it forward.

· Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks on the mirror.

Troubleshooting

If database mirroring is not working, check the following to make sure the configuration is correct:

·         The endpoints are all started. Look at the column STATE_DESC in the catalog view sys.database_mirroring_endpoints. The default state at the time of the creation of the endpoint is STOPPED. You can start the endpoint at the time you create it by explicitly specifying the state as STARTED. Alternatively, you can use the ALTER ENDPOINT command to start the endpoint.

·         The endpoints in the principal, mirror, and witness (if any) have compatible encryption settings. Look at the column IS_ENCRYPTION_ENABLED in the catalog view sys.database_mirroring_endpointsfor the principal, mirror, and witness endpoints. This column has a value of either 0 or 1. A value of 0 means encryption is DISABLED for the endpoint and a value of 1 means encryption is either REQUIRED or SUPPORTED. (For information on how to change the encryption setting of a database mirroring endpoint, see Endpoint encryption earlier in this paper.)

·         The port numbers of the endpoints are the same as the corresponding port numbers specified in the SET PARTNER statements. The port number used by an endpoint is listed in the PORT column of the catalog view sys.tcp_endpoints. The port number specified in the SET PARTNER statements while establishing the database mirroring session is listed in the column MIRRORING_PARTNER_NAME of the catalog view sys.database_mirroring. Make sure that the port number in MIRRORING_PARTNER_NAME matches the port number of the corresponding endpoint. 

·         The endpoints have the correct type and role. The type and role of an endpoint are listed in the columns TYPE_DESC and ROLE_DESC respectively of the catalog view sys.database_mirroring_endpoints. Make sure that the type is DATABASE_MIRRORING. The role should be PARTNER if this endpoint belongs to a server that is used as the principal or mirror. The role should be WITNESS if this endpoint belongs to a server that is used as the witness.

·         The user account under which the SQL Server instance is running has the necessary CONNECT permissions. If SQL Server is running under the same domain user on all the servers involved in the mirroring session (principal, mirror, witness), then there is no need to grant permissions. But if SQL Server runs under a different user on one or more of these servers, then you need to grant CONNECT permission on the endpoint of a server to the login account of the other servers.

·         The remote connection through TCP/IP is enabled on all SQL Server instances involved in the database mirroring session.

·         If you have a firewall between the partners and the witness, make sure the port that is used for database mirroring endpoints is opened through the firewall.

msdb..sp_dbmmonitorresults @database_name = 'Test_SQLServer_Mirroring'

Useful queries while troubleshooting Database Mirroring

To view the database mirroring metadata for each mirrored database.

SELECT DB_NAME(database_id) AS Database_Name,

mirroring_state_desc,

mirroring_role_desc,

mirroring_safety_level_desc,

mirroring_partner_name,

mirroring_witness_name,

mirroring_witness_state_desc

FROM sys.database_mirroring

WHERE mirroring_state IS NOT NULL;

Query the sys.database_mirroring_witnesses catalog view to review database mirroring session information.

SELECT database_name,

principal_server_name,

mirror_server_name,

saftey_level_desc,

partner_sync_state_desc

FROM sys.database_mirroring_witnesses;

Query the sys.database_mirroring_endpoints catalog view to review database mirroring endpoints information

SELECT state_desc FROM sys.database_mirroring_endpoints;

Query to check the associated ports with DB Mirroring

           

SELECT type_desc, port FROM sys.tcp_endpoints;

Query to check the state of the DB Mirroring

           

SELECT state_desc FROM sys.database_mirroring_endpoints

Query to check the service account connect permission on the DB Mirror endpoints

           

SELECT 'Metadata Check';

SELECT EndPnt.name, SvrPerm.STATE,

            CONVERT(nvarchar(38), suser_name(SvrPerm.grantor_principal_id))

            AS GRANTOR,

            SvrPerm.TYPE AS PERMISSION,

            CONVERT(nvarchar(46),suser_name(SvrPerm.grantee_principal_id))

            AS GRANTEE

            FROM sys.server_permissions SvrPerm, sys.endpoints EndPnt

            WHERE SvrPerm.major_id = EndPnt.endpoint_id

            ORDER BY Permission, grantor, grantee;

GO

Query to check the DB Mirror timeout and resetting the DB Mirror timeout

           

SELECT mirroring_connection_timeout FROM sys.database_mirroring

ALTER DATABASE SQLDBPOOL SET PARTNER TIMEOUT 15

To view about Principal, mirror server details and mirroring state run the following query in witness server

Select * from sys.database_mirroring_witnesses

The following table introduces the stored procedures for managing and using database mirroring monitoring independently of the Database Mirroring Monitor.

Procedure	Description
sp_dbmmonitoraddmonitoring	Creates a job that periodically updates the status information for every mirrored database on the server instance.
sp_dbmmonitorchangemonitoring	Changes the value of a database mirroring monitoring parameter.
sp_dbmmonitorhelpmonitoring	Returns the current update period.
sp_dbmmonitorresults	Returns status rows for a monitored database and allows you to choose whether the procedure obtains the latest status beforehand.
sp_dbmmonitordropmonitoring	Stops and deletes the mirroring monitor job for all the databases on the server instance.

Other Tables to monitor

sp_dbmmonitorchangealert (Transact-SQL)

sp_dbmmonitordropalert (Transact-SQL)

sp_dbmmonitorhelpalert (Transact-SQL)

sp_dbmmonitorupdate (Transact-SQL)

The default timeout for communication between the principal, mirror, and witness servers is 10 seconds.

Adjusting the automatic failover time for SQL Server Database Mirroring

ALTER DATABASE dbName SET PARTNER TIMEOUT 20

Note:

To drop mirror database first disable mirror in that databse

Run this in Mirror Server

ALTER DATABASE  <dbname> SET PARTNER OFF

Then you can drop Mirror Server

Endpoint can drop

DROP ENDPOINT <Endpoint name>

Endpoint name can get by running below query

SELECT * FROM sys.database_mirroring_endpoints;

Possible Failures during Database Mirroring

As part of mirroring generally we have two types of errors

•Soft errors

•Hard Errors

Soft Errors

•Errors identified by SQL Server service i.e. sqlservr.exe is called soft error.

•Network errors such as TCP link time-outs, dropped or corrupted packets, or packets that are in an incorrect order.

•A hanging operating system, server, or database state.

•A Windows server timing out.

Hard Errors

•Errors identified by windows and notified to sqlservr.exe file are called hard errors.

•A broken connection or wire

•A bad network card

•A router change

•Changes in the firewall

•Endpoint reconfiguration

•Loss of the drive where the transaction log resides

•Operating system or process failure

Manual Fail Over Error

Could not write a checkpoint record in database ID 8 because the log is out of space. Contact the database administrator to truncate the log or allocate more

space to the database log files.

Msg 3167, Level 16, State 3, Line 1

RESTORE could not start database 'Hospital'.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'Hospital'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Msg 9002, Level 17, State 2, Line 1

The transaction log for database 'Hospital' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

To perform a manual failover

ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;

Forced failover, also referred to as forced service (with possible data loss) is available only in synchronous mode without failover and asynchronous mode.

ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Pausing or Removing Database Mirroring

After configuring database mirroring you can pause, resume, or remove database mirroring. If database mirroring is affecting your application’s performance, you may want to pause a database mirroring session. Pausing a database mirroring session causes the mirroring state to change to SUSPENDED. During this time, the principal will not send any transactions to the mirror, and its principal database’s transaction log will continue to grow even if you have scheduled transaction log backups. The transaction log will not be truncated because it has to send the transactions to the mirror once the database mirroring session is resumed.

To pause the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER SUSPEND;

To resume the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER RESUME;

To remove the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER OFF;

Points to Remember

1. One job is created on both the servers Database Mirroring Monitor Job

2. Default Partner Timeout is 10Sec.

3. How can you say that both the dbs are 100% sync?

          a.We can view unsent log and un restored log values. If both are 0 then 100% sync.

          (In Mirroring Monitor)

          b. We can view mirroring_end_of_log_lsnand mirroring_replication_lsnIN  sys.database_mirroring table. Both should be same.

FAQ: - While configuring Mirroring what errors you have faced?

Answer:

1.       Error No: 1418

Solution:

Before starting Mirroring check that logins are created for the respective instance service accounts. If they are not created create manually and grant connect permission on Endpoint, then start mirroring again.

 To change mirroring timeout (Run in principal server)

Alter database OptimizeSQL SET PARTNER TIMEOUT 30

FAQ: - What are the major new features introduced in Mirroring 2008 version?

1.Auto Page Repair.

Select * from sys.dm_db_mirroring_auto_page_repair

2.Transactions are sending to Mirror by compressing.

To view total bytes send from principal and total bytes received at mirror we can use (run in witness server)

Select * from sys.dm_db_mirroring_connections