BizTalk DR: Configuring BizTalk Backup for Disaster Recovery – Part 2

Originally posted by Nick Heppleston at: http://www.modhul.com/2009/09/04/configuring-biztalk-backup-for-disaster-recovery-part-2/

This is the second of a three-part series covering the BizTalk Backup Process, where I’m looking at one of the two supported methods to backup and restore a BizTalk Server environment – Log Shipping. The series will cover the following topics:

  • In Part 1, I’ll provide a high-level overview of the backup and restore process. Part 1 is already published, go an read it now!
  • In Part 2, I’ll walk through an actual backup and restore setup, looking at the configuration steps as we go.
  • In Part 3, I’ll demonstrate a long-running BizTalk application that is restored on a sample disaster recovery environment using the backup and restore process configured in Part 2.

Please accept my apologies for the wide screenshots in this part of the series. Please also note that I have not repeated the configuration steps on the MSDN website – this is an excellent resource and I do not wish to repeat or plagarise this work. In this part of the series, I have simply added my own observations that I thought were lacking from the MSDN text.

BizTalk Log Shipping – A Quick Recap

Before we look at how the BizTalk Log Shipping process is configured, let us quickly recap on the process; for a more detailed overview, I recommend that you read Part One of this series of blog posts, specifically the section which covers The Backup and Restore Process.

The backup and restore process (BizTalk Log Shipping) is as follows:

  • The BizTalk Backup SQL Agent Job creates data and transaction-log backups from the production environment, placing log marks into the transaction-log backups. An audit table is maintained which lists the backups that have been created, the type of backup (data or transaction-log backup) and details the location of the backup files. The physical data- and log-files are then pushed to a UNC share, as shown below:

BizTalk Backup Process Overview

  • The standby environment reads the audit table maintained by the production environment, using this information to determine which data- and transaction-log files need to be restored. These files are restored from the UNC share using the SQL ‘NORECOVERY’ option, leaving the databases in a non-operational state, allowing additional transaction-logs to be applied. Note: the transaction-logs are not restored to the transaction-log mark in this step, instead the full transaction-log is restored:

BizTalk Backup Process Overview

These two steps continue throughout live operations. In the event of a disaster recovery situation, where the production environment has been lost and the standby environment needs to be brought online, three recovery steps must be performed on the standby environment:

  • The most recent transaction-log backup is restored to its transaction-log mark using the SQL ‘WITH RECOVERY’ option, placing the databases into an operationally ready state.
  • Database and registry updates statements are run to re-configure the restored databases to use the standby environment.
  • BizTalk services are brought online (including the Enterprise Single Sing-On server and Business Rules Engine service etc.) and the standby environment is made live as the production environment.

Once the live environment is recovered and the switch from the standby environment back to the live environment is ready to be made, the process is reversed, with the standby environment creating backups that are restored to the live environment.

In this post, we’ll look at how to configure each of these steps.

BizTalk Log Shipping – Prerequisites

Several prerequisites need to be setup before configuring BizTalk Log Shipping. I would recommend that you spend time to ensure that these areas work, as they will save you a great deal of pain when configuring log shipping. They include:

  1. Before starting to configure the BizTalk Log Shipping process, ensure that the production and standby servers can communicate with each other over the network;
  2. The production and standby SQL Server instances need to communicate with each other via MSDTC (Microsoft Distributed Transaction Coordinator). There are a number of excellent resources on the internet addressing MSDTC configuration issues including Troubleshooting Problems with MSDTC on the MSDN website. Ensure that the two serverscan communicate over MSDTC before starting to configure Log Shipping;
  3. The BizTalk backup and restore SQL Agent jobs should run as under an isolated user account – choose a Windows user account for this purpose and create a SQL Server login for this account. Map this SQL Server login to the BTS_BACKUP_USERS database role in the BizTalk Server databases; and finally
  4. It is recommend that the data- and transaction-log backup files are written to a highly-available UNC share. Ensure that an UNC share is created that is accessible by both the production and standby servers and that the necessary permissions are assigned. Check that you can read, write and delete files from this share using Windows Explorer on both  servers.

Configuring The Backup Job

The first task in configuring BizTalk Log Shipping is to configure the SQL Agent ‘Backup BizTalk Server (BizTalkMgmtDb)’ backup job on the production environment. This job is created by the BizTalk installer and is responsible for a number of tasks, including:

  • Creating a full database backup at the specified interval;
  • Writing a transaction-log mark to the transaction-log and creating a backup of the transaction-log; and finally
  • Clearing the backup history table, based on specified criteria.

To configure the job, follow the instructions detailed in the excellent How to Configure the Backup BizTalk Server Job on the MSDN website.

When configuring the backup job, remember the following:

  • The Frequency parameter denotes the frequency at which a full backup is taken. By default, this is set to d (daily) which is sufficient for most BizTalk environments. If you require more frequent full backups, change this accordingly – other values include h (hourly), w (weekly), m (monthly), or y (yearly). Note that irrespective of what frequency you set here, transaction log backups will occur every time this job is run.
  • The (optional) Force full backup after partial backup failures parameter instructs the backup job to create a full backup if a transaction-log backup failed the last time the job ran, ignoring the Frequency setting. For example, if you use a setting of ‘daily’ for the Frequency parameter and the job fails to create a full set of transaction-log backups, the next time the job runs, it will create a full-backup followed by a further transaction-log backup; a second full-backup followed by a transaction-log backup will be created when the job runs again at midnight (UTC time) to honour the Frequency setting.
  • When setting the Location of backup files parameter, ensure that the user who is running this SQL Agent job has sufficient privileges to write to that location.

By default, this job runs once every fifteen minutes; this means that a transaction-log backup will be created and restored once every fifteen minutes (once you have configured the log shipping restore jobs, which execute once every minute). If you want to reduce delay in restoring these transaction-logs to your standby environment, reduce the scheduled frequency of the backup job. I would however recommend that this job is run no more frequently than once every three minutes; you will however need to find a happy medium that works best in your environment – this will be based on the load on your BizTalk Server and the rate at which your SQL Server transaction-logs grow.

Once you have configured the backup job, execute it starting at step one and ensure you have backup data- and log-files in your UNC share location, and entries in the BizTalkMgmtDb.dbo.adm_BackupHistory table, as follows:

BackupHistory

These entries in the Backup History table will be read by the restore jobs on the standby SQL Server and used to identify which data and log files need to be restored. Of particular interest in the above screenshot of the Backup History table are the following columns:

  • The BackupSetId column, which is used during the restore to identify an entire backup-set;
  • The MarkName column, which details the Transaction-Log Mark Name given to transaction-logs (Notice how the full database-backup has a value of NULL instead of a a log mark name, as these cannot be applied to full backups);
  • The BackupFilename and BackupFileLocation columns, which are used by the restore jobs to locate the data- and log-files to restore;
  • The BackupType column, which is used by the restore jobs to identify the type of backup the backup set relates to; and finally
  • The SetComplete column, which is used by the (optional) Force full backup after partial backup failures parameter of the backup job to determine whether a full or partial backup was taken during the last execution.

One final point to note about the ‘Backup BizTalk Server (BizTalkMgmtDb)’ backup job: if the SQL Agent Service or the backup-job is disabled and misses the time-window in which the full-backup should be taken (based on the Frequency parameter), the next execution of the job will cause a full backup to be created. For example, if you use a setting of ‘daily’ for the Frequency parameter and the job is disabled between 2000 and 0200 (i.e. during the time window that the daily full-backup will occur – 0000UTC), when the job is enabled, it will detect that its ‘frequency’ has been missed and cause a full-back to be created, followed by a transaction-log backup.

Forcing a Full Backup

There may be cases where you wish to perform an ad-hoc full-backup, outside of the Frequency window specified in Backup BizTalk Server SQL Agent Job – for example, you may want to take a full backup before applying a hotfix or service pack, or when replacing hardware.

When a backup is forced, the Frequency window is ignored next time the Backup BizTalk Server job executes and both data- and log-file backups are generated. If a problem occurs in the scenarios described above and the databases need to be recovered, only one transaction log needs to be restored for each database, reducing the recovery time significantly.

To force a full backup, execute the BizTalkMgmtDb.dbo.sp_ForceFullBackup stored procedure; the next time the Backup BizTalk Server job executes, both data- and log-file backups will be generated.

Configuring BizTalk Log Shipping – The Restore Jobs

We’re now ready to configure the standby environment to automagically restore our data- and log-file backups that are created by our production environment.

Several new jobs will be created in the standby SQL Server environment to handle the restoring of backups (along with a number of tables and stored procedures in the master database, which are used to hold information about the Log Shipping process). The jobs in particular are responsible for a number of tasks, including:

  • Retrieving the backup history from the production environment;
  • Restoring the data- and transaction-log backup files to the standby SQL Server in a non-operational state, allowing further transaction-logs to be restored; and finally
  • Restoring all of the databases to an operational state, by restoring the next available transaction-log to their last log mark only when disaster recovery is invoked and the standby SQL Server environment is to be made operational.

For more detailed information on these jobs, I recommend that you read the first part of this series, specifically the section which covers the Restore Process and the BizTalk Log Shipping Jobs.

To configure log shipping on the standby environment, follow the instructions detailed in the excellent How to Configure the Destination System for Log Shipping on the MSDN website.

When configuring the backup job, remember the following:

  • The standby environment will try and read the data- and log-files from the same location as they were written to by the backup job; it is therefore prudent to use a UNC share location (mapped to a drive letter if necessary) that both the standby and production environments have read/write permissions to.
  • If you encounter problems executing the bts_ConfigureBizTalkLogShipping stored procedure, ensure that you have correctly configured MSDTC between the production and standby environments; additionally, check that you have enabled Ad Hoc Distributed Queries on the standby SQL Server (if using SQL Server 2005/2008) as this stored procedure executes distributed queries over a linked server to the production SQL Server.
  • If you need to re-configure your standby environment, re-run the configuration process including starting with the LogShipping_Destination_Schema.sql and LogShipping_Destination_Logic.sql before executing the bts_ConfigureBizTalkLogShipping stored procedure.

A note about the Physical Location of Database Files when Restoring

If you have been around SQL Server for any length of time, you will have come across some of the more ‘helpful’ features of restoring data- and transaction-log files – when you restore a backup, SQL Server will attempt to re-create the database on the standby environment in exactly the same directory structure as the data- and log-file/s appeared on the production SQL Server.

If the directory structure doesn’t exist on the standby server exactly as it did on the production server, you will receive an error similar to the following and the restore will fail:

Executed as user: BIZTALKDRBizTalkBackup. Directory lookup for the file “D:MSSQL.4MSSQLDATABizTalkMgmtDb.mdf” failed with the operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 5133) File ‘BizTalkMgmtDb’ cannot be restored to ‘D:MSSQL.4MSSQLDATABizTalkMgmtDb.mdf’. Use WITH MOVE to identify a valid location for the file. [SQLSTATE 42000] (Error 3156) Directory lookup for the file “D:MSSQL.4MSSQLDATABizTalkMgmtDb_log.LDF” failed with the operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 5133) File ‘BizTalkMgmtDb_log’ cannot be restored to ‘D:MSSQL.4MSSQLDATABizTalkMgmtDb_log.LDF’. Use WITH MOVE to identify a valid location for the file. [SQLSTATE 42000] (Error 3156) Problems were identified while planning for the RESTORE statement. Previous messages provide details. [SQLSTATE 42000] (Error 3119) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

There are a number of ways we can resolve this, including using the WITH MOVE statement, as suggested in the error text, however this would require us to make changes to the stored procedure that is invoked by the Restore Databases SQL Agent job – this would invalidate our warranty with Microsoft as changes to the SQL Server portion of the codebase are not supported.

SQL Server uses several techniques to determine the physical location that databases should be restored to (further information can be found in Microsoft Knowledgebase Article 221465); the easiest method is to perform an implicit ‘MOVE’ – if the database already exists on the standby environment, but at a different location than specified in the backup set, the location on the server is used during the restore.

I would therefore recommend that you create the various BizTalk databases (including the Business Rules Engine Db and SSO Db) in their correct locations on the standby environment before you enable the restore job, this way SQL Server will restore the databases to your preferred location.

Testing the Restore Functionality

With log shipping successfully configured, enable the BTS Log Shipping – Get Backup History (DBServer: “[Database Server Name]”, DBName: “[Database Name]”) SQL Agent job to retrieve backup history information from the production environment.

If everything is configured correctly, you will see entries in the standby SQL Server master.dbo.bts_LogShippingHistory table which show the backups that are yet to be restored (see the Restored and RestoredDateTime columns highlighted in Green). In the following screenshot, there is one full database backup, plus three transaction-log backups to be restored.

LogShippingHistory-DatabasesToBeRestoredv2

With the backup history successfully retrieved from the production environment, we’re now ready to start restoring the data- and log-files to our standby environment.

Enable the BTS Log Shipping – Restore Databases (DBServer: “[Database Server Name]”, DBName: “[Database Name]”) SQL Agent job. As described in the first post of this series, this job restores the BizTalk databases to a non-operational state, allowing further transaction-logs to be restored. The job uses the  ‘NORECOVERY’ option, leaving each database in the ‘Restoring…’ state, thereby allowing additional transaction-logs to be applied.

In the screenshot below, the Restore Databases job has been enabled and has executed, restoring the full backup set (BackupType ‘Db’) and two transaction-log backup sets (BackupType ‘Lg”) as highlighted in Green; the transaction log set that has not been restored – highlighted in Orange – is the N-1 backup-set that is only restored when the standby environment is to be brought online as the new production environment; this is to ensure that we always have one transaction-log that can be restored to the transaction-log mark. Also note that the RestoredDateTime column is populated with the UTC time, not the actual system time.

LogShippingHistory-RestoredDatabasesv2

One final point to take away when restoring databases is the way in which the restore job handles full backups (BackupType ‘Db’). If the production environment creates a full backup – either because the Backup job has crossed its Frequency window, or the BizTalk Administrator has forced a full backup – the Log Shipping Restore job will only restore the transaction log created during the full backup – it skips the data-file backup as this isn’t required to maintain database integrity. As demonstrated in the screenshot below, the transaction logs before and after the full backup have been restored (highlighted in Green), however the data-file backup hasn’t been restored (highlighted in Purple); finally, the N-1 transaction log set is yet to be restored (highlighted in Orange):

LogShippingHistory-RestoredDatabasesFullBackupNotRestored

Cleaning the Standby Environment

You may want to clean the standby environment without performing a full reconfiguration of BizTalk Log Shipping, so that the restore process starts from scratch: the databases are re-created and restoration starts as if it is the first time it has run.

To achieve this, execute the master.dbo.bts_LogShippingClean stored procedure on the standby environment. This stored procedure deletes all references to the previously restored backup-sets and physically deletes the databases that are in the ‘restoring’ state. The bts_LogShippingClean stored proc accepts a single parameter @SourceId; the value you should use for this  parameter is based on your log shipping configuration which is available in the master.dbo.bts_LogShippingDatabases table, an example of which is shown below:

CleanupLogShipping-SourceId

These two actions force the BTS Log Shipping – Restore Databases job to re-create the databases in a restoring state by applying the last full backup, followed by subsequent transaction log backups the next time it runs. This is demonstrated in the screenshot below – the data-file and transaction-log backups are restored (highlighted in Green), with the N-1 transaction log set is yet to be restored (highlighted in Orange); also notice how the full- and transaction log backups are restored sequentially at the same time, within a few seconds of each other (see the RestoredDateTime column):

LogShippingHistory-LogShippingCleanFullBackupRestored

Handling Partial Backup-Sets

One of the most common errors I encountered while putting together this series of blog posts related to partially restored backups-sets. The SQL Agent BTS Log Shipping – Restore Databases job automagically detects this condition and raises an error in the Event Log similar to the following:

Executed as user: BIZTALKDRSqlAgent. A partial set has been detected but a valid full backup set does not exist yet.  Please force a full backup on the source server. [SQLSTATE 42000] (Error 50000)  @LastSetId: 487 [SQLSTATE 01000] (Error 0)  @RestoredToMark: 0 [SQLSTATE 01000] (Error 0)  Here101 [SQLSTATE 01000] (Error 0)  @NextSetId: 488 [SQLSTATE 01000] (Error 0)  Here102 [SQLSTATE 01000] (Error 0)   [SQLSTATE 01000] (Error 0)  Statement2: 1 [SQLSTATE 01000] (Error 0)  Statement3: 1 [SQLSTATE 01000] (Error 0).  The step failed.

The restore job identifies this error condition by simply checking for a backup set that has both restored and non-restored entries, as shown in the screenshot below (highlighted in Orange):

LogShippingHistory-PartialRestore

When a partially restored backup-set is detected, the restore job will attempt to identify and restore a more recent full data- and transaction-log backup, thereby minimizing the synchronization gap between the production and standby environments. If a more recent full backup cannot be found, the job will fail reporting the error detailed above and either wait until a new full backup is created as the backup job passes its Frequency window, or when the BizTalk Administration forces a full backup.

Wrap-Up

So that’s the end of Part 2. We’ve walked through configuring the backup and restore jobs and looked at how to check that these jobs are working correctly, how to force full backups and problems caused by partial restores.

In Part 3, we’ll demonstrate a long-running BizTalk application that is restored and brought operational on a standby environment using the instructions detailed here.

Postscript: Creating a Sandbox Environment to Test Log Shipping

Before jumping into configuring Log Shipping on a production environment, I would suggest that you become familiar with how the process itself works, either in a test or sandboxed environment. In writing this series of blog posts, I created a small VirtualBox based virtual-machine sandbox with which I could test the various scenario’s I planned on writing about. This sandbox consisted of:

  • A ‘production’ server hosting BizTalk and the BizTalk databases in an operational state;
  • A ‘standby’ server hosting BizTalk and the restored BizTalk databases;
  • A standalone server acting as an Active Directory Domain Controller.

All three servers communicate through a private VirtualBox network and cannot be seen outside of the physical host machine. The ‘production’ and ‘standby’ servers run Windows Server 2008, plus BizTalk Server 2009 and SQL Server 2008 (all Enterprise Edition), running with 512Mb RAM; the AD server runs Windows Server 2008 Core, running with 256Mb RAM. All three virtual-machines run extremely well given their small amount of RAM, however they need to be located on a separate HDD to the physical machine’s OS otherwise heavy I/O thrashing causes a significant degradation in performance.

Reblog this post [with Zemanta]
Advertisements

41 thoughts on “BizTalk DR: Configuring BizTalk Backup for Disaster Recovery – Part 2

  1. Gary,
    Re. your ‘3 SQL Server Instances’ question, I don’t have an answer to hand, but will put together a test rig and test this out

    Hi Nick,

    I went through the PART2 of your article and coming up with a big issue that I’m facing. Here I’m starting with our configuration and what I did and what issues I’m facing. I hope there will be some solution for my problem and expecting that solution from you.

    I have tested having 3 SQL server instances for BizTalk Application.

    SQL1INS1 has BizTalkMsgBoxDb database

    SQL1INS2 has BizTalkDTADb database

    SQL1INS3 has BizTalkMgmtDb and SSODB databases

    SQL1INS2 has the built-in backup job Backup BizTalk Server (BizTalkMgmtDb) and have enabled it & configured the parameters for the job steps.

    Amazing..all the databases from the 3 instances are backing up!!

    Next step, I’m configuring BizTalk Logshipping. For this I did the following:

    On Destination:

    STEP1:I have Installed 3 SQL server instances of Server 2005.

    SQL2INS1

    SQL2INS2

    SQL2INS3

    STEP2:Installed BizTalk server 2006 R2 and it created databases on all of the 3 instances same as in Source Server (Primary/Live SQL Sevrer)

    STEP3: Connected to SQL2INS2 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2INS2 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ”,
    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,
    @nvcMgmtServerName = ‘SQL1INS2’, (why SQL1INS2? because it is the instance where Backup job is there on Source SQL Server)
    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    STEP5:The Backup BTS Log Shipping – Restore Databases (DBServer: “sql1ins2”, DBName: “BizTalkMgmtDb”) is failing with the below error message

    Executed as user: ABCmssqlsrv. Directory lookup for the file “D:MSSQL.4MSSQLDATABizTalkMgmtDb.mdf” failed with the operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 5133) File ‘BizTalkMgmtDb’ cannot be restored to ‘D:MSSQL.4MSSQLDATABizTalkMgmtDb.mdf’. Use WITH MOVE to identify a valid location for the file. [SQLSTATE 42000] (Error 3156) Directory lookup for the file “D:MSSQL.4MSSQLDATABizTalkMgmtDb_log.LDF” failed with the operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 5133) File ‘BizTalkMgmtDb_log’ cannot be restored to ‘D:MSSQL.4MSSQLDATABizTalkMgmtDb_log.LDF’. Use WITH MOVE to identify a valid location for the file. [SQLSTATE 42000] (Error 3156) Problems were identified while planning for the RESTORE statement. Previous messages provide details. [SQLSTATE 42000] (Error 3119) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

    Here, I got what the problem is, But I want to know the solution for that.

    Problem:

    On Source, the 3 SQL instances along with one Reporting services instance installed to the below mentioned paths:

    SQL1INS1–>D:MSSQL.1MSSQLDATA
    D:MSSQL.2Reproting Services

    SQL1INS1–>D:MSSQL.3MSSQLDATA

    SQL1INS3–>D:MSSQL.4MSSQLDATA

    On Destination, the 3 SQL inntances with NO Reporting services to the below mentioned paths:

    SQL2INS1–>D:MSSQL.1MSSQLDATA

    SQL2INS1–>D:MSSQL.2MSSQLDATA

    SQL2INS3–>D:MSSQL.3MSSQLDATA

    So when restoring the databases on Destination SQL instance SQL2INS2, it looking for the path D:MSSQL.4MSSQLDATABizTalkMgmtDb.mdf, which DOES NOT EXIST on Destionation.

    Then, I performed the following steps to correct the error, but I DID NOT get the solution.

    Steps performed:

    I have manulally copied the full backups of all databases form the source to destionation and the below steps

    Step1: connected to SQL2INS1 and restored the BizTalkMsgBoxDb database using WITH NORECOVERY and used with move options to place mdf & ldf files on D:MSSQL.1MSSQLDATA

    Step2:connected to SQL2INS2 and restored the BizTalkDTADb database using WITH NORECOVERY and used with move options to place mdf & ldf files on D:MSSQL.2MSSQLDATA

    Step3: connected to SQL3INS3 and restored theBizTalkMgmtDb and SSODB database susing WITH NORECOVERY and used with move options to place mdf & ldf files on D:MSSQL.3MSSQLDATA

    Having done with all steps, I’m getting the same error and it still looking for D:MSSQL.4MSSQLDATA —No luck

    please advice me what to do next?

    Thank you
    Gary

  2. Hi,

    I have made some improvement!! Now the databases are restoring with no errors (I tried one more time)! But, one more thing to make sure.

    I went to the error log which is in SQL2INS1, and look for the messages “log was restored” but I can see only the full backup restored message “Database was restored: Database: BizTalkMsgBoxDb, creation date(time): 2009/01/05(18:26:23), first LSN: 3249:2589:68, last LSN: 3249:2617:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘D:backupsSQL1_INS1_BizTalkMsgBoxDb_Full_BTS_2009_09_04_18_00_00_530.bak’}). Informational message. No user action required.” which I have manually restored in Step1 above.

    In SQL2INS3 error log also has no messages regaridng the log backup has restored.

    But I can see all database’s logs applying in the error log of SQL2INS2 as below:

    2009-09-04 14:02:03.46 Backup Log was restored. Database: BizTalkMgmtDb, creation date(time): 2009/01/05(18:25:39), first LSN: 1516:1239:1, last LSN: 1516:1308:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘\SQL1BackupsSQL1_INS2_BizTalkMgmtDb_Log_BTS_2009_09_04_13_45_00_430.bak’}). This is an informational message. No user action is required.
    2009-09-04 14:02:04.44 Backup Log was restored. Database: BizTalkRuleEngineDb, creation date(time): 2009/01/05(18:33:31), first LSN: 39:113:1, last LSN: 39:115:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘\SQL1BackupsSQL1_INS2_BizTalkRuleEngineDb_Log_BTS_2009_09_04_13_45_00_430.bak’}). This is an informational message. No user action is required.
    2009-09-04 14:02:05.67 Backup Log was restored. Database: SSODB, creation date(time): 2009/01/05(18:22:34), first LSN: 134:120:1, last LSN: 134:122:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘\SQL1BackupsSQL1_INS2_SSODB_Log_BTS_2009_09_04_13_45_00_430.bak’}). This is an informational message. No user action is required.
    2009-09-04 14:02:10.07 Backup Log was restored. Database: BizTalkMsgBoxDb, creation date(time): 2009/01/05(18:26:23), first LSN: 3249:9041:1, last LSN: 3249:9674:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘\SQL1BackupsSQL1_INS1_BizTalkMsgBoxDb_Log_BTS_2009_09_04_13_45_00_430.bak’}). This is an informational message. No user action is required.
    2009-09-04 14:02:11.23 Backup Log was restored. Database: BizTalkDTADb, creation date(time): 2009/01/05(18:26:05), first LSN: 104:237:1, last LSN: 104:239:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘\SQL1BackupsSQL1_INS3_BizTalkDTADb_Log_BTS_2009_09_04_13_45_00_430.bak’}). This is an informational message. No user action is required.

    So why, its writing all the 3 instances information only in SQL2INS2’s error log? why not in the error log of SQL2INS1 & SQL2INS3?? Is this normal behavior of BizTalk Log shipping involved with multiple SQL instances??

    If this is clarified, then I’m almost done with the major configiration in BizTalk logshipping.

    One more thing:

    One Source, even the Backup job on SQL1INS2 is backing up all the databases from other 2 instances, the log information (i.e messages like database backed up, log back was backed up ) is writing to each instance’s error log. There is no problem. But on Destination, all information is writing to SQL2INS2’s error log.

    Please advice…

  3. Madhu,
    The @SourceId parameter is based on the field of the same name in the ‘master.dbo.bts_LogShippingDatabases’ table. Pass the SourceId for the database/s you wish to clean.

    I will update the post to identify where to get the SourceId – Post updated ;-)

    Cheers, Nick.

  4. Gary,
    Let me start by saying that you don’t need the same number of SQL Server instances in your destination / standby environment as you have in production. Unless you have a specific requirement for this setup, I would recommend that you use just one SQL instance for your destination server.

    Re. your first comment – the reason you are receiving this error after manually performing the restore is because the Log Shipping jobs write entries into their tables, indicating that a restore has been started. Because these records are not present after you have done a manual restore, the jobs assume they have to start from scratch and throw exactly the same error. So in a nutshell, you always need to run the restore from the supplied jobs.

    With regards to restoring the databases, I have added a new section regarding the location of the restores – reading http://support.microsoft.com/kb/221465, it would appear that if you create your databases in the directory structure you want, and then perform a restore, SQL Server will use the location you specify (as the databases already exist) and will not try and create them in a directory structure that does not exist.

    Hope this helps, Nick.

  5. Thanks,

    Our requirement is to have the same number of SQL Server instances on destination / standby environment as we have in production. Here the BizTalk Log shipping is working fine. But I want to know why its writing all the 3 instances information (restore log information of all databases) only to SQL2INS2 error log? why not in the error log of SQL2INS1 & SQL2INS3??

  6. Who should be the owner of BizTalk Databases?

    I’m a SQL DBA and I have maintaining BizTalk databases in SQL Server 2005.

    Server A—SQL Server 2005
    Server B– BizTalk Server 2006 R2

    BizTalk databases were created on Server A, while installing the BizTalk Server 2006 R2 application on Server B. Before installing the Application, I have created a login “BizAdmin” and granted Sysadmin rights (as requested by BizTalk Server Admin). All the Databases and all the jobs were created with BizAdmin has the OWNER.

    Later, I have changed the database owner for all databases from “BizAdmin” to SA and I have changed the owner of all the jobs to MSSQLAdmin, in order NOT to have Sysadmin rights to “BizAdmin”

    Everything is working fine. But now we want configure BizTalk Backup job and BizTalk Log shipping. Is changing the owner from BizAdmin to MSSQLAdmin will effect anything? Is this change allowed?

  7. Hi Nick,

    In standby environment, I have restored the all the full backups of BizTalk databases (Full backups have been taken on Source & copied to Standby & then restored with norecovery option) using WITH MOVE & NORECOVERY options and then enabled the BizTalk Logshipping and Log Shipping working fine so far.

    Is restoring the databases prior to enable BizTalk Log shipping SUPPORTED or NOT?

    thanks

  8. Thanks Nick,

    What about the Database owner? changing database owner from Bizadmin to SA will effect any thing? Here SA login is disabled. Do I need enable this SA login?

    please advice

  9. Hi Nick,

    We have 3 SQL server instances for BizTalk Application as below:

    SQL1INS1, which has BizTalkMsgBoxDb database

    SQL1INS2, which has BizTalkDTADb database

    SQL1INS3 ,which has BizTalkMgmtDb and SSODB databases

    My question is whats the use of having 3 instances and having only one or two databases in each instance? Is there any requiremnet for BizTalk application to separate its databases across multiple SQL instances to increase performance or any advantage?

  10. Hi Nick,

    Did you try Configuring LogShipping having multiple SQL instances on Source & Standby Server?

    On Stand by environment, I have installed 3 instances, SQL2INS1, SQL2INS2, SQL2INS3 to match exactly with the Source Server and trying to do the following.

    Step 1:

    Connect to SQL1INS1 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    and then executing:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ‘MyLogShippingSolution’,
    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,
    @nvcMgmtServerName = ‘SQL1INS1′,
    @SourceServerName = BizTalkMgmtDb’,’BizTalkRuleEngineDb’, ‘SSODB’, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    But I’m getting the below error:

    Msg 119, Level 15, State 1, Line 1
    Must pass parameter number 5 and subsequent parameters as ‘@name = value’. After the form ‘@name = value’ has been used, all subsequent parameters must be passed in the form ‘@name = value’.

    In Step2 & Step3, I want to repeat Step1 on other 2 instances SQL1INS2, SQL2INS1, by replacing @sourceServername and @nvcMgmtServerName to Respected Source instance name & database names if I did not get above error?

    thanks

  11. Hi,

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ”,
    @nvcMgmtDatabaseName = ”,
    @nvcMgmtServerName = ”,
    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    In above script, do we need to MUST ONLY enter “BizTalkMgMtDb” database in the line @nvcMgmtDatabaseName = ‘?

    Because, Out of my 3 Stand-by SQL instances, SQL2INS3 has “BizTalkMgMtDb”. So I must need to give BizTalkMgMtDb & SQL1INS3 as below:

    @nvcMgmtDatabaseName =BizTalkMgMtDb ‘
    @nvcMgmtServerName = ”(here I need to give Source SQL instance where “BizTalkMgMtDb” is exist right? In my case SQL1INS3 right?)

    What I have to do with the rest of the stand-by SQL instances, SQL2INS1, SQL2INS2?

    I appreciate your help!!

  12. Hi,

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ”,
    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,
    @nvcMgmtServerName = ‘SQL1INS3′, (why SQL1INS3? because it is the instance where Backup job is there on Source SQL Server)
    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    From above script, what should I change in below to 2 lines instead of @SourceServerName = null & @fLinkServers = 1

    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    Because I do not want to backup all databases to only oneinstance on Stand-by SQL2INS3. But I want to have as below:

    1. SQL1INS1 has BizTalkMsgBoxDb database and it should restore to SQL2INS1

    2.SQL1INS2 has BizTalkDTADb database and it should restore to SQL2INS2

    3.SQL1INS3 has BizTalkMgmtDb and SSODB databases and it should restore to SQL2INS3.

    thanks

  13. Gary,
    You need to provide the database and SQL Instance name of your management database (so you are correct, it would be ‘BizTalkMgmtDb’ and ‘SQL1INS3’); the configure stored proc will go and interrogate the MgmtDb to determine where all of your other databases are located.

    Rgds, Nick.

  14. Kln,
    I would not recommend that you run the BizTalk Backup Job as the ‘sa’ user – create a SQL Server user specifically for the backup and restore tasks (or a user in AD and add that user to SQL).

    With regards to your multiple database question, BizTalk does not impose a requirement to partition your databases; if however you are experiencing performance issues, it is recommended that you physically separate out your MsgBoxDb from the other databases as a starting point (the MsgBoxDb has the majority of ‘traffic’ in a BizTalk system). For example, my current client runs an active/active/passive cluster with the MsgBox on the first active server and the remaining databases on the second active server – if either of the two active servers die, the databases will fall-over to the passive server.

    Hope this helps, Nick.

  15. Thanks Nick,

    SOURCE:

    We have one BizTalk application Server and have 3 SQL server instances on same server for BizTalk Application on SourceProduction as below:

    SQL1INS1 has BizTalkMsgBoxDb database

    SQL1INS2 has BizTalkDTADb database

    SQL1INS3 has BizTalkMgmtDb and SSODB databases

    SQL1INS2 has the built-in backup job Backup BizTalk Server (BizTalkMgmtDb) and have enabled it & configured the parameters for the job steps and it backing up all the databases from all the 3 SQL instances.

    DESTINATION:

    We have one BizTalk application Server and have 3 SQL server instances on same server on DestinationStand-by same as Source SourceProduction as below:

    SQL2INS1 has BizTalkMsgBoxDb database

    SQL2INS2 has BizTalkDTADb database

    SQL2INS3 has BizTalkMgmtDb and SSODB databases

    Next step, I’m configuring BizTalk Logshipping. For this I did the following:

    On Destination:

    STEP1:I have Installed 3 SQL server instances of Server 2005.

    SQL2INS1

    SQL2INS2

    SQL2INS3

    STEP2:Installed BizTalk server 2006 R2 and it created databases on all of the 3 instances same as in Source Server (Primary/Live SQL Sevrer)

    STEP3: Connected to SQL2INS3 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2INS3 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ”,
    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,
    @nvcMgmtServerName = ‘SQL1INS3′, (why SQL1INS3? because it is the instance where Backup job is there on Source SQL Server)
    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    here all the 4 databases are restoring to SQL2INS3 and in other 2 instances SQL2INS1 & SQL2INS2 nothing is happening. But I want to have as below:

    1. SQL1INS1 has BizTalkMsgBoxDb database and it should restore to SQL2INS1

    2.SQL1INS2 has BizTalkDTADb database and it should restore to SQL2INS2

    3.SQL1INS3 has BizTalkMgmtDb and SSODB databases and it should restore to SQL2INS3.

    To acheive the above, what should I change in the below script and Do I need to run all the 3 scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql
    in other 2 instances, SQL2INS1 & SQL2INS2 too

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ”,
    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,
    @nvcMgmtServerName = ‘SQL1INS3′, (why SQL1INS3? because it is the instance where Backup job is there on Source SQL Server)
    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    what should I change in below to lines instaead of @SourceServerName = null & @fLinkServers = 1

    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    thanks

  16. Do we need to run the below 3 scripts in each Stand-by SQL instance as below:

    1)

    STEP3: Connected to SQL2INS3 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2INS3 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ‘Logshipping’,
    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,
    @nvcMgmtServerName = ‘SQL2INS3′,
    @SourceServerName = SQL2INS3, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    2)

    STEP3: Connected to SQL2INS2 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2INS2 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ‘Logshipping’,
    @nvcMgmtDatabaseName = ‘SSODB’,
    @nvcMgmtServerName = ‘SQL1INS3′,
    @SourceServerName = SQL1INS3, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    3)

    STEP3: Connected to SQL2INS1 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2INS1 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ‘Logshipping’,
    @nvcMgmtDatabaseName = ‘BizTalkMsgBoxDb’,
    @nvcMgmtServerName = ‘SQL1INS1′,
    @SourceServerName = SQL1INS1, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    4)

    STEP3: Connected to SQL2INS2 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2INS2 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ‘Logshipping’,
    @nvcMgmtDatabaseName = ‘BizTalkMsgBoxDb’,
    @nvcMgmtServerName = ‘SQL1INS2′,
    @SourceServerName = SQL1INS2, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

  17. Hi Nick,

    I have a SQL server 2005, Server A and BizTalk Server 2006 R2, Server B on Primary location. Similarly, we have a SQL Server 2005, Server C, BizTalk Server 2006 R2, Server D on Secondary/Stand-by location.

    BizTalk application is deloyed on Primary & it is a Production Server. On Secondary/Stand-by, SQL Server is installed, BizTalk is installed but the developed application is NOT deployed.

    Now, we configured BizTalk Logshipping between Primary & Secondary. While configuring BizTalk Logshipping:

    1. Took the backup of current databases on Secondary SQL Server.
    2. Deleted the databases on Secondary
    3. Configured the BizTalk Log shipping
    4. All the databases are in Restoring mode.
    5. BizTalk Log shipping Test is completed succesfully.
    6. Now, we want to Disable the Configured Log shipping and want to restore the databases of Step1.
    7. Then Deploy the BizTalk Appliccation on Secondary BizTalk Server Server D.
    8.Then Reconfigure the BizTalk Log shipping again, which will be a full fledged Production configuration

    In-order to achieve the above, what is the best method to Disable Log Shipping, to freshly re-configure Log shipping configuration??

    thanks

  18. Hi Nick,

    Can we take the Differential backup, if the BizTalk Log shipping is configured and its Taking Daily full backups and every 15 mins Log backups?

    What happens if we schedule the Differential backup every 6 hrs? Is that supported?

    Please advice

    thanks
    Kln

  19. Great set of articles about BizTalk log-shipping, how does this all work though with a stand-by BizTalk host server? Currently we run a seperate Biztalk host server at our DR site with it’s own set of BizTalk databases and seperate service accounts/domain groups. I am about to redesign this, would it be best to chnage our DR installation of BizTalk to use the same service accounts and domain groups as production? I’d like to implement the BizTalk log-shipping process but we also want to keep our DR installation independent for DR testing.

  20. –>RestoredDateTime column is populated with the UTC time, not the actual system time

    Can we change this to populate the system time instead of UTC time?

    thanks

    • Not that I’m aware of, unless you go hacking the stored proc’s. I would suggest creating a view and modify the datetime there. Nick.

  21. Hello

    I’m failing to get the bts_ConfigureBizTalkLogShipping stored procedure to execute properly. It created the linked server perfectly fine, and then bombs out with the following error message:

    OLE DB provider “SQLNCLI10” for linked server “(null)” returned message “Unable to complete login process due to delay in opening server connection”.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “SQLNCLI10” for linked server “(null)”.

    I’m using SQL 2008, MSDTC is running, configured Select Network DTC Access, Allow Inbound, Allow Outbound, No Authentication Required, and Enable XA Transactions. Both servers are running Windows Server 2008, and have been restarted since setting the DTC. There’s no firewall, and Ad Hoc Distributed Queries are enabled on both servers. I’ve increased the timeout on the server that I’m trying to set log shipping up on to 0 (i.e. unlimited) but it’s still having issues with opening the server connection. The Linked Server connection tests fine. I’m running out of ideas! Help!

    Thanks

    Jane

  22. My situation is that we will have MIMIC of production environment in DR with SSO server, BizTalk Servers (2) and SQL cluster servers. We’ll deploy application changes across all environments manually. So, at the time of DR, the things we need to restore in DR environment will be Msgbox DB and DTA Db. Will this be possible with the standard backup and restore process from BizTalk jobs? Also, what will happen to the messages that are in ACTIVE state? Will they also be carried over in the T-Log?
    Thanks!

  23. HI Nick:
    Thanks for the great arcticle series. I am seeting up biztalk log shipping as part of our DR plan.
    Regarding the restore of the jobs so that it wont throw error when we run the biztalk restore sql agents jobs.
    1. Do we need to take an ad-hoc full backup of the biztalk dbs on the source server and then restore on the destiantion with no recovery or recovery option?
    2. Once I configure the restore of log shipped db backups on the standby by running the .sql script mentiond in the above steps, does it automatically take the existing dbs location and change teh restore property to no recovery?
    Pleaes help me with this confusion.
    Thanks,
    Sridhar

  24. HI Nick:
    When I am trying to execute the last script to bts_configureBizTalkLogShipping I am getting an error;
    Msg 515, Level 16, State 2, Procedure bts_ImportSQLAgentJobs, Line 48
    Cannot insert the value NULL into column ‘database_name’, table ‘tempdb.dbo.#job_databases______________________________________________________________________________________________________000000000013’; column does not allow nulls. INSERT fails.

    looks like there is an hot fix to fix this issue. SO do I need to use that hot fix on sql server or biztalk front ends?
    Is there any other way to fix this issue.
    Hope you will help me in this matter.

    thanks,
    Sridhar

  25. HI Nick:
    I have noticed that the all the tables are created in the master database but sql server agent jobs on the destination server are not created. WHAT am I dont wrong here. I am pulling my hair to know why this unusual situation?
    Any thoughts or suggestions.
    Thanks,
    Sridhar

  26. Hi, I have to disable de copy and restore of logshipping secondary server, after that, enable the copy, then after 2 days enable again the restore, but doesnt work.
    ¿Some suggest please?

    Saludos Andrés
    PD: Dont speak, read and wrote english very well, but i think you could know how to solve this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s