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.
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:
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.
[IMAGE]
Figure 1: Accessing the log shipping wizard. (Click on image for enlarged view.)
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
Figure 5: Transaction Log Backup Settings and options. (Click on image for enlarged view.)
There are two options to backup the transaction log to:
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 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.
[IMAGE]
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.
[IMAGE]
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:
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.
[IMAGE]
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.
[IMAGE]
Figure 9: Restore Transaction Log tab. (Click on image for enlarged view.)
The database recovery state options are:
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).
[IMAGE]
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.
[IMAGE]
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.
[TABLE]
