One of my High Performance Learning Environment post reader gave me a hint to have a look at BizTalk Best Practices Analyzer. In past I have used this tool and I forgot that it could be useful to apply on the environment I created. The BizTalk Server 2006 Best Practices Analyzer (version V1.1)performs configuration-level verification by reading and reporting only. The Best Practices Analyzer gathers data from different information sources, such as Windows Management Instrumentation (WMI) classes, SQL Server databases, and registry entries. The Best Practices Analyzer uses the data to evaluate the deployment configuration. The Best Practices Analyzer does not modify any system settings, and is not a self-tuning tool. I downloaded it and gave it a spin inside my environment.
It came up with some critical issues; which were resolved by enabling first of all SQL Server agent (so jobs can run) and enable jobs. Second thing was to change steps inside the job ‘Backup BizTalk Server’ because of the following error:
BackupDiskFile::CreateMedia: Backup device ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup
I fixed this by change two steps called BackupFull and MarkAndBackupLog, both containing TSQL Script commands.
exec [dbo].[sp_BackupAllFull_Schedule] ‘d’ /* Frequency */, ‘BTS’ /* Name */, ‘
Into:
exec [dbo].[sp_BackupAllFull_Schedule] ‘d’ /* Frequency */, ‘BTS’ /* Name */, ‘BizTalkDTADb_Bak’ /* location of backup files */
exec [dbo].[sp_MarkAll] ‘BTS’ /* Log mark name */, ‘
Into:
exec [dbo].[sp_MarkAll] ‘BTS’ /* Log mark name */, ‘
Correcting this job and running the others resulted in only one critical error:
Error DTA Purge and Archive Job on Server HPLBIZTALK Did Not Succeeded In Last Run The SQL job DTA Purge and Archive (BizTalkDTADb) need to run successfully on your DTA database server HPLBIZTALK. Contact your SQL administrator to troubleshoot the problem.Do not rename this SQL job as this is not supported.
So I looked at TSQL Command:
exec dtasp_BackupAndPurgeTrackingDatabase
0, –@nLiveHours tinyint, –Any completed instance older than the live hours +live days
1, –@nLiveDays tinyint = 0, –will be deleted along with all associated data
30, –@nHardDeleteDays tinyint = 0, –all data older than this will be deleted.
null, –@nvcFolder nvarchar(1024) = null, –folder for backup files
null, –@nvcValidatingServer sysname = null,
0 –@fForceBackup int = 0 –
Googled around and I tackled this problem through a post by Saravana Kumar, where he states to change the above script into:
declare @dtLastBackup datetime set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 1, @dtLastBackup
And yes this job works too, so now I am rid of all my critical issues. I must say using this tool is very useful after installation and configuration of your BizTalk environment. This should definitely be a step during your rollout of BizTalk inside an organization.
Technorati:
BizTalk
BizTalk Server 2006 R2
BizTalk Best Practices Analyzer
