SQL Server
Log Shipping in SQL Server has been around for a while and still provides an easy method for redundancy and load balancing. It uses standard backup and restore technology to ship backups to a standby server creating a read-only copy of the database. The frequency of the log backups defines the latency of the standby server. The default is 15 minutes. These backups are “shipped” via a shared folder and then restored to the database on the standby server using WITH STANDBY. Reporting can be made on the Standby server for load balancing by freeing up the primary server for the critical OLTP activity. Watch out, because the RESTORE statements require exclusive access so make sure your standby queries use the WITH (NOLOCK) option. If the primary database fails, you can switch roles so the standby database becomes the primary updateable database. However, this is a manual process and the clients need to be directed to the new primary server manually too. Database Mirroring has automatic failover when you provide a third Witness server and Clients automatically redirect themselves during a failover using the failover_partner setting in the connection string. There is also protection of data using synchronous updates that commit on the Principal and the Mirror at the same time. So during a failover, there is no loss of data. But this solution does not inherently provide a load balancing option since the mirror database is in Restoring mode until failover. But you can create a Snapshot database on the Mirror and point reports to the snapshot. The frequency of creating snapshots defines the latency. You have to be careful because if the mirror is doing too much work maintaining snapshots and running reports, you may be slowing down the synchronous partnership between the Principal and the Mirror. Dropping old snapshots helps this. As always, full system testing will provide critical performance metrics. In SQL Server 2008 R2 you can set up a Utility Control Point and monitor both solutions to see which one performs best with your data. That will give me something tangible to test using this new functionality. Watch this space… cheers Brian
