Of late we see a lot of incidents in which customers face issues with LogShipping. Either they are not able to properly configure the BizTalk LogShipping or while they are able to configure Logshipping, they face an issue getting the DR(Disaster Recovery) server up and running or encounter trouble pointing the BizTalk server to the DR SQL server.  I have put down the steps to be followed for BizTalk Log Shipping. The steps mentioned are not detailed but have been broken down into simpler ones. If you need detailed information please refer to the corresponding MSDN article. 

In case of disaster recovery and database restoration, Log Shipping in the only supported method for BizTalk databases.

Articles covered:

  • Planning for Disaster Recovery
  • —Configuring Log Shipping
  • —Recovering from failure
  • —How to reconfigure destination SQL server

Planning for Disaster Recovery:

  • Source and Destination SQL Server
    1. Both servers should be at the same patch level
    2. —Both servers can communicate to each other over the network
    3. —Both servers can perform MSDTC communication (use dtcping tool to check)
    4. —Both servers have the same location for MDF and LDF files
  • —It is recommended that back-up and transaction-log files should be written to a highly-available?
    UNC share, which is accessible to both the servers and have ample space available.— 
  • Make sure that Backup BizTalk Server job is configured properly and working fine.

Configuring Log Shipping:

[Refer Configuring LogShipping for detailed
information]

  • —  Run the following two SQL scripts on the Destination SQL Server to create the infrastructure for Log Shipping:
    1. —  LogShipping_Destination_Schema.sql
    2.   LogShipping_Destination_Logic.sql
  • Execute bts_ConfigureBizTalkLogShipping

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

  • Make sure the following two jobs are up and running:
    1. —  BTS Log Shipping Get Backup History
    2. —  BTS Server Log Shipping Restore Databases (WITH NORECOVERY)
  • —  And that the following job is disabled:
    1. —  BTS Log Shipping Restore To Mark (WITH RECOVERY)

Your Log Shipping is configured now and you should see BizTalk DBs in restoring state on the DR site

Update “SampleUpdateInfo.xml” on the BizTalk Server so that all database server information is updated with DR SQL server details.

 Recovering from failure:

  • —  Disable following jobs on the Destination SQL Server:
    1. —  BTS Log Shipping Get Backup History
    2. —  BTS Server Log Shipping Restore Databases
  • —  Enable following job on the Destination SQL Server:
    1. —  BTS Log Shipping Restore To Mark

Once this job is completed, go to the next step for pointing the BizTalk server to the newly restored BizTalk DB.

  • —  Run the following command on BizTalk Server:
    1. —  cscript UpdateDatabase.vbs SampleUpdateInfo.xml (Any one BizTalk Server in  group)
    2. —  cscript UpdateRegistry.vbs SampleUpdateInfo.xml (All BizTalk Server in group)
  • —  Restart all the BizTalk related services on BizTalk Server
  • —  Point BizTalk Administration Console to the new SQL Server: After you run UpdateRegistry.vbs BizTalk Administration Console will point to new SQL server, However if you are not seeing this then Right Click on BizTalk Administration Console, Connect to Existing Group and select new SQL server on which the databases were restored.

How to reconfigure Destination Database server for LogShipping:

  • —Run stored procedure master.dbo.bts_LogShippingClean, to clean up destination SQL server . After running this the whole database for which we are doing LogShipping will be deleted.
  • —Delete jobs manually which were created previously to perform LogShipping
  • Run the following two SQL scripts on the Destination SQL Server to create the infrastructure for Log Shipping:
    1. LogShipping_Destination_Schema.sql
    2. LogShipping_Destination_Logic.sql

Note: This will clean everything from the DR DB site

  • —Execute bts_ConfigureBizTalkLogShipping. After executing this you will observer the jobs are recreated again and will be in the ‘running state”. Once you wait
    a while, you will see all BizTalk databases appearing under database with state as (restoring).

Below is simple illustration on what we do in BizTalk Logshipping:


  
Written by:        
   Anand Singh

Reviewed By:
   Chirag Pavecha

Microsoft GTSC.