However, SQL Server 2008 does not address the issue with the restore operation having to almost complete before the DBA is informed that the database cannot actually be restored. In fact, this issue is unlikely to be addressed given the nature of how restore works—see the
SQL Q&A column from the October 2008 issue
for more details.
If it's possible that a database needs to be restored on Standard Edition (or a lower edition), it's imperative that the DBA either disallows these features or uses the DMV regularly to avoid getting a nasty surprise when trying to restore in a critical situation.
QI've implemented synchronous database mirroring and was under the impression that this meant the mirror database is always synchronized with the principal database. However, I've occasionally seen the mirror state become SYNCHRONIZING instead of SYNCHRONIZED, as well as when we first set up a mirror. Why does this happen?
ABefore I answer the question, I'll define the SYNCHRONIZED and SYNCHRONIZING database mirroring states. In a nutshell, database mirroring works by continually sending physical transaction log records between the SQL Server instance hosting the principal database and the instance hosting the mirror database. If there are no transaction log records waiting on the principal to be sent to the mirror, the mirroring state is SYNCHRONIZED (in other words, the two databases are in sync). If there are transaction log records that have not yet been sent to the mirror (the mirror database is "behind" the principal), the mirroring state is SYNCHRONIZING.
The method for initializing a database mirror is to take a full database backup, plus at least one transaction log backup, and restore them (using WITH NO_RECOVERY) on the mirror. Then when mirroring is enabled, the mirroring state is initially SYNCHRONIZING. This is because there may have been some transaction activity on the principal since the last log backup that was restored on the mirror. This is very likely for a database with a constant workload.
The trick to minimizing the time in which the mirroring state stays SYNCHRONIZING is to try to make the mirror database as up-to-date as possible using log backups. However, for a database with a constant workload, this may be very difficult and so the mirroring state will initially be SYNCHRONIZING for some time. Once the mirror catches up, the mirroring state will change to SYNCHRONIZED.
After that point, it is possible for the mirror database to again fall behind the principal, in which case the mirroring state will drop back to SYNCHRONIZING. One cause of this could be if the mirror and principal are unable to communicate for a period of time. Or it could happen if the principal is generating the transaction log faster than the log can be sent to the mirror. In either case, and depending on how database mirroring is configured, the principal database may continue to process transactions and a queue of transaction log records will build up (called the SEND queue). This must be sent to the mirror in order for it to catch up. Until the mirror database has caught up, the mirroring state will remain SYNCHRONIZING.
If the network link is unreliable between the principal and mirror, you may see the mirroring state switching back-and-forth between SYNCHRONIZING and SYNCHRONIZED. You can find a full discussion of these states in the whitepaper
Database Mirroring in SQL Server 2005
QWe have synchronous database mirroring set up for our main application database and our monitoring shows that the redo queue on the mirror is usually near-zero. We'd like to use the mirror database for reporting and consistency checking so we can make better use of the redundant hardware. We know this is possible, but are there any drawbacks to doing this that we should know of?
AThis is a pretty common practice, but there are a few drawbacks that you may need to watch out for.
The first issue is licensing. The license for the SQL Server instance hosting the mirror database is free if the instance is only used for that purpose. As soon as you create a database snapshot on the mirror database or do anything else with that SQL Server instance, you must purchase a license.
As far as consistency checking is concerned, running them on the mirror database (through a database snapshot) doesn't give you any guarantees about the consistency of the principal database. Only transaction log records are mirrored between the databases, so if the I/O subsystem corrupts a page in the principal database, that corruption will not be mirrored on the mirror database. This means that a consistency check of the mirror database would not see that corrupt page on the principal database.
Reporting is a good use of a database mirror. The first issue that people run into is how to refresh the database snapshot that the reports are running against. A database snapshot cannot be refreshed so a new snapshot must be created and the reporting application must connect to the new snapshot. This requires extra logic to be built into the reporting application. The second issue is determining how the reporting application should behave in the event of a mirroring failover—should it remain connected to the new principal or should it move to the new mirror? Going into details on this is beyond the scope of this article.
With any extra use of the mirror database, the main concern is the potential for causing performance problems on the mirror. Creating a database snapshot adds extra I/O load in that the first time a page changes in the mirror database, it must be pushed into the database snapshot. Also, any workload you then place on the database snapshot increases the I/O load on the mirror database—as many of the pages read from the database snapshot will really be read from the mirror database, as they will not have changed since the snapshot was created.
This extra I/O load on the mirror database can slow down the replaying of transaction log records and in turn lead to a backlog. This backlog is called the REDO queue and is the amount of transaction log that must be replayed before the mirror database can come online after a failover. The larger the I/O load on the mirror database from the database snapshot, the larger the REDO queue will potentially grow and the longer the database will be unavailable after a failover.
You did mention that your REDO queue length is usually near-zero so this may not be an issue for you, but it is definitely something to watch out for so you don't end up compromising the database's availability in return for the increased ability to run reports.
Paul S. Randal
is the Managing Director of
and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. Paul wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development. Paul is an expert on disaster recovery, high-availability, and database maintenance and is a regular presenter at conferences around the world. He blogs at
, and you can find him on Twitter at