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.