How Database Mirroring Works
The server of the Principal database is called the Principal server instance. The Principal server instance allows users to connect and update the database. The server of Mirror database is called the Mirror server instance. All changes occurring on Principal database are immediately applied to the Mirror database. Thus, the mirror server instance residing on a different computer becomes a hot standby server.
Failover Event
In case of failure event of the Principal database, the Mirror database will take over the role of Principal database. Once the Principal database is up, it will behave as a Mirrored instance.
Overview of Automatic Database Level Failover
Automatic failover requires a third instance of SQL Server 2005, known as the Witness Instance. Typically, this witness instance resides on a third computer. The Witness instance does not serve any of the databases. It is a separate server instance that monitors the status of Principal and Mirror server and can initiate the automatic failover if the Principal instance fails.
Each server instance may participate in multiple database mirroring sessions. A given server instance can act concurrently as the Principal server instance for some databases, and the Mirror server instance for other databases. A Witness can participate in concurrent sessions with the same or different pairs of partners.
Automatic Failover Involving Witness Instance
Mirrored Backups
SQL Server supports Mirrored Backup sets. Typically, mirrored backup sets have two to four mirror sets, each set containing all the volumes of every media family. Each mirror requires a separate backup device for each media copy. Mirrored backups are available on both disk and tape. All backup devices for a single backup or restore operation must be of the same type, disk, or tape. It is a must that you use similar devices that have the same properties and configuration. Insufficiently similar devices generate an error message with error code of 3212. To avoid the possibility of a device mismatch, use devices that are equivalent, such as, only drives with the same model number from the same manufacturer.
Database Snapshots
SQL Server 2005 introduces database snapshots, which are a read-only, static view of any given database. Each database snapshot is transaction-consistent with the source database as it existed at the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner. Multiple snapshots can exist of a single database.