Published on : Sep 1, 2022
Category : BizTalk Server
I started speaking about BizTalk Server tips and tricks for administrators and developers back in 2015 when I delivered my first session about these topics at INTEGRATE event in London.
It seems a good time to translate these sessions into a series of blog posts.
Today I start with a tip for BizTalk Server Administrators in a format of a question: What do RosettaNet, ESB, and UDDI have in common?
#1 What do RosettaNet, ESB, and UDDI have in common?
Have you had the time to think about what some features like RosettaNet, ESB Toolkit, or UDDI have in common?
First, all of these features are optional, and additional BizTalk Server features:
But all of these additional features have another thing in common: all of them require custom databases that will be automatically created, normally, on the BizTalk Server SQL Server instance:
- RosettaNet will create the following databases:
- ESB Toolkit will create (at least) the following databases:
- UDDI will create (at least) the following database:
Because all of that requires custom databases, and normally when we speak about backing up BizTalk Server databases, we normally think that:
- We cannot backup BizTalk Server-related databases using custom jobs!
- The BizTalk Server-related databases backup is made by the Backup BizTalk Server (BizTalkMgmtDb) job.
The critical question here is: Do you think your data are being backup? Are you saved regarding these databases?
And the simple response is NO… by default, the Backup BizTalk Server (BizTalkMgmtDb) job doesn’t perform any operations in these databases.
Because your custom databases are not installed with BizTalk Server, they are not included in the default list of databases to be marked and backed up by the Backup BizTalk Server job. If you want the Backup BizTalk Server job to back up your custom databases, you must manually add the databases to the Backup BizTalk Server job.
How to guarantee that these databases are being backed up?
To backup up these databases, you have two options:
- Create a custom job to perform a backup to these databases like any other regular database.
- Yes, this is possible, and we are not breaking any BizTalk Server law and not breaking the BizTalk Server support. This method is fully supported.
- Or enable the out-of-the-box Backup BizTalk Server (BizTalkMgmtDb) job to start backing up these databases.
How to enable the out-of-the-box Backup BizTalk Server (BizTalkMgmtDb) job to start backing up these databases?
Fortunately for us, Microsoft provides two SQL Scripts:
You need to run those scripts against these databases to extend the standard backup mechanisms. But you also need to modify the adm_OtherBackupDatabases table to include a row for each of your custom databases.
You can find these two scripts on the BizTalk Server installation directory, normally, drive:\Program Files\Microsoft BizTalk Server <version>\Schema.
Once again, you have to run the Backup_Setup_All_Procs.sql and Backup_Setup_All_Tables.sql scripts against all your custom databases that you want to back up. This will create the necessary procedures, table, and role and assigns permissions to the internal stored procedures in order for the Backup BizTalk Server (BizTalkMgmtDb) job to be able to backup these databases.
To do that, you need to make sure that:
- You must be logged on with a user account that has access to each of the databases you are backing up.
- Browse to the \Program Files (x86)\Microsoft BizTalk Server <VERSION>Schema directory, and then run Backup_Setup_All_Procs.sql and Backup_Setup_All_Tables.sql against all your custom databases that you want to back up. This creates the necessary procedures, table, and role and assigns permissions to the stored procedures.
- Using SQL Server Enterprise Manager or SQL Server Management Studio, in the BizTalk Management (BizTalkMgmtDb) database, modify the adm_OtherBackupDatabases table to include a row for each of your custom databases.
- Type the new server and database names in the corresponding columns as follows:
- DefaultDatabaseName: The friendly name of your custom database.
- DatabaseName: The name of your custom database.
- ServerName: The name of the computer running SQL Server.
- BTSServerName: The name of the BizTalk Server. This value is not used, but it must contain a value nonetheless.
The next time you run the Backup BizTalk Server job, it will back up your custom databases.
Of course, this applies to all third-party databases (external configuration, other databases of the ESB Toolkit…). It is a good practice to use the job to backup BizTalk Server-related databases because it ensures consistency in the backups. If you use the BizTalk Log Shipping for a recovery site, all the databases that are backed up by the BizTalk job will automatically be restored on-site without effort.
But do not use this approach to start back up all your custom application databases that you normally have (you shouldn’t) in the BizTalk Server SQL Server instance. This will also mean that the BizTalk Server (BizTalkMgmtDb) job will take more time to complete, and do not forget that this job runs each 15 minutes.
Stay tuned for the following BizTalk Server Best practices, Tips, and Tricks both for developers and administrators. And if you have any ideas that you want to share, don’t hesitate to send me an email.