Database mirroring is a reliable, high-availability solution in SQL Server 2008.
It will help you get the database back online with automatic or manual failover to your mirror database. We also discuss the database snapshot, which you can use with database mirroring to read the mirrored databases.
To maximize database availability, you need to minimize planned as well as unplanned downtime.
Database mirroring involves two copies of a single database, residing on separate instances of SQL Server, usually on different computers.You can have separate instances of SQL Server 2008 on the same computer, but that would most likely not fit your high-availability requirements.At any given time, only one copy of the database is available to clients. This copy of the database is known as the principal database.
Database mirroring works by transferring and applying the stream of database log records to the copy of the database. The copy of the database is known as the mirror database.
The principal and mirror servers are each considered a partner in a database mirroring session.
Database mirroring applies every database modification (DML, DDL, and so on) made to the principal database to the mirror database, including physical and logical database changes such as database files and indexes. For automatic failover, a third server called the witness is required.
Database mirroring helps minimize both planned and unplanned downtime as follows:
- It provides ways to perform automatic or manual failover for mirrored databases.
- It keeps the mirrored database up-to-date with the production database,
It enables the mirrored database to be in a remote data center, to provide a foundation for disaster recovery.
You have two options: SAFETY FULL or SAFETY OFF.
As you know, in SQL Server 2008, data changes are first recorded in the transaction log before any changes to the actual data pages are made. The transaction log records are first placed in the database’s log buffer in memory and then flushed to the log file on the disk as soon as possible.
If you choose SAFETY FULL, you are setting up database mirroring in high-safety mode. As the principal server hardens log records of the principal database to disk, it also sends log buffers to the mirror. The principal then waits for a response from the mirror server. The mirror responds to a commit when it has hardened those same log records to the mirror’s transaction log. The commit is then reported to the client.
In this mode, the log transfer process is the same but the principal does not wait for acknowledgment from the mirror that the log buffer is hardened to the disk on a commit.
Database Mirroring and SQL Server 2008 Editions
|Database Mirroring feature||Enterprise edition|
|Partner(principal ose mirror)||*|
|Safety = FULL||*|
|Safety = OFF||*|
|Available during UNDO after failover||*|
High-Availability solutions as is database mirroring are designed to return your systems online as soon as possible from the primary to secondary (failover), and vice versa (failback).
Automatic failover is a database mirroring feature in high-availability mode. The following events occur in an automatic failover scenario:
- The failure occurs: The principal database becomes unavailable. This could be the result of a power failure, a hardware failure, a storage failure, or some other reason.
- The failure is detected: The failure is detected by the mirror and the witness.
- The mirror becomes the principal
Normally, the time taken to failover in this operating mode is very short, usually seconds.
Monitoring Database Mirroring
There are different ways to monitor database mirroring, based on what information you want to track.
To monitor the performance of database mirroring, SQL Server 2008 provides a set of System Monitor performance objects.
Hardware, Software, and Server Configuration
Your mirror server hardware should be identical (CPU, memory, storage, and network capacity) to that of the principal if you want your mirror server to handle the same load as your principal.
You can also use your mirror server for noncritical work so that while it is a mirror, it can be used for some other work.
Make sure that you have the same operating system version, service packs, on both servers.
You need to have the same edition of SQL Server on both partners. On both principal and mirror, make sure that all the SQL Server configurations are identical. Logins and their permissions are very important. All SQL Server logins on the principal must also be present on the mirror server. You can use SQL Server 2008 Integration Services, with the ‘‘Transfer logins’’ task, to copy logins and passwords from one server to another.
Database Mirroring and Clustering
Obviously, the most distinct difference between database mirroring and a Window failover cluster solution is the level at which each provides redundancy. Database mirroring provides protection at the database level, whereas a cluster solution provides protection at the SQL Server instance level. Database mirroring brings the standby database online faster than any other SQL Server high-availability technology and works well in ADO.NET and SQL Native Access Client for client-side redirect.
Database mirroring provides a database redundancy solution using the log-transfer mechanism. The transaction log records are sent to the mirror transaction log as soon as the log buffer is written to the disk on the principal. Mirroring can be configured in either high-performance mode or high-safety mode.
In high-safety mode, if the principal fails, the mirror server automatically becomes a new principal and recovers its database.
© 2011 Atdhe Buja
All rights reserved.