Posts Tagged ‘Optimization’

Microsoft announced on October 27, 2016 the release of Microsoft BizTalk Server 2016 – the 10th major release of BizTalk Server –and, as usual with previous versions, I updated my installation and configuration manual for BizTalk Server 2016. This whitepaper will explain in detail – a step-by-step guideline – how to install and configure Microsoft BizTalk Server 2016 on a standalone environment running Windows Server 2016. This information will help you plan the installation and configuration of BizTalk Server 2016, applications and components on which it depends focused on creating a development environment (you can also follow this tutorial to help you create production environments, however if this is the case you need to skip some steps).

BizTalk-Server-Installation-scenario

Assumptions and out of scope

It will be assumed that the operating system: Windows Server 2016 and latest critical Windows updates from Microsoft already have been installed.

In this scenario, it will be performed a full installation of Microsoft BizTalk Server 2016, with the exception of the deprecated SharePoint Adapter (SharePoint Services Service Side Object Model (SSOM)). The following components will be installed:

  • Enterprise Single Sign-On (SSO).
  • BizTalk Group.
  • BizTalk Runtime.
  • Business Rule Engine.
  • BAM Tools and Alerts.
  • BAM Portal.
  • BizTalk EDI/AS2 Runtime
  • Microsoft BizTalk Adapters for Enterprise Applications (BizTalk Adapter Pack)
What’s in store for you?

By reading this whitepaper, you will understand:

  • Important considerations before setting up BizTalk Server 2016
  • Prerequisites for installing BizTalk Server 2016
  • Step by step process of installing BizTalk Server 2016 on Windows Server 2016
  • Best ways in which you can optimize your BizTalk Server 2016 environment
  • Hardware and Software requirements
  • Basic Configuration vs. Custom Configuration

Installing-Configure-BizTalk-Server-2016-Standalone-Machine

Contents
  • BizTalk Server Installation scenario
    • Assumptions and out of scope
  • Important considerations before set up the server.
    • Join the Local Administrators Group.
    • Change the Machine name.
    • Turn off Internet Explorer Enhanced Security Configuration (optional)
    • Disable User Account Control (optional).
    • Turn Windows Firewall off (optional).
    • Configure the Application Event Log.
    • Configure Microsoft Distributed Transaction Coordinator (MS DTC).
    • Customize Taskbar and Start Menu Properties (optional).
    • Set the default browser.
    • Install Critical Windows Updates.
  • Preparing and install prerequisites for BizTalk Server 2016.
    • Important considerations before set up the servers.
    • Enable Internet Information Services.
      • Running the BAM Portal in a 64-bit Environment.
    • Install Windows Identity Foundation (WIF) (optional).
    • Install and configure SMTP Server Feature (optional)
      • Verify Your Installation
    • Install Microsoft Office Excel 2016 (optional).
      • Installing and configuring Office 2016 Deployment Tool
      • Installing Microsoft Office Excel 2016
    • Install Visual Studio 2015 (optional).
      • Remove Microsoft SQL Server Express.
    • Install SQL Server 2016.
    • Configure SQL Server Database Mail feature.
  • Install and configure BizTalk Server 2016.
    • Install BizTalk Server 2016.
      • Verify Your Installation.
    • Configure BizTalk Server.
      • Pin BizTalk Server Administration to taskbar.
    • Configure SQL Server Network Configuration protocols.
    • Validate Mail account used by BizTalk to send BAM Alerts.
    • Install BizTalk Adapter Pack
      • Microsoft BizTalk Adapter Pack and Microsoft BizTalk Adapter Pack (x64).
      • Steps to install BizTalk Adapter Pack.
      • Add adapters to BizTalk Administration Console.
    • Install Critical Windows Updates and BizTalk Server Cumulative Update Package.
    • Configure BizTalk Server SQL Jobs.
      • How to configure Backup BizTalk Server (BizTalkMgmtDb).
      • How to configure DTA Purge and Archive (BizTalkDTADb).
      • MessageBox_Message_Cleanup_BizTalkMsgBoxDb.
  • Optimize the BizTalk Server 2016 environment
    • Deleting BizTalk backup files.
      • Implementing a custom sp_DeleteBackupHistory.
      • Implementing a Maintenance Plan to clean BizTalk Database backup’s files.
    • Pre-allocate space and define auto-growth settings for BizTalk Server databases.
    • Configure BizTalk Server Windows Services.
    • Install and configure BizTalk Health Monitor.
      • How to register BizTalk Health Monitor Snap-In.
      • How to integrate BHM Snap-In into BizTalk Admin Console
    • Configure BizTalk Jobs History (Microsoft SQL Server Agent job history log).
      • To resize the job history log based on raw size.
    • Managing and cleaning BizTalk Server MarkLog database tables.
    • Configure host and Host instances.
      • What’s is Host, Host Instances and Adapter Handlers?.
      • Best practices to Configuring Hosts and Host Instances.
      • How can I automate this task?.
    • Power Mode.
    • Consider setting the ‘text in row’ table option to boost BizTalk Server Performance
      • How to exploit the Text in Row table option in BizTalk Server
  • Appendix A: Hardware and Software Requirements
  • Appendix B: Redistributable CAB Files
  • Appendix C: Basic Configuration vs. Custom Configuration

 

Download the whitepaper for free here: Installing BizTalk Server 2016 in a Standalone Machine

Installing-Configure-BizTalk-Server-2016-Standalone-Machine-vertical

If you are interest to see other version check the following posts:

 

I would like to take this opportunity also to say thanks to Lex Hegt for reviewing this whitepaper.

Download the whitepaper for free here: Installing BizTalk Server 2016 in a Standalone Machine

Finally, something that many community members have been requesting me to publish is here! I already made this manual several months ago, however, for several reasons (speaking engagements, publishing other content and so on) I have been delaying its publication. But I have offered this same guide to all my customers.

There are many things to consider when planning this type of installation, often the network infrastructure already exists and BizTalk Server must coexist with other network applications.

Installing BizTalk Server 2013 R2 in a Basic Multi-Computer Environment (User Guide) is a 145 pages detailed installation document that explains how to install, configure and optimize Microsoft BizTalk Server 2013 R2 on a basic multi-computer running Windows Server 2012 R2. This information will help you to plan the installation and configuration of BizTalk Server 2013 R2, applications and components on which it depends.

Contents                                      
  1. BizTalk Server Installation scenario.
  2. The need for a Domain Controller – Windows Groups and Service Accounts.
    1. Create Domain Groups and Users.
    2. Planning the use of a new Organizational Unit.
    3. Windows Groups Used In BizTalk Server.
    4. IIS_WPG and IIS_IUSRS Group.
    5. User and Service Accounts Used In BizTalk Server.
    6. Summary of users and Groups Affiliation.
    7. SQL Server Service Accounts.
    8. References.
  3. Preparing Computers for Installation – Important considerations before set up the servers.
    1. Change the Machine name.
    2. Join the Local Administrators Group.
    3. The user running the BizTalk Server configuration must belong…
    4. Install Critical Windows Updates.
    5. Disable IPv6 (optional).
    6. Turn off Internet Explorer Enhanced Security Configuration (optional).
    7. Disable User Account Control (optional).
    8. Turn Windows Firewall off (optional).
    9. Configure Microsoft Distributed Transaction Coordinator (MS DTC).
    10. COM+ Network Access considerations.
  4. Preparing and Install SQL Server 2014 machine.
    1. Important considerations before set up the servers.
    2. Install SQL Server 2014.
    3. Configure SQL Server Database Mail feature.
    4. Install Service Pack 1 for Microsoft SQL Server 2014.
    5. Configure SQL Server for Remote Connections.
    6. Configured SQL Server protocols – Disable the Shared Memory Protocol, Enable TCP/IP and Named Pipes.
    7. Configure SQL Server Database Engine to listen on a specific TCP Port (optional).
    8. Configure SQL Analysis Server to listen on a specific TCP Port (optional).
    9. Configuring Microsoft Distributed Transaction Coordinator (DTC) to work through a firewall or network address translation firewalls (optional).
    10. List of ports between BizTalk Server and SQL Server (optional).
    11. Configure Firewall on SQL Server machine (optional).
      1. Inbound Rules.
      2. Outbound Rules.
  5. Preparing and install prerequisites on BizTalk Server 2013 R2 machine.
    1. Important considerations before set up the servers.
    2. Enable Internet Information Services.
    3. Running the BAM Portal in a 64-bit Environment.
    4. Install Windows Identity Foundation (WIF) (optional).
    5. Install Microsoft Office Excel 2013 (optional).
    6. Install Visual Studio 2013 (optional).
    7. Remove Microsoft SQL Server Express.
    8. SQL Server Considerations.
    9. Install SQL Server 2014 Client Tools.
    10. Create SQL Alias to communicate with remote SQL Server using Non-Standard Port (optional).
    11. List of ports between SQL Server and BizTalk Server (optional).
    12. Configure Firewall on BizTalk Server machine.
      1. Inbound Rules.
      2. Outbound Rules.
  6. Testing environment connectivity’s.
    1. TCPView.
    2. DTCPing.
    3. DTCTester.
    4. SQL Server 2014 Client Tools.
  7. Install and configure BizTalk Server 2013 R2 machine.
    1. Install BizTalk Server 2013 R2.
      1. Verify Your Installation.
    2. Configure BizTalk Server.
      1. Pin BizTalk Server Administration to taskbar.
    3. Validate Mail account used by BizTalk to send BAM Alerts.
    4. Install BizTalk Adapter Pack.
      1. Microsoft BizTalk Adapter Pack and Microsoft BizTalk Adapter Pack (x64).
      2. Steps to install BizTalk Adapter Pack.
      3. Add adapters to BizTalk Administration Console.
    5. Install Critical Windows Updates and BizTalk Server Cumulative Update Package.
    6. Configure BizTalk Server SQL Jobs.
      1. How to configure Backup BizTalk Server (BizTalkMgmtDb).
      2. How to configure DTA Purge and Archive (BizTalkDTADb).
      3. MessageBox_Message_Cleanup_BizTalkMsgBoxDb.
  8. Optimize the BizTalk Server 2013 R2 environment
    1. Deleting BizTalk backup files.
      1. Implementing a custom sp_DeleteBackupHistory.
      2. Implementing a Maintenance Plan to clean BizTalk Database backup’s files.
    2. Pre-allocate space and define auto-growth settings for BizTalk Server databases. 112
    3. Configure BizTalk Server Windows Services.
    4. Install and configure BizTalk Health Monitor.
      1. How to register BizTalk Health Monitor Snap-In.
      2. How to integrate BHM Snap-In into BizTalk Admin Console
    5. Install SSO Configuration Application MMC Snap-In
    6. Configure BizTalk Jobs History (Microsoft SQL Server Agent job history log).
      1. To resize the job history log based on raw size.
    7. Force Full Backup BizTalk Server (BizTalkMgmtDb) job.
    8. Managing and cleaning BizTalk Server MarkLog database tables.
    9. Configure host and Host instances.
      1. What’s is Host, Host Instances and Adapter Handlers?.
      2. Best practices to Configuring Hosts and Host Instances.
      3. How can I automate this task?.
    10. Power Mode.
    11. Consider setting the ‘text in row’ table option to boost BizTalk Server Performance.
      1. How to exploit the Text in Row table option in BizTalk Server.
    12. General network TCP settings that can impact BizTalk Server
      1. How to Disable the Scalable Networking Pack (SNP) features
      2. How to Disable Privilege Attribute Certificate (PAC)
      3. How to Check and if necessary increase the ephemeral ports and reduce the TCP re-use timeout setting

download-whitepaper

If you are interest to see other version check the following posts:

In the second post of this series we follow a simple walkthrough explaining the basic principles to create a flat file schema from a file delimited by symbols. Now it’s time to do the same thing, but this time translating an inbound positional flat-file.

A positional flat file it’s normally a file that has fields that have a fixed maximum length and records that have a common end-of-record terminator, for example:

Sandro      Pereira         {CR}{LF}
José        Silva           {CR}{LF}

Where the first fixed 12 characters describe the Name field and the following 16 characters describe the Surname field. White space padding the end of each field and the Carriage Return/Line Feed characters is the common end-of-record terminator.

The following walkthrough shows you how to create a flat file schema from a positional flat-file document that stores employee information using the BizTalk Flat File Schema Wizard.

Field Description Length
GroupId 4
PrimaryKey 2
SecondaryKey 2
ControlFlag 1
PartyNo 1
Remark 3
Country 10
Name 12
Surname 16
Street 22
City 12
District 19
ZipCode

File sample:

0120200300 01 PT        Sandro      Pereira         Rua Central, 133      Crestuma    Vila Nova de Gaia  4415 Crestuma
0120200300 01 PT        José        Silva           Crestuna              Crestuma    Vila Nova de Gaia  4415 Crestuma
0120200300 01 PT        Rui         Barbosa         Porto                 Porto       Porto              4400 Crestuma
0120200300 01 PT        Miguel      Silva                                 Porto       Porto              4415 Crestuma

Once again I will walk you through the required steps at design time:

  • using the Flat File Schema Wizard to generate the Flat File Schema;
  • and the pipeline editor to create a custom pipeline;

To enable the runtime to support the processing of the Flat File described earlier (Employees)

We begin then by launching Visual Studio and create or open a BizTalk project:

  • “File –> New –> Project”, on BizTalk Projects, select the option “Empty BizTalk Server Project”;
  • Insert the project name, physical location on the disk and the name of the solution.
Using Flat File Schema Wizard to generate a schema based on a positional flat-file

To create the schema which will recognize the ‘Employees’ text file, we need to go to the BizTalk solution created in Visual Studio and perform the following steps:

  • Press the right button on top of the project in Solution Explorer, and select the option “Add –> New Item…
  • On “Installed Templates” menu in the window “Add New Item”, select the option “Schema Files”, and then select the option “Flat File Schema Wizard”, then provide the name you want to give the scheme in this example: “FF_Positional_Employees.xsd”

Once again, has we explain previous, by selecting this option, we will be guided automatically by the tool “BizTalk Flat File Schema Wizard” that will help us to create a Flat File Schema and define its data structure (records, elements, attributes …) based on the text file specified.

  • Select “Next” to continue.

01-BizTalk-Flat-File-Schema-Wizard-Welcome-Page-positional

  • In the window “Flat File Schema Information” we will have to:
    • Select an instance of the text file that will serve as the model of the structure that we want to transform;
    • Although it is not necessary, it is good practice to rename the Record name “Root”.
      • In this case we will rename it to “Employees”
    • And finally, assign a “Target namespace” to the scheme and define the encoding of the input file

02-BizTalk-Flat-File-Schema-Wizard-information-positional

Note: The wizard will load the text file so that we can begin to split it and map it into the desired structure. In this step we need to define how the records or rows are differentiated.

  • Since each record “Employee” that we want to create is defined in a single line, in the “Select Document Data” we can select only the data portion of the document that will set the record, i.e. the whole first line.

04-BizTalk-Flat-File-Schema-Wizard-Document-Data-positional

Note: Alternatively, let’s call it option 2, we could select all the data of the file, however with this approach we need to treat differently the data a few steps ahead in the “Child Elements” window.

05-BizTalk-Flat-File-Schema-Wizard-Document-Data-positional

Note: if you notice that some characters are not being parsed correctly as the picture bellow shows, then you probably need to go back to the “Flat File Schema Information” windows and set a different code page, for example “Western-European (1252)” instead of “UTF-8 (65001)”.

03-BizTalk-Flat-File-Schema-Wizard-Document-Data-positional

  • In the window “Select Record Format”, even though we are dealing with a positional flat-file, at this point we are trying to define the structure of each “Employee” record witch is delimited by a return Carriage Return/Line Feed ({CR}{LF}). So in this case we will have to select the “By delimiter symbol” option.

06-BizTalk-Flat-File-Schema-Wizard-Record-Format-positional

  • In the window “Delimited Record” we will provide the record delimiter, once again, we want to define the structure of Employee, i.e. each row is an Employee node, our limiter is this sample will be the {CR}{LF} (Carriage Return/Line Feed).

07-BizTalk-Flat-File-Schema-Wizard-Delimited-Record-positional

  • In the window “Child Elements” we will define what kind of element we want to assign to the registry. As we are defining the Employee structure and the file contains multiple employees, we have to select the “Element Type” as “Repeating record” If we do not perform this step, we will not have the ability to split the record into multiple elements / attributes individual.
    • Note: If you do not define the element as “Repeating record” (or “Record”), you will not have the ability to break down your record into individual elements or attributes.

08-BizTalk-Flat-File-Schema-Wizard-Child-Elements-positional

Note: earlier I told that we could select all the data inside the flat file and that we would need to treat the information differently in this step – option 2. So if you select this option, it will present all the lines in the document in this window, because we want to have an “Array” of Employees, in this step we need to select the “Element Type” of the first element as “Repeating record” and the rest of the set as “Ignore” (You set this to Ignore because you have already specified Repeating Record for the Employee node)

09-BizTalk-Flat-File-Schema-Wizard-Child-Elements-positional

In this point, we have defined the translation rules for the repeating record “Employee” but we have not broken down the various elements that compose the record itself. We have just map that each line of the text file corresponds to a record Employee.

  • In the “Schema View” select “Next” to continue processing the message

10-BizTalk-Flat-File-Schema-Wizard-Employee-Record-positional

At this point everything is exactly the same as we did in the Delimited by Symbols example. From here forward is that the changes will emerge.

  • In this phase the wizard restarts the whole process described above, but if you noticed, the wizard no longer selects all information contained in the text file, but only what was selected to define the record Employee. What we will do now is split the information of record “Employee” in different elements, for that, we will select only the information required leaving out the Carriage Return/Line Feed. On the “Select Document Data” screen click “Next”.

11-BizTalk-Flat-File-Schema-Wizard-Document-Data-positional

  • On the “Select Record Format” screen, because now the content of the “Employee” record needs to be parsed according to predefined length (positional), we need to select the option “By relative positions” and then click “Next”

12-BizTalk-Flat-File-Schema-Wizard-Record-Format-positional

  • The wizard provides a visual tool for showing and calculating the distance between the fields. On the “Positional Record” page, Use the left mouse button (or the right) to click a position marker in the position selection box to set the new position marker line. The position marker is represented as a solid line in will represent the rule in which a specific name field begins.
    • Position Marker selection box: Click an area on the page to represent the beginning position of the element. Click again to remove the position marker.
    • By default, a position marker line exists at the beginning of the record at position zero.
    • Click the following position markers to represent the rest of the data fields:
Field name Position Marker
GroupId 0
PrimaryKey 4
SecondaryKey 6
ControlFlag 8
PartyNo 9
Remark 11
Country 14
Name 24
Surname 36
Street 52
City 74
District 86
ZipCode 105

13-BizTalk-Flat-File-Schema-Wizard-Positional-Record-positional

  • In the “Child Elements” page, we will define the different elements/attributes of the structure of the record person. This operation is very similar to any XSD, where we can define the different names and data types. Adjust the values to:
    • Have the same name of the table above in the “Element Name”
    • Leave the default “Element Type” and “Date Type” – Field element and String

14-BizTalk-Flat-File-Schema-Wizard-Child-Elements-positional

Note: Once again, you should rename the parent node, each record, element or attribute in the BizTalk Flat File Schema Wizard, mainly in this step by modify the “Element Name” field to your desired name, however if you forgot to do that or if you make a mistake defining one of them, you can always rectify it in the Schema Editor. The same applies to the Data Type.

  • Select “Next” to continue.
  • Finally the wizard will show the equivalent XML structure that your text file document will have. Once you select the option “Finish”, the scheme will be available for you to use in your BizTalk solution.

15-BizTalk-Flat-File-Schema-Wizard-Complete-positional

After we finalize the creation of the Flat File Schema which will contain the translation/transformation rules of the text file, we can easily test our parsing rules, without having to get out of our development tool (Visual Studio) and without having to deploy our solution.

If we select the Flat File Schema that we just created and access to its properties, we can see that by default all properties are preconfigured so that we can perform tests to our message transformation:

  • The input instance file is configured with the file which was used to create the schema;
  • And with the correct input and output formats
    • Validate Instance Input Type: Native
    • Generate Instance Output Type: XML

To test, you simply right-click in the schema that we just created and select the option “Validate Instance”. This option will use the configured file to execute and validate all transformation rules defined in the Flat File Schema and present the final result or occurring errors in the “Output” window

16-BizTalk-Flat-File-Schema-Wizard-Testing-positional

Did you know that Fields in positional records have a limit of 50000000 characters?

Creating the Pipeline that will be responsible for processing and transforming the text file

Once again we need to create at least one custom pipeline (receive pipeline) to process this flat-file document and translate it into is equivalent in XML.

To create the Receive Pipeline that will be responsible for processing and transforming the text file, we need to go to the BizTalk solution created in Visual Studio and perform the following steps:

  • Press the right button on top of the project in Solution Explorer, and select the option “Add –> New Item…”.
  • On “Installed Templates” menu in the window “Add New Item”, select the option “Pipeline Files”, and then select the option “Receive Pipeline”, then provide the name you want to give the pipeline in this example: “FF_Employee_ReceivePipeline.btp”
  • By selecting the option “Add”, the pipeline editor (BizTalk Pipeline Designer) will appear and that will let you view and add components to all the steps associated with receive pipeline: Decode, Disassemble, Validate and ResolveParty.

In this case, the pipeline that we will create, will be responsible for receiving a text file through a receive location and convert it to XML. For that, we will use the “Flat File Disassembler” component which is available in the Visual Studio Toolbox window (Ctrl+W, X).

  • Drag it into the Pipeline in step “Disassemble”.

17-BizTalk-Flat-File-Pipeline-positional

  • Finally, select the component “Flat file disassembler”, go to its properties and on the “Document Schema” option: select the schema created earlier, in case the “PositionalTextFiles.FF_Positional_Employees”.

18-BizTalk-Flat-File-Pipeline-positional

Note: If you want to create a send pipeline in order to transform an XML document into Flat File, we would follow the same steps, the difference is that we would have to drag the component “Flat File Assembler” on stage “Assemble”.

Once you have deployed your solution, you will want to ensure that you select the custom pipeline we just created in the Receive Locations that you expect to receive these flat files.

Now here it comes the same questions: Do you think the schema is entirely correct? Does it handles with all files accordingly to the expected results?

Or do we need to tune some of the properties of your schema… stay tuned to the next post.

Related posts:

You can download all the code used here from:

BizTalk Server: Dealing with Positional Flat Files (14 KB)
Microsoft | Code Gallery

We ended last post asking: Do you think the schema is entirely correct? Does it handles with all files accordingly to the expected results? Or do we need to tune some of the properties of your schema…

When we start working with Flat File schemas for BizTalk, we may think that just using the Flat File wizard is going to be enough. Once the schema is created, we just need to create a custom pipeline and add the schema to the Document Schemas of a Flat File disassembler and it will be good… well let me tell you that most probably is not enough and the reality may be very different.

A common mistake that we sometimes do is to rely on a single sample message to generate/validate our Flat File Schemas. And we ended up getting possibly several different errors in production due to unforeseen cases that end up not being supported in our current schema version.

So it is real important to get all the information, as possible, from the different teams, for example:

  • Are there any optional elements?
  • If so, will they be delete from the message? Or will they be present without any content (blank/null)
  • Does the message will always end with a blank line? i.e., does the last line contain a Carriage Return/Line Feed (CR+LF)
  • What are the Data type of the elements?

And many, many more….

The following walkthrough shows you some of the common scenarios that you can expect and how you can tune some of the properties of our Flat File Schemas to deal with them.

Cosmetic operations

While generating the Flat File Schema with the Flat File Schema Wizard you should always provide/set:

  • The correct or proper namespace for the schema
  • Rename the parent node, each record, element or attribute
  • And set the correct data type of each element or attribute

However if you forgot to do that or if you make a mistake defining one of them, you can always easily rectify it in the Schema Editor.

I call it cosmetic operations because they don’t infer “any changes” in the translation rules generated by the wizard, they only changed the way the elements are presented in the message, if you change the name of the element or attributes. Or they will have effect in the validation of the message, if we change the data types.

You can accomplished that by:

  • Open the schema in BizTalk Editor.
  • Change the Schema Target Namespace
    • In the BizTalk Editor dialog, right-click the <Schema> node in the tree view and click Properties. The Properties dialog appears.
    • In the Properties dialog, the Target Namespace displays under the General category for the <Schema> node.
    • In the Target Namespace field, type the name of your new target namespace and click OK.
  • Change the Field Attribute or Field Element Name
    • Select the Field Attribute or Field Element node and then right-click and select the “Rename” option
    • Type the desired Field Attribute or Field Element name and press “Enter”
  • Change the Field Attribute or Field Element Data Type
    • Select the Field Attribute or Field Element node and then right-click and select the “Properties” option
    • In Properties window, set the “Data Type” property to the desired type

01-BizTalk-Flat-File-Schema-Cosmetic-Operations

Validating Flat Files Schemas through the Visual Studio with different scenarios

Let us forget the runtime for now, and focus firstly on the development phase where we can test and validate our schemas directly from Visual Studio as explained, once again, in the previous post.

Dealing with Files ending with a newline or that doesn’t ends with a newline

Initially we had the following example:

Sandro;Pereira;1978;Crestuma;4415{CR}{LF}
Vânia;Braziela;1981;Vila Nova de Gaia;4400{CR}{LF}
José;Silva;1972;Crestuma;4415{CR}{LF}
Rui;Barbosa;1975;Lever;4415{CR}{LF}

Which was the base to generate our Flat File schema: “FF_DSYMBOL_PEOPLE.xsd”.

If you paid attention, while we were generating our Flat File Schema, that in the “Select Document Data” window of the BizTalk Flat File Schema Wizard, you can notice that the last line contains a Carriage Return/Line Feed (CR+LF), which means that if you open the file for example in Notepad, you will notice that the file ends with a blank line.

01-BizTalk-Flat-File-Schema-Wizard-Select-Document-Data-Page-CR-LF

Note: You can check the file “FF_DSYMBOL_PEOPLE.txt” that is available with the source code.

So let us try with a simple change by removing the Carriage Return/Line Feed so that the files does not end with a newline:

Sandro;Pereira;1978;Crestuma;4415{CR}{LF}
Vânia;Braziela;1981;Vila Nova de Gaia;4400{CR}{LF}
José;Silva;1972;Crestuma;4415{CR}{LF}
Rui;Barbosa;1975;Lever;4415

Check the file: “FF_DSYMBOL_PEOPLE WithoutEnterLastLine.txt

If we now try this file instance against our Flat File Schema by:

  • In Solution Explorer, right-click the schema that you want to validate, and then click Properties.
  • In Properties window for the schema, for Input Instance Filename enter the name and path for the “FF_DSYMBOL_PEOPLE
    • Check more about Schema File Properties here.
  • WithoutEnterLastLine.txt” message instance, or browse to the file, select it, and then click OK.
  • Right-click the schema that you want to validate, and then click Validate Schema

You will notice that we will get the following error:

Invoking component…
<path>\DemoFile\FF_DSYMBOL_PEOPLE WithoutEnterLastLine.txt: error BEC2004: Unexpected data found while looking for:
‘\r\n’
The current definition being parsed is Person. The stream offset where the error occured is 133. The line number where the error occured is 4. The column where the error occured is 23.
<path>\FF_DSYMBOL_PEOPLE.xsd: error BEC2004: Validate Instance failed for schema FF_DSYMBOL_PEOPLE.xsd, file: <file:///path\FF_DSYMBOL_PEOPLE WithoutEnterLastLine.txt>.
Component invocation succeeded.

02-Visual-Studio-Flat-File-Instance-Validation-Failed

If, on the other hand, we had originally generated the Flat File Schema using the message instance without the Carriage Return/Line Feed at the end.

03-BizTalk-Flat-File-Schema-Wizard-Select-Document-Data-Page-option-3

And if we know try it with an instance containing a blank line at the end of the message we would receive a similar but different error:

Invoking component…
<path>\FF_DSYMBOL_PEOPLE.txt: error BEC2004: Unexpected end of stream while looking for:
‘;’
The current definition being parsed is People. The stream offset where the error occured is 139. The line number where the error occured is 5. The column where the error occured is 0.
<path>\FF_DSYMBOL_PEOPLE_Advance.xsd: error BEC2004: Validate Instance failed for schema FF_DSYMBOL_PEOPLE_Advance.xsd, file: <file:///path\FF_DSYMBOL_PEOPLE.txt>.
Component invocation succeeded.

04-Visual-Studio-Flat-File-Instance-Validation-Failed

I’ve seen situations/solutions where there were created pipeline components to address and solve these type of problems, which isn’t the best and the proper solution!

In both cases the solution for solving these problems is the same: ensure that the “Allow Message Breakup At Infix Root” property on the Schemas node is set to “true” by:

  • Double-click the schema to open the item in the Schema Editor;
  • In BizTalk Editor, right-click in the Schema node and select the Properties option
  • In Properties window for the schema, set the “Allow Message Breakup At Infix Root” property to “true”

05-Visual-Studio-Flat-File-Instance-Validation-Failed-solved

After making this change, if we try to validate the two previous messages instances, we will notice that now everything will work perfectly.

So if you notice in the properties of our Schema:

  • The “Child Order” property of the People Root Node is set as: “Postfix”
  • And the “Child Order” property of the “Person” record is set as: “Infix”

What does it means? Child Order must always have a value. The possible values are:

  • Prefix: Specifies that the delimiter appears before the data that it is delimiting.
  • Postfix: Specifies that the delimiter appears after the data that it is delimiting.
  • Infix: Specifies that the delimiter appears between different items of data, resulting in one less delimiter than when Prefix or Postfix is chosen.
  • Default Child Order: Sets the child_order attribute to “default”, specifying that the child order specified using the Default Child Order property should be used.
  • And Conditional Default: Removes the corresponding annotation in the XSD representation of the schema, if any, resulting in the following behavior:
    • If the Record node has a tag identifier, then the child order will be prefix.
    • Otherwise, the child order will be infix.

There are two scenarios related to delimited flat files for which special considerations apply when setting the Child Order property.

  • The first such scenario concerns situations in which the flat file document has a header, a body, and optionally, a trailer.
  • The second scenario is that this property must be set according to what the runtime components expect for the nodes

In our instance message, the data/delimiter sequence in the conceptual loop would merely be one or more person records. Of course, only in the case where there are more than one person records would there be a delimiter to separate them. In that case, the number of delimiters is one less than the sets of things being delimited, and the delimiters are located between the delimited items in a structure known as Infix.

  • Number delimiter = Number Records – 1

Which is not the case in all our messages instances. Our first instance have the same number of “valid records” and delimiters.

By setting “Allow Message Breakup At Infix Root” property to “true”, this tells the parser whether to allow message breakup, and will ensure that the Infix stream is processed properly by the FF Disassembler.

Dealing with Optional Elements

Using Suppress Empty Nodes property

So let’s imagine a different scenario, this time we will deal with optional elements. In our first scenario while discussing with our time we realize that the element “Birthyear” was appearing empty in several messages, and it should be considered as an optional element:

Sandro;Pereira;1978;Crestuma;4415
Vânia;Braziela;1981;Vila Nova de Gaia;4300
José;Silva;;Crestuma;4415
Rui;Barbosa;1975;Lever;4415

By default, we need to have a complete instance of the message, for us developers be able to create all the transformation rules, which means that by default, when we finish creating the Flat File Schema through Flat File Schema Wizard tool, none of the records, elements or attributes are optional.

All of these changes/optimizations/configurations, they have to be made after the Flat File Schema been created.

You can always modify an existing Flat File schema that was created by the wizard (name of the elements, data type and so on). So to accomplish that we need to:

  • Double-click the schema to open the item in the Schema Editor;
  • In BizTalk Editor, expand People root node, expand Person record and right-click in the Birthyear element and select the Properties option
  • In Properties window, set the “Min Occurs” property to “0”

If we now try to validate the previous message instance (“FF_DSYMBOL_PEOPLE WithOptional.txt”), we ended up receiving another error:

Invoking component…
Validation generated XML output <file:///<path>\FF_DSYMBOL_PEOPLE_Advance_output.xml>.
<path>\FF_DSYMBOL_PEOPLE_Advance_output.xml: error BEC2004: The ‘Birthyear’ element is invalid – The value ” is invalid according to its datatype ‘http://www.w3.org/2001/XMLSchema:int&#8217; – The string ” is not a valid Int32 value.
<path>\FF_DSYMBOL_PEOPLE_Advance.xsd: error BEC2004: Validate Instance failed for schema FF_DSYMBOL_PEOPLE_Advance.xsd, file: file:///path\FF_DSYMBOL_PEOPLE WithOptional.txt.
Component invocation succeeded.

06-Visual-Studio-Flat-File-Instance-Validation-Failed-Element-String-Not-valid

And of course the reason is obvious, an empty string is not a valid integer.

Making this element nillable doesn’t make any difference, because in both situations the parser will continue to add an empty element:

<Person xmlns="">
    <Name>José</Name>
    <Surname>Silva</Surname>
    <Birthyear></Birthyear>
    <Address>Crestuma</Address>
    <ZipCode>4415</ZipCode>
  </Person>

To solve that situation there are a few additional flat file node properties that we can use. You can see the full list here:

And in our case we are looking for the “Suppress Empty Nodes” property (or suppress_empty_nodes). By setting this property to “True” it will remove all the empty nodes from the XML stream.

  • This can be used to eliminate fields that are empty after being parsed, but the XSD type doesn’t allow empty values.

To accomplish that we need to:

  • In BizTalk Editor, right-click in the Schema node and select the Properties option
  • In Properties window for the schema, set the Suppress Empty Nodes property to “true”

07-Visual-Studio-Flat-File-Schema-Suppress-Empty-Nodes

After making this change, if we try to validate the previous message instance, we will notice that now everything will work perfectly.

Note: When using Delimited files, the optional element in the beginning or in the middle of the each line (record node) need always to be present in the text with empty values (;;) otherwise the parsing rules will fail or will perform an incorrect translation of the message.

If the “Birthyear” is set as string and we receive this type of messages

Sandro;Pereira;1978;Crestuma;4415
Vânia;Braziela;1981;Vila Nova de Gaia;4300
José;Silva;Crestuma;4415
Rui;Barbosa;1975;Lever;4415

The message will be translated incorrectly and there is nothing you can do about it, the end result would look like this:

  <Person xmlns="">
    <Name>José</Name>
    <Surname>Silva</Surname>
    <Birthyear>Crestuma</Birthyear>
    <Address>
      4415
      Rui
    </Address>
    <ZipCode>Barbosa;1975;Lever;4415</ZipCode>
  </Person>
Dealing with Optional Elements (last element with the data type string)

Continue in the same scenario/message type, after a further series of tests, we noticed another unforeseen situation. Once again several messages were being executed incorrected because some of the element were not being parsed the element “ZipCode” – note that this element is a string

Sandro;Pereira;1978;Crestuma;4415
Vânia;Braziela;1981;Vila Nova de Gaia;4300
José;Silva;1972;Crestuma
Rui;Barbosa;1975;Lever;4415

Note: Contrary to what happens with elements at the beginning and end of the message, if the last element is an optional element, then it is not required to appear in the text (see line 3 – it has one element less)

We don’t receive any error message in BizTalk Server runtime but we notice that we messages are being integrated with incorrect data. After further analysis, let’s take the sample above, we notice that the message are being translated incorrectly and the last node is being added to the “ZipCode” element and not in a different Person node as supposed

  <Person xmlns="">
    <Name>José</Name>
    <Surname>Silva</Surname>
    <Birthyear>1972</Birthyear>
    <Address>
      Crestuma
      Rui
    </Address>
    <ZipCode>Barbosa;1975;Lever;4415</ZipCode>
  </Person>

To solve this problem we once again need set the element, in this case “ZipCode” as optional:

  • Double-click the schema to open the item in the Schema Editor;
  • In BizTalk Editor, expand People root node, expand Person record and right-click in the ZipCode element and select the Properties option
  • In Properties window, set the “Min Occurs” property to “0”
<People xmlns="http://DelimitedBySymbols.FF_DSYMBOL_PEOPLE_Advance">
  <Person xmlns="">
    <Name>Sandro</Name>
    <Surname>Pereira</Surname>
    <Birthyear>1978</Birthyear>
    <Address>Crestuma</Address>
    <ZipCode>4415</ZipCode>
  </Person>
  <Person xmlns="">
    <Name>Vânia</Name>
    <Surname>Braziela</Surname>
    <Birthyear>1981</Birthyear>
    <Address>Vila Nova de Gaia</Address>
    <ZipCode>4300</ZipCode>
  </Person>
  <Person xmlns="">
    <Name>José</Name>
    <Surname>Silva</Surname>
    <Birthyear>1972</Birthyear>
    <Address>Crestuma</Address>
  </Person>
  <Person xmlns="">
    <Name>Rui</Name>
    <Surname>Barbosa</Surname>
    <Birthyear>1975</Birthyear>
    <Address>Lever</Address>
    <ZipCode>4415</ZipCode>
  </Person>
</People>

There is a Flat File Schema property call “allow_early_termination” (Early Terminate Optional Fields) that is a Boolean property. The use of this property is this situation is incorrect and does produce any changes in outcome. We will address and explain this property at another time.

Some references:

Related posts:

You can download all the code used here from:

BizTalk Server: Dealing with Files Delimited by Symbols (96 KB)
Microsoft | Code Gallery

This next posts will focus on optimizing some BizTalk Server 2013 R2 configurations. The following recommendations can be used to increase BizTalk Server performance or just to make the platform more resilient to failures. The optimizations listed in this topic are applied after BizTalk Server has been installed and configured.

Configure host and Host instances

One of the task that we need to do in all our new BizTalk environment over and over again is creating and configuring the Host, Host Instances and of course the adapter handlers.

BizTalk Server provides great flexibility for addressing high availability, because you can strategically dedicate logical hosts to run specific areas of functionality such as receiving messages, sending messages or processing orchestrations.
By default the BizTalk configuration will create two BizTalk Host and Host Instances:

  • BizTalkServerApplication: This is the default Host and Host Instance created during configuration that will do all the work on the BizTalk Server, i.e. is the default send and receive handler for all installed adapters (other than HTTP, WCF (BasicHttp, CustomIsolated, WebHttp and WSHttp) and SOAP Receive Handlers), and is also used for processing orchestration and tracking.

BizTalk-Server-2013-R2-BizTalkServerApplication-Host

  • BizTalkServerIsolatedHost: The logical container for HTTP, WCF (BasicHttp, CustomIsolated, WebHttp and WSHttp) and SOAP Receive Handlers.

BizTalk-Server-2013-R2-BizTalkServerIsolatedHost-Host

Although a single BizTalk Host can contain items that receive, send, and process messages, it is considered a best practice to create different hosts for each function to create security boundaries and for easier management and scalability. In particular, we recommend that you use different hosts for processing and for receive/send operations, and that you separate trusted and non-trusted items.

As the official documentation specify, in addition to the high availability aspects of the host instance configuration, you should separate sending, receiving, processing, and tracking functionality into multiple hosts. This provides flexibility when configuring the workload in your BizTalk group and is the primary means of distributing processing across a BizTalk group.

This also allows you to stop one host without affecting other hosts. For example, you may want to stop sending messages to let them queue up in the MessageBox database, while still allowing the inbound receiving of messages to occur.

Separating host instances by functionality also provides some of the following benefits:

  • Each host instance has its own set of resources such as memory, handles, and threads in the .NET thread pool.
  • Multiple BizTalk Hosts will also reduce contention on the MessageBox database host queue tables since each host is assigned its own work queue tables in the MessageBox database.
  • Throttling is implemented in BizTalk Server at the host level. This allows you to set different throttling characteristics for each host.
  • Security is implemented at the host level; each host runs under a discrete Windows identity.

However this also may bring some potential drawbacks if too many host instances are created because each host instance is a Windows service (BTSNTSvc.exe or BTSNTSvc64.exe), which generates additional load against the MessageBox database and consumes computer resources (such as CPU, memory, threads), so you need to be careful.

The following figure will provide you with a general overview of the relationship between servers, hosts and host instances, however is not intended to be the best practices. This architecture will depend from many factors and will change from client to client:

BizTalk-Server-2013-R2-Host-and-Host-Instances

Normally we read that we need to create at least 4 host instances:

  • Sending
  • Receiving
  • Processing
  • And tracking

but that’s not absolutely true because, at least since BizTalk Server is supported in 64 bits, we typically use 64-bits versions and in this case we also need to create at least one Host Instance that will run on 32-bits because FTP adapter, SQL adapter, POP3 adapter and MIME Decoder on 64-bit host instances is not supported by the product (http://technet.microsoft.com/en-us/library/aa560166.aspx)

We can define that one of the best practices for hosts and host instances is the following:

  • BizTalkServerTrackingHost: A BizTalk Host that hosts tracking is responsible for moving the DTA and BAM tracking data from the MessageBox database to the BizTalk Tracking (DTA) and BAM Primary Import databases.
  • BizTalkServerReceiveHost: This host will be responsible for processing messages after they are picked up in a receive location.
  • BizTalkServerReceive32Host: has the same goal as the previous however this must have the “32-bits only” option select so that we can run the 23-bits adapters.
  • BizTalkServerSendHost: This host will be responsible for processing messages before they are sent out to the send port.
  • BizTalkServerSend32Host: has the same goal as the previous however this must have the “32-bits only” option select so that we can run the 32-bits adapters.
  • BizTalkServerApplication: This host will be responsible for process messages based on the instructions in orchestrations that need to run in 32-bits.
  • BizTalkServerApplication64Host:This host will be responsible for process messages based on the instructions in all or most common orchestrations.

Note: You can create other Application Host if you want to separate process base in some application logic.

You can read more about this topic in more details in my previous post: PowerShell to Configure BizTalk Server Host and Host Instances according to some of the Best Practices or in TechNet Wiki: BizTalk Server Best Practices: Create and Configure BizTalk Server Host and Host Instances.

You can find a simple script to configure Host, Host Instance and Adapter Handlers described earlier in this post optimized for BizTalk Server 2013 R2 (and also 2013) in TechNet Gallery:

Power Mode

The different performance states are dynamically managed by Windows in conjunction with hardware and platform firmware to respond to varying workload requirements. The 3 default power plans exposed by Windows provide varying tradeoffs of performance vs. power consumption. For example, if the High Performance power plan is selected, Windows places the system in the highest performance state and disables the dynamic scaling of performance in response to varying workload levels. Therefore, special care should be taken before setting the power plan to High Performance as this can increase power consumption unnecessarily when the system is underutilized.

In some cases you may experience degraded overall performance on a machine when running with the default (Balanced) power plan. The issue may occur irrespective of platform and may be exhibited on both native and virtual environments. The degraded performance may increase the average response time for some tasks and cause performance issues with CPU-intensive applications

To change a power plan:

  • Press the “Windows key” to switch to the Start screen and type “Power Options” and click in “Power Options” option from the Search menu.

Windows-Server-2012-R2-Power-Options-Menu

  • From the power plan page, choose the High Performance option

Windows-Server-2012-R2-Power-Options-High-Performance

  • Close the Power Option window.

 

Consider setting the ‘text in row’ table option to boost BizTalk Server Performance

SQL Server provides a table option called text in row to declare that the contents of the fields of type text, ntext, or image data whose dimensions are smaller than those of a data page (8Kb) must be stored in a data row. By setting this option on BizTalkMsgBoxDb tables (Parts table, Spool table and DynamicStateInfo Tables), you can increase message throughput when working with small messages which have a small context and orchestrations that have a small persistence size. This makes reading and writing the in-row strings about as fast as reading or writing limited size varchar, nvarchar, or varbinary strings. Similarly, when the values are stored off-row, the Database Engine incurs an additional page read or write.

How to exploit the Text in Row table option in BizTalk Server

The following section explains how and when applying the text in row table option to boost BizTalk performance.

  • Parts Table: When the message size is smaller than the dimensions of a data page that are of 8kb, applying the text in row table option on the Parts table can lead to BizTalk Server performance improvement.
  • Spool Table: When the average size of the message context is less than 8 kb, enabling the text in row table option on the Spool table helps you reduce the number of accesses when reading messages from the MessageBox along with their context. To apply this option to the Spool table, you must eliminate unnecessary context properties and distinguished fields to reduce the size of the message context lower than 8 Kb.
  • DynamicStateInfo Tables: These tables, one for each host, contain a field of type image called imgData that contains binary-serialized orchestration state when they encounter a persistence point during their execution. When the internal state of orchestrations within a host HostA is so small that its size once serialized is less than 8 kb, the text in row technique can successfully be applied to the DynamicStateInfo_HostA table. Therefore we recommend that you keep the internal state of orchestrations as small as possible. This technique can significantly reduce the time that is spent by the XLANG Engine to serialize, persist, de-serialize and restore the internal state of an orchestration in case of persistence point.

See more about this topic in the following resources:

You can use the following settings sample that in your environment:

  • EXEC sp_tableoption N’Spool’, ‘text in row’, ‘6000’
  • EXEC sp_tableoption N’Parts’, ‘text in row’, ‘6000’
Related links

This next posts will focus on optimizing some BizTalk Server 2013 R2 configurations. The following recommendations can be used to increase BizTalk Server performance or just to make the platform more resilient to failures. The optimizations listed in this topic are applied after BizTalk Server has been installed and configured.

Install SSO Configuration Application MMC Snap-In

BizTalk Server leverages the Enterprise Single Sign-On (SSO) capabilities for securely storing critical information such as secure configuration properties (for example, the proxy user ID, and proxy password) for the BizTalk adapters. Therefore, BizTalk Server requires SSO to work properly. BizTalk Server automatically installs SSO on every computer where you install the BizTalk Server runtime.

But it also can keep your own application configuration data in SSO database, let say the usual configurations that we normally keep in a configuration file (“app.config”)). One of the great and useful tool that we normally use for archiving this is a custom tool original created by Richard Seroter, the: SSO Config Data Store Tool.

However since 2009 that Microsoft released a MMC snap-in to tackle this exact issue: SSO Configuration Application MMC Snap-In provides the ability to add and manage applications, add and manage key value pairs in the SSO database, as well as import and export configuration applications so that they can be deployed to different environment.

To install SSO Configuration Application MMC Snap-In you need to:

  • Unzip the SSOConfigurationMMCSnapIn.zip file available in Microsoft page.
  • This file is composed by three zip files, so after unzip the first file you should unzip also the SSOMMCSnapInSetup.zip file.
  • Run as Administrator the setup.exe or SSOMMCSnapInSetup.msi file
  • In the Welcome to the SSO Application Configuration Setup Wizard page, click “Next”

155-BizTalk-Server-2013-R2-SSO-App-Snapin

  • In the Select Installation Folder page:
    • Accept the default installation folder or set another one
    • In the “Install SSO Application for yourself, or for anyone who uses this computer” option, select “Everyone”
    • And click “Next”

156-BizTalk-Server-2013-R2-SSO-App-Snapin

  • In the Enter Company Name page, enter the name of your company and click “Next”

157-BizTalk-Server-2013-R2-SSO-App-Snapin

  • In the Confirm Installation page, click “Next” to install the SSO Application Configuration MMC Snap-In

158-BizTalk-Server-2013-R2-SSO-App-Snapin

  • In the installation Complete page, click “Close”

159-BizTalk-Server-2013-R2-SSO-App-Snapin

However unfortunately this tool will not work properly in BizTalk Server 2013 R2, at least running in Windows Server 2012 R2. At the first sight it seems that everything is working properly but when you try to create a key value pair you will see that nothing happens and no key is created:

160-BizTalk-Server-2013-R2-SSO-App-Snapin

To fix this issue I recompile the SSOMMCSnapIn.dll using the latest version of “Microsoft.EnterpriseSingleSignOn.Interop.dll” available with BizTalk Server 2013 R2 that you can download at Microsoft TechNet Gallery: BizTalk Server 2013 R2: Fix for SSO Configuration Application MMC Snap-In

Update: Notice that this issue it’s also documented here https://support.microsoft.com/kb/2954101 (Known issues in BizTalk Server 2013) and you will find different solutions to solve it.

To solve the issue you just need to overlap the existing SSOMMCSnapIn.dll file, normally present in “C:\Program Files\Common Files\Enterprise Single Sign-On” folder with this version of the file. Now if you try to add a new key value pair everything will work fine.

161-BizTalk-Server-2013-R2-SSO-App-Snapin

Configure BizTalk Jobs History (Microsoft SQL Server Agent job history log)

Have you ever notice that you never have present a log history of the Monitor BizTalk Server job?

170-BizTalk-Server-2013-R2-BizTalk-Jobs-History

The reason why this happens is that BizTalk Server is shipped out with a total of 13 SQL Agent jobs, most of them running each minute, the only exception is the Monitor BizTalk Server job that by default occurs every week on Sunday at 00:00:00.

And to be completely honest there are two problems present:

  • We rarely inspect our environment in the weekend, only in critical situations or customers that have a 25x 7 administration team.
  • By default the Microsoft SQL Server Agent job history log is configure to
    • “Limit size of job history log” with a maximum of 1000 rows
    • And “Maximum job history row per job” with 100 rows

168-BizTalk-Server-2013-R2-BizTalk-Jobs-History

Which means that a few minutes after the Monitor BizTalk Server job run successfully or unsuccessfully, it’s historic is overwritten with the row log historical of the remaining 12 jobs. Therefore never expected to have this historic job on Monday when you arrive to the office.

Because BizTalk Server has 13 Jobs to keep a decent and consistent job history log you should increase the “Limit size of job history log” according to the number of existing jobs in your environment, for example at least 1300 for BizTalk Server environment (100 for each job)

To resize the job history log based on raw size

  • In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  • Right-click SQL Server Agent, and then click Properties.
  • Select the History page, and then confirm that Limit size of job history logis checked.
  • In the Maximum job history log size box, enter the maximum number of rows the job history log should allow.
  • In the Maximum job history rows per job box, enter the maximum number of job history rows to allow for a job.

169-BizTalk-Server-2013-R2-BizTalk-Jobs-History

To resize the job history log based on time

  • In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  • Right-click SQL Server Agent, and then click Properties.
  • Select the History page, and then click Automatically remove agent history.
  • Select the appropriate number of Days(s), Week(s), or Month(s).

Force Full Backup BizTalk Server (BizTalkMgmtDb) job

I will not enter in much details in this steep, you can read more about it in my blog at: A different way to Force a Full Backup of the 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. 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.

Alternative, to help one of my DBA teams you may create a custom Force Full Backup BizTalk Server 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

1723-BizTalk-Server-2013-R2-Force-Full-Backup-BizTalk-Server-job

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

Related links

This next posts will focus on optimizing some BizTalk Server 2013 R2 configurations. The following recommendations can be used to increase BizTalk Server performance or just to make the platform more resilient to failures. The optimizations listed in this topic are applied after BizTalk Server has been installed and configured.

Deleting BizTalk backup files

BizTalk Server database 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. By default, the following BizTalk jobs aren’t configured and enabled upon installation and the two jobs that needs configuration are the two most important jobs: the “Backup BizTalk Server” and the “DTA Purge and Archive“. This is nothing new and everybody knows!

However what many times we forget is that this two jobs, by default, don’t provide functionalities for deleting backup files that have accumulated over time on our file system and we normally forget to create a “process” or a “job” to accomplish this until is too late. The result of that is… lots of times we just remember when the disks are full and everything stop to work!

You can solve this problem by implementing you custom sp_DeleteBackupHistory or by creating a Maintenance Plan to delete BizTalk Database Backups files.

Implementing a custom sp_DeleteBackupHistory

To implement a custom sp_DeleteBackupHistory you need to:

· Start SQL Server Management Studio, and then connect to the BizTalk Management Database. By default, this database is named BizTalkMgmtDb.

· Click New Query to open a Query Editor window.

· Run the following Transact-SQL script to create the sp_DeleteBackupHistoryAndFiles stored procedure:

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
AS
BEGIN
   set nocount on
   IF @DaysToKeep IS NULL OR @DaysToKeep <= 1
      RETURN
   /* Only delete full sets
   If a set spans a day in such a way that some items fall into the deleted group and the other does not, 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

· Change the “Clear Backup History” step of the Backup BizTalk Server job so that it calls the sp_DeleteBackupHistoryAndFiles stored procedure instead of the sp_DeleteBackupHistory stored procedure.

However I personally don’t like this approach for two main reasons:

· I avoid to change the scripts of BizTalk standard jobs

· And I also believe that this approach is very limited and doesn’t allow the flexibility that we all want.

Instead I really prefer to take advantage of all features that SQL gives me and create a maintenance plan with a Cleanup task to remove this files.

Implementing a Maintenance Plan to clean BizTalk Database backup’s files

Maintenance Plans allows DBA’s to have flexibility to create a workflow to execute several tasks required to make sure that database are optimized, regularly backed up, and free of inconsistencies. However almost all of these tasks are warranted by the existing BizTalk jobs and I will not use them. The only thing we need is to create a task to clean BizTalk Database backup’s files from our file system.

The main advantage of this approach is that will allow us more flexibility for further changes and we can also use them to other backup’s that we have.

Note: You should ensure that SQL Server Agent service is running because the maintenance plans depend on the Microsoft SQL Server Agent in order to run on a regular basis.

Important Note: this example is created to run in a developer machine, so if you are implementing this approach in production environments make sure to keep the backups for the time you see that fit your requirements or make sure the backups are save in an external storage before you delete this files from the file system.

To create the maintenance check the following post: How to create a Maintenance Plan to delete BizTalk Database Backups files

Pre-allocate space and define auto-growth settings for BizTalk Server databases

By default BizTalk Server databases are defined with small files size and with the parameter “Autogrowth” set to:

  • Increase by 1 MB for database file
  • And by 10% for log file

So what’s the problem with these settings?

Auto growth setting plays an important role in BizTalk configuration for performance reasons, why?

SQL Server database auto-growth is a blocking operation which hinders BizTalk Server database performance. When SQL Server increases the size of a file, it must first initialize the new space before it can be used. This is a blocking operation that involves filling the new space with empty pages.

Therefore it’s recommended to:

  • Set this value (databases auto-growth) to a fixed value of megabytes instead of to a percentage, so SQL server doesn’t waste is resources expanding the data and log files during heavy processing. This is especially true for the MessageBox and Tracking (DTA) databases:
    • In a high throughput BizTalk Server environment, the MessageBox and Tracking databases can significantly increase. If auto-growth is set to a percentage, then auto-growth will be substantial as well.
    • As a guideline for auto-growth, for large files increment should be no larger than 100 MB, for medium-sized files 10 MB, or for small files 1 MB.
    • This should be done so that, if auto-growth occurs, it does so in a measured fashion. This reduces the likelihood of excessive database growth.
  • Also allocate sufficient space for the BizTalk Server databases in advance to minimize the occurrence of database auto-growth.

To define auto-growth and Pre-allocate space for BizTalk Server databases check the following post: Pre-allocate space and define auto-growth settings for BizTalk Server databases

Configure BizTalk Server Windows Services

By default, the “Startup type” propriety of BizTalk Windows Services, Enterprise Single Sign-On Service, Microsoft UDDI Notification Service and the BAMAlerts service are set as “Automatic”, however some of this Windows Services may not start automatically after a system restart, to avoid this behavior you must configure the “Startup type” to “Automatic (Delayed Start)” option in this services:

  • BAMAlerts
  • Microsoft UDDI Notification Service
  • Rule Engine Update Service

The previous version of BizTalk Server also had this problem for:

  • Enterprise Single Sign-On Service
  • BizTalk Service BizTalk Group : BizTalkServerApplication Service

153-BizTalk-Server-2013-R2-Services-automatic

Despite being configured as “Automatic“, the Recovery properties are configured properly to restart the service after failures.

154-BizTalk-Server-2013-R2-Services

To proper configure the BizTalk Services, “Enterprise Single Sign-On Service”, “BAMAlerts” and “Microsoft UDDI Notification Service” Windows service:

  • Press the “Windows key” to switch to the Start screen, type “Services” and click in “View local services” option rom the Search menu.

153-BizTalk-Server-2013-R2-Services-search-menu

  • In the Services window, on the Services (Local) panel select the service for example “BAMAlerts”, right click and select “Properties” option.

153-BizTalk-Server-2013-R2-Services-BamAlerts

  • On the BAMAlerts Properties (Local Computer) window:
    • On the General tab apply the following configuration.
      • Startup type: “Automatic (Delayed Start)”

153-BizTalk-Server-2013-R2-Services-Bam-Alerts-automatic-delay

    • On the Recovery tab, apply the following configuration:
      • First failure: “Restart the Service”
      • Second failure: “Restart the Service”
      • Subsequent failures: “Restart the Service”
      • Reset fail count after … day: 1
      • Restart service after … minutes: 1

153-BizTalk-Server-2013-R2-Services-Bam-Alerts-recovery

    • Click “OK” to apply the changes and close the window
  • Repeat the steps for the other services.

You could and should apply the same Recovery properties configurations for the SQL Server services:

  • SQL Full-text Filter Daemon Launcher
  • SQL Server
  • SQL Server Agent
  • SQL Server Analysis Services
  • SQL Server Browser
  • SQL Server Integration Services 12.0
  • SQL Server Reporting Services
  • SQL Server VSS Writer

By default they are configure as “Take No Action”

171-BizTalk-Server-2013-R2-SQL-Services

And should be configured as:

  • Startup type: “Automatic”
  • and First, Second and Subsequent failures: “Restart the Service”

172-BizTalk-Server-2013-R2-SQL-Services

Install and configure BizTalk Health Monitor V2

BizTalk Health Monitor is a snap-in, basically it’s the same of BizTalk MsgBoxViewer tool that we used monitor a BizTalk environment by gathering all information of a BizTalk group and detecting any issues, non-critical or critical warnings to detect any potential problems in advance, but in this case is integrated more closely with the BizTalk Administration Console to provide BizTalk administrators a quick and complete dashboard of a BizTalk group which will help them monitor the health of their BizTalk platform.

You can download the standalone version of BHM from Microsoft Download Center here: BizTalk Health Monitor

Prerequisites:

  • BizTalk Server 2010, 2013 or 2013 R2 should be installed and configured.

To install and configure BizTalk Health Monitor V2 check the following post: Installing the new BizTalk Health Monitor snap-in on Biztalk Server 2010 or BizTalk Server 2013

Related links

Today I want to talk to you about two small optimizations often forgotten but with performance implications. Some of these implications may be noticed more in the early days of our BizTalk environment when the databases are small or almost empty and often need to increase, others can have impact later when databases are large and the percentage increase is also too large.

By default BizTalk Server databases are defined with small files size and with the parameter “Autogrowth” set to:

  • Increase by 1 MB for database file
  • And by 10% for log file

Autogrowth

There’s a great post from Jeroen Hendriks: Checking database autogrowth settings where it is explained how we can check all BizTalk Server database settings using an SQL query and also Database file size – data collection so that we can recommend the right values.

Autogrowth-BizTalkMgmtDb
Autogrowth-BizTalkMsgBoxDb

So what’s the problem with these settings?

Auto growth setting plays an important role in BizTalk configuration for performance reasons, why?

SQL Server database auto-growth is a blocking operation which hinders BizTalk Server database performance. When SQL Server increases the size of a file, it must first initialize the new space before it can be used. This is a blocking operation that involves filling the new space with empty pages.

Therefore it’s recommended to:

  1. Set this value (databases auto-growth) to a fixed value of megabytes instead of to a percentage, so SQL server doesn’t waste is resources expanding the data and log files during heavy processing. This is especially true for the MessageBox and Tracking (DTA) databases:
    1. In a high throughput BizTalk Server environment, the MessageBox and Tracking databases can significantly increase. If auto-growth is set to a percentage, then auto-growth will be substantial as well.
    2. As a guideline for auto-growth, for large files increment should be no larger than 100 MB, for medium-sized files 10 MB, or for small files 1 MB.
    3. This should be done so that, if auto-growth occurs, it does so in a measured fashion. This reduces the likelihood of excessive database growth.
  2. Also allocate sufficient space for the BizTalk Server databases in advance to minimize the occurrence of database auto-growth.

How can I implement these optimizations?

You can do these optimizations by two ways:

  • Manually, by opening “Microsoft SQL Server Management Studio”
    • Maximize you SQL Server and then Databases
    • Right-click in the database, for example “BizTalkMsgBoxDb” and select “Properties” option
    • On Database Properties window, select Files page option and then you can check and change Autogrowth property associated to database and log file.

Database-properties

However checking and changing all BizTalk databases manually is too much work and can be a little boring …

  • Or you can do this by running the following SQL Script:
ALTER DATABASE BizTalkDTADb MODIFY FILE (NAME = BizTalkDTADb , SIZE = 2GB , FILEGROWTH = 100MB)
GO
ALTER DATABASE BizTalkDTADb MODIFY FILE (NAME = BizTalkDTADb_log , SIZE =  1GB , FILEGROWTH = 100MB)
GO

ALTER DATABASE BizTalkMgmtdb MODIFY FILE (NAME = BizTalkMgmtDb , SIZE = 512MB , FILEGROWTH = 100MB)
GO
ALTER DATABASE BizTalkMgmtdb MODIFY FILE (NAME = BizTalkMgmtDb_log , SIZE =  512MB , FILEGROWTH = 100MB)
GO

ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb , SIZE = 2GB , FILEGROWTH = 100MB)
GO
ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb_log , SIZE =  2GB , FILEGROWTH = 100MB)
GO

ALTER DATABASE SSODB MODIFY FILE (NAME = SSODB , SIZE = 512MB , FILEGROWTH = 100MB)
GO
ALTER DATABASE SSODB MODIFY FILE (NAME = SSODB_log , SIZE =  512MB , FILEGROWTH = 100MB)
GO

ALTER DATABASE BAMPrimaryImport MODIFY FILE (NAME = BAMPrimaryImport , SIZE = 150MB , FILEGROWTH = 10MB)
GO
ALTER DATABASE BAMPrimaryImport MODIFY FILE (NAME = BAMPrimaryImport_log , SIZE =  150MB , FILEGROWTH = 10MB)
GO

ALTER DATABASE BAMArchive MODIFY FILE (NAME = BAMArchive , SIZE = 70MB , FILEGROWTH = 10MB)
GO
ALTER DATABASE BAMArchive MODIFY FILE (NAME = BAMArchive_log , SIZE =  200MB , FILEGROWTH = 10MB)
GO

ALTER DATABASE BizTalkRuleEngineDb MODIFY FILE ( NAME = BizTalkRuleEngineDb , FILEGROWTH = 1024KB )
GO
ALTER DATABASE BizTalkRuleEngineDb MODIFY FILE ( NAME = BizTalkRuleEngineDb_log , FILEGROWTH = 1024KB )
GO

The execution of this SQL script will set automatically the values for all BizTalk Server databases according to what is recommended. Not only the auto-growth property but also the database and log file size:

  • BizTalkDTADb (BizTalk Tracking database): Data file having a file size of 2 GB with 100 MB growth and a log file of 1 GB with 100 MB growth.
  • BizTalkMgmtdb (BizTalk Management database): Data file having a file size of 512 MB with 100 MB growth and a log file of 512 MB with 100 MB growth.
  • SSODB (SSO database): Data file having a file size of 512 MB with 100 MB growth and a log file of 512 MB with 100 MB growth.
  • BizTalkMsgBoxDb (BizTalk MessageBox database): Data file having a file size of 2 GB with 100 MB growth and a log file of 2 GB with 100 MB growth.
  • BAMPrimaryImport (BAM Primary Import database): Data file having a file size of 150 MB with 10 MB growth and a log file of 150 MB with 100 MB growth.
  • BAMArchive (BAM Archive): Data file having a file size of 70 MB with 10 MB growth and a log file of 200 MB with 10 MB growth.
  • BizTalkRuleEngineDb (Rule Engine database): Data file with 1 MB growth and a log file with 1 MB growth.

Note: These values were used for a standalone environment. In a high throughput BizTalk Server environment you should consider devide the BizTalkMsgBoxDb in 8 data files, each having a file size of 2 GB with 100 MB growth and a log file of 20 GB with 100 MB growth. Because the BizTalk MessageBox databases are the most active, we recommend you place the data files and transaction log files on dedicated drives to reduce the likelihood of problems with disk I/O contention, as is explained here: Post-Configuration Database Optimizations

EXEC dbo.sp_helpdb BizTalkMsgBoxDb
ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb , FILENAME = 'J:\BizTalkMsgBoxDb.mdf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_2 , FILENAME = 'J:\BizTalkMsgBoxDb_2.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_3 , FILENAME = 'J:\BizTalkMsgBoxDb_3.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_4 , FILENAME = 'J:\BizTalkMsgBoxDb_4.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_5 , FILENAME = 'J:\BizTalkMsgBoxDb_5.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_6 , FILENAME = 'J:\BizTalkMsgBoxDb_6.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_7 , FILENAME = 'J:\BizTalkMsgBoxDb_7.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_8 , FILENAME = 'J:\BizTalkMsgBoxDb_8.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
GO
ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb_log , FILENAME = 'K:\BizTalkMsgBoxDb_log.LDF', SIZE =  20GB , FILEGROWTH = 100MB)
GO

The script can be found and download on Microsoft TechNet Gallery:
Pre-allocate space and define auto-growth settings for BizTalk Server databases (1.5 KB)
Microsoft TechNet Gallery