Professional SQL Server 2005 Administration (Wrox Professional Guides)

Before you can begin the log-shipping deployment process, you need to do some initial configuration. Then you have a choice of how you want to deploy: using the SQL Server 2005 Management Studio or using T-SQL scripts. Typically, a DBA will use the SQL Server 2005 Management Studio to configure log shipping and then generate SQL scripts for future redeployment. We'll cover both procedures here.

Initial Configuration

To configure your network for log shipping, first create a backup folder that the primary server can access that is network shared and accessible by the standby server. For example, you could use the folder c:\primaryBackupLog, which is also accessible by a UNC path:

• \\primaryserver\primaryBackupLog. The primary server's SQL Agent account must have read and write permission to the folder, and the standby server's SQL Agent account or the proxy account executing the job should have read permission to this folder.

• Next, create a destination folder on the standby server such as c:\secondaryBackupDest. The standby server's SQL Agent account or the proxy account executing the job must have read and write permission to this folder.

• The recovery model for the log-shipped database must be set to either Full or Bulk_logged. There are two ways to set the recovery model: with Management Studio or with a T-SQL command. Using Management Studio, open the database properties and select Options. From the Recovery Model drop-down, choose the recovery model, as shown in Figure 19-2.

Figure 19-2

To use T-SQL, open a SQL query window and use the ALTER DATABASE command to change the recovery model. For example, to change the AdventureWorks database to full, use this T-SQL:

USE master; GO ALTER DATABASE AdventureWorks SET RECOVERY FULL; GO

Deploying with Management Studio

To deploy log shipping with Management Studio, start by opening the database to be configured and select the database properties; then select Transactional Log Shipping. Click the checkbox that reads "Enable this as a primary database in a log shipping configuration," as shown in Figure 19-3.

Figure 19-3

Then click the Backup Settings button, and you'll be taken to the Transaction Log Backup Settings dialog, as shown in Figure 19-4.

Figure 19-4

On this page, you need to provide the network path to the backup folder and the local path if the folder is local to the primary server. If the folder is local to the primary server, log shipping will use the local path. Remember that the SQL Server service and the SQL Agent account or its proxy running the backup job must have read and write permission to this folder. Whenever possible, have this folder should reside on a fault-tolerant disk system so that if a drive is lost, all the transaction log files are not lost.

Transaction-log backup files that have been applied and are older than the value in the "Delete files older than" field are deleted to control the folder size containing older transaction backup log files. However, for an additional level of protection, if the business requires point-in-time recovery, leave the files there until the OS backup program backups them up to another storage device, provided that a full database backup is also available to apply these transaction logs. The default setting is 72 hours.

For the "Alert if no backup occurs within" field, the value you choose should be based on the business requirements. For example, how much data your organization can stand to lose determines the transaction backup interval setting or how critical the data is. Additionally, the alert time depends on the transaction backup interval setting. For example if the business requires a highly available standby server where the transaction log is backed up every couple of minutes, this setting should be configured to send an alert if the job fails to run within that interval. The default setting is one hour.

Click the Schedule button and set up a schedule for the transaction-log backup job. The important setting is the "Occurs every" field, which defaults to 15 minutes. This setting can be configured down to once every minute for higher availability. However, the time interval should be appropriately set to allow the previous transaction-log backup job to complete. This value helps determine how in sync the primary and standby servers are. When you're done here, click OK on the Job Schedule Properties page; then click OK on the Transaction Log Backup Settings to return to the database properties page for Transaction Log Shipping.

Click Add to set up a secondary (standby) server, as shown in Figure 19-5.

Figure 19-5

On the Secondary Database Settings page, click Connect and choose the Secondary Server instance. Then choose an existing database or a new database name. On the Initialize Secondary Database tab, there are three options to choose from for the secondary database.

• Perform a new database backup and create a new secondary database using the database backup. The Restore Options allows you to set the database folder locations for the data and the log files. If this is not set, the default database locations are used.

• Restore from a previous database backup file, and provide a database backup file. The Restore Options allows you to set database folder locations for the data and the log files. If this is not set, the location at database backup is taken as the default and will fail if the path is no longer available.

• Secondary database already initialized. This option means that the database has already been created. The transaction logs preceding the database restore must be available to enable log shipping to work. For example, the log sequence number (LSN) must match between the primary server and the secondary server databases. Additionally, the secondary database must be in either NoRecovery or Standby mode to allow additional transaction-log files to be applied.

On the Copy Files tab, choose the Destination folder for copied files directory (for example, c:\secondaryBackupDest). The SQL Agent account or the proxy executing the copy job must have read and write permissions to this folder. The "Delete copied files after" option controls the folder size after the transaction log is restored on the secondary server's database. Any files older than the specified time are deleted. The default is 72 hours.

Click the Schedule button to set up a schedule for the transaction-log-file copy job. The important setting is the "Occurs every" field, which defaults to 15 minutes. Click OK when you're done to return to the Secondary Database Setting page.

Click the Restore Transaction Log tab. You have two options for the "On Database state when restoring backups" field:

• No recovery mode: The secondary database is left in NORECOVERY mode, which allows the server to restore additional transactional-log backups but doesn't allow user access.

• Standby mode: The secondary database allows read-only operations to be performed in the database, such as reporting. However, as mentioned previously, the restore process needs exclusive access to the secondary database; if users are accessing the database, the restore process cannot complete.

For the "Delay restoring backups at least" setting, the default is 0 minutes. Typically, you would change this setting if your organization wants to keep the secondary database around in case of a primary database's data corruption. This delay may prevent the secondary database from restoring the corrupted transaction-log file.

The "Alert if no restore occurs within" setting defaults to 45 minutes and should be set to the tolerance level of the business. An alert can be a symptom of a serious error on the secondary database that will prevent it from accepting additional transaction-log restores. Look in the history of the restore job; the default name is LS_Restore_ServerName_DatabaseName and is found under SQL Agent jobs on the secondary server. Additionally, look in the Windows Event Viewer for any additional information. Furthermore, the OPERATOR may copy and paste the restore job command into a SQL command window, which provides additional error information to help diagnose the problem.

Click OK on the Secondary Database Settings page when you're done. To add another secondary server instance, click Add and follow the same steps to add another secondary server.

To add a monitor server, from the Transaction Log Shipping page of the primary database properties, click "Use a monitor server instance." Then click Settings. A separate monitor instance from either the primary or secondary server is recommended so that a failure of the primary or secondary server won't bring down the monitor server.

On the Log Shipping Monitor Setting page, Click Connect, and choose a monitor server instance for this log-shipping environment. The account must have sysadmin role permission on the secondary server. In the "By impersonating the proxy account of the job or Using the following SQL Server login" field, choose how the backup; copy and restore jobs connect to this server instance to update MSDB job history information. For integrated security, the jobs should connect by impersonating the proxy account of the SQL Server Agent running the jobs or by SQL Server login.

The "Delete history after" field controls the amount of history data held in MSDB and defaults to 96 hours. How long to hold history depends on your business-retention requirements and the disk space you have available. The default value will be fine for most deployments unless you're planning to perform data analysis over time; then you should change the default.

When you're done, click OK on the Log Shipping Monitor Settings page. Then click OK on the Database Properties to finish setting up the Log Shipping Configuration.

Deploying with T-SQL commands

Another deployment option is to use the actual T-SQL commands to configure log shipping. Even if you choose to use the SQL Server Management Studio to configure log shipping, you should save the generated command script to allow you to quickly reconfigure the server to expedite a disaster recovery scenario while avoiding any user-induced errors. The following T-SQL commands are equivalent to the steps you took in SQL Server Management Studio.

On the primary server, execute the following stored procedures in the MSDB:

• master.dbo.sp_add_log_shipping_primary_database - Configures the primary database for a log-shipping configuration; this configures the log-shipping backup job.

• msdb.dbo.sp_add_schedule - Creates a schedule for the log-shipping configuration

• msdb.dbo.sp_attach_schedule - Links the log-shipping job to the schedule

• msdb.dbo.sp_update_job - Enables the transaction-log backup job

• master.dbo.sp_add_log_shipping_alert_job - Creates the alert job and adds the job ID in the log_shipping_monitor_alert table. The alert job is enabled then.

On the secondary server, execute the following stored procedures:

• master.dbo.sp_add_log_shipping_secondary_primary - Sets up the primary information, adds local and remote monitor links, and creates copy and restore jobs on the secondary server for the specified primary database.

• msdb.dbo.sp_add_schedule - Sets the schedule for the copy job

• msdb.dbo.sp_attach_schedule - Links the copy job to the schedule

• master.dbo.sp_add_log_shipping_secondary_database - Sets up secondary databases for log shipping

• msdb.dbo.sp_update_job - Enables the copy job

• msdb.dbo.sp_update_job - Enables the transaction-log restore job

Back on the primary server, execute this stored procedure in the MSDB:

• master.dbo.sp_add_log_shipping_primary_secondary - Adds an entry for a secondary database on the primary server