logshipping

Log shipping:

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.
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.
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.
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.
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.
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




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.
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.
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.
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.
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).
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.
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 defines 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-