BizTalk Log Shipping


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.

Comments (9)

  1. nilesh says:

    Excellent article.

  2. KP says:

    Really, answers most of the questions. Can we ask some questions here??

  3. Jainath V R says:

    Yes. Please post your questions.

  4. vasu says:

    When I am executing BTS Log shipping – restore to mark , the job is failing.

  5. chiragpa says:

    @Vasu – What is the error message that you are getting?

  6. KP says:

    Dear Jainath, Thanks for accepting to post questions here.

    We have a new requirement from customer. They do not want to bring Production offline during DR exercise. Normally, during the log shipping we follow the steps described in

    msdn.microsoft.com/…/aa546753(v=bts.10).aspx to restore the databases in DR.

    Also we promote the SSO as per msdn.microsoft.com/…/aa559842(v=bts.10).aspx

    Note that both production and DR are in same domain(network). During DR exercise we advise customer to stop the production services. This is because once you promote the master secret it won’t be available in production environment. Now customer is refused to take production off line during DR exercise. They want both up and running because of their own reasons.

    Is there any other way to resolve this issue.

    Regards,

    KP.

  7. sridhar says:

    Hi,

    When i run the last bts sp it is throwing error about  bts_importSQLAgentJobs cannot insert null into column database name. But when I checked the tables in teh master it did populate with source server name and source databases. BUT the big questions is none of the BTS jobs are created on the destination server. Any thoughts or suggestions please.

    Thanks,

    Sridhar

  8. Lex Hegt says:

    Great article! I've added it to an article on BizTalk backups on TechNet Wiki (social.technet.microsoft.com/…/7702.aspects-of-backup-for-your-biztalk-databases.aspx)

  9. Ermir says:

    Great Article, but i have one issue:

    I configured biztalk logshipping and restored the database in DR and everything seems fine.

    Now what do i have to do to go back to my live envoirment?

    Do i have to do the reverse configuration?