November 16, 2004
**********************************************************************
© 2004 Microsoft Corporation. All rights reserved.
**********************************************************************
These release notes contain important information about the release of the Microsoft® BizTalk® Server 2004 Log Shipping feature included with SP1.
You should have a working knowledge of the log shipping implementation in Microsoft® SQL Server™ and familiarity with the full recovery model and log restoration process in SQL Server. For more information about these features, see SQL Server Books Online.
The following information is covered in this document:
The Microsoft® BizTalk® Server 2004 Log Shipping (Log Shipping) feature for SP1 extends the existing Backup BizTalk Server SQL Agent job provided with BizTalk Server 2004. Log Shipping eliminates the need to manually restore a series of backup sets produced by the backup job, and reduces downtime in the event of a system failure.
Due to the distributed database design of BizTalk Server 2004, when you produce backups you must be certain to provide a consistent point to which the backups can be restored. Transactions can span multiple databases; if one database goes offline and must be restored, then all related databases must be restored to a single point in time to ensure that the system is in a consistent state.
Note Not all databases participate in distributed transactions. For more information, see "Backing up your databases" in BizTalk Server 2004 Help.
The Backup BizTalk Server job uses Microsoft® SQL Server™ log marking to provide an automated process that produces database backup sets. These sets include synchronized points that are used during the restore process. As part of the process of restoring a set of databases produced by the Backup BizTalk Server job, the last log backup file for each database is restored to a specific log mark. When the SQL Server implementation of log shipping is applied to BizTalk Server databases, there is no final log to restore to the mark.
To keep an outstanding log backup set, Log Shipping does not restore the most current log backup set. In the event of a system failure, you must manually restore the most current log backup set to the log mark in order to provide a consistent database environment.
| Back to Top |
After you install Microsoft® BizTalk® Server 2004 SP1, you must configure Log Shipping and modify both the source and destination system.
Log Shipping works in both single server and distributed server environments. The server or group of servers that is live is known as the source system. The server or group of servers that will be used to restore the database backups produced by the source system is known as the destination system. You must make modifications to both the source and destination systems in order to enable Log Shipping.
For the purposes of Log Shipping, it does not matter if the source system is located on a single server or if it is distributed among multiple servers. There are no additional considerations other than those required to successfully run the Backup BizTalk Server job.
The text in the Command window should now look as follows:
EXEC [dbo].[sp_BackupAllFull_Schedule] 'h' /* Frequency */,
'BTS' /* Name */,
'\\BackupShare' /* location of backup files */,
1 /* 1 to force full backups after backup failures*/
Note This is optional and configures the job to automatically create full backups when failures are detected. To disable this feature set the parameter to 0 or remove the parameter completely.
Note The default value is 14 days.
Use the following instructions to create a destination environment that consists of one server for a single source system. If the destination environment contains multiple servers, repeat the steps on each destination server.
To modify the destination system
<install directory>\Schema\LogShipping_Destination_Schema.sql
<install directory>\Schema\LogShipping_Destination_Logic.sql
| Column Name | Data Type | Description |
|---|---|---|
| SourceId | Int | Creates an ID for the source system. Any number will work. This is used to identify the source for all operations. |
| SourceDesc | nvarchar(256) | Description of the source system. |
| MgmtDatabaseName | Sysname | Name of the Mgmt database for the source system. |
| MgmtServerName | nvarchar(256) | Name of the source system server that holds the Configuration database (BizTalkMgmtDb). |
| Column Name | Data Type | Description |
|---|---|---|
| SourceId | int | ID for the source system to which the database belongs. |
| DatabaseId | int | ID for the database. This can be any unique integer. It does not have to be the system database ID. |
| DatabaseName | sysname | Name of the database in the source system. |
| ServerName | nvarchar(256) | Name of the source server where this database lives. |
| LogFileLocation | nvarchar(1500) | Location of the log backup files. If null, bts_LogShippingHistory.BackupFileLocation is used as the default. |
| DBFileLocation | nvarchar(1500) | Location of the full database backup files. If null, bts_LogShippingHistory.BackupFileLocation is used as the default. |
| RestoreName | sysname | Name to use when restoring the database. If null, the original source database name is used. |
Note Two new jobs have been created:
This section assumes a good understanding of the process of manually restoring backups produced by the Backup BizTalk Server job. For more information, see "Restoring your databases" in the BizTalk Server 2004 Help.
If there is only one server in the destination environment, make sure that all of the log backup sets (except for the most recent set) have been restored. For more information, see Viewing Restore History and Restore History Gaps. If all the log backup sets have not been restored, and the restore job is not currently running, run the restore job (manually if necessary). If there are outstanding backup sets that can be restored, the job will process them until they are all restored.
When all of the backup sets have been restored, manually restore all of the log files in the last backup set using the STOPATMARK option and specify the log mark name. This brings the destination environment online. For more information, see Viewing Restore History.
If there are multiple servers in the destination environment, all servers must be restored to the same backup set. You must view the restore history on each server and make sure that the most recent log backup set restored is the same on all servers. If it is not, you must manually run the restore job on each server that needs the most recent log backup set restored. After all of the servers are on the same backup set, the final set can be manually restored.
| Back to Top |
When you install BizTalk Server 2004 SP1, the Log Shipping feature is enabled. There are also a number of files that are changed on the source and destination system.
As a result of installing BizTalk Server 2004 SP1, the source system is changed in several areas to support the log shipping process and to provide easier administration of the Backup BizTalk Server job. The changes are as follows.
The adm_BackupHistory table is the central history point for the log shipping process for the source system. All backup work performed is recorded to this table. All destination servers read from this table to receive the information needed to perform their restore work.
This new step in the Backup BizTalk Server job deletes records from the adm_BackupHistory table. Set the @DaysToKeep value to the number of days to keep history. The default value is 14 days. Records older than this value will be deleted.
The Backup BizTalk Server job logic has been modified to automatically detect cases of backup failures. You can configure the job to automatically create a full database backup set the next time it runs. This begins a new log restore chain.
Note In some cases, such as a Microsoft® Windows® failure to write data to the backup file, it may not be recognized as a failure by the backup process because SQL Server sees the backup as having succeeded. In this case, the restore process fails due to corruption in the backup file and indicates that the log chain is broken and that a full database backup is needed.
This functionality is not automatically enabled to avoid overwriting existing settings for the BackupFull step.
To enable full backup after a backup failure:
The text in the Command window should now look as follows:
EXEC [dbo].[sp_BackupAllFull_Schedule] 'h' /* Frequency */,
'BTS' /* Name */,
'\\BackupShare' /* location of backup files */,
1 /* 1 to force full backups after backup failures*/
Note To disable this feature, set the parameter to 0 or remove the parameter completely.
Important In some cases it is necessary to force the source system to produce a full backup immediately. This might be necessary in the following situations.
For more information, see Forcing a Full Backup.
The Backup BizTalk Server logic has been modified to support up to 12 MessageBox databases and up to 12 databases (12 total records) in the adm_OtherBackupDatabases table.
The destination system is the server or set of servers that restore the backup files produced by a source or set of source systems. When Log Shipping is enabled, each destination environment is comprised of the following pieces:
SQL Server Agent Jobs
This job should be scheduled to run more frequently than the Backup BizTalk Server job on the source system. By default, it runs once per minute. It is important that the destination server have as current a copy of backup history as possible.
This job should be scheduled to run at the same frequency as the Backup BizTalk Server job on the source system.
Note This job will continue to run as long as it has backup sets to restore.
Tables
Stored Procedures
| Back to Top |
To determine the last successful backup set restored, review the contents of the Master.dbo.bts_LogShippingHistory table. This table is populated by the "get backup history" job and updated by the restore job. When a backup is successfully restored, the Restored column is set to 1 and the RestoredDateTime is set to the current date and time.
When all of the databases being restored to the server from a particular backup set have been successfully restored, that backup set ID is written to the Master.dbo.bts_LogShippingLastRestoreSet table.
When reviewing records in the bts_LogShippingHistory table, you may find gaps in the sets restored. This can occur for several reasons. However, you can still recover the stability of your destination system, even when gaps have occurred. A gap must be followed by a restore of a full backup set to repair the destination environment. If a gap is not followed by a full backup set restore, the destination environment is not stable. For more information, see Troubleshooting.
Note Full backups are only restored to initially create the database and to repair problems in the log history backup chain. In most cases full backup sets are not restored, as they do not participate in the log backup chain.
| Back to Top |
If the source system fails, you can recover by pointing to the destination server so that the destination server becomes the source.
Before you experience a failure, these items must be completed:
To recover from a source system failure using the destination backup files
If the source system becomes unreliable, you can restore the databases from the destination to the source.
To restore the databases from the destination to the source.
| Back to Top |
The following information explains some issues common in Log Shipping.
This error may occur when Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for network transactions when Microsoft Windows XP Service Pack 2 is running on the source system.
To resolve this issue you must enable MSDTC. See Microsoft Knowledge Base article 873160 at http://support.microsoft.com/?kbid=873160 for more information.
You may encounter problems when backing up the databases on the source system that result in a partial backup set. When this occurs, the Master.dbo.bts_LogShippingHistory table will contain a 0 in the SetComplete column for all records in the set.
These sets cannot be restored. As a result the log backup set chain is broken. You must ignore this set, as well as all log backup sets after it, up to the next full backup set. The restore job will automatically look for the next valid full backup set. If it does not find one, it fails. If it does find one, it will restore that set in order to repair the destination environment.
In most cases the source system will detect that a partial backup set has occurred and will automatically produce a full backup set the next time it runs if it is configured to do so. For more information, see" Automatically Producing a Full Backup after a Backup Failure" in the topic Changes to the Source System.
Note The most common cause of this problem is running out of disk space where the files are stored.
A backup file may become corrupt, damaged or missing. If this occurs, at least one file cannot be restored. The restore job on the system that suffered the failure searches for the next valid full backup set. In most cases it will be necessary to force a full backup on the source system. If no such set exists, the restore job fails and each subsequent run also fails until a valid full backup set arrives. If a set does exist, it is used to repair the environment.
Note Due to the manner in which SQL Server writes backup data to a file, it is possible that SQL Server will report successful completion even if the backup failed during the actual writing of the data. This scenario primarily occurs when the disk being written to is not local to the machine and a network failure or interruption occurs. As a general precaution, if a network failure occurs while the backup job is running, you should force a full backup after the problem is resolved.
If you are adding a new database to the source system, use the following steps to modify the Log Shipping system to include the new database:
To add a new database to the source system
Check that the new database has been backed up.
Note The restore job will see that the databases do not exist and will create all of them using the most recent full backup set, which is the set created by step 4.
When you restore a database on an alternate server you get the local users but not the accompanying login. When a log is restored to a different server, you can get "orphaned" logins - that is, the login does not match its database user. SQL Log Shipping requires you to manage your logins separately from your Log Shipping process. For more information, see "HOW TO: Transfer Logins and Passwords Between Instances of SQL Server" in the Microsoft Knowledge Base at http://support.microsoft.com/default.aspx?kbid=246133.
In a distributed destination system the restore jobs are not aware of errors or problems on the other machines. For example, machine A is restoring the BizTalkMgmt database and the BizTalkDTA database, and machine B is restoring the BizTalkMsgBox database. Both machines successfully restore backup sets 1 through 25. Set 26 however has a corrupted log backup file of the BizTalkMsgBox database. Machine A restores its databases correctly. but machine B fails to restore the corrupted file.
In this situation, you must force a full backup on the source system. Continuing the example above, assume that the problem was diagnosed and a full backup was created for set 35. Machine A then restores sets 26 to 34, because it is not aware of the problem on Machine B. Machine B will fail until it sees full backup set 35 and subsequent log backup set 36 (remember that there must always be one subsequent complete log backup for a set to be restored). When sets 35 and 36 arrive on machine B, it will repair itself using 35. After the repair is complete, machine A and B will be in sync.
If a restore job is failing and cannot repair itself because it does not have a full backup set, or if the destination environment has been cleaned it will be necessary to force the source system to produce a full backup. To force a full backup, run the stored procedure sp_ForceFullBackup. This procedure is located in the BizTalk Configuration database on the source system. The procedure flags the backup process to produce a full database backup for all databases the next time the job runs. You can also run it manually to immediately create a full backup set.
If the restore job encounters error conditions that cannot be resolved, clean the destination environment so that it can start from an empty environment. You clean the environment by running the stored procedure sp_LogShippingClean. This procedure drops all databases and deletes the last restored set data for the specified source.
Before you run this procedure, you should disable the restore job (the get backup history job may continue running). After you run this procedure, the next time the restore job runs it will find the most recent full backup set with a valid subsequent log backup set. If this set has already been restored, it clears the Restored column for the set and all subsequent sets. and then proceeds with restoring the set.
Note Because the job looks for the most recent full backup set after the environment has been cleaned, you should force a full backup on the source system after running this procedure but before running the restore job.
Note The sp_LogShippingClean procedure must be repeated on all servers that are restoring databases for a given source system in order to keep the different servers in synch with each other in terms of which sets have been applied.
Depending on how the source system was configured during deployment, you may have to resolve "orphaned" users. An orphaned database user is a user who does not have a corresponding login. You must create corresponding logins for these users before the system can be brought online. You can create these logins at any point, but it is recommended that you create them when BizTalk Server 2004 Log Shipping is configured.
The logins that you create should correspond to the Windows accounts and groups that were used when BizTalk Server was configured on the source system and to any logins that were manually created and used in any BizTalk Server-created role. If these logins correspond to local Windows accounts or groups, the accounts and groups must first be created before the login can be added. If the computer name for the SQL Server is not changed, then you must resolve the users associated with the logins for the local accounts and groups. For more information, see "Establishing Secure Group Accounts" in BizTalk Server 2004 Help.
Note Knowledge Base article 246133 provides two different methods for moving logins between servers. The method for SQL Server 2000 does not work for BizTalk Server 2004 Log Shipping, because the databases remain in a loading state at all times until they are manually recovered to a log mark.
You can use the method for Microsoft® SQL Server™ 7.0 to create a script that will add the necessary logins to the destination server. This process creates the login script when you first enable BizTalk Server 2004 Log Shipping. When logins are added or removed, you can re-run the script. For more information, see "HOW TO: Transfer Logins and Passwords Between Instances of SQL Server" in the Microsoft Knowledge Base at http://support.microsoft.com/default.aspx?kbid=246133.
Note Knowledge Base article 240872 provides a method for resolving security identification numbers (SIDs) and computer name conflicts when local Windows users/groups are used. For more information, see "HOW TO: Resolve Permission Issues When You Move a Database Between Servers That Are Running SQL Server" in the Microsoft Knowledge Base at http://support.microsoft.com/default.aspx?kbid=240872.
| Back to Top |
Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
© 2004 Microsoft Corporation. All rights reserved.
Microsoft, MS-DOS, Windows, Windows NT, Windows Server, and BizTalk are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.