Log Shipping Step by Step

Now that you know how log shipping works on the backend, let's go ahead and create a log shipping plan using Enterprise Manager. What's nice about log shipping in SQL Server 2000 is that it's handled through wizards. Essentially, preparing and configuring log shipping in SQL Server involves the following steps:

  1. Identify the pair of servers you want to ship between (log shipping pairs consist of the primary and secondary server combination you want to participate in log shipping). You can ship between two databases on the same server if you only have one database server available to you. The log shipping pair must be able to communicate with each other. This means the servers will either need to be in the same domain or be able to pass credentials to a trusting system. Your pair will also need access to the monitoring server so it can write events.

  2. Identify the monitoring server, which Microsoft recommends be different than the primary and secondary servers.

  3. Ensure that you have SA rights to all of the servers.

  4. Create the necessary file share on the primary database server. Ensure that the account that starts the SQL Server Agent service on all of the log shipping servers has rights and network access to the share. Don't forget to also open up the firewall rules to allow file sharing between the primary and secondary servers.

  5. Register all of three servers (primary, secondary, and the monitoring servers) in Enterprise Manager.

  6. Create the initial databases and restore the initial backup on the secondary servers. Log shipping can automatically take care of this as well if you wish.

  7. Ensure that the same logins are on each of the servers in the log shipping pair. If this is not done, the secondary server will not be able to change roles. You can do this through DTS or through a script.

  8. Create the log shipping plan.

  9. Point your users and applications to the new primary server.

Note 

If you do not have SQL Server 2000 Enterprise or Developer Edition, you will not be able to follow this example on your system. If one server in your log shipping pair is not Enterprise or Developer Edition, it will not work. You can also set up log shipping between SQL Server 7.0 SP2 machines and SQL Server 2000 machines.

Log shipping is configured through the Database Maintenance Plan Wizard. When you're configuring the maintenance plan for log shipping, I would recommend that you only use the plan for log shipping and keep your normal complete backups and health checks out of it. Begin by starting the Database Maintenance Plan Wizard (right-click on Database Maintenance Plans under the Management group in Enterprise Manager).

On the Select Databases screen (shown in Figure 10-2), isolate the plan to a single database. If you select more than one database, the log shipping will fail. You can add additional databases by creating additional maintenance plans for each database. After selecting what database you want to use log shipping on, select the 'Ship the transaction logs to other SQL Servers (log shipping)' option. If you select more than one database or select a database that uses Simple recovery model, this option will become grayed out.

click to expand
Figure 10-2: Selecting the database and beginning the log shipping process

Note 

For my example, I'm using the Northwind database. Before I began, I changed the recovery model on the database to Full. By default, this recovery model is set to Simple and will not work. I have also dropped the Northwind database on the remote system to show how SQL Server will create the database automatically for you.

After you click Next, continue through the wizard, not selecting any of the options, until you get to the Specify Transaction Log Backup Disk Directory screen. Select the Use This Directory option and specify where you want the transaction logs to go for this database. You will also need to share this directory out so the secondary servers can copy the transaction logs from it. I've selected the C:\Program Files\Microsoft SQL Server\MSSQL\NorthwindBackupShare directory to output my transaction logs to in Figure 10-3, but specify whatever makes sense to you.

click to expand
Figure 10-3: Specify Transaction Log Backup Disk Directory screen

Make sure you also select the Remove Files Older Than option and specify a duration that's long enough for you to back up the transaction log backup files to tape as a part of your regular disaster recovery plan. Another consideration for this option is that you want to allow enough time for the secondary servers to copy the files and apply them to their database. If you specify a duration that's too short and you have a problem with the job over a few days, your database will be out of synch and you'll have to restore a complete backup. For the transaction log backup extension, accept the default of .TRN.

The next screen is the Specify The Transaction Log Share screen (Figure 10-4). Click the ellipsis (…) button to find the share for the primary server that contains the transaction logs. I shared the C:\Program Files\Microsoft SQL Server\MSSQL\NorthwindBackupShare directory out as \\Sql2ksecondary\NorthwindBackupShare. This option will specify what UNC share the secondary servers will use to find your transaction log backup files.

click to expand
Figure 10-4: Specifying the transaction log backup share

The next screen is the Specify the Log Shipping Destinations screen (Figure 10-5). This is where you add all the secondary servers you want to ship your transaction log backups to. Click Add to add a secondary server. This takes you to the Add Destination Database dialog box (shown in Figure 10-6), which is the meat of the log shipping section of the wizard.

click to expand
Figure 10-5: Adding destinations in the Specify Log Shipping Destinations screen

click to expand
Figure 10-6: Adding and configuring a destination database

The Server Name option is where you specify the secondary server. The drop-down box will show you a list of all the SQL Servers that you have registered in Enterprise Manager. If you skipped a step and have not registered the server in Enterprise Manager, you must either exit the wizard or open another instance of Enterprise Manager, register the server, and close and reopen the Add Destination Database dialog box.

Once you select the database server you want to be your secondary server, many of the options will auto-populate with the remote system's local path information. For example, Enterprise Manager will connect to the database in the background and retrieve the directory structure for the secondary server. You can also choose for the wizard to create the database on the secondary server and load it. Choose the Create and Initialize New Database option if you haven't already created the database and restored the latest complete backup.

Select the Use Existing Database option if you want to use an existing database that's already been loaded with the latest backup and is in a nonrecovered state. In the Database Load State section, you can choose to leave the database in one of two states: No Recovery Mode or Standby Mode. In No Recovery Mode, the database is left in a mode where no one can query it and the only activity on the database is transaction log restores. If you choose Standby Mode, users can query the database in read-only mode only, meaning they cannot run any statement that would alter the database. Also in this section is the Terminate Users In Database (Recommended) option. If you enable this option, SQL Server will disconnect any users that are in the database either during the complete database initialization or during transaction log restores. I recommend always enabling this option. Otherwise, your restores may be slower than anticipated.

The last option is to select Allow Database To Assume Primary Role. Selecting this option allows the secondary database to become the primary in the event of a role change. If this option is not selected, the secondary database will not be able to assume the source database role. If you select this option, you'll also need to specify a UNC path on the remote system from which SQL Server will be able to retrieve the logs.

Tip 

If your database is large, you may not want to have the wizard automatically create and initialize the database. You may want to create and load the database during an off-peak time.

Once you have set all the options, click OK to move back to the Log Shipping Destinations screen. Add any additional secondary servers you want and click Next.

You'll then be asked if you'd like to pull a recent backup or create a new one in the Initialize The Destination Databases screen (Figure 10-7). I generally like to just create a new backup unless I have a large database that could cause latency on my system to back up in real time. If you choose to use an existing backup, make sure that the transaction logs that have occurred since that backup are on the transaction log share that's being used for log shipping.

click to expand
Figure 10-7: Initializing the Destination Databases screen

The next screen, the Log Shipping Schedules screen (Figure 10-8), is where you specify how often you'd like to the logs to ship. On larger, more active databases you will want to specify a small interval to keep your file sizes small. You can lower the interval to as small as one minute or as large as hours by setting the Copy/Load Frequency setting.

click to expand
Figure 10-8: Setting the frequency of the copying and restoring of your transaction logs

The Load Delay option indicates how long the transaction log is kept after copying before actually restoring it to the system. The default, 0, will apply the transaction immediately after it's copied. This generally is the preferred setting. The File Retention Period is how long the transaction logs will be kept on the secondary system before purging them. Since the files will be kept also on the primary server for disaster recovery, there's generally no need to keep them on the secondary servers for more than 24 hours. You can specify how long to keep the transaction log backups on the share back in the earlier screen where you created the transaction logs.

Note 

This setting will vary widely based on how much data is acceptable to lose. If 15 minutes of data loss is acceptable, then specify that setting. This is very much a business decision, so make sure you involve your product owners. Keep in mind that this setting is tied in with how often you make transaction log backups. If you only choose to make transaction log backups every 30 minutes in the earlier screen, it's pointless to choose anything less than 30 minutes here since there won't be any new backups to copy and restore.

The Log Shipping Thresholds screen (Figure 10-9) gives you the option to set an acceptable latency threshold before the monitoring server raises an alert. The Backup Alert Threshold option lets you raise an alert on the monitoring server if a transaction log backup on the primary server hasn't occurred in a given amount of time. The Out Of Sync Alert Threshold option will raise an alert if the transaction logs haven't been copied and restored onto the destination (secondary) server in a specified period of time.

click to expand
Figure 10-9: Setting the log shipping thresholds

Tip 

Raising the alert does no good unless the administrator is watching for them. Make sure you have a system in place where you'll be paged or e-mailed upon the thresholds being crossed.

The next screen is where you finally set up the monitoring server. In the Specify The Log Shipping Monitor Server Information screen (Figure 10-10), choose the server name you want to make your monitoring server from the drop-down box. Again, it is important to have registered your monitoring server in Enterprise Manager before beginning the wizard. If you haven't registered the server, open another instance of Enterprise Manager, register the server, then go back to the wizard, click Back, and then click Next. You won't need a tremendously beefy server to be a monitoring server, but you will need good connectivity between this server and all the log shipping servers. The purpose of this server is merely to log the activity of the log shipping and raise alerts.

click to expand
Figure 10-10: Setting the log shipping monitor

The next few screens you should be familiar with from the normal maintenance plans. This is where you specify if you want logs written out and how much history to keep. Finally, you're complete when you arrive at the Completing The Database Maintenance Plan Wizard screen (Figure 10-11). On this screen, validate the information and click Finish. At that point, the wizard will go through and create the remote database, jobs, and plans and synchronize the database for the first time.

click to expand
Figure 10-11: Completing the Database Maintenance Plan Wizard

Congratulations, you're done! In the next sections, I'll discuss troubleshooting your newly created environment and changing the roles of the servers.

start sidebar
In the Trenches

As I mentioned earlier, you have the option to have a database in Standby Mode, which allows users to connect to the database in read-only mode and query it. While this sounds like a neat idea, keep in mind that when a database transaction log is being applied to the secondary system, users will be disconnected and will receive the following error when trying to connect: This could be a big problem if the interval between restores is short and users keep getting disconnected.

click to expand

end sidebar

Monitoring and Troubleshooting Log Shipping

From the monitoring server, you can go to the Log Shipping Pair Properties screen (shown in Figure 10-12) under the Management group to see the status of the log shipping process. This Status tab in the monitor will show you the last time the transaction log backup was made, copied, and loaded into the secondary servers. The screen is quite useful when you're trying to quickly determine how out of synch your database is.

click to expand
Figure 10-12: Status tab on the Log Shipping Pair Properties screen on the monitored server

The Source tab will show you when an alert will be raised if a transaction log backup hasn't occurred on the primary server. In my example, shown in Figure 10-13, if the backup hasn't occurred in 45 minutes, alert number 14420 will be triggered. This will write an error in the SQL Server error log and the Windows event viewer. You can also specify for alerts to be suppressed during certain hours. This is useful if you plan to disable transaction logs backups on weekends when there is no activity on the server.

click to expand
Figure 10-13: Source tab in the Log Shipping Pair Properties screen

The Destination tab (see Figure 10-14) monitors what an acceptable threshold is for the database to be out of synch. This means the last time the database transaction log backup has been copied and restored onto the secondary server. If the database is determined to be out of synch in your specified threshold, then alert number 14421 is raised. This too will write errors out to the Windows and SQL logs. Again, you can specify here when to suppress these messages.

click to expand
Figure 10-14: Destination tab in the Log Shipping Pair Properties screen

I would recommend that you create events inside SQL Server to page or e-mail your operators if either of these two events has occurred. If you're trying to diagnose if your database is out of synch, the Windows Application Log is a good starting point. These errors would output a message like the one shown in Figure 10-15 in the event the database is out of synch.

click to expand
Figure 10-15: Error in event log when database is out of synch

You can also check the SQL Server Error Log. In Figure 10-16, you can see that the database transaction log has not been backed up in an excessive time.

click to expand
Figure 10-16: Error in SQL Server Log when a log shipped database is not backed up

The number one item to check in the event of a problem is SQL Server Agent itself. Ensure that it is running and is operational with no errors in its logs. You want to also make sure that the secondary database has all the transaction logs it needs to get back in synch. For example, if the primary database is outputting transaction log backups to the share but the secondary server's SQL Server Agent is stopped, this would prevent it from synchronizing with the primary. The primary database server may delete the logs before the secondary has a chance to load them, causing you to have to either restore the transaction logs from tape onto the share for the secondary server to load and restore or restore the entire database again from a complete backup.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net