Delete BizTalk backup files

The SQL Server agent job "Backup BizTalk Server" will not delete the generated backup files automatically. The job does clear the backup history table in the database, but it will never delete the backup files from the disk. Which will of course result in the disk to fill up eventually and the backup job will fail from then on.

Here's a simple stored procedure to call from the "Backup BizTalk Server" job in the "Clear Backup History" step. Just follow these steps:

1. Open SQL Server management Studio
2. Open a new query window and connect to the BizTalkMgmtDb database
3. Execute this script to add a new stored procedure called sp_DeleteBackupHistoryAndFiles
   

   USE [BizTalkMgmtDb]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   ALTER PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null, @UseLocalTime bit = 0
   
   AS
    BEGIN
   
    set nocount on
   
     IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
     RETURN
   
        /*
           Only delete full sets
           If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set
   
           Delete history only if history of full Backup exists at a later point of time
           why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
           If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)
       
       */
           
     declare @PurgeDateTime datetime
   
     if (@UseLocalTime = 0)
       set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
     else
       set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())
   
     DECLARE DeleteBackupFiles CURSOR
   
     FOR SELECT 'del "' + [BackupFileLocation] +  case right(BackupFileLocation,1) when '\' then '' else '\' end + [BackupFileName] + '"' FROM [adm_BackupHistory] [h1]
       WHERE     [BackupDateTime] < @PurgeDateTime
       AND    [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
       AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')
   
     DECLARE @cmd varchar(400)
   
     OPEN DeleteBackupFiles
   
      FETCH NEXT FROM DeleteBackupFiles INTO @cmd
   
     WHILE (@@fetch_status <> -1)
     BEGIN
               IF (@@fetch_status <> -2)
               BEGIN
   
                           EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
   
                           delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
   
                           print @cmd
               END
   
               FETCH NEXT FROM DeleteBackupFiles INTO @cmd
     END
   
     CLOSE DeleteBackupFiles
   
     DEALLOCATE DeleteBackupFiles
   
     END
   

4. 5.Modify the "Clear Backup History" step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles, instead of calling the stored procedure sp_DeleteBackupHistory
5. Make sure xp_cmdshell for the SQL Server instance is enabled. this will be disabled by default. To enable this, execute following SQL script:
   

   EXEC master.dbo.sp_configure 'show advanced options', 1
   
   RECONFIGURE
   
   EXEC master.dbo.sp_configure 'xp_cmdshell', 1
   
   RECONFIGURE
   
   

This stored procedure is made for BizTalk Server 2010.
Because with the release of BizTalk 2010 there have been changes to the sp_DeleteBackupHistory stored procedures. They added a parameter to the stored procedure to use local time and changed the query to prevent the deletion of the history from the last full backup set forward.

For older versions of BizTalk you should use this script instead:

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
AS
BEGIN
set nocount on
  IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
  RETURN
/*
  Only delete full sets
  If a set spans a day such that some items fall into the deleted group and the other doesn't, do not delete the set
*/ 

DECLARE DeleteBackupFiles CURSOR
FOR SELECT 'del "' + [BackupFileLocation] + '\' + [BackupFileName] + '"' FROM [adm_BackupHistory]
WHERE  datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep
AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )
DECLARE @cmd varchar(400)
OPEN DeleteBackupFiles
FETCH NEXT FROM DeleteBackupFiles INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN 

            IF (@@fetch_status <> -2)
            BEGIN
                        EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
                        delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
                        print @cmd
            END 

            FETCH NEXT FROM DeleteBackupFiles INTO @cmd
END 

CLOSE DeleteBackupFiles
DEALLOCATE DeleteBackupFiles
  END
GO

Source: http://www.biztalkbill.com/Home/tabid/40/EntryId/81/Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx

Using System Colors in C#

When choosing a color in the properties window in visual studio, there is the possibility to choose from so called System Colors. An example for this is the color 'Window', which is the default for a textbox.
Now, I tried to set the BackColor of a Textbox programmatically back to this system color 'Window'. Turned out not to be as easy as I thought it would be.

I tried things like Color.? ... I hoped to find the color in this namespace, or even a System subsection for the systemcolors. Nothing to be found however.
Nor was there any namespace like System.Color to be found.

But how can these system colors be set from C# code than?

Well, one way turned to be using this command:

System.Drawing.Color.FromKnownColor(KnownColor.Window)

And, as you'd expect, the KnownColor enumeration contains all of the other user-definable colors.

Or even an easier method to achieve this is

System.Drawing.SystemColors.Window

SQL Fragmentation explained

Found an interesting article on fragmentation in SQL.

It explains the two types of fragmentation that can occur, namely internal and external fragmentation.
Internal fragmentation means that the pages are not completely full. While external fragmentation refers to the lack of correlation between the logical sequence of an index and its physical sequence.

For more details I'll refer to this excellent blog post.

Source: Stairway to SQL Server Indexes: Level 11, Index Fragmentation - SQLServerCentral

Difference between 2 dates in C#

I tried to do a simple DateTime calculation in C#. What I wanted to achieve is to know how many years, months and days where between 2 DateTime variables. Or at least I thought this would be easy. Turns out there is know real out-of-the-box functionality available to handle this in C#.

The only thing available out-of-the-box in C# is using TimeSpan. Unfortuanately the only calculations TimeSpan can return is the number of days, hours, minutes, seconds and milliseconds. But how about the years, months and days than?

Here's a simple method to show the possibilities of the TimeSpan:

public void PrintDateDiffUsingTimeSpan(DateTime begin, DateTime end)
        {
            TimeSpan span = end - begin;

            StringBuilder str = new StringBuilder();
            str.Append("Total span: ");
            str.Append(span.Days + " days, ");
            str.Append(span.Hours + " hours, ");
            str.Append(span.Minutes + " minutes, ");
            str.Append(span.Seconds + " seconds, ");
            str.Append(span.Milliseconds + " milliseconds");

            Console.WriteLine(str.ToString() + "\n");


            Console.WriteLine("Total Days: " + span.TotalDays);
            Console.WriteLine("Total Hours: " + span.TotalHours);
            Console.WriteLine("Total Minutes: " + span.TotalMinutes);
            Console.WriteLine("Total Seconds: " + span.TotalSeconds);
            Console.WriteLine("Total Milliseconds: " + span.TotalMilliseconds);
        }

I ended up using a freely available .NET library called 'Time Period'. The source code can be found here. There's also an on line demo and documentation available.
Here's a simple method to show some the possibilities of the TimeSpan:

  public void PrintDateDiff(DateTime begin, DateTime end)
        {
            DateDiff diff = new DateDiff(begin, end);
            
            StringBuilder str = new StringBuilder();
            str.Append("Total difference: ");
            str.Append(diff.ElapsedYears + " years, ");
            str.Append(diff.ElapsedMonths + " months, ");
            str.Append(diff.ElapsedDays + " days");

            Console.WriteLine(str.ToString() + "\n");

            Console.WriteLine("Total Days: " + diff.Days);
            Console.WriteLine("Total Weeks: " + diff.Weeks);
            Console.WriteLine("Total Weekdays: " + diff.Weekdays);
            Console.WriteLine("Total Quarters: " + diff.Quarters);
            Console.WriteLine("Total Hours: " + diff.Hours);
        }

There are much more possibilities in the DateDiff class of the Time Period library. There are even much more classes available in the library. To know more of the capabilities of this library I again refer to the documentation.

Source: http://www.itenso.com/en/

BizTalk Server Performance

Have you ever wanted to evaluate the performance of your Biztalk installation?
Evaluating your performance could be done by analyzing quite some counters in the performance monitor of Windows. Or maybe by using the Performance Analysis of Logs (PAL) Tool.

However I just found a recently released tool to benchmark your biztalk environments.
The tool is called blogical and can be found on codeplexe here.

How it works:

1. After the user has started the application and specified the BizTalk Group, the tool analyzes its configuration, finding all the BizTalk servers, Messageboxes etc.
2. Secondly, the user gets to select one of two scenarios: Messaging or Orchestration. Each scenario has a set of tested environments such as
   • “Single server (2*Quad CPU, 4GB RAM)”
   • “1*BTS (1*Quad CPU. 4GB RAM) + 1*SQL(1*Quad CPU, 8GB RAM)”.
   • “2*BTS (2*Quad CPU. 8GB RAM) + 2*SQL(2*Quad CPU, 16GB RAM)”.
3. The user selects the environment which most resembles his/her own.
4. The user then starts the Indigo Service, a console application hosting a service which will be called from the BizTalk Send port.
5. As the user clicks “Run test”, the tool continues to start ports and orchestrations. It will also start the Perfmon collector sets if the user has chosen to create those.
6. As the test proceeds the user can monitor the counter values through the gauges (CPU utilization, Received msgs/sec and Processed msgs/sec). The default test duration is 30 minutes, with a warm-up of 2 minutes.
7. Finally, the user is presented a result, which is either Succeeded or Failed.

For more information:
Benchmark your BizTalk Server (Part 1)

How to install:
Benchmark your BizTalk Server (Part 2)

BBW Drill Down:
Benchmark your BizTalk Server (Part 3)

High Scores:
http://blogical.se/bbw


Another thing I wanted to share with you regarding performance of Biztalk installations is the Microsoft BizTalk Server 2009 Performance Optimization Guide .
This guide provides in-depth information for optimizing the performance of a BizTalk Server solution.

Also check BizTalk Health Check post on Technet Wiki. It's a list of checkpoints that should be covered when performing a health check

Promote using Correlation initialisation inside a loop

I assume you already know how to promote properties when sending a message from an orchestration to the messagebox.
This can of course be achieved by initializing a correlation set with containing all context properties you like to promote. But that 's not why I'm posting this blog. If you need more info on how to promote properties from an orchestration I'll refer you to this blog.

Problem

The problem is when you try to do this from a send shape inside a loop, you'll get an error when building the project in visual studio.
The received error is "Correlation set may be initialized only once".

Solution

The solution to this issue is actually real easy. All you have to do is create a scope inside the loop. And place the send shape (and even other shapes if needed) in the created orchestration. The benefit of the orchestration is that you can define the correlation set on the scope itself (instead of as a global correlation set).
Now can initialize your correlation set on your send shape inside the scope.

And that's all you'll have to do!! It really is that easy! :)

Passed the Microsoft BizTalk Exam

As of yesterday I can call myself a Microsoft Certified Technology Specialist (MCTS): Microsoft BizTalk Server 2010.

I passed the exam 70-595: Developing Business Process and Integration Solutions by Using Microsoft BizTalk Server 2010. I did this making only a single mistake, not bad at all :).

All I did as preparation was reading the book (MCTS): Microsoft BizTalk Server 2010 (70-595) Certification Guide once. This together with the practical knowledge I had gathered on the job made sure I passed the exam easily.

So all credits go to the authors of the book (Johan Hedberg, Kent Weare , Morten la Cour). They definitely achieved their goal. Also the sample questions (and their answers) were very helpful in the preparation.