by community-syndication | Apr 11, 2007 | BizTalk Community Blogs via Syndication
Over the past couple of weeks I have been documenting the procedure I use to build new BizTalk Environments, this includes things like making sure that the backup jobs are in place and that the backup files get deleted (the default backup job shipped with BizTalk cleans up the history records in the database, but does not clean up the files created by the backup). This week I was helping a client clean up a tracking database that had gotten very large because of a miss configured purge job, while I was working on this problem I noticed that the full backups for the tracking database via the BizTalk backup job were happening at 10:00am local time (Melbourne, Australia). From my past experience as a SQL DBA, doing a full backup on a production database at 10:00am in the morning is probably not the best time to do it, I dismissed it as something up with the scheduler. Today while completing the setup of a new BizTalk server for another client, I again noticed that the full backups for all the BizTalk databases were happening at 10:00am, so I though to myself, what is up with this. The other strange thing is the file name did not match the time the file was created, here is an example:
READIFYPOC_BizTalkDTADb_Full_BTS_2007_04_12_00_00_08_497.bak – with a create date & time of –12/04/2007 10:00 AM
So something is really up with this backup job, I opened the backup job and looked at thefirst step, it calls the stored procedure sp_BackupAllFull_Schedule, so I open the stored procedure and on line 29 I find this:
select @CurrDT = getutcdate()
the picture suddenly becomes very clear, Melbourne has a UTC offset of +10 hours, now I know why the backups are running at 10:00am in the morning.
I understand why they used getutcdate(), almost all the dates in the BizTalk tracking database are stored in UTC time and when they generate reports they use something like this:
dateadd(minute, @UtcOffsetMin, [ServiceInstance/StartTime]) – from the “Most recent 100 services instances” query in HAT.
Now that I figured out why it was doing the full backups at 10:00am in the morning I started trying to find out how to fix the problem without changing the BizTalkstored procedures, I searched Google and the only thing that I found was someone that have setup a job that ran at 5 minutes before they wanted to do the full backup that executed the sp_ForceFullBackup stored procedure to force the next run of the backup job to do a full backup. This was not the solution I was looking for, since what that would produce is a backup at the time I select and then another backup when the UTC date changed at 10:00am. I then went and looked at the other 2 steps of the backup job, the second step that does the transaction log backups also uses getutcdate(), but the job that delete the backup history uses getdate(), so it deletes the backup history 10 hours ahead of schedule.
So as bad as I hate to change the shipped stored procedures, I decided to change them and see if that fixed the problem, YES, the problem is fixed, the full backup runs the 1st run after midnight local time and the file name matches the file create time of the file system.
I guess it is now time to open a support incident and see if they can explain why doing a full backup at 10:00am is a good thing. If they don’t I will have to create a set of _LocalTime stored procedure with my changes in them to prevent services packs from unfixing my fix. I have already done this with my sp_DeleteBackupHistoryAndFiles stored procedure that also deletes the backup files from the file system (see the download section of my website for your copy)
by community-syndication | Apr 10, 2007 | BizTalk Community Blogs via Syndication
In the past I have noticed that system testers find it difficult to test middleware applications such as those implemented using BizTalk. System testers generally don’t want to read BizTalk xml messages dumped out to disk, they like to be able to query for their test results via some form of GUI. Many are adept at writing SQL to return result sets containing the test data they want to analyze.
I came up with the idea of using BAM relationships between different activities within a single view to log before and after snapshots of BizTalk messages. So when messages were transformed by maps, orchestrations or the BRE system testers could test those transforms. You can then give system testers’ access to the BAM Portal Website where they could view and query these before and after snapshots and match the actuals against the expected results to pass or fail their tests.
This solution for system testing only works for certain scenarios’ with certain types of schemas as each message logged cannot contain repeating records if it is to be logged to BAM.
So what am I on about? Take a single record schema which validates an xml message like the one below as I said before this technique won’t work with messages which contain multiple records.
<ns1:SampleTransaction TransactionID=”123456″ ProductName=”ABC” Amount=”1234.56″ Quantity=”24″ xmlns:ns1=”http://SynbogalooSamples/SampleTransaction/1.0.0.0″ />
Each time a map, orchestration or business rules were applied to a message I wanted to send messages to BAM so I was basically just logging messages to BAM each time the contents of the message changed. I used BAM relationships to associate the message before it was transformed to the message after it was transformed so the tester could assess whether the transformation worked.
OK this was all easy but very time consuming using the Excel add-in to create the xlsb files for each schema so I wrote a simple xlsb file generator. Note: For this code to work with schemas that have xsd types other than DateTime, Integer, Double and String you will need to add to the switch statement in the CreateActivity method.
using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.IO;
namespace BAMXslbGenerator
{
class Program
{
private const string UCName = “Name”;
private const string LCName = “name”;
private const string View = “View”;
private const string BAMDefinition = “BAMDefinition”;
private const string BAMDefinitionNamespace = “http://schemas.microsoft.com/BizTalkServer/2004/10/BAM“;
private const string Extension = “Extension”;
private const string OWC = “OWC”;
private const string OWCNamespace = “urn:schemas-microsoft-com:office:excel”;
private const string ActivityView = “ActivityView”;
private const string ActivityRef = “ActivityRef”;
private const string Activity = “Activity”;
private const string Alias = “Alias”;
private const string CheckpointRef = “CheckpointRef”;
private const string Id = “ID”;
private const string Checkpoint = “Checkpoint”;
private const string XpathToAttribute =
“(//*[local-name()=’element’])[last()]/*[local-name()=’complexType’]/*[local-name()=’attribute’]”;
private const string DataType = “DataType”;
private const string DataLength = “DataLength”;
private const string Length = “length”;
private const string MaxLength = “maxLength”;
private const string XpathToLength = “*[local-name()=’simpleType’]/*[local-name()=’restriction’]/*[local-name()=’length’]/@value”;
private const string XpathToMaxLength = “*[local-name()=’simpleType’]/*[local-name()=’restriction’]/*[local-name()=’maxLength’]/@value”;
private const string SqlDateTime = “DATETIME”;
private const string SqlNvarchar = “NVARCHAR”;
private const string SqlInt = “INT”;
private const string SqlFloat = “FLOAT”;
private const string XsdDateTime = “xs:dateTime”;
private const string XsdString = “xs:string”;
private const string XsdInteger = “xs:int”;
private const string XsdDouble = “xs:double”;
private static StringBuilder xlsbText = null;
static void Main(string[] args)
{
try
{
//Check for help
if (args.Length == 0)
{
ShowUsageMessage();
}
else if ((args[0] == “/?”) || (args[0] == “/help”))
{
ShowUsageMessage();
}
else
{
//Parse out command line arguements
string xlsbFileNameArg = GetCommand(“/x”, args);
string viewNameArg = GetCommand(“/v”, args);
string activityNameArg = GetCommand(“/a”, args).Replace(“[“,””).Replace(“]”,””);
string schemaArg = GetCommand(“/s”, args).Replace(“[“, “”).Replace(“]”, “”); ;
if (xlsbFileNameArg != string.Empty && viewNameArg != string.Empty
&& activityNameArg != string.Empty && schemaArg != string.Empty)
{
string[] activityNames = activityNameArg.Split(new string[] { “,” }, StringSplitOptions.None);
string[] schemas = schemaArg.Split(new string[] { “,” }, StringSplitOptions.None);
GenerateXlsbXml(viewNameArg, activityNames, schemas);
//Write out the file
File.Delete(xlsbFileNameArg);
File.AppendAllText(xlsbFileNameArg, xlsbText.ToString(), Encoding.Unicode);
}
else
{
Console.WriteLine(“Incorrect arguements!”);
}
}
}
catch (System.Exception ex)
{
Console.Write(ex.ToString());
}
}
private static string GetCommand(string commandToken, string[] args)
{
string commandValue = string.Empty;
for (int i = 0; i < args.Length; i++)
{
if (args[i] == commandToken)
{
commandValue = args[i + 1];
break;
}
}
return commandValue;
}
private static void ShowUsageMessage()
{
Console.WriteLine(“Generates an xlsb BAM definition file conaining one view and multiple schema definitions to log to BAM.”);
Console.WriteLine(“Syntax: BAMXlsbGenerator”);
Console.WriteLine(“Commands:”);
Console.WriteLine(“\t/x \txlsb file name”);
Console.WriteLine(“\t/v \tBAM View definition name”);
Console.WriteLine(“\t/a[] \tActivity names enclosed in [] comma separated, there must be one activity name per schema.”);
Console.WriteLine(“\t/s[] \tSchema names enclosed in [] comma separated, there must be one schema name per activity.”);
Console.WriteLine(“\t/? or /help \tDisplay this usage message”);
}
private static void GenerateXlsbXml(string viewName, string[] activityNames, string[] schemas)
{
//Create an writer for building the xlsb
xlsbText = new StringBuilder();
XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();
xmlWriterSettings.Indent = true;
XmlWriter xlsbWriter = XmlWriter.Create(xlsbText, xmlWriterSettings);
//Start building xml
xlsbWriter.WriteStartDocument();
xlsbWriter.WriteStartElement(BAMDefinition, BAMDefinitionNamespace);
if (schemas.Length != activityNames.Length)
{
throw new ApplicationException(“The number of activity names and schemas don’t match.”);
}
Dictionary<string, string>[] checkpointIDsForSchemas = new Dictionary<string, string>[schemas.Length];
string[] activityIDs = new string[schemas.Length];
//Create the activities
for (int i = 0; i < schemas.Length; i++)
{
Dictionary<string, string> checkpointIDs;
string activityID;
CreateActivity(xlsbWriter, schemas[i], activityNames[i],
out checkpointIDs, out activityID);
checkpointIDsForSchemas[i] = checkpointIDs;
activityIDs[i] = activityID;
}
//Now create the view
xlsbWriter.WriteStartElement(View);
xlsbWriter.WriteAttributeString(UCName, viewName);
string viewID = Id + Guid.NewGuid().ToString(“N”);
xlsbWriter.WriteAttributeString(Id, viewID);
//Create Activity Views
for (int i = 0; i < schemas.Length; i++)
{
CreateActivityView(xlsbWriter, activityNames[i],
checkpointIDsForSchemas[i], activityIDs[i]);
}
xlsbWriter.WriteEndElement(); //END View
xlsbWriter.WriteStartElement(Extension);
xlsbWriter.WriteElementString(OWC, OWCNamespace, “”);
xlsbWriter.WriteEndElement(); //END Extension
xlsbWriter.WriteEndElement(); //END BAMDefinition
xlsbWriter.WriteEndDocument();
xlsbWriter.Flush();
}
private static void CreateActivityView(XmlWriter xlsbWriter, string activityName,
Dictionary<string, string> checkpointIDs, string activityID)
{
xlsbWriter.WriteStartElement(ActivityView);
xlsbWriter.WriteAttributeString(UCName, View + activityName);
xlsbWriter.WriteAttributeString(Id, Id + Guid.NewGuid().ToString(“N”));
xlsbWriter.WriteAttributeString(ActivityRef, activityID);
//Loop through the dictionary and create the Aliases
foreach (string key in checkpointIDs.Keys)
{
xlsbWriter.WriteStartElement(Alias);
xlsbWriter.WriteAttributeString(UCName, key);
xlsbWriter.WriteAttributeString(Id, Id + Guid.NewGuid().ToString(“N”));
xlsbWriter.WriteElementString(CheckpointRef, checkpointIDs[key]);
xlsbWriter.WriteEndElement(); //END Alias
}
xlsbWriter.WriteEndElement(); //END ActivityView
}
private static void CreateActivity(XmlWriter xlsbWriter, string schema,
string activityName, out Dictionary<string, string> checkpointIDs,
out string activityID)
{
xlsbWriter.WriteStartElement(Activity);
xlsbWriter.WriteAttributeString(UCName, activityName);
activityID = Id + Guid.NewGuid().ToString(“N”);
xlsbWriter.WriteAttributeString(Id, activityID);
//Create a dictionary object to store the IDs in
checkpointIDs = new Dictionary<string, string>();
//Open the schema
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(schema);
//The rules are
XmlNodeList xmlNodeList = xmlDocument.SelectNodes(XpathToAttribute);
//Loop through all the attributes and create the new xslb schema
foreach (XmlNode xmlNode in xmlNodeList)
{
string checkpointID = Id + Guid.NewGuid().ToString(“N”);
checkpointIDs.Add(xmlNode.Attributes[LCName].Value, checkpointID);
//Add the data type attributes
if (xmlNode.OuterXml.Contains(XsdDateTime))
{
xlsbWriter.WriteStartElement(Checkpoint);
xlsbWriter.WriteAttributeString(UCName, xmlNode.Attributes[LCName].Value);
xlsbWriter.WriteAttributeString(Id, checkpointID);
xlsbWriter.WriteAttributeString(DataType, SqlDateTime);
xlsbWriter.WriteEndElement(); //End Checkpoint
}
else if (xmlNode.OuterXml.Contains(XsdInteger))
{
xlsbWriter.WriteStartElement(Checkpoint);
xlsbWriter.WriteAttributeString(UCName, xmlNode.Attributes[LCName].Value);
xlsbWriter.WriteAttributeString(Id, checkpointID);
xlsbWriter.WriteAttributeString(DataType, SqlInt);
xlsbWriter.WriteEndElement(); //End Checkpoint
}
else if (xmlNode.OuterXml.Contains(XsdDouble))
{
xlsbWriter.WriteStartElement(Checkpoint);
xlsbWriter.WriteAttributeString(UCName, xmlNode.Attributes[LCName].Value);
xlsbWriter.WriteAttributeString(Id, checkpointID);
xlsbWriter.WriteAttributeString(DataType, SqlFloat);
xlsbWriter.WriteEndElement(); //End Checkpoint
}
else if (xmlNode.InnerXml.Contains(XsdString))
{
xlsbWriter.WriteStartElement(Checkpoint);
xlsbWriter.WriteAttributeString(UCName, xmlNode.Attributes[LCName].Value);
xlsbWriter.WriteAttributeString(Id, checkpointID);
xlsbWriter.WriteAttributeString(DataType, SqlNvarchar);
if (xmlNode.InnerXml.Contains(Length))
{
xlsbWriter.WriteAttributeString(DataLength,
xmlNode.SelectSingleNode(XpathToLength).InnerText);
}
else if (xmlNode.InnerXml.Contains(MaxLength))
{
xlsbWriter.WriteAttributeString(DataLength,
xmlNode.SelectSingleNode(XpathToMaxLength).InnerText);
}
else
{
xlsbWriter.WriteAttributeString(DataLength, “255”);
}
xlsbWriter.WriteEndElement(); //End Checkpoint
}
else if (xmlNode.OuterXml.Contains(XsdString))
{
xlsbWriter.WriteStartElement(Checkpoint);
xlsbWriter.WriteAttributeString(UCName, xmlNode.Attributes[LCName].Value);
xlsbWriter.WriteAttributeString(Id, checkpointID);
xlsbWriter.WriteAttributeString(DataType, SqlNvarchar);
xlsbWriter.WriteAttributeString(DataLength, “255”);
xlsbWriter.WriteEndElement(); //End Checkpoint
}
else
{
Console.Write(“Unknown xsd datatype: ” + xmlNode.OuterXml + ” continuing to create xlsb.”);
checkpointIDs.Remove(xmlNode.Attributes[LCName].Value);
}
}
xlsbWriter.WriteEndElement(); //End Activity
}
}
}
How to call example: BAMXlsbGenerator /x “C:\Projects\SynboogalooSamples\Test.xlsb” /v TestView /a [TestActivityA,TestActivityB] /s [C:\Projects\SynboogalooSamples\SampleTransactionA.xsd,C:\Projects\SynboogalooSamples\SampleTransactionB.xsd]
This saved me a lot of time all I needed to do now to finish setting up BAM was deploy the xlsb BAM definition using the BM.exe tool and use the Tracking Profile Editor to associate the message payload with the BAM activity definition about 5 minutes of drag n’ drop work. In the BAM Portal system testers can query transformed data and can even query the BAM tables if they want. They are able through BAM relationships to view the message contents each time it has been modified and logged to BAM.
I found this technique was a quick and simple way of empowering system testers to test BizTalk applications.
I should note another option for System Testing EAI solutions is to go with an implementation of FIT (Framework For Integration Testing) here is a blog with more info.
Rob
