SQL Server 2005 log shipping setup using the wizard

When you set up log shipping in SQL Server 2005, there are many options to choose from to get the optimal configuration between primary and secondary servers. Along with some best practices, SQL Server MVP Hilary Cotter shares a log shipping setup process from start to finish that includes selecting the right database recovery mode, transaction log paths and secondary database settings.

When setting up log shipping in SQL Server 2005, you have a lot of options to choose from in order to nail down

optimal configuration between primary and secondary servers. Along with some best practices, follow SQL Server MVP Hilary Cotter's log shipping setup process from start to finish, which includes selecting the right database recovery mode, transaction log paths and secondary database settings.

Log shipping is a native SQL Server high-availability technology, which is essentially a continuous backup and restore operation. Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondaries (destination) servers. The database and transaction logs are restored on the secondary servers in standby or no-recovery mode, which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and applied there.

  • The standby mode allows users to access the restored database, but will not allow them to make any changes to it and will allow future log backups to be restored to the database.
  • The no-recovery mode does not allow users to access the database and allows future transaction log backups to be restored to the database.

Log shipping makes it possible to keep the primary (source) and the secondary or standby server in sync on a per-database level.

It is important to note the following caveats with log shipping:

  • You can ship all databases, except read-only, tempdb, model and any databases not in full recovery model or simple recovery model.
  • During defragging/indexing/re-indexing operations, the transaction log's backup files can become very large. To keep the size of the transaction logs to a minimum, you can switch between full and bulk logged recovery models (bulk logged operations minimally log indexing operations).
  • Certain operations will "break" log shipping, like changing the recovery from full or bulk logged recovery model to simple recovery model.
  • Log shipping operations will have to be suspended during a backup operation.
  • The latency period (how out of sync the primary will be from the secondary) can vary; one example would be a log shipping scenario where you back up the transaction logs every 10 minutes. A transaction log backup starting at 1:00 a.m. may take 10 minutes to complete. It may take another five minutes to copy the backup to the destination server. This means your exposure to data loss will be 15 minutes. Consider what would happen if your server crashed at 1:09 a.m. Your backup would not be generated or copied to the secondary/standby server, and the data on your secondary server would be as of 12:50 a.m.
  • Log shipping is not considered to be a highly scalable operation for topologies that involve a large number of databases.
  • All dependencies must be in place on the secondary server (for example, logins, DTS or SSIS packages, SQL Server jobs and any external dependencies).
  • Clients will have to be manually redirected to the standby server.
  • The application must be log-shipping aware – if the primary server goes offline, the data is queued on the client until the standby server comes back online. Best practices dictate that this is a manual redirection; otherwise, the application might redirect to the standby server prematurely or in error.

Log shipping is popular because it's a well-understood technology, there is no real distance limitation, it can be done over the Internet, the destination database can be accessible but is read only and the standby SQL Server does not require licensing for 30 days after a failover. There will have to be an OS license for the standby server, however.

In this article, we will look at how to set up log shipping with the wizard that ships in SQL Server 2005.

Using the log shipping wizard

To use the log shipping wizard in SQL Server 2005, right click on your database and select Tasks and Ship Transaction Logs as illustrated in Figure 1.

Accessing the log shipping wizard
Figure 1: Accessing the log shipping wizard. (Click on image for enlarged view.)

You may get the dialog you see displayed in Figure 2. SQL Server is telling you that this database is not a full or bulk logged recovery model.

Dialog displayed if your database is in the simple recovery model
Figure 2: Dialog displayed if your database is in the simple recovery model. (Click on image for enlarged view.)

To change the recovery model for your database, right click on your database, select properties and in the Options tab, click on the Recovery Model drop down list box and select Full or Bulk_logged recovery model. This is illustrated in Figure 3. Then click OK.

Note: If you do select these recovery models and you back up your database or have already backed up the database, you will need to maintain your transaction logs by scheduling a backup of them. The log shipping wizard will configure this for you.

Changing the recovery model for your database
Figure 3: Changing the recovery model for your database. (Click on image for enlarged view.)

Once you've set the correct recovery model for your database, right click on your database and select Ship Transaction Logs again. You will receive the dialog box illustrated in Figure 4.

Configuring log shipping
Figure 4: Configuring log shipping. (Click on image for enlarged view.)

Note: In this figure, I selected the check box that enables this as a primary database in a log shipping configuration. Make sure you select this check box as well. Clicking on the Backup Settings button will display the Transaction Log Backup Settings as displayed in Figure 5.

Transaction Log Backup Settings and options
Figure 5: Transaction Log Backup Settings and options. (Click on image for enlarged view.)

There are two options to backup the transaction log to:

  • Network path
  • Local path

If you are backing up to a network path, there will be some increase in the transaction log backup times and you will decrease the space requirements on your primary server . The network path should go to the location where you want store the transaction log backups on the secondary server. Most DBAs use a network share to back up the files to

More on SQL Server high availability:

 because they want the transaction log backup files on the secondary server to protect them in the event that the primary server goes down.

The local path option will back up the transaction logs to a path on your primary server. Make sure the transaction log backups are not backed up to the same physical drive where your database data files or log files exist. Should they be backed up to the same physical drive, it would cause I/O contention and overall SQL Server performance degradation.

Figure 6 illustrates a completed Transaction Log Backup Settings dialog. Note that the share name has a dollar sign after it. This has the effect of hiding the share from users, and only users who know the share name will be able to access it. This is considered a good security practice.

Transaction Log Backup Settings illustrating a hidden share for the network path
Figure 6: Transaction Log Backup Settings illustrating a hidden share for the network path. (Click on image for enlarged view.)

There is an option for how long to retain your transaction log backups and an option for the threshold to raise an alert if there is no backup within a specific period. I've found the default for transaction log backup retention of three days to be adequate. I normally set a much smaller alert, like 20 minutes, but while a database backup is occurring, no log backups can occur, so if your database backups take more than an hour, you will get frequent alerts. Set this to a value that works for you. And remember: Too frequent alerts will result in your ignoring critical ones.

Once you have set the transaction log backup location, the retention period, the alerting threshold and the schedule, click OK. You will return to the Database Properties dialog box as illustrated in Figure 4. Click on the Next button to display the secondary Database Settings dialog displayed in Figure 7.

The secondary database settings
Figure 7: The secondary database settings. (Click on image for enlarged view.)

In this dialog box, you can select the secondary server (the server you'll be copying the transaction logs to) as well as the secondary database (the name of the database you will be log shipping to). Notice these options for Initialize Secondary Database:

  • Yes, generate a full backup of the primary and restore it into the secondary database. This option creates a backup of the database you wish to log ship and restores it on the secondary server.
  • Yes, restore an existing backup of the primary database. Use this option if you want to use a previously backed up database. There is an option to select the path and backup name.
  • No, the secondary database is initialized. Use this option if:
    • A copy of the database you wish to log ship has already been restored on the secondary.
    • The primary database is already in the full or bulk logged recovery model.
    • No transaction log backups have been done on the primary database since the backup was done, or if they have been done, they have been restored on the secondary.
    • The secondary database has been restored with the no-recovery option.

Once you have configured the options that work best for you, click on the Copy Files tab, to display the dialog, as illustrated in Figure 8.

Copy Files dialog
Figure 8: Copy Files dialog. (Click on image for enlarged view.)

In the Copy Files tab, select where you copied the files from primary to the secondary. If you selected a network path in the Transaction Log Backup Settings (as illustrated in Figure 1), the network path should map to a physical location on your secondary server, and you should enter that path here (i.e., if \\ServerName\ShareName$ is the share name for the local path C:\Backup, enter this path here.)

You can also use a network path where the transaction log backups are stored on the primary server. Select how long you wish to retain the transaction log backups – understanding, of course, that the retention period might conflict with what you set in the Transaction Log Backup Settings dialog box (Figure 1).

You can also select how frequently you want the logs to be copied to the secondary server.

Once you have configured the copying of the transaction log backup files, click on the Restore Transaction Log tab as illustrated in Figure 9.

Restore Transaction Log tab
Figure 9: Restore Transaction Log tab. (Click on image for enlarged view.)

The database recovery state options are:

  • No Recovery Mode – This is the default option. In this option, the destination database will be inaccessible.
  • Standby Mode – In this option, the destination database will be read only until the next transaction log backup is applied. After the transaction log is applied, the database will be returned to read-only mode again. This allows read-only access and users will not be able to make any changes to the database (i.e., create indexes) and they will be disconnected when the next database backup is applied.

There is also an option to delay restoring the transaction log backups by a set number of hours or minutes. Some enterprises like to keep their standby server several hours out of sync from their source server.

By default, if the transaction logs are not restored within the interval defined in the "Alert if no restore occurs within" setting, an alert will be raised. Most frequently this alert is raised during a backup operation on the primary.

You also have an option to set how frequently the transaction log restores will take place. These settings are made in the Restore job dialog.

Once you make those settings, click OK and you'll see the Transaction Log Shipping dialog of the Database Properties dialog box (as illustrated in Figure 10).

Transaction Log Shipping tab
Figure 10: Transaction Log Shipping tab. (Click on image for enlarged view.)

In this dialog, notice how we have configured server F as our secondary server and that database p2p3 is the database we are log shipping the Northwind database to. You can also log ship to a second secondary/standby server – this could be another secondary server in your DR site.

In any enterprise with a large number of log ship databases, you may want to create a monitor server. To create a monitor server, select "Use a monitor server instance" (Figure 10) and click on the settings button to configure a Log Shipping Monitor server. That option is illustrated in Figure 11.

Create a monitor server
Figure 11: Create a monitor server. (Click on image for enlarged view.)

In the connect button, connect to the server you wish to use as your monitor. Define how to make the connection, either through Windows authentication or through a SQL Server login. You can define the job history retention and the default is normally a good choice, and then define how you want to send the alerts. The default option (Start Automatically when SQL Server Agent starts) is the best choice as alerts will be near real time. You can alternatively select an hourly schedule or whatever time interval you choose.

Summary

This completes our look at using the log shipping dialog in SQL Server 2005. This wizard has many options to it and can be quite bewildering to the new user. But the defaults are generally optimal in most cases and I've noted the cases where it's best to use non-default options.

ABOUT THE AUTHOR
Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.

This was first published in April 2008

Dig deeper on SQL Server High Availability, Scalability and Reliability

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

2 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close