Issue while performing BizTalk Log Shipping in Disaster Recovery process

Recently, we had a case in which our customer was performing a Disaster Recovery for his BizTalk Server environment. We were facing some issues while restoring BizTalkMgmtDb.

Environment Information:

1. While configuring the BizTalk Server, customer configured it with DNS name for SQL server “Sy-abcd-01.env.ia.iapq.net,46154”. They have configured BizTalkMsgBoxDb on a different Server (Sy-abcd-02.env.ia.iapq.net,46155 ). So, all the other Databases except BizTalkMsgBoxDb are on the other Server Sy-abcd-01.env.ia.iapq.net,46154.

   BizTalk Server Configuration -> Group.
   

2. They want to perform a DR process:
   Source Servers:
   Sy-abcd-01.env.ia.iapq.net,46154 : This node contains BizTalkMgmtDb and all other database except BizTalkMsgBoxDb.
   Sy-abcd-02.env.ia.iapq.net,46155 : This node contains only BizTalkMsgBoxDb.   

   DestinationServers:
   Sy-abcd-11.env.ia.iapq.net,46154: This node will contain BizTalkMgmtDb and all other database except BizTalkMsgBoxDb.
   Sy-abcd-12.env.ia.iapq.net,46155: This node contains only BizTalkMsgBoxDb.  

3. While doing DR, we are having issues with restoring the databases from the first server Sy-abcd-01.env.ia.iapq.net,46154 (BizTalkMgmtDb andthe other Dbs) . Henceforth, we will only discuss about restoring all the DBs except BizTalkMsgBoxDb.

Issue Details:

1. We checked the View admv_BackupDatabases on the Source Server:
    

   Note: BizTalkMgmtDb shows server name as SY-ABCD-01\BIZTALK01ENV while others show as “Sy-abcd-11.env.ia.iapq.net,46154”. Destination BizTalkMsgBoxDb in on a different server "Sy-abcd-12.env.ia.iapq.net,4615".        

2. Followed How to Configure the Destination System for Log Shipping to start with Log Shipping.     

3. We ran the following scripts on the Destination Server (Sy-abcd-11.env.ia.iapq.net,46154) :
   a. LogShipping_Destination_Schema.sql
   b. LogShipping_Destination_Logic.sql
   c.  

   exec bts_ConfigureBizTalkLogShipping @nvcDescription = 'BizTalkLogShippingIA',@nvcMgmtDatabaseName = 'BizTalkMgmtDb',@nvcMgmtServerName = 'sy-abcd-01.env.ia.iapq.net,46154',@SourceServerName = 'sy-abcd-01.env.ia.iapq.net,46154' , -- null indicates that  this destination server restores all databases@fLinkServers = 1 -- 1 automatically links the server to the management database

   It starts restoring all the Databases except 'BizTalkMgmtDb' .
   Note: We are not setting SourceServerName as null because BizTalkMsgBoxDb is on a different Server.

4.  Tried again for SourceServerName = 'SY-ABCD-01\BIZTALK01ENV' , and ran the following scripts:

   1. LogShipping_Destination_Schema.sql

   2. LogShipping_Destination_Logic.sql

   3. exec bts_ConfigureBizTalkLogShipping @nvcDescription = 'BizTalkLogShippingIA',@nvcMgmtDatabaseName = 'BizTalkMgmtDb',@nvcMgmtServerName = 'sy-abcd-01.env.ia.iapq.net,46154',@SourceServerName = 'SY-ABCD-01\BIZTALK01ENV' , -- null indicates that this destination server  restores all databases@fLinkServers = 1 -- 1 automatically links the server to the management database

      This time, it starts restoring only 'BizTalkMgmtDb' .

Root Cause :

1. It seems the issue is because of the entries created in Table [master].[dbo].[bts_LogShippingDatabases] (and bts_LogShippingJobs for the jobs).

   The entry that was creating issue was for column [DatabaseName] with value BizTalkMgmtDb.

   So, we need to modify/add the column [ServerName] corresponding to the above column to value sy-abcd-01.env.ia.iapq.net,46154.

2. Also, the jobs for BizTalkMgmtDb were not getting created due to the entries in [bts_LogShippingJobs] due to the same reason.

   We need to enter the information here for BizTalkMgmtDb jobs .

Action plan to resolve the issue:

We made the changes to the tables due to which this issue was occurring. We followed the below steps:

1. Execute : LogShipping_Destination_Schema.sql

2. Execute : LogShipping_Destination_Logic.sql

3. Execute : 

   exec bts_ConfigureBizTalkLogShipping @nvcDescription = 'BizTalkLogShippingIA',@nvcMgmtDatabaseName = 'BizTalkMgmtDb',@nvcMgmtServerName = 'sy-abcd-01.env.ia.iapq.net,46154',@SourceServerName = null, -- null indicates that this destination server restores all databases@fLinkServers = 1 -- 1 automatically links the server to the management database

   Note: We have entered null here so that all the entries are being made and we will delete or modify the ones that we don’t require.

4.  Stop SQL Agent jobs.
   This is to make sure that BTS Log Shipping Get Backup History, BTS Server Log Shipping Restore Databases and BTS Log Shipping Restore To Mark don’t run before we start making changes to the tables.

5. Modify bts_LogShippingDatabases by following scripts:

   a. DELETE FROM [master].[dbo].[bts_LogShippingDatabases] WHERE DatabaseName = 'BizTalkMsgBoxDb'

   b. UPDATE [master].[dbo].[bts_LogShippingDatabases] SET ServerName = REPLACE (Servername, 'SY-ABCD-01\BIZTALK01ENV', 'sy-abcd-01.env.ia.iapq.net,46154')
    WHERE DatabaseName = 'BizTalkMgmtDb' 

                   bts_LogShippingDatabases after the changes:

   

6. Modify bts_LogShippingJobs by following scripts:

   a. DELETE FROM [dbo].[bts_LogShippingJobs] WHERE SourceDatabaseName = 'BizTalkMsgBoxDb' 
   Note: To delete the jobs created for 'BizTalkMsgBoxDb' as it’s on different server.

   b. UPDATE [dbo].[bts_LogShippingJobs]
       SET enabled = '0'
       WHERE job_name = 'Backup BizTalk Server (BizTalkMgmtDb)'

   Note: This is because customer wants to keep 'Backup BizTalk Server’ as disabled when it’s created. They will start it after configuring.

   bts_LogShippingJobs after the changes:
   

7. Start the SQL Agent jobs and follow the normal Log shipping and Disaster Recovery steps now. Eg: After performing the above steps, follow the steps starting from step 10 in To configure the destination system for log shipping.

   With these steps, we were able to successfully perform the disaster recovery.              

   Written BY
   Kunal Gupta

   Reviewed By
   Xuehong Gan

   Microsoft GTSC