Integrating Log Shipping with other High Availability Solutions

Log shipping can be deployed along with other Microsoft high-availability solutions (for example, in a Windows failover cluster, where it maintains a remote disaster recovery site if the local Windows failover cluster becomes unavailable). Moreover, it can be deployed with data mirroring to maintain a remote site if the data-mirroring pair becomes unavailable. Furthermore, log shipping can be deployed with replication to maintain a highly available replication publisher.

SQL Server 2005 Data Mirroring

Log shipping can be integrated with a SQL Server 2005 data mirroring solution. For example, an organization may deploy local data mirroring and log shipping from the principal server to a remote location. This would be applicable because data mirroring cannot have more than two partners; therefore, if an organization wants to protect its data investment by having a local data mirroring for high availability but require a business-continuity plan for disaster recovery, you can deploy log shipping from the primary server to a remote secondary server. However, during a data-mirroring role switch, log shipping will not automatically switch roles, and manual steps must be taken to allow the former mirror, which is now the principal, to start to log ship its transaction log to the secondary server by deploying log shipping from the new principal server.

Windows Failover Clustering

Log shipping can be deployed to ship a database from inside a Windows failover SQL cluster to a remote location for disaster recovery. For example, in a local disaster where the Windows failover cluster is not available, log shipping will offer business continuity at the remote location. Unless an organization is deploying a geographically dispersed Windows failover cluster, the cluster nodes are located near each other and can be both down in a local disaster. For best results in a cluster environment, the backup folder should be setup as a cluster resource and should be in the same cluster group with the SQL Server that contains the log-shipping database, so that in a cluster-failover scenario, the backup folder will failover with the log-shipping SQL Server to be accessible by the other Windows failover cluster node. Additionally, any configuration and data files in that Windows failover cluster that the other cluster node will need to access should be set up as a cluster resource included in the log-shipping SQL Server cluster group. For example, if you choose to execute the SSIS Transfer Logins Task package from a file instead of storing it in SQL Server, that folder should be included as a cluster resource to make it available to all cluster nodes. A folder can be set up as a cluster resource by first creating it. Then, using the cluster administrator, create a new resource and choose Shared Folder. Make sure that it is placed in the same group with the SQL Server that contains the log-shipping database. Inside the Windows failover cluster, the SQL Server can be made to depend on the folder. For example, if the folder is not yet online, SQL Server will wait for that resource. The decision to make something a dependency is based on whether the resource must be available before SQL Server starts. For example, if the backup folder for the transaction-log files is not available, should SQL Server wait? Probably not, because it is more critical that the database is up and serving users than if the transaction-log backup is down.

SQL Server 2005 Replication

In a replication topology where the SQL Server 2005 publisher is the data consolidator, it is a single point of failure. All of the subscribers connect to the publisher to receive data updates. If the publisher fails, replication would stop until the publisher can be brought online again. Oftentimes, log shipping has been used as a high-availability solution to protect the publisher from becoming a single point of failure. In this configuration, the primary server is the publisher who log ships its transaction log to a secondary server. Additionally, in order for replication to continue to work after the role switch, the primary and secondary servers configurations must be identical. Also, for transactional replication, to prevent the subscribers from having data that has not been shipped to the secondary server, the primary server should be configured with backup, where a transaction is not replicated to the subscribers until a transaction-log backup has been performed. This does produce a latency penalty where the subscribers are not quite real time, as replication needs to wait for log shipping. This latency can be reduced by decreasing the interval to perform the transaction-log backup. Without sync with backup, there is a possibility that the subscriber's data may not match with the publisher during a log-shipping role switch and data loss may occur. In merge replication, after the role switch, the merge publisher may be able to synchronize any changes lost during the role switch by merge replicating with each subscriber. A more common high-availability solution to prevent a single point of failure for the publisher is to configure a Windows failover cluster so that in case of a failure, replication fails over to the other cluster node. Log shipping is supported by transactional and merge replication.