What is the performance impact of additional readonly mirrors?

By: Steven Shaughnessy

Abstract: faq: What is the performance impact of additional readonly mirrors?

1) Readonly Mirrors that have INSTANT_SYNCH or AUTO_FAIL_OVER property set to false. After a readonly mirror is created, the only performance impact on the system is when the mirror is synchronized with the primary. Synchronization causes the primary to transfer log file records to the readonly mirror. After new log records have been transmitted to a readonly mirror, the readonly mirror will replay these new log records against its database to bring the readonly mirror up to date with the primary. The impact on the primary mirror is the cost of transferring log file bytes over a tcp/ip connection to a readonly mirror. The cost to the readonly mirror is the cost of receiving the log file bytes from the primary mirror and the cost of replaying those log records against the readonly mirror database. Note that replaying log records against the readonly mirror is typically many times faster than the original transactions that were executed against the primary mirror.

2) Readonly Mirrors that have INSTANT_SYNCH or AUTO_FAIL_OVER property set to true. Unlike #1 above, log records are transmitted to the readonly mirror at the same time they are generated on the primary mirror from executing transactions. There is a little more network traffic for INSTANT_SYNCH/AUTO_FAIL_OVER readonly mirrors because a) A higher number of smaller fragments of log records can be transmitted to keep the readonly mirror log files constantly up to date with the primary. b) For AUTO_FAIL_OVER mirrors extra transmission requests must be sent to the readonly mirror when transactions are commited. The primary mirror cannot commit a transaction unless a majority of AUTO_FAIL_OVER mirrors have all the log records for that transaction. Note that you still must synchronize INSTANT_SYNCH/AUTO_FAIL_OVER mirrors to see the changes from the primary mirror. For these synchronization operations the log files to not need to be updated since the primary mirror is constantly keeping them up to date. The log records just need to be replayed against the readonly mirrors database.

The synchronization of readonly mirrors can be performed manually from the ServerConsole or by calling the DB_ADMIN.SYNCH_MIRROR stored procedure. You can also configure a mirror schedule to have readonly mirrors periodically synchronize with the primary mirrors. These mirror schedules cause the primary mirror to launch a scheduler thread to execute these periodic synchronizations.

Server Response from: ETNASC03