All SQL Server Network Protocols are installed by SQL Server Setup, but may or may not be enabled. And you need to be aware that this protocols can have impact in your BizTalk Environment, for example:
One of the SQL jobs created during the configuration of BizTalk is the DTA purge and archive job. This job purges tracking data from the tracking database (BizTalkDTADb). By default it also archives this data to disk. To do this the job calls the stored procedure dtasp_BackupAndPurgeTrackingDatabase.
However, most of my customers do not use the archived data and just remove it periodically.
Besides your BizTalk databases your SQL Servers also have the following system databases:
Since most of these databases are relatively static, they don't need to be in the Backup BizTalk Server job, but they do need to be in a backup. Such a backup can be made with a Maintenance plan. In this article I show you how to create such a Maintenance Plan in the most easiest way, with the Maintenance Plan Wizard.
At my company we are running over 10 BizTalk development and test servers. On those servers I enabled the SQL Server BizTalk backup job. I do not however want to manually delete old backups of BizTalk databases for all those servers. I want those old backups deleted automatically. This will prevent the database files from growing indefinitely (or till your disk is full…). Also, on my production servers I do not want old backups files to take up all free disk space and cause incidents. To automatically remove old backup files I use a maintenance plan. This blog post shows you how to create this maintenance plan.
Everybody who loves BizTalk and programming might fire SQL queries at the BizTalk databases. Of course you know that you should be extremely carefull when doing this.
Besides giving some tips on do and dont's with the BizTalk databases, I'll show you how BizTalk databases can be identified.
When you have a BizTalk system your BizTalk databases should be backed up by the SQL job 'Backup BizTalk Server'. This is, at least for BizTalk, the only by Microsoft support method to make backups. Besides the BizTalk databases you might also have custom databases which are used by your system and need to be backed up as well. For recovery purposes it is important that the backups of all of these databases are in sync, so your custom databases should be added to the BizTalk Backup job.
In this article I'll tell a bit more about the ins and outs of the BizTalk backup and how you can add custom databases to it.
From the Group Hub in BizTalk 2010 you can have a look at all existing subscriptions. However to have a look at the expression which is related to the instance, you have to open the Subscription Details screen and go to the Expression tab page.
If you need to know all the expressions, you can use the query below: