Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

How to Configure the Destination System

BizTalk Server log shipping provides standby database server capabilities, which reduces downtime in the event of a system failure. BizTalk Server log shipping works in both single server and distributed server environments. The server or group of servers that contain live data is known as the source system. The server or group of servers that are used to restore the database backups produced by the source system are known as the destination system.

You can use the following instructions to create a destination system that consists of one server for a single source system. If the destination system contains multiple servers, repeat the steps on each destination server.

Cc296836.Important(en-US,BTS.10).gifImportant
Always keep a copy of your backup files in a secure location. Even if you have log backups, you cannot restore your databases without the backup files.

You must be logged on as a member of the BizTalk Server Administrators group to perform these procedures.

Cc296836.Important(en-US,BTS.10).gifImportant
You must have the same version of SQL Server on both the source and destination systems. In addition, SQL Server must be installed in the same location on both the source and destination systems.

Cc296836.note(en-US,BTS.10).gifNote
The directory for SQL transaction log (.ldf files) on the source system must also exist on the destination system. If this directory is not on the destination system, create the directory with the same name and permissions as on the source system.

  1. On the computer or computers that you have identified as the destination system, click Start, click Run, type isqlw.exe, and then click OK.

  2. In the Connect to SQL Server dialog box, in SQL Server, specify the SQL Server on the destination system, select the type of authentication, and then click OK.

  3. In SQL Server 2000 SP4 or later, ad hoc distributed queries are disabled by default. Ad hoc distributed queries must be enabled on the disaster recovery SQL Server database instances to accommodate BizTalk Server log shipping functionality. To enable ad hoc distributed queries, execute the following SQL command from SQL Server 2000 Query Analyzer in the master database on each disaster recovery SQL Server instance:

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'Ad Hoc Distributed Queries',1;
    GO
    RECONFIGURE
    GO
    
  4. To confirm the change, run this query from SQL Server 2000 Query Analyzer to view the configured value:

    SELECT * FROM sys.configurations
    ORDER BY name ;
    

    'Ad Hoc Distributed Queries' should now be set to a value of 1.

  5. Click File, click Open, and then browse to the following SQL script:

    %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\LogShipping_Destination_Schema.sql
    
    Cc296836.note(en-US,BTS.10).gifNote
    If you do not have BizTalk Server installed on the destination system, you can copy the LogShipping_Destination_Schema.sql script to the destination system from the source system.

  6. Click Query, and then click Execute.

    The LogShipping_Destination_Schema drops and recreates the tables used for restoring the source databases on the destination system. This includes tables to store the list of databases being recovered, copies of the backup history imported from the source system's BizTalk management database, and information about SQL Server Agent jobs configured to run against the source databases.

  7. To address known issues with BizTalk Server 2006 log shipping, obtain and install the hot fix for Knowledge Base article 944838, which installs an updated version of the file LogShipping_Destination_Logic.sql used with BizTalk log shipping. To obtain this hotfix; complete the hotfix request web submission form at http://go.microsoft.com/?linkid=6294451.

  8. Click File, click Open, and then browse to the following SQL script:

    %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\LogShipping_Destination_Logic.sql
    
    Cc296836.note(en-US,BTS.10).gifNote
    If you do not have BizTalk Server installed on the destination system, you can copy the LogShipping_Destination_Logic.sql script to the destination system from the source system.

  9. Click Query, and then click Execute.

  10. Press Ctrl + N to open a new query window.

  11. In the query window paste the following command:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = '<MyLogShippingSolution>',
    @nvcMgmtDatabaseName = '<BizTalkServerManagementDatabaseName>',
    @nvcMgmtServerName = '<BizTalkServerManagementDatabaseServer>',
    @SourceServerName = null, -- null indicates that this destination server restores all databases
    @fLinkServers = 1 -- If set to 1, the server is automatically linked to the management database. If set to 0, you must manually link the server to the management database. Without the link, the job will fail.
    
  12. In the command, replace <MyLogShippingSolution> with a meaningful description, surrounded by single quotes.

    Replace <BizTalkServerManagementDatabaseName> and <BizTalkServerManagementDatabaseServer> with the name and location of your source BizTalk Management database, surrounded by single quotes.

    Cc296836.note(en-US,BTS.10).gifNote
    If you have more than one source server, you can restore each source server to its own destination server. On each destination server, in the @SourceServerName = null parameter, replace null with the name of the appropriate source server, surrounded by single quotes (for example, @SourceServerName = 'MySourceServer',).

  13. Click Query, and then click Execute.

    Cc296836.Important(en-US,BTS.10).gifImportant
    if the query fails, after you fix the problem with the query, you must start over from step 1 of this procedure to reconfigure the destination system.

    Cc296836.note(en-US,BTS.10).gifNote
    The restore jobs on the destination system will attempt to recreate the log and data files for each restored database in the same location as they existed on the source database server.

  14. On the destination system, click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager.

  15. Open the appropriate server by clicking it, click Management, click SQL Server Agent, and then click Jobs.

  16. In the details pane, you will see three new jobs:

    • BTS Log Shipping Get Backup History

      The BizTalk Server Log Shipping Get Backup History job moves backup history records from the source to the destination. It is scheduled by default to run every minute. This job runs as frequently as possible in order to move history records from the source to the destination. In the event of a system failure to the source system, the server that you identified as the destination system will continue to process the history records that have already been imported.

    • BTS Log Shipping Restore Databases

      The BizTalk Server Log Shipping Restore Databases job restores backup files for the given databases for the source to the destination server. It is scheduled by default to run every minute. This job runs continuously without completing as long as there are backup files to restore. As an extra precaution, you can run this job an additional time to ensure that it is complete.

    • BTS Log Shipping Restore To Mark

      The BizTalk Server Log Shipping Restore To Mark job restores all of the databases to a mark in the last log backup. This ensures that all of the databases are in a transactionally consistent state. In addition, this job recreates all of the SQL Server Agent jobs on the destination system that had been on the source system.

  17. Once everything is configured, check the status of the newly created SQL Server Agent jobs to make sure that they are running successfully. Check the following if any of the SQL Server Agent jobs are failing:

    • Ensure that the system time and time zone is consistent between all servers.

    • Ensure that the job is configured with the appropriate owner.

    • Ensure that NETWORK COM+ and NETWORK DTC are enabled via Add / Remove Windows Components.

    • Ensure that the Security Configuration for MSDTC is configured correctly. For information about configuring MSDTC security settings, see "Troubleshooting Problems with MSDTC" in the BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkId=101609.

  18. On a computer running BizTalk Server 2006, browse to the following folder: %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\Restore.

    Cc296836.note(en-US,BTS.10).gifNote
    On 64-bit computers, browse to the following folder: %SystemRoot%\Program Files (x86)\Microsoft BizTalk Server 2006\Bins32\Schema\Restore.

  19. Right-click SampleUpdateInfo.xml, and then click Edit.

  20. Replace all instances of "SourceServer" with the name of the source system, and then replace all instances of "DestinationServer" with the name of the destination system.

    Cc296836.Important(en-US,BTS.10).gifImportant
    Include the quotation marks around the name of the source and destination systems.

    Cc296836.note(en-US,BTS.10).gifNote
    If you renamed any of the BizTalk Server databases, you must also update the database names as appropriate.

    Cc296836.note(en-US,BTS.10).gifNote
    If you have configured BAM, you must add two more lines in OtherDatabases section of the SampleUpdateInfo.xml file for the BAMAlertsApplication and BAMAlertsNSMain databases. If you changed the default name for these two databases, please use the actual database names.

    <Database Name="BAM Alerts Application DB" oldDBName="BAMAlertsApplication" oldDBServer="SourceServer" newDBName=" BAMAlertsApplication" newDBServer="DestinationServer"/>

    <Database Name="BAM Alerts Instance DB" oldDBName="BAMAlertsNSMain" oldDBServer="SourceServer" newDBName="BAMAlertsNSMain" newDBServer="DestinationServer"/>

  21. If you have more than one MessageBox database in your BizTalk Server system, add another MessageBoxDB line to the list, and then set IsMaster="0" for the non-master databases.

  22. If you are using BAM, HWS, or the Rules Engine, EDI, uncomment these lines as appropriate.

  23. If you have any custom databases, add them as appropriate under the <OtherDatabases> section. For more information, see How to Back Up Custom Databases.

  24. When you are finished editing the file, save it and exit.

  1. On the computer or computers that you have identified as the destination system, click Start, click Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. In the Connect to Server dialog box, specify the name of the SQL Server on the destination computer, and then click Connect to connect to the appropriate SQL Server.

  3. In SQL Server 2005, ad hoc distributed queries are disabled by default. Ad hoc distributed queries must be enabled on the disaster recovery SQL Server database instances to accommodate BizTalk Server log shipping functionality. To enable ad hoc distributed queries, execute the following SQL command from SQL Server 2005 SQL Management Studio in the master database on each disaster recovery SQL Server instance:

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'Ad Hoc Distributed Queries',1;
    GO
    RECONFIGURE
    GO
    
  4. To confirm the change, run this query from SQL Server 2005 SQL Management Studio to view the configured value:

    SELECT * FROM sys.configurations
    ORDER BY name ;
    

    'Ad Hoc Distributed Queries' should now be set to a value of 1.

  5. In Microsoft SQL Server Management Studio, click File, click Open, and then click File.

  6. In the Open File dialog box, browse to the following SQL script:

    %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\LogShipping_Destination_Schema.sql
    
  7. Click the Query menu, and then click Execute.

    The LogShipping_Destination_Schema drops and recreates the tables used for restoring the source databases on the destination system. This includes tables to store the list of databases being recovered, copies of the backup history imported from the source system's BizTalkMgmtDb database, and information about SQL Server Agent jobs configured to run against the source databases.

  8. To address known issues with BizTalk Server 2006 log shipping, obtain and install the hot fix for Knowledge Base article 944838, which installs an updated version of the file LogShipping_Destination_Logic.sql used with BizTalk log shipping. Since this article is not yet publicly available, please contact Microsoft Product Support Services to obtain the hot fix associated with this Knowledge Base article. To contact Microsoft Product Support services call 1-800-936-5800, select option #2 for developer support, and then request the hotfix for KB# 944838.

  9. In Microsoft SQL Server Management Studio, click File, click Open, and then click File.

  10. In the Open File dialog box, browse to the following SQL script:

    %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\LogShipping_Destination_Logic.sql
    
  11. Click the Query menu, and then click Execute.

  12. On the computer or computers you have identified as the destination system, click Start, click Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  13. In the Connect to Server dialog box, specify the name of the SQL Server on the destination computer, and then click Connect to connect to the appropriate SQL server.

  14. In Microsoft SQL Server Management Studio, click New Query.

  15. In the query window paste the following command:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = '<MyLogShippingSolution>',
    @nvcMgmtDatabaseName = '<BizTalkServerManagementDatabaseName>',
    @nvcMgmtServerName = '<BizTalkServerManagementDatabaseServer>',
    @SourceServerName = null, -- null indicates that this destination server restores all databases
    @fLinkServers = 1 -- 1 automatically links the server to the management database
    
  16. In the command, replace <MyLogShippingSolution> with a meaningful description, surrounded by single quotes. Replace <BizTalkServerManagementDatabaseName> and <BizTalkServerManagementDatabaseServer> with the name and location of your source BizTalk Management database, surrounded by single quotes.

    Cc296836.Important(en-US,BTS.10).gifImportant
    Before you execute this statement, you must enable the Ad Hoc Distributed Queries configuration option on the destination system.

    Cc296836.note(en-US,BTS.10).gifNote
    If you have more than one source server, you can restore each source server to its own destination server. On each destination server, in the @SourceServerName = null parameter, replace null with the name of the appropriate source server, surrounded by single quotes (for example, @SourceServerName = 'MySourceServer',).

  17. Click the Query menu, and then click Execute.

    Cc296836.Important(en-US,BTS.10).gifImportant
    if the query fails, after you fix the problem with the query, you must start over from step 1 of this procedure to reconfigure the destination system.

    Cc296836.note(en-US,BTS.10).gifNote
    The restore jobs on the destination system will attempt to recreate the log and data files for each restored database in the same location as they existed on the source database server.

  18. On the destination system, in SQL Server Management Studio, double-click the appropriate server, double-click SQL Server Agent, and then double-click Jobs.

  19. In the details pane, you will see three new jobs:

    • BTS Log Shipping Get Backup History

      The BizTalk Server Log Shipping Get Backup History job moves backup history records from the source to the destination. It is scheduled by default to run every minute. This job runs as frequently as possible in order to move history records from the source to the destination. In the event of a system failure to the source system, the server that you identified as the destination system will continue to process the history records that have already been imported.

    • BTS Server Log Shipping Restore Databases

      The BizTalk Server Log Shipping Restore Databases job restores backup files for the given databases for the source to the destination server. It is scheduled by default to run every minute. This job runs continuously without completing as long as there are backup files to restore. As an extra precaution, you can run this job an additional time to ensure that it is complete.

    • BTS Log Shipping Restore To Mark

      The BizTalk Server Log Shipping Restore To Mark job restores all of the databases to a mark in the last log backup. This ensures that all of the databases are in a transactionally consistent state. In addition, this job recreates all of the SQL Server Agent jobs on the destination system that had been on the source system.

  20. Once everything is configured, check the status of the newly created SQL Server Agent jobs to make sure that they are running successfully. Check the following if any of the SQL Server Agent jobs are failing:

    • Ensure that the system time and time zone is consistent between all servers.

    • Ensure that the job is configured with the appropriate owner.

    • Ensure that NETWORK COM+ and NETWORK DTC are enabled via Add / Remove Windows Components.

    • Ensure that the Security Configuration for MSDTC is configured correctly. For information about configuring MSDTC security settings, see "Troubleshooting Problems with MSDTC" in the BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkId=101609.

  21. On a computer running BizTalk Server 2006, browse to the following folder: %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\Restore.

    Cc296836.note(en-US,BTS.10).gifNote
    On 64-bit computers, browse to the following folder: %SystemRoot%\Program Files (x86)\Microsoft BizTalk Server 2006\Bins32\Schema\Restore.

  22. Right-click SampleUpdateInfo.xml, and then click Edit.

  23. Replace all instances of "SourceServer" with the name of the source system, and then replace all instances of "DestinationServer" with the name of the destination system.

    Cc296836.Important(en-US,BTS.10).gifImportant
    Include the quotation marks around the name of the source and destination systems.

    Cc296836.note(en-US,BTS.10).gifNote
    If you renamed any of the BizTalk Server databases, you must also update the database names as appropriate.

    Cc296836.note(en-US,BTS.10).gifNote
    If you have configured BAM, you must add two more lines in OtherDatabases section of the SampleUpdateInfo.xml file for the BAMAlertsApplication and BAMAlertsNSMain databases. If you changed the default name for these two databases, please use the actual database names.

    <Database Name="BAM Alerts Application DB" oldDBName="BAMAlertsApplication" oldDBServer="SourceServer" newDBName=" BAMAlertsApplication" newDBServer="DestinationServer"/>

    <Database Name="BAM Alerts Instance DB" oldDBName="BAMAlertsNSMain" oldDBServer="SourceServer" newDBName="BAMAlertsNSMain" newDBServer="DestinationServer"/>

  24. If you have more than one MessageBox database in your BizTalk Server system, add another MessageBoxDB line to the list, and then set IsMaster="0" for the non-master databases.

  25. If you are using BAM, HWS, or the Rules Engine, EDI, uncomment these lines as appropriate.

  26. If you have any custom databases, add them as appropriate under the <OtherDatabases> section. For more information, see How to Back Up Custom Databases.

  27. When you are finished editing the file, save it and exit.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.