Improve SQL Server 2008 availability

Database Mirroring

Database mirroring is a technology that can improve database availability. These changes are also automatically applied to the mirrored database when the principal database changes. When a principal server fails, the client application can be automatically redirected to the mirror server, and the application does not need to make any changes.

You can configure the Database mirroring to apply changes to the mirror server in real time, called synchronization, or near-real-time application changes, called "asynchrony," so you can specify protection levels and performance overhead in your system.

With SQL Server 2008 Enterprise Edition, mirroring protection can be extended to data pages. If the data page on the principal or mirror server is found to be corrupted, the corresponding data page is retrieved from the partner server, and the data operation continues smoothly. SQL Server 2008 also improves database mirroring, which compresses the data flow between the principal server and the mirror server, and eliminates the need to restart the database when performing a manual failover.

In the case of an internet-based sporting goods company, database mirroring provides very rapid failover capabilities when a server, DB, or page-level failure occurs. Although you must purchase a new server, you do not need to rewrite the application, the standby server is a standard device and no SQL Server license is required.

Log shipping

Log shipping is a highly available technology that provides a warm standby server. The backup is performed on the primary server, and the restore is performed on one or more secondary servers. Log shipping then applies the periodic log backups to the secondary server. Although database mirroring can have only one mirror server, log shipping can have a number of secondary servers, which can increase the level of protection.

Log shipping is performed according to the timesheet, so there is a time lag between the data changes on the primary server and the transfer of these changes to the secondary server. This delay can result in data loss, but in many scenarios, these delays can be used as a way to restore user errors because the application of the log data on the secondary server is deferred (thereby retaining the original data on the secondary server).

To reduce the workload on the primary server, the secondary server can be used as a read-only reporting server, but the report cannot be run when the log backup is applied.

In a headhunter's case, log shipping can be applied with a delay between a log backup and a standby server to perform a restore. This can reverse the error situation caused by the user because if the primary server fails, the log can be applied directly to reduce data loss.