Export (0) Print
Expand All

How to Configure the Destination System for Log Shipping

[Unless specifically noted, the content in this topic applies to BizTalk Server 2013 and 2013 R2.]

Log shipping provides standby server capabilities, which reduces downtime in the event of a system failure. Log shipping allows you to automatically send transaction logs from the source system to the destination system. At the destination system the transaction logs are restored to the BizTalk Server databases, keeping them closely synchronized with the source databases.

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 (or primary) system. The server or group of servers that are used to restore the database backups produced by the source (or primary) system is known as the destination (or secondary) system.

For more information about log shipping, see the SQL Server Books Online athttp://go.microsoft.com/fwlink/?LinkID=131096.

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.

ImportantImportant
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 this procedure. You must have the same version of SQL Server on both the source and destination systems. SQL Server must be installed in the same relative location on both the source and destination systems.

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 All Programs, click Microsoft SQL Server 2008 R2, 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 Microsoft SQL Server Management Studio, in the File menu, click Open, and then click File.

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

    noteNote
    You should run these scripts on the master database (msdb).

    %SystemDrive%\Program Files\Microsoft BizTalk Server <version>\Schema\LogShipping_Destination_Schema.sql
    
  5. 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.

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

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

    noteNote
    You should run these scripts on the master database (msdb).

    %SystemDrive%\Program Files\Microsoft BizTalk Server <version>\Schema\LogShipping_Destination_Logic.sql
    
  8. Click the Query menu, and then click Execute.

  9. On the computer or computers you have identified as the destination system, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

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

  11. In Microsoft SQL Server Management Studio, on the toolbar, click New Query.

  12. 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
    
  13. 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.

    ImportantImportant
    Before you execute this statement, you must enable the Ad Hoc Distributed Queries configuration option on the destination system. For more information about setting server configuration options, see the SQL Server 2008 Books Online at http://go.microsoft.com/fwlink/?LinkID=131455.

    noteNote
    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',).

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

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

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

  15. On the destination system, in SQL Server Management Studio, double-click the appropriate server, double-click SQL Server Agent, and then double-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 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 re-creates all of the SQL Server Agent jobs on the destination system that had been on the source system.

    ImportantImportant
    You should monitor these jobs to ensure that they do not fail.

  17. On a computer running BizTalk Server 2013 R2, browse to the following folder: drive:\Program Files\Microsoft BizTalk Server 2013 R2\Schema\Restore.

    noteNote
    On 64-bit computers, browse to the following folder: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore.

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

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

    ImportantImportant
    Include the quotation marks around the name of the source and destination systems.

    noteNote
    If you renamed any of the BizTalk Server databases, you must also update the database names as appropriate.

    noteNote
    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"/>

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

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

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

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

© 2014 Microsoft Corporation. All rights reserved.

Community Additions

ADD
Show:
© 2014 Microsoft