Let’s have a look to a little of inside information that will be in my upcoming Book about maps: demystify and clarify some features about the Table Looping Functoid.

First of all this functoid requires at least 3 inputs and a maximum of 100, in which the official documentation states that:

  • Parameter 1: A link from a repeating node in the source schema. The number of instances of this structure that occur in a particular input instance message defines the number of times that the associated table looping grid is processed.
  • Parameter 2: A constant input parameter that defines the number of columns in the associated table looping grid.
  • Parameters 3 – 100: A link from a node in the source schema or from another functoid, such as a Value Extractor functoid, or a constant input parameter. The relative order of parameters 3 – 100 is unimportant.

I think, at least for those who know the minimum about functoid, the last parameter(s), from 3 to 100, does not present any doubts. They are the values that we want to map from the source to the destination. And the order is not important because we need to define the Table Looping Grid property were we will define the order of appearance of these values.

The Table Looping Grid is basically a configurable table containing an arbitrary number of rows, configurable while editing the table, and a number of columns specified by the second input parameter of the Table Looping functoid. Each table cell is configured by using a drop-down list where the entries in the drop-down list are the values from to third through the last input parameters of the Table Looping functoid. These input parameters consist of a combination of the links into the functoid and any constant input parameters that have been defined. If link input parameters have been given a Label property value, that value is shown in the drop-down lists; otherwise, the value of the Link Source property is shown (generally, the former is friendlier than the latter). Constant input parameters are shown according to their constant value.

Important note: However the number of rows present in the Table Looping Grid are not defined in any input parameter from the Table Looping Functoid and in under no circumstances they are dynamic. The number of rows are statically defined during developing in order to apply a specific transformation rule and resolve a particular transformation problem.

Although people use this functoid correctly, especially if we are using it with a repeating record in the source schema, we will see in more detail further on in this chapter, is went we are trying to use this Functoid to transform a flat structure to a recursive structure that we realize that many developers don’t properly understand the first input parameter of this Functoid and to be honest, neither the explanation present in the official documentation will explain it correctly.

Although the Table Looping Functoid states that the first input must be a scoping element linked from a repeating group, that is not really true, in fact for me this description is complete false!

  • A link from a repeating node in the source schema – most common used but not entire true, you can also make use of a constant value, a simple node or an element.
  • The number of instances of this structure that occur in a particular input instance message defines the number of times that the associated table looping grid is processed. – I understand and in a way yes, but sometimes developers understand that this as the number of rows that they have to define in the table Looping Grid, with is not true, or that this input requires a number and in fact this is also not true.

So to be more clear, for me the best description of this parameter is:

  • Parameter 1: the first input parameter defines the action scope of the Table Looping Functoid and it can be defined by a link from a source tree node, repeating record, simple records or even elements, or by a constant value. This means:
    • If the scope is defined by a repeating record or repeating element, the Table Looping functoid will be created/execute in each occurrence of the record or element (in each iteration over the node). We can define this as multiple scope action.
    • If the scope is defined by a simple element or record (only occurs one time), than the Table Looping will be executed only one time. We can define this as simple scope action.
    • If the scope is defined by a simple constant value, regardless if it is an integer, alpha-numeric or string, than the Table Looping will be executed only one time. Again this is a simple scope action.

Table-Looping-Functoid-Scope-defined-by-a-simple-constant-value

Both this options are valid! More details will be found in my upcoming BizTalk Mapping Patterns and Best Practices free eBook.

Basically there are two properties inside the schema element decides whether an element can be absent from the document: Min Occurs and Nillable.

If Min Occurs property is set 0 then that element can be absent from the XML message but if it is set to 1 it has to be present though its value can be empty. This is useful to reduce the size of the document if only not all the elements are mandatory to end systems.

In other hand, if the Nillable property of the element is set to true, this will indicate that the value of an element in the document may be null. This NULL values will be expressed with xsi:nil = true attribute in the element, ex:

<IntExist xsi:nil="true" />

An element with the attribute xsi:nil = true explicitly means that the value is unavailable or unknown at that moment and sometimes the end system explicitly requires to be notified that the value of the element is NULL so that they can take appropriate action.

In this sample scenario we will have a 2 mandatory elements that can be nillable that we need to map to the destination schema. In this scenario all the destination elements are also mandatory and we need to fill them with a valid value or specify the nillable property as true: so if the element exist we need to map the correct source value otherwise we need to set the destination element as nillable.

The first element “DateExist” is a mandatory element that can be null. If null we need to set a null value in the destination element also as null, otherwise we need to map the source value. To accomplish that we need to:

  • Drag one IsNil Functoid from the Toolbox window onto the Grid.
    • Drag a link from the “NillValue” field element in the source schema to the IsNill Functoid
  • Drag one Nil Value Functoid from the Toolbox window onto the Grid.
  • Drag one Logical NOT Functoid from the Toolbox window onto the Grid.
  • Drag one Value Mapping Functoid from the Toolbox window onto the Grid.
  • To create a rule for mapping the value if the element is null
    • Drag a link from the IsNill Functoid to the Nil Value Functoid
    • Drag a link from the Nil Value Functoid to the “NillValueOutput” field element in the destination schema
  • Otherwise, To create a rule for mapping the value if the element different of null
    • Drag a link from the IsNill Functoid to the Logical NOT Functoid
    • Drag a link from the Logical NOT Functoid to the Value Mapping Functoid Functoid
    • Drag a link from the “NillValue” field element in the source schema to the Value Mapping Functoid
    • Drag a link from the Value Mapping Functoid to the “NillValueOutput” field element in the destination schema

Do the exact same logic for the second element present in the source schema.

BizTalk-Mapper-Working-With-Nillable-Values

Sometimes the maps are misunderstood and notorious for producing a lot of unnecessary code that may cause a in some cases lack of performance. So the question that we can and should ask is whether this is the best solution or not to address this type of operations. To respond this question we should also inspect the generated code produce by the BizTalk Mapper:

<xsl:variable name="var:v1" select="string(NillValue/@xsi:nil) = 'true'" />
    <xsl:variable name="var:v2" select="userCSharp:LogicalNot(string($var:v1))" />
    <xsl:variable name="var:v4" select="string(AnotherNilValue/@xsi:nil) = 'true'" />
    <xsl:variable name="var:v5" select="userCSharp:LogicalNot(string($var:v4))" />
    <ns0:OutputSchema>
      <xsl:if test="string($var:v1)='true'">
        <NillValueOutput>
          <xsl:attribute name="xsi:nil">
            <xsl:value-of select="'true'" />
          </xsl:attribute>
        </NillValueOutput>
      </xsl:if>
      <xsl:if test="string($var:v2)='true'">
        <xsl:variable name="var:v3" select="NillValue/text()" />
        <NillValueOutput>
          <xsl:value-of select="$var:v3" />
        </NillValueOutput>
      </xsl:if>
      <xsl:if test="string($var:v4)='true'">
        <AnotherNilValueOutput>
          <xsl:attribute name="xsi:nil">
            <xsl:value-of select="'true'" />
          </xsl:attribute>
        </AnotherNilValueOutput>
      </xsl:if>
      <xsl:if test="string($var:v5)='true'">
        <xsl:variable name="var:v6" select="AnotherNilValue/text()" />
        <AnotherNilValueOutput>
          <xsl:value-of select="$var:v6" />
        </AnotherNilValueOutput>
      </xsl:if>
    </ns0:OutputSchema>
  </xsl:template>

In fact is a pretty decent XSLT code but the reality is that it can be better, we don’t need to use any support variables and we can remove one if condition by replacing the xsl:if condition for one xsl:choose condition.

This is a very simple approach, easy to implement and readable that you should use even in small or large messages (transformations) but only if you have to deal with a small number of nillable element.

However applying this approach in transformation that will need to deal with a large number of nillable elements, can lead to two problems:

  • A lot of unnecessary XSLT code that can in fact and of course always depending in the size of the message can lead to some lack of performance
  • A lot of functoid shapes (4 Functoids) and links (7 links) for each element that can lead to lack of visual Readability

So can we improve this solution for transformations that needs to deal with a large number of nillable elements?

Well that’s the problem, there isn’t a simple solution for that. At the first look you may think that’s easy, just copy the XSLT code inside to a Scripting Functoid and optimize the XSLT code.

However by doing that you will receive an error:

error btm1050: XSL transform error: Unable to write output instance to the following <file:///C:\…\MapNillValuesWithCustomXSLT_output.xml>. Prefix ‘xsi’ is not defined.

The problem is that the nil attribute is defined in the XML Schema instance namespace, http://www.w3.org/2001/XMLSchema-instance (commonly associated with the prefix xsi) and this namespace is not declared by default in the XSL code generated by the BizTalk Mapper.

This namespace is automatically declare only if you use the Nil Functoids in the map.

So the normal solution here is… to bypass the BizTalk Mapper and generate an external XSLT code and add it to the map by specifying the Custom XSLT Path by:

  • Open the map
  • Click the grid zone and on the properties window there will be a “Custom XSLT Path” property.  Click the ellipses and navigate to the file containing the XSLT.

BizTalk-Mapper-Working-With-Nillable-Values-External-XSLT

You then can use a similar code to check and map the elements:

<xsl:choose>
  <xsl:when test="NillValue/@xsi:nil">
    <NillValueOutput>
      <xsl:attribute name="xsi:nil">
        <xsl:value-of select="'true'" />
      </xsl:attribute>
    </NillValueOutput>
  </xsl:when>
  <xsl:otherwise>
    <NillValueOutput>
      <xsl:value-of select="NillValue/text()" />
    </NillValueOutput>
  </xsl:otherwise>
</xsl:choose>

However applying this approach we have a major problem for me:

  • We lose all the BizTalk Mapper functionalities.
Workaround

Well, at least that I know, unfortunately there is no simple way to declared the xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance namespace to the stylesheet of the map.

However we can apply one small workaround, is not perfect but in most of the case it will solve my problems:

  • For only one of the nillable elements we need to use the Nil Functoids explained in the beginning of this post.
    • This will declare automatically the name xsi namespace for us.
  • In the rest of the elements we now can use Scripting Functoids with the optimized XSLT code described above

BizTalk-Mapper-Working-With-Nillable-Values-XSLT

I’m still working to find a better way but until then

You can download the source code from:

BizTalk Mapper: Working With Nillable Values (xsi:nil=”true”) (92.7 KB)
Microsoft | Code Gallery

I publicly announce for the first time that I was working in an eBook about BizTalk Mapping Patterns and Best Practices, and that I was published for free, this March during BizTalk Summit 2014 London…

BizTalk-Mapping-Patterns-and-Best-Practices-eBook-soon

… At the time I said it would be published in mid-April or May but since then I’ve been slightly quiet, a few tweets and that’s it!

So how’s the book and when will it be published? This is the question that some people have asked me and that you probably are asking too, at least if you attended the event in London

Well I have some good news Smile… the book will be a reality and will definitely be for free. I’ve already made ​​the commitment to the BizTalk community and I’ll fulfill it. And the first chapters had already been sent to my awesome team of reviewers… I’m waiting for their feedback Smile!

However there are still many things to finish, the structure of the book may still suffer some changes (depending on the reviewers feedback), although it is unlikely that I make some radical changes in its current structure. And I still need to finish the main chapter “BizTalk Mapper Patterns” and one additional final chapter.

You also need to remember that this eBook will be for free and that all those involved are spending their free time to make this happen! Personally, the last few months have been difficult for me to find some additional free time to engage and finish this project… and I also cannot ask and demand nothing more from my reviewers, we all are busy people. I prefer to take a little more time and provide some quality work (I hope).

State of art and key point to take:

  • The book will be a reality and will definitely be for free
  • 12 patterns addressed in the eBook, currently 233 pages…
  • Estimated that 85% of the work is done
  • All demos are finished, finally! (is hard to invent some good and practical scenarios)
  • I promise that I will push my reviewer to send me the feedback (but they are not the blocking point)
  • I’m currently have some free time to finish the eBook!!! SmileSmile

I hope that soon I will be sending all the chapter to the reviewers and then depending on the feedback: improve it, fix it and finally publish it… so I ask you to have a little more patience.

Almost three weeks ago in my last post I release a SQL script to manager and delete the unnecessary records in the BizTalk “MarkLog” tables according to some of the best practices (you can read more here: Managing and cleaning BizTalk Server MarkLog database tables according to some Best Practices), however I knew in advance that the script had some limitations:

  • Basically the user needs to update the script manually after “installing” it. He/she needs to add and remove the databases that might not be in use (like BAM).
  • Or add, if more than one MsgBox is used.

But the beautiful of the BizTalk Community is that sometimes they provided awesome feedback, and that was exactly what happened in this case. 10 days after I published my post, I received an email from Mikael Sand, Mikael is a strong BizTalk Community member from Sweden that I had the pleasure of meeting him in person, I think the first time was two years ago in Norway, describing this exact limitations but… you are making a mistake if you think that he only described the limitations!!

Indeed he spent some time describing the limitations but I also provided me with an updated scripts with all the improvement necessary to suppress these limitations! Basically Mikael updated the script using an identical logic from another SP in the backup job so that user doesn’t need to update the script once it is installed. Also, it does not need to be updated if a database (like BAM) is added later or if you are using more than one MsgBox.

Basically the differences between the first release and this one is that:

  • Instead of hard coding implement all the delete necessary queries
/****** Delete MarkLog History from BAMAlertsApplication database ******/
DELETE FROM [BAMAlertsApplication].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BAMArchive database* *****/
DELETE FROM [BAMArchive].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BAMPrimaryImport database ******/
DELETE FROM [BAMPrimaryImport].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkDTADb database ******/
DELETE FROM [BizTalkDTADb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkMgmtDb database ******/
DELETE FROM [BizTalkMgmtDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkMsgBoxDb database ******/
DELETE FROM [BizTalkMsgBoxDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkRuleEngineDb database ******/
DELETE FROM [BizTalkRuleEngineDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from SSODB database ******/
DELETE FROM [SSODB].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep
  • Mikael have implementing this same operation dynamically by “stolen” the cursors logic and realservername from the BizTalk procedure “sp_MarkBTSLogs”. The cursor iterates all the databases that are backed up by BizTalk.
DECLARE @BackupServer sysname, @BackupDB sysname, @RealServerName sysname
DECLARE @tsql nvarchar(1024)
DECLARE @ret int
/* Create a cursor */
DECLARE BackupDB_Cursor insensitive cursor for
   SELECT ServerName, DatabaseName
   FROM	admv_BackupDatabases
   ORDER BY ServerName

open BackupDB_Cursor
fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
WHILE (@@FETCH_STATUS = 0)
   BEGIN
      -- Get the proper server name
      EXEC @ret = sp_GetRemoteServerName @ServerName = @BackupServer, @DatabaseName = @BackupDB, @RemoteServerName = @RealServerName OUTPUT

      /* Create the delete statement */
      select @tsql =
      'DELETE FROM [' + @RealServerName + '].[' + @BackupDB + '].[dbo].[MarkLog]
      WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),''_'',''''), GETDATE()) > ' + cast(@DaysToKeep as nvarchar(5) )
                            
      /* Execute the delete statement */
      exec (@tsql)
      /* Get the next DB. */
      fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
   END
close BackupDB_Cursor
deallocate BackupDB_Cursor

Then I spent some days testing and improving a little more the script, and again by looking to the same SP: “sp_MarkBTSLogs”, I was able to implemented some kind of error handling, just in case of something fails that an exception will be raised, as you can see in this example picture bellow:

sp_DeleteBackupHistoryAndMarkLogsHistory-error-handling

Special thanks for Mikael Sand for the feedback and for this new implementation logic, and of course to the previous persons involved: Tord Glad Nordahl, Rui Romano and Pedro Sousa.

You can download the last version of the custom sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure from:

BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices (1.2 KB)
Microsoft | TechNet Gallery

All the BizTalk database which is being backed up by the ‘Backup BizTalk Server’ job, so all databases with the exception of the BAM Star Schema database (BAMStarSchema), has one table called “MarkLog”.

These tables are holding all the transaction marks (they are actually timestamps in a string format), set to a specific database, created by the 3th step (MarkAndBackUpLog) of the ‘Backup BizTalk Server’ job. This step, MarkAndBackupLog, is responsible for marking the logs for backup, and then backing them up. So each time this step runs, by default each 15 minutes, a string is stored on that table with the following naming convention:

  • Log_<yyyy>_<MM>_<dd>_<HH>_<mm>_<ss>_<fff>

Where:

  • Log”: Constant string
  • yyyy: The year as a four-digit number.
  • MM: The month, from 01 through 12
  • dd: The day of the month, from 01 through 31.
  • HH: The hour, using a 24-hour clock from 00 to 23.
  • mm: The minute, from 00 through 59.
  • ss: The second, from 00 through 59.
  • fff: The milliseconds in a date and time value.

Again this marks are timestamps in which the Log backups were set to be made. Example:

  • BTS_2014_05_06_14_15_01_327

Unfortunately BizTalk has no out-of-the-box possibilities to clean up these tables. And the normal procedure is to run the terminator tool to clean it up. See also: Clean up the MarkLog table with Terminator

Terminator-Tool-Purge-MarkLogs

BizTalk Terminator is an excellent tool that allows for common BizTalk problems to be addressed by scripts provided by the BizTalk Team, but needs to be used carefully and by users who know what they are doing.

Although most of the times this is extremely useful tool and our friend, in this particular operation/situation using this tool it’s not really the best option for two reasons:

  • Using this tool means that we need to stop our environment, i.e., downtime in our integration platform.
  • And if we look at the description of this “PURGE Marklog table” task it says that this operation calls a SQL script that cleans up everything in Marklog table – and maybe this is not a best practices!

I spoke to my dear friend, and one of the best BizTalk Administrator that I know, Tord Glad Nordahl to discuss and know what should be the best practice to maintain these tables:

  • Is these information (timestamps) useful for BizTalk Administrators?
  • Should I clean all the data inside this tables or should I maintain a history?
  • Is safe to clean this information in runtime?

Were some of the questions that we discussed.

Is these information (timestamps) useful for BizTalk Administrators? Should I clean all the data inside this tables or should I maintain a history?

For the same reason that we maintain a Backup history in the Adm_BackupHistory table controlled by the step “Clear Backup History” of the ‘Backup BizTalk Server’ job. This information is important for example to keep an eye on the backup/log shipping history records to see whether the back is working correctly and data/logs are restored correctly in the stand by environment. The information on the MarkLog tables are also useful for the BizTalk Administration team!

So as long as the MarkLog tables have the same info (data from the same dates) as the backup job days to keep you can safely delete the rest of the information.

As a best practices: you should respect the @DaysToKeep parameter that you specify in the “Clear Backup History” step of the ‘Backup BizTalk Server’ job.

And this is why that in my opinion, you shouldn’t use the Terminator tool to perform this operation!

Is safe to clean this information in runtime?

The rows in the Marklog table are not “required” and can be cleaned whenever you want as long the BizTalk Backup Job is not running.

Cleaning MarkLog Tables According to Some of the Best Practices

So the main challenger is how can we safely delete and maintain a history of all MarkLog tables according to some of the best practices described earlier?

My first approach was creating a new job that according to a scheduler would run a stored procedure to delete all the unnecessary information on that tables but I realized I could have two problems with this approach:

  • I need to be sure that BizTalk Backup Job wasn’t running performing the backups;
  • And I didn’t want to have to places to define the @DaysToKeep and I didn’t want to make unnecessary joins or additional selects

I was trying to find an approach for this operation to be carried out in an easy way and as fast as possible.

So I end up recreating sp_DeleteBackupHistory (that is configured is the Backup BizTalk Server (BizTalkMgmtDb) job in the last step) with a different name sp_DeleteBackupHistoryAndMarkLogsHistory and configure the job to run this step:

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndMarkLogsHistory] @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())

	DELETE [dbo].[adm_BackupHistory]
	FROM [dbo].[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')

	/****** Delete MarkLog History from BAMAlertsApplication database ******/
	DELETE FROM [BAMAlertsApplication].[dbo].[MarkLog]
    WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BAMArchive database* *****/
	DELETE FROM [BAMArchive].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BAMPrimaryImport database ******/
	DELETE FROM [BAMPrimaryImport].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkDTADb database ******/
	DELETE FROM [BizTalkDTADb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkMgmtDb database ******/
	DELETE FROM [BizTalkMgmtDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkMsgBoxDb database ******/
	DELETE FROM [BizTalkMsgBoxDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkRuleEngineDb database ******/
	DELETE FROM [BizTalkRuleEngineDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from SSODB database ******/
	DELETE FROM [SSODB].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep
 END

Steps required to install/configure

  • Create the sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure described earlier
  • You need to adjust the SQL Script to your environment because some environment may not have all features installed, for example: Business Rules and in this case BizTalkRuleEngineDb database will not exist.
  • Change and configure the 4th step of the ‘Backup BizTalk Server’ job – “Clear Backup History” to call this new stored procedure

sp_DeleteBackupHistoryAndMarkLogsHistory

Note: Do not change or delete the “sp_DeleteBackupHistory”!

Special thanks for Tord Glad Nordahl for reviewing and all the feedback, and to my friends at DevScope Rui Romano and Pedro Sousa for the helping me developing this SQL Query.

Hope you find it useful

You can download the custom sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure from:

BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices (1.2 KB)
Microsoft | TechNet Gallery

 

BizTalk Server databases and their health are very important for a successful BizTalk Server database messaging environment. BizTalk is shipped out with a total of 13 SQL Agent jobs that perform important functions to keep your servers operational and healthy.

Like any other system, all BizTalk Server databases should be backed up and BizTalk Server will provide out-of-the-box a job for accomplished that: Backup BizTalk Server (BizTalkMgmtDb) job.

This job makes both Full and Log backups. By default the Backup BizTalk Server job performs a full backup once a day and performs log backups every 15 minutes. This means that once the full backup is performed you need to wait 24 hours for it to automatically do another full backup of the BizTalk Server databases… even if you try to manually run the job, it will only make the backups of the log files.

But sometimes we need, for several reasons, to have the ability and the possibility to force a full backup:

  • We will have some maintaining plan on the server, or apply a new configuration, and we want to backup the environment
  • Or simple we will install a new integration application and again we want to have a backup in this exact moment

Each company have is policies, so again for several reasons, we sometimes need to force a full backup of all BizTalk Server databases.

The standard way is to use the “BizTalkMgmtDb.dbo.sp_ForceFullBackup” stored procedure. However, and unlike what many people think, this stored procedure does not perform a full backup it only marks internally on BizTalk Server databases that the next time the Backup BizTalk Server job runs it will need to perform a full backup of the data and log files.

All BizTalk Admins know this (if they don’t know, they should!), however sometimes we need to really on DBA Admins to perform this task, and do not misunderstand me, I have nothing against DBA! All the BizTalk Administration team should be composed by several element with different skills: DBA Admins, network admins, Sys Admins and a BizTalk Admins… each playing its role. But sometime DBA don’t really understand the BizTalk infrastructure and BizTalk Administrator should have an important role advising and helping them (like in other situations it will be the opposite).

And because this is not a day by day task people tend to forget all the steps and sometimes they will create custom scripts to perform this task, but you really need to be aware of two important things:

  • The Backup BizTalk Server job is the only supported method for backing up the BizTalk Server databases. Use of SQL Server jobs to back up the BizTalk Server databases in a production environment is not supported.
  • You can use the SQL Server methods to backup the BizTalk Server databases only if the SQL Server service is stopped and if all BizTalk Server processes are stopped.

So to help one of my DBA teams I end up creating this job that is composed by two steps:

  • Step 1: Force Full Backup
    • That will call the “BizTalkMgmtDb.dbo.sp_ForceFullBackup” stored procedure
  • Step 2: Backup BizTalk Server
    • That will call the standard Backup BizTalk Server (BizTalkMgmtDb) job

Force-Full-Backup-BizTalk-Server-BizTalkMgmtDb

Note: Of course this custom job should be disabled and manually executed when you need it!

Hope you find it useful

You can download the custom “Force Full Backup BizTalk Server (BizTalkMgmtDb)” Job from:

Force Full Backup BizTalk Server (BizTalkMgmtDb) Job (863 bytes)
Microsoft | TechNet Gallery

 

This post is for the BizTalk Server and SQL Server Portuguese Community, will be held on April 23, 2014 between 18:30 – 21:50 the XLIX SQLPort Community Meeting at the Science and Technology Park of University of Porto (UPTEC) in Oporto.

XLIX Encontro da comunidade SQLPort

Realiza-se no dia 23 de Abril de 2014, o XLIX Encontro da comunidade SQLPort, que tem como objectivo a partilha de experiências e conhecimento entre os profissionais e entusiastas de SQL Server em Portugal. O evento vai ser realizado no PORTO, no auditório da UPTEC, pelas 18:30, sendo a entrada livre para todos os inscritos.

Sobre minha sessão…

A minha sessão será uma introdução a dois papéis existentes em BizTalk: BizTalk Administrator e BizTalk Developer.

Podemos dizer que o coração do motor do BizTalk Server é a MessageBox que é composta por dois principais componentes: uma ou mais base de dados sobre Microsoft SQL Server e um e o agente de Mensagens.

As base de dados SQL Server fornecem persistência para diversas dados, nos quais se incluem mensagens ou partes das mensagens, propriedades da mensagem, subscrições, estado das orquestração, dados de tracking, host queues para roteamento entre outros. Desta forma podemos dizer que existe um forte dependência entre o BizTalk Server e o SQL Server e com base nisso qualquer equipa de administração desta plataforma também requer um SQL Admin.

Uma das partes desta sessão irá abordar alguns dos tópicos que um SQL Admin deverá conhecer para administrar uma plataforma BizTalk Server ao nível do SQL, demonstrando alguns erros comuns e como os podemos evitar.

Mas como o BizTalk Server também têm uma forte componente de desenvolvimento, que nos permite desenvolver processos de negócios complexos que irão comunicar com os mais diversos sistemas de uma organização ou entre parceiros de negócios e geri-los de uma forma organizada, e uma vez que estamos num evento de SQL, nesta sessão também iremos efectuar uma introdução e abordar como podemos trocar mensagens com o SQL Server: consumir mensagens provenientes de SQL ou enviar mensagens para SQL Server.

A Agenda…

18:30: Abertura e recepção;

19:30: Community News

19:10: Utilidade das funções de: Ranking, Window e Analytic no nosso dia-a-dia!
           by Etienne Lopes [Consultor Senior – Newvision SA]

20:10: Networking / Coffee Break;

20:30: BizTalk and SQL Together: For Developers & Admin
           by Sandro Pereira [Microsoft Integration MVP, Senior Developer - DevScope]

21:30: Sorteio de prémios

21:30: Fim do evento;

22:00: Jantar livre

Data Evento: 23 Abril 2014 – 18:30H

Local do Evento: Parque de Ciência e Tecnologia da Universidade do Porto (UPTEC) | Rua Alfredo Allen, n.º455/461 | 4200-135 PORTO | Portugal

Saiba mais sobre o evento na página da Comunidade SQLPort.

O evento é gratuito! De que está à espera? Reserve já o seu lugar aqui e participe!

Estamos a sua espera.

Exciting news for the BizTalk Community… specially the 200 persons that attended the BizTalk Summit 2014 London that already know the potential of BizTalk NoS addin!

Nino Crudele just public announce in is blog: BizTalk NoS Add-in Beta version has been officially released through Visual Studio Gallery that the BizTalk NoS Addin is now available for you to download it in install it on Visual Studio gallery at: BizTalk NoS Addin!!!!!

What is BTSG NoS Addin purpose?

The purpose of BTSG NoS addin is to help all BizTalk Developer, why not, all BizTalk Administrator too in a lot of different situations, by improving the developer experience and why not reduce the development time in new or existent BizTalk projects.

It will provide several functionalities like quick search inside artifact, fast register/unregister in GAC, find critical, internal or external dependencies… and many fore functionalities like JackHammering, which will compare your VS artifact with the artifact deployed in BizTalk environment, you can also extract the artifact (Orchestration, map, schema and so on) from BizTalk environment and put it in the VS solution or even test your pipeline in VS simply… several features that are usefully in our day by day work and a time saver in a lot of situations.

How can I install BTSG NoS Addin

You can install BTSG NoS Addin directly from Visual Studio “Extensions and Updates” option. For that you must:

  • Open Visual Studio as Administrator, go to the “Tools” menu and select the “Extensions and Updates” option.
  • In the “Extensions and Updates”, in the right panel select “Online” tab and search for “BizTalk” or for “BizTalk NoS Addin”.
  • And Download the BizTalk Nos Addin”.

The rest of the installation process is described in one of my previous post: How to install BTSG NoS Addin for Visual Studio 2012

You can find all the documentation about this addin in:

Or in my posts:

Special Note

It has been a challenge, an extreme playfulness and a privilege to work with my dear friend Nino Crudele! I feel extremely honored that Nino have chosen me to help in this final phase of his work and for that THANKS Nino!

_DSC2735

Now is time to take a small vacations of BizTalk NoS addin and finish my ongoing work: BizTalk Mapper Pattern eBook… witch of course Nino will be one of the reviewers J

In my last posts I’m being describing some of the features of BTSG NoS Addin. This is the final post about the overview of all features available with this Visual Studio addin and how can you use them.

Test Pipeline

There are many ways to test BizTalk pipelines: by using Biztalk Pipeline Framework to create unit testing, using the Pipeline.exe tool or by create and use them by implementing "for real" in BizTalk messaging scenarios. But there isn’t for real an easy build support feature in Visual Studio to test our custom pipelines.

Once again BizTalk NoS addin will provide that for you. This operation will test our custom pipeline pipelines directly from Visual STudio by simple right click and “Test Pipeline”:

BTSG-NoS-Addin-Test-Pipeline

  • Visual Studio will prompt a window for to select and instance of the message that you want to try against the pipeline.

BTSG-NoS-Addin-Test-Pipeline-open-file

  • and will show the result for the pipeline directly in VS IE

BTSG-NoS-Addin-Test-Pipeline-result

Test Pipeline Component

As the previous one testing or debugging a custom pipeline component can be a challenger and most of the times you will need to create and testing them by implementing and configuring a BizTalk messaging scenarios and then attach the BizTalk process in Visual Studio to debug the components.

You now will be able to debug your pipeline component attaching an external process from VS without care about BizTalk environment by:

  • Right click on the pipeline that contains the custom pipeline component that you want to try and select the option “Test Pipeline Component”:

BTSG-NoS-Addin-Test-Pipeline-Component

  • A window will be show that will describe all the steeps necessary for you to configure you testing scenario and debug the custom pipeline component

BTSG-NoS-Addin-Test-Pipeline-Component-2

  • The first thing you need to do is select an instance of a message that you want to try against the custom pipeline component, by clicking in the add wheel in the left:

BTSG-NoS-Addin-Test-Pipeline-Component-3

    • A window will prompt for to select and instance of the message

BTSG-NoS-Addin-Test-Pipeline-Component-open-file

  • The second step is to set a breakpoint in you custom pipeline component and attach the BTSG TestPipeline process with the Visual Studio Debugger by:
    • On the Visual Studio “Debug” menu, select “Attach to Process”

BTSG-NoS-Addin-Test-Pipeline-Component-attach-process

    • Select the BTSG.TestPipeline.exe process (the title will also contains the name of our pipeline) and click “Attach”

BTSG-NoS-Addin-Test-Pipeline-Component-attach-process-select

  • For the third and final step, you will need to go to the BTSG window to configure you testing scenario and debug the custom pipeline component, to submit the select message instance to debug it against the custom pipeline component, by clicking in the submit wheel in the right side:

BTSG-NoS-Addin-Test-Pipeline-Component-4

And you be able to start debug your custom pipeline component code.

Heuristics

Heuristics are basically the same of the above dependencies features (internal and external) but more useful during refactoring or before to do an update in an artifact. For example you need to modify an artifact and you want to know what will be the artifact update impact in the entire solution.

Other extreme situation could be, I want to know where this property schema or schema could be used, and this is not so simple to know because a property schema or schema could be used in lot of different way using his root nodes.

Heuristic Internal Propagation

This feature is basically the same as the “Internal Dependencies” but with the difference that we will also go ahead and will also the sub-levels of dependencies, i.e., the sub-artifacts.

BTSG-NoS-Addin-Heuristic-Internal-Propagation

You can access this features by:

  • Right click in your resource (file) name and select the “Heuristic Internal Propagation” option

BTSG-NoS-Addin-Heuristic-Internal-Propagation-option

Heuristic External Propagation

This feature is basically the same as the “External Dependencies” but with the difference that we will also go ahead and will also the sub-levels of dependencies, i.e., the sub-artifacts.

BTSG-NoS-Addin-Heuristic-External-Propagation

You can access this features by:

  • Right click in your resource (file) name and select the “Heuristic External Propagation” option

BTSG-NoS-Addin-Heuristic-External-Propagation-option

Heuristic Circular Propagation

This feature is basically the combination of both above heuristic operation (internal and external propagation) in a single operation.

BTSG-NoS-Addin-Heuristic-Circular-Propagation

You can access this features by:

  • Right click in your resource (file) name and select the “Heuristic Circular Propagation” option

BTSG-NoS-Addin-Heuristic-Circular-Propagation-option

Heuristic Anchoring Dependencies

Looking in all files in the solution for all artifact that could be associated with the artifact selected.

You can access this features by:

  • Right click in your resource (file) name and select the “Heuristic Anchoring Dependencies” option

BTSG-NoS-Addin-Heuristic-Anchoring-Dependencies-option

Heuristic Similarity Dependencies

Looking in all files in the solution for all artifact that could be associated (same solution domain, for example are using the same namespace) in any possible option, with the artifact selected.

You can access this features by:

  • Right click in your resource (file) name and select the “Heuristic Similarity Dependencies” option

BTSG-NoS-Addin-Heuristic-Similarity-Dependencies-option

Heuristic Contagion Dependencies

Looking in all files in the solution for all artifact that could be associated with the artifact selected, in all of possible combinations, type name, fields used, root name and more…

You can access this features by:

  • Right click in your resource (file) name and select the “Heuristic Contagion Dependencies” option

BTSG-NoS-Addin-Heuristic-Contagion-Dependencies-option

Finally all the features are documented… And we only need to wait the release announcement!!!!