When deciding to mirror a device, you must weigh such factors as the costs of system downtime, possible reduction in performance, and the cost of storage media. Reviewing these issues will help you decide what to mirror—just the transaction logs, all devices on a server, or selected devices.
You cannot mirror a dump device.
You should mirror all default database devices so that you are protected if a create or alter database command affects a database device in the default list.
In addition to mirroring user database devices, you should always put their transaction logs on a separate database device. You can also mirror the database device used for transaction logs for even greater protection.
To put a database’s transaction log (that is, the system table syslogs) on a different device than the one on which the rest of the database is stored, name the database device and the log device when you create the database. You can also use alter database to add a second device and then run the system procedure sp_logdevice.
Here are three examples that involve different cost and performance trade-offs:
Speed of recovery – you can achieve nonstop recovery when the master and user databases (including logs) are mirrored and can recover without the need to reload transaction logs.
Storage space – immediate recovery requires full redundancy (all databases and logs mirrored), which consumes disk space.
Impact on performance – Mirroring the user databases (as shown in Figure 17-2 and Figure 17-3) increases the time needed to write transactions to both disks.
