Automatically maintaining BizTalkDTADb archives
BizTalk Health & Activity Tracking (HAT) is one of the killer features of BizTalk. BAM (Business Activity Monitoring) provides fantastic insights into your business processes but for a support perspective, it is HAT that shines.
Being able to view the actual data that went over the wire - and often, working out which wire it went down - is invaluable to support personnel.
The BizTalkDTADb is where all your tracking data lands up and if you track even a reasonable amount of data it rapidly gets full. Microsoft recommend keeping this small, with 15 to 20GB considered too large.
In the past I've seen 100GB + databases running without any problem but that no longer seems sustainable even on SSD backed arrays. I'm not sure what has changed or if it is just more use of TDE (you are encrypting your databases, right?).
You should all be familiar with the mandatory DTA Purge and Archive job - that must be configured unless you want your environment to collapse into a pathetic heap. On the environment I'm working with currently, this job is set to maintain a two week window live which keeps the BizTalkDTADb under 50GB which is on the safe side of what this particular platform seems to be able to handle without too much CPU usage. Go too far over the limit and the CPU usage of your SQL server goes through the roof and you can never get on top of the data.
But what happens to the archived data beyond two weeks? It's tied up nicely in SQL backups (did I mention TDE, you are using TDE, right?), but on its own it isn't any use to anybody. You need to restore it, and I don't know about you but I don't have time to restore databases every couple of weeks.
So instead I put together a couple of little SQL scripts to manage restoring your archived databases and dropping them after a period of time.
This is designed to restore to the same server and default location of your production environment, but the script could be easily modified to do whatever you need.
usp_RestoreArchives
- Iterates BizTalkDTADb archives
- Restores any archives not already restored
- Switches recovery model to simple
- Shrinks the database to a minimum size
- Marks them readonly
USE [BizTalkDTADb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_RestoreArchives] ( @days int = 90 ) AS BEGIN /********************************************************************* * Author: Alastair Grant www.aligrant.com * * Description: Restores BizTalkDTADb archives, shrinks and marks as * * readonly * *********************************************************************/ DECLARE cur_backups INSENSITIVE CURSOR FOR SELECT nvcBackupLocation, dtTimeStamp FROM [dbo].[dta_ArchiveHistory] WHERE dtTimeStamp > getdate() - @days OPEN cur_backups DECLARE @location nvarchar(2000), @dt datetime, @defaultFile nvarchar(255), @defaultLog nvarchar(255); SET @defaultFile = CAST(ServerProperty(N'InstanceDefaultDataPath') as nvarchar(255)); SET @defaultLog = CAST(ServerProperty(N'InstanceDefaultLogPath') as nvarchar(255)); FETCH NEXT FROM cur_backups INTO @location, @dt WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @dbName nvarchar(50); SET @dbName = 'BizTalkDTADb_' + FORMAT(@dt, 'yyyyMMddTHHmm'); IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name = @dbName) BEGIN PRINT 'Restoring ' + @location + ' to ' + @dbName DECLARE @log nvarchar(255), @data nvarchar(255); SET @data = @defaultFile + @dbName + '.mdf'; SET @log = @defaultLog + @dbName + '_log.ldf'; BEGIN TRY RESTORE DATABASE @dbName FROM DISK = @location WITH FILE = 1, MOVE N'BizTalkDTADb' TO @data, MOVE N'BizTalkDTADb_log' TO @log, NOUNLOAD, STATS = 5; EXEC('ALTER DATABASE [' + @dbName + '] SET RECOVERY SIMPLE'); EXEC('ALTER DATABASE [' + @dbName + '] SET RECOVERY FULL'); EXEC('ALTER DATABASE [' + @dbName + '] SET RECOVERY SIMPLE'); EXEC('USE [' + @dbName + ']; DBCC SHRINKFILE (BizTalkDTADb, 1);') EXEC('USE [' + @dbName + ']; DBCC SHRINKFILE (BizTalkDTADb_log, 1);') EXEC('ALTER DATABASE [' + @dbName + '] SET READ_ONLY'); END TRY BEGIN CATCH PRINT 'Error restoring ' + @location + ' : ' + ERROR_MESSAGE() END CATCH END ELSE BEGIN PRINT 'Skipping existing db: ' + @dbname END FETCH NEXT FROM cur_backups INTO @location, @dt END CLOSE cur_backups; DEALLOCATE cur_backups; END GO
usp_DropArchives
- Drops previously restored BizTalkDTADb archives after a set period (defaults to 90 days)
USE [BizTalkDTADb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_DropArchives] ( @days int = 90 ) AS BEGIN /********************************************************************* * Author: Alastair Grant www.aligrant.com * * Description: Prunes old BizTalkDTADb archives * *********************************************************************/ DECLARE cur_archives INSENSITIVE CURSOR FOR SELECT name, create_date FROM sys.databases WHERE name like 'BizTalkDTADb[_]%' AND CAST(SUBSTRING(name, 14, 8) as datetime) < getdate() - @days OPEN cur_archives DECLARE @dbname nvarchar(50), @dt datetime; FETCH NEXT FROM cur_archives INTO @dbname, @dt WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY print 'Dropping ' + @dbname EXEC('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE') EXEC('DROP DATABASE ' + @dbname) END TRY BEGIN CATCH PRINT 'Error dropping ' + @dbname + ' : ' + ERROR_MESSAGE() END CATCH FETCH NEXT FROM cur_archives INTO @dbname, @dt END CLOSE cur_archives; DEALLOCATE cur_archives; END GO
This is now one of my standard deployment scripts and keeps on top of keeping archives available for a useful time whilst allowing you to keep the BizTalkDTADb small in size.