通过

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

  • Anonymous
    January 19, 2012
    Excellent article.
  • Anonymous
    December 13, 2012
    Really, answers most of the questions. Can we ask some questions here??
  • Anonymous
    December 18, 2012
    Yes. Please post your questions.
  • Anonymous
    February 20, 2013
    When I am executing BTS Log shipping - restore to mark , the job is failing.
  • Anonymous
    March 06, 2013
    @Vasu - What is the error message that you are getting?
  • Anonymous
    March 20, 2013
    The comment has been removed
  • Anonymous
    May 07, 2013
    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
  • Anonymous
    July 29, 2013
    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)
  • Anonymous
    August 31, 2014
    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?