SQL Server 2008 : Transaction log shipping - Usage scenarios, Setting up and monitoring log shipping

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
10/10/2013 9:04:35 PM

Log shipping was first introduced in SQL Server 2000, although many DBAs implemented it before then using a series of custom jobs. Put simply, log shipping automates the backup, copy, and restore of a source database's transaction logs to one or more secondary server instances seeded with a full backup of the source database restored in recovering mode.

A commonly referenced backup best practice is to restore backups on a regular basis to ensure they're valid. Until they're restored you can't be 100 percent sure that they'll work in an emergency restore scenario. The problem a lot of organizations have with this recommendation is the time and equipment required to carry it out. Manually copying and restoring backup files on the off chance that something may be wrong isn't the most rewarding of tasks, and as a result, it's often shelved as a good idea for implementation at a later point, once everything else is done—which of course never happens.

The best way of implementing this recommendation is to automate it as part of an infrastructure solution.

As we covered earlier, transaction logs are a crucial component in the ability to recover a database to a point in time, and a broken log chain destroys this ability. Perhaps the best method for automating the restore of transaction logs, and therefore validating the log chain, is through the use of transaction log shipping.

Later in this section we'll walk through the process of establishing a log shipping session, which will highlight its various components and advantages. Before we do that, let's examine some of the common log shipping usage scenarios.

1. Usage scenarios

In addition to the major benefit of constant, automatic log chain validation, log shipping provides a number of other benefits, making it a commonly used solution for a variety of scenarios.

Offsite recovery point

Log shipping can be used to provide an offsite, up-to-date copy of a mission-critical database. In the event of total environment destruction, the offsite copy can quickly restore any remaining logs and be brought online and available for application usage.

Compared to database mirroring, log shipping has a much greater transaction latency. The default frequency of the log backup, copy, and restore jobs is 15 minutes, so the secondary database will typically be between 30 and 45 minutes behind the primary. While at first this may appear to be a weakness of log shipping, in some instances it presents a stronger solution than database mirroring.

Consider a situation in which a table is accidentally (or maliciously) dropped. In the case of database mirroring, by the time the error was realized, the change would probably already exist on the mirror. With log shipping, the restore containing the error can be canceled, or restored just prior to the error. Think of this as a 7-second broadcast delay.

Reporting solution

As we'll see shortly, when restoring the transaction logs on the secondary database, the standby option can be invoked. This option enables the secondary database to be used for read purposes in between log restores. Together with the ability to schedule the restores, this can enable the database to be used for reporting purposes for certain periods of the day.

The major benefit of log shipping when compared to database mirroring is that a log shipping source can be shipped to multiple destinations. For example, one destination could be used as a reporting source and another destination set up as a dedicated failover point. Each can have its own copy and restore schedules. Further, each destination can be in different physical locations from the source for further geographical failure resilience.

Upgrade solution

Log shipping can be set up between one version of SQL Server and the next. For example, a SQL Server 2005 source database can log ship to a 2008 destination. Such a configuration could be used to minimize the upgrade time in the following way:

  1. A new server can be installed, configured, and tested as required with SQL Server 2008 loaded and configured.

  2. A full backup of the source 2005 database is made and copied to the new server.

  3. The 2005 database is restored to the new 2008 instance on the new server in recovering mode.

  4. Log shipping is set up between the old and new server instances with transaction logs shipping on a regular basis.

  5. When the upgrade time arrives, users are disconnected from the 2005 instance, remaining logs shipped to the 2008 instance, and the database is then recovered with applications reconfigured to point at the new instance.

Until step 5, the original 2005 instance continues to run with no user interruption. The only downtime comes in step 5, which will typically be very short given the small number of transactions to ship across. The other benefit of this approach is that the original 2005 server still exists as a rollback point with its data still accurate as at the time of the last transaction log ship.

This solution could also be used when upgrading or decommissioning server hardware, but like database mirroring, log shipping is implemented on a database-by-database basis, so if a given server has many databases, it may not be the best approach.

To further highlight the options and benefits of log shipping, let's walk through an implementation using the SQL Server Management Studio tool.

2. Setting up and monitoring log shipping

You establish a log shipping session in SQL Server Management Studio by right-clicking a database and choosing Tasks > Ship Transaction Logs. Note that the selected database must be using either the Full or Bulk_Logged recovery model. As shown in figure 1, the resulting screen provides the option "Enable this as a primary database in a log shipping configuration." If the database is already log shipped, you can deselect this option to remove log shipping from the database.

After choosing the option to enable the database for log shipping, click the Backup Settings button. In the resulting screen, shown in figure 2, you can set a number of properties, such as the transaction log backup destination (including a file share accessible from the log shipping destination server), the log backup retention period, the backup frequency, and whether to use backup compression.

Figure 1. The initial log shipping configuration screen permits access to the various configuration components, including backup settings and secondary servers.

Figure 2. The Transaction Log Backup Settings screen enables the configuration of log backup frequency, destination, and retention period.

Figure 3. On the Initialize Secondary Database tab you can specify how the log shipped database will be created on the destination server.

Once you complete these settings, click OK to return to the screen shown earlier in figure 1. The next step is repeated for each required log shipping destination. In our example, we'll do this once for a simple 1:1 primary:secondary relationship.

Click Add under Secondary databases to specify a log shipping destination. In the resulting screen, you can specify a secondary server instance. On the Initialize Secondary Database tab, you're given three options, as you can see in figure 2.

For our example, we've chosen the third option, "No, the secondary database is initialized." We've done this because a full backup of the source database has already been restored in NORECOVERY mode on the destination server. Alternatively, we could choose the first option, which will automate the full backup, copy, and restore process, or the second option, if we want to restore a preexisting backup.

Next, click the Copy Files tab, where you can specify where the log backup files will be copied to and how frequently the copy will take place. As shown in figure 4, this tab also contains the option to automatically remove copied log backup files after a certain time period.

Finally, click the Restore Transaction Log tab, shown in figure 5, and select a restore mode and frequency. The default restore mode is No Recovery Mode, which leaves the database in the recovering status, and therefore unable to be used. For our example, we've chosen Standby Mode, along with the option to disconnect users. These options enable the log shipped database to be used in read-only mode in between log restores for reporting purposes.

Figure 4. The Copy Files tab lets you specify the log backup copy location, frequency, and retention period.

Figure 5. Figure The Restore Transaction Log tab allows you to set the restore frequency along with the database state between restores.

Note that when using standby mode, you must take into account the restore frequency. The default restore frequency of 15 minutes is inappropriate for a reporting solution, as users would be disconnected (if the disconnect option is chosen) every 15 minutes to enable the log restore. For our example, we've chosen a 12-hour restore frequency. This will enable 12 hours of uninterrupted reporting before the next restore, and assuming users are advised on the restore times, such a configuration may be quite acceptable.

Regardless of the restore frequency, the backup and copy jobs have their own independent schedules. In our example, the backup and copy jobs run every 15 minutes. If the secondary database is required in an emergency, the restore process—accessible via a SQL Server Agent job created by the log shipping setup process—can be manually executed at any time, which will restore any outstanding transaction logs and thus bring the secondary server up to date.

Once you complete these steps, click OK to return to the screen shown in figure 1. The other log shipping component for consideration is the monitoring instance, which will actively monitor (via SQL Server Agent jobs) the backup, copy, and restore progress. If any of these events don't run within the specified intervals, the monitoring server generates alerts, which can be configured to notify the appropriate DBAs.

Monitoring instance

Although the primary or secondary log shipping instance can also be the monitoring instance, using an independent instance for monitoring is recommended. In such a configuration, the load on the primary and secondary instances is reduced, and monitoring continues in the event of failure on either the primary or secondary instance. Further, a monitoring instance can monitor more than one log shipping configuration.

After deciding whether to use a monitoring instance, save the log shipping configuration for later use by clicking the Script Configuration button. SQL Server implements the backup, copy, restore, and monitoring steps as SQL Server Agent jobs, requiring this service to be running on all instances (primary, secondary, and monitoring) to enable log shipping to function.

Finally, in order to determine the status of the log shipping session, SQL Server includes a number of options, one of which is the built-in transaction log shipping status report, as shown in figure 6. Access this report by right-clicking on a server involved in a log shipping role (primary, secondary, or monitor) and select Reports > Standard Reports > Transaction Log Shipping Status.

Note that this report contains additional information not shown in figure 6, including the filename of the last file backed up, copied, and restored.

If you're using log shipping to maintain a warm standby server for failover purposes (as opposed to a pure reporting server solution), then one of the important tasks is the failover and role reversal process.

Figure 6. The built-in transaction log shipping status report allows you to view status information easily.

3. Failover and role reversal

If the primary instance in a log shipping solution fails (or a manual failover is required for planned maintenance), the failover process is largely a manual effort, requiring you to back up, copy, and restore outstanding logs to the secondary instance before configuring log shipping in the reverse direction. This process can be summarized as follows:

  1. Disable the log shipping backup, copy, and restore SQL Server Agent jobs.

  2. Manually run the backup, copy, and restore SQL Server Agent jobs to push across any outstanding transaction log backups.

  3. Back up the transaction log on the primary instance using the WITH NORECOVERY option. This will place the database in the recovering mode, enabling it to receive logs from the secondary server once the roles are swapped.

  4. Restore the backup from step 3 on the secondary instance using the WITH RECOVERY option. This will place the secondary database in read/write mode and make it available for use. At this point, you will need to manually redirect client connections to the secondary instance.

  5. Set up log shipping in the reverse direction, ensuring that the "No, the secondary database is initialized" option is selected, as shown in figure 3 earlier. At this point, there will be a set of SQL Agent jobs for the new configuration as well as disabled jobs for the old configuration. Leaving the old disabled jobs in place makes future role changes easier—you simply disable one set, enable another, and ensure tail log backups are applied in the appropriate direction. At this point, the log shipping roles are reversed.

One of the real benefits of log shipping is its simplicity. The core components of log shipping are fundamental SQL Server tasks: transaction log backups and restores. Other than running out of disk space, there isn't a lot that can go wrong, unless someone makes (and removes) an additional transaction log backup without the COPY_ONLY option . That being said, it's not without its limitations; the primary one is the manual effort required to change roles and reconnect clients. In that regard, database mirroring offers a superior solution.

  •  SQL Server 2008 : High-availability options
  •  SQL Server 2008 : Policy-based management - Advanced policy-based management
  •  SQL Server 2008 : Policy-based management - Enterprise policy management
  •  SQL Server 2012 : Interpreting Query Execution Plans - Viewing Query Execution Plans
  •  SQL Server 2012 : SQL Server Management and Development Tools - Using the Query Editor
  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 2)
  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 1)
  •  SQL Server 2012 : SQL Server Management and Development Tools - Registered Servers
  •  SQL Server 2012 : SQL Server Management and Development Tools - Organizing the Interface
  •  SQL Server 2012 : SQL Server Private Cloud - Upgrading SQL Server
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us