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