BizTalk Server Best practices, Tips, and Tricks: #1 What do RosettaNet, ESB, and UDDI have in common?

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:

• The Microsoft BizTalk Accelerator for RosettaNet (BTARN) streamlines the development and deployment of RosettaNet standards-based integration solutions.
• The ​Microsoft BizTalk ESB Toolkit consists of a series of interoperating components that support and implement a loosely coupled messaging environment that makes it easier to build message-based enterprise applications.

• Universal Description and Discovery Information (UDDI) is a type of registry whose primary purpose is to represent information about web services – this is currently removed from the latest BizTalk Server versions.

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:
  • BTARNARCHIVE
  • BTARNCONFIG
  • BTARNDATA
• ESB Toolkit will create (at least) the following databases:
  • EsbExceptionDb
  • EsbItineraryDb
• UDDI will create (at least) the following database:
  • UDDI3

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:

• Backup_Setup_All_Procs.sql
• Backup_Setup_All_Tables.sql

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.