Using SQL Server Log Shipping

If a single production server provides both read and write access to data, you should log all transactions. You can use SQL Server Log Shipping to transfer files to a nonproduction server that is continuously updated with the transaction log files. This option is inexpensive and easy to manage, and provides a strategy for availability in environments where there is some tolerance for downtime.

You can use this method in combination with other strategies described in this section to recover from disaster when availability is particularly critical. You can also use warm backups to keep geographically dispersed data centers reasonably synchronized. In fact, this is an inexpensive way to maintain a synchronized database in a separate data center for site disaster recovery.

Combining Network Load Balancing and SQL Server Log Shipping provides excellent warm failover capability. In this scenario, you can use Network Load Balancing to provide the same IP address to at least two servers. This makes it easier to fail over to the secondary computer running SQL Server if the primary computer running SQL Server fails. You direct client applications through the Network Load Balancing cluster to one of the computers running SQL Server. If a failure occurs, or when it is time for scheduled downtime, you can use Network Load Balancing to transfer access to another computer running SQL Server in the cluster. Log Shipping synchronizes the servers, based on the shipping frequency of the log.

This section contains:

Retry Code Logic

Considerations for Network Load Balancing and Log Shipping

Retry Code Logic

This section contains examples of retry logic to protect against loss of database connectivity:

  • One retry level

    If (getAuth == error)
       Sleep 1 second
       GetAuth again 
    Response.write ("Sorry, logins are disabled. Please e-mail questions to mailto:helpdesk.")
    
  • Two retry levels

    If (getcatalog == error)
       Sleep 1 second
       Getcatalog again 
       If (failed again)
          Response.write ("I am retrying your query; please wait.")
          Sleep 10
          Getcatalog again
    Response.write ("Sorry, our catalog is temporarily unavailable. Please e-mail questions to mailto:helpdesk.")
    

Considerations for Network Load Balancing and Log Shipping

In Microsoft SQL Server 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database.

When you set up Network Load Balancing and log shipping, consider the following:

  • To set up log shipping within a Network Load Balancing cluster, the servers running SQL Server must communicate over a private network that is isolated from the Network Load Balancing network. To create the private network, you must install a secondary network interface card (NIC) into both of the servers running SQL Server.
  • You must set up log shipping with a hard IP address instead of using universal naming convention (UNC) server names, or do one of the following:
    • Create an Lmhosts file to resolve the IP addresses to a UNC server name. An Lmhosts file is a local text file that maps IP addresses to the computer names of Windows 2000–based networking computers outside of the local subnet. For more information, see Microsoft Windows 2000 documentation.
    • Register the IP addresses in Windows Internet Name Service (WINS) to resolve the UNC name, if a WINS server exists on the private network.
  • When you set up the servers running SQL Server in the Network Load Balancing cluster, the primary computer running SQL Server is connected to the cluster. You must then disconnect the secondary computer, running Log Shipping SQL Server, from the cluster. The servers stay synchronized by having the primary server "log ship" the data to the secondary server.
  • If you do not isolate the communications between the servers running SQL Server, Network Load Balancing attempts to communicate directly to the IP address on the second NIC card, causing looped traffic and other problems.

If the primary computer running SQL Server fails, Network Load Balancing can automatically fail over to a secondary server. Depending on the log shipping frequency, however, it is considered a best practice to make the decision manually to fail over to the secondary server. For example, if you have set log shipping to run every five minutes, the secondary server could be as much as five minutes behind (assuming all processes are functioning properly). Failing over the system manually enables you to check to see if the latest transaction log can be applied so that more data can be saved, instead of failing over automatically and perhaps losing up to five minutes of data.

You install stored procedures to facilitate failover when you install SQL Server Log Shipping. For more information about log shipping, see SQL Server Books Online.

See Also

Securing Your Site

Securing Your Databases

Copyright © 2005 Microsoft Corporation.
All rights reserved.