BizTalk Server 2020 – 20 days, 20 posts: XML Namespace Remover Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts: XML Namespace Remover Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts – day 13. Once again, here is another pipeline component migrate to BizTalk Server 2020 that makes part of my BizTalk Pipeline Components Extensions UtilityPack project: XML Namespace Remover Pipeline Component. This component was initially developed by Johan Hedberg

XML Namespace Remover Pipeline Component

The XML Namespace Remover Pipeline Component is a pipeline component for BizTalk Server which can be used in any stage of both receive and send pipelines to remove XML namespaces from XML documents.

This component has the availability to transform the root of the message from this:

<ns0:Blah xmlns:ns0="http://RemoveXmlNamespace.BTS.BlahMessage">

Into this:

<Blah>

If you have the option, you shouldn’t remove the namespace from the messages. However, we don’t leave in a perfect world and we need to deal with legacy systems and sometimes they required, for unknown reasons for me, that we deliver messages without namespace.

To use this pipeline component in your projects you just copy the RemoveXmlNamespace.PipelineComponents.dll file into the Pipeline Components folder that exists in the BizTalk Server installation directory:

  • ..Program Files (x86)Microsoft BizTalk ServerPipeline Components;

on every server.

You do not need to add this custom pipeline component to be used by the BizTalk Runtime to the Global Assembly Cache (GAC).

What is BizTalk Pipeline Components Extensions Utility Pack?

BizTalk Pipeline Components Extensions Utility Pack is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in received and sent pipelines, which will provide an extension of BizTalk out-of-the-box pipeline capabilities.

The project is available on the BizTalk Server Open Source Community repository on GitHub (https://github.com/BizTalkCommunity), and everyone can contribute with new pipeline components that can be extended or improve the existing BizTalk Server capabilities.

BizTalk Pipeline Components Extensions Utility Pack: XML Namespace Remover Pipeline for BizTalk Server 2020

At the moment this project is available for:

  • BizTalk Server 2020;
  • BizTalk Server 2016;
  • BizTalk Server 2010;
  • BizTalk Server 2006-2009

Where to download it?

You can download BizTalk Pipeline Components Extensions Utility Pack from GitHub here:

BizTalk Pipeline Components Extensions Utility Pack

The post BizTalk Server 2020 – 20 days, 20 posts: XML Namespace Remover Pipeline Component for BizTalk Server 2020 appeared first on SANDRO PEREIRA BIZTALK BLOG.

INTEGRATE 2020 Remote (our first virtual conference)

INTEGRATE 2020 Remote (our first virtual conference)

I hope you are all keeping safe in these unprecedented times. That’s the number one priority for all of us to protect ourselves and close family, friends, and employees. 

None of us anticipated the changes that we are going through at the moment with COVID-19. Every business is forced to rethink their strategies and adapt to conditions no one has experienced before. 

For the past few weeks, we were closely monitoring the situation and thinking deeply about the INTEGRATE 2020 plans. Until the mid of March, our response was, “things will be normal by June”, “90 days is a long time for this to last” but as the days passed by we soon started to realize the reality watching the daily increases in positive cases and deaths across the world. 

The situation is not going to get any better for another few months, even if it all gets resolved we are going to see a huge difference in the way people are going to interact. Maybe giving handshakes might forever be replaced by India’s way of greeting “Namaste” without physical contact. People may be hesitant to get into crowded spaces like conferences. Social distancing might become the norm of day to day life. We need to wait and see. 

Considering all the factors, we made a big decision to take INTEGRATE 2020 into a virtual conference with a new branding called “INTEGRATE 2020 REMOTE” The same INTEGRATE we all loved but going virtual/remote this year.

INTEGRATE 2020 REMOTE

This is one of the world’s first virtual Remote conference focused on Microsoft Integration Technologies. The event will be held between 1st-3rd June as we originally planned. The complete speaker’s list and agenda are now updated on our brand new website INTEGRATE 2020 REMOTE.

INTEGRATE 2020 REMOTE

Speakers: 30+ (Microsoft Product Teams, Microsoft MVP’s)

Sessions: 29 (pure Microsoft Integration tech)

Timing: 12pm -7pm (UK Time)

Delivery: via Special virtual web conferencing platform

Registration: Register Now

Why I should attend INTEGRATE 2020 Remote?

When we started the idea of the virtual conference, we started the discussion with people to brainstorm ideas including the speakers from Microsoft and MVP’s. One of the questions that came up is, how can we differentiate INTEGRATE compared to just watching videos from other Microsoft Conferences like Ignite and Build.

The principle of INTEGRATE still remains the same. A conference focused purely on Microsoft Integration Technologies. There may be odd sessions here and there on Microsoft Conferences, I can guarantee, you are never going to see a single session on BizTalk Server. 

However, with INTEGRATE, we are consolidating all Microsoft Integration focused content in a single place covering on-premise (BizTalk Server), cloud (Azure Logic Apps, Functions, API Management, Service Bus, Event Grid, Event Hub, Power Platform), and Hybrid in an intense 3 days conference, with its own keynote. 

If you are a Microsoft Integration professional, even if you attend part of the conference here and there, you’ll still see significant value educating and preparing yourself for the future.  Please go ahead and register now

Timing

12pm – 7pm UK Time

Running a global conference brings its own challenges. One of the main ones is picking up a convenient time that’s suitable for the global audience (Do you know? there are 24 timezones in the world). Of course, it will be very difficult to cater to every timezone, so we picked up a time slot that’s convenient to the majority of Europe (since INTEGRATE London is targeted mainly for European audience), the major portion of the USA, Middle East, and Asia. It’s going to be a challenge for people from Australia, New Zealand (that part of the world), but they can catch up with live recordings.  

Pricing

We significantly reduced the pricing of INTEGRATE 2020 REMOTE, we wanted as many people from the world to join and benefit from the conference. We kept the pricing really affordable to everyone. We are assuming at least 2-3 people will join from any company, bringing the price to $100/attendee (for the full 3 days conference), if you are above 6 people it will further reduce to about $80/attendees. 

Why are we charging? In order to run a conference of this magnitude in a professional way requires a lot of effort (even for a virtual conference). Right from the time investment in organizing and executing the event, marketing, and promotional activities to make the event successful, and running a conference with 1000+ attendees requires some investment in the technology.

We are not intending to make a profit out of the event, hence the price is kept at a bare minimum. Register Now

Will the videos be made public?

Eventually, we will make all the video content of INTEGRATE 2020 Remote available to everyone, similar to how we have done previous years (2016, 2017, 2018, 2019). However, we will not release the content immediately, it will be at least a few months to respect the people who have paid for the conference to support us. 

So if you wanted to hear the latest and great news, please register for the INTEGRATE 2020 Remote event (it’s affordable). 

What will happen to the existing INTEGRATE 2020 (London) attendees?

We are going to send out a separate email communication to all the current INTEGRATE 2020 (London) attendees. Basically, you’ll get a 100% refund of the ticket price you have paid. You simply buy the new INTEGRATE 2020 Remote tickets based on your requirements.  

Sponsors

We would like to thank the majority of our Sponsors who put their hands together to help us during this difficult situation. We are incurring a heavy financial loss by canceling the in-person event (ex: Venue agreed only for a partial refund).

They simply agreed to move their sponsorship package to the REMOTE event. We greatly appreciate their help, we are trying to compensate for their loss by increasing their brand visibility with greater attendees numbers. We also added a few more choices for the sponsors to match the virtual event.

If you are interested to become a sponsor, please email event@biztalk360.com requesting sponsorship opportunities. 

The post INTEGRATE 2020 Remote (our first virtual conference) appeared first on BizTalk360.

BizTalk Server 2020 – 20 days, 20 posts: Multi-Part Message Attachments Zipper Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts: Multi-Part Message Attachments Zipper Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts – day 12. Once again, following my recent blog posts here is another pipeline component migrate to BizTalk Server 2020 that makes part of my BizTalk Pipeline Components Extensions UtilityPack project: Multi-Part Message Attachments Zipper Pipeline Component.

Multi-Part Message Attachments Zipper Pipeline Component

The Multi-Part Message Attachments Zipper is a pipeline component for BizTalk Server which can be used in a send pipeline and is intended to replace all attachments of a multi-part message for its zipped equivalent.

The capabilities are similar to those available in compression software such as WinZip or 7-zip:

  • Attachments Compression – Extracts, in a send pipeline, all message parts include in a multi-part message that is not included in the message body (Message Body Part = False), compresses it and attaches the compressed attachment back to the message.

No compression/decompression software needs to be installed in the BizTalk Server machines.

Multi-Part Message Attachments Zipper Pipeline Component
  • This component requires one configuration that is the FileExtension where you can specify if you want for example a .zip or .gz file.

To use this pipeline component in your projects you just copy the PipelineComponentMultipartMsgZipAttach.dll file into the Pipeline Components folder that exists in the BizTalk Server installation directory:

  • ..Program Files (x86)Microsoft BizTalk ServerPipeline Components;

on every server.

You do not need to add a custom pipeline component to be used by the BizTalk Runtime to the Global Assembly Cache (GAC).

What is BizTalk Pipeline Components Extensions Utility Pack?

BizTalk Pipeline Components Extensions Utility Pack is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in received and sent pipelines, which will provide an extension of BizTalk out-of-the-box pipeline capabilities.

The project is available on the BizTalk Server Open Source Community repository on GitHub (https://github.com/BizTalkCommunity), and everyone can contribute with new pipeline components that can be extended or improve the existing BizTalk Server capabilities.

BizTalk Pipeline Components Extensions Utility Pack: Multi-Part Message Attachments Zipper Pipeline Component for BizTalk Server 2020

At the moment this project is available for:

  • BizTalk Server 2020;
  • BizTalk Server 2016;
  • BizTalk Server 2010;
  • BizTalk Server 2006-2009

Where to download it?

You can download BizTalk Pipeline Components Extensions Utility Pack from GitHub here:

BizTalk Pipeline Components Extensions Utility Pack

The post BizTalk Server 2020 – 20 days, 20 posts: Multi-Part Message Attachments Zipper Pipeline Component for BizTalk Server 2020 appeared first on SANDRO PEREIRA BIZTALK BLOG.

BizTalk Server 2020 – 20 days, 20 posts: Zip File Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts: Zip File Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts – day 11. Following my recent blog posts here is another pipeline component migrate to BizTalk Server 2020 that makes part of my BizTalk Pipeline Components Extensions UtilityPack project: Zip File Pipeline Component.

Zip File Pipeline Component

The Zip File Pipeline Component is a pipeline component for BizTalk Server which can be used in a send pipeline (encode stage) and is intended to compress (zip/gzip) outgoing messages.

  • The capabilities are similar to those available in compression software such as WinZip or 7-zip.
  • No compression/decompression software needs to be installed in the BizTalk Server machines.
Zip File Pipeline Component for BizTalk Server 2020

To use this pipeline component in your projects you just copy the BizTalk.PipelineComponents.ZipFile.dll file into the Pipeline Components folder that exists in BizTalk Server Installation directory: “..Program Files (x86)Microsoft BizTalk ServerPipeline Components” on every server.

You do not need to add a custom pipeline component to be used by the BizTalk Runtime to the Global Assembly Cache (GAC).

This component requires two configurations that are:

  • the FileExtension: where you can specify if you want for example a .zip or .gz file.
  • and Enabled: that is a true or false value to activate the compression.

What is BizTalk Pipeline Components Extensions Utility Pack?

BizTalk Pipeline Components Extensions Utility Pack is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in received and sent pipelines, which will provide an extension of BizTalk out-of-the-box pipeline capabilities.

The project is available on the BizTalk Server Open Source Community repository on GitHub (https://github.com/BizTalkCommunity), and everyone can contribute with new pipeline components that can be extended or improve the existing BizTalk Server capabilities.

BizTalk Pipeline Components Extensions Utility Pack: Zip File Pipeline Component for BizTalk Server 2020

At the moment this project is available for:

  • BizTalk Server 2020;
  • BizTalk Server 2016;
  • BizTalk Server 2010;
  • BizTalk Server 2006-2009

Where to download it?

You can download BizTalk Pipeline Components Extensions Utility Pack from GitHub here:

BizTalk Pipeline Components Extensions Utility Pack

The post BizTalk Server 2020 – 20 days, 20 posts: Zip File Pipeline Component for BizTalk Server 2020 appeared first on SANDRO PEREIRA BIZTALK BLOG.

BizTalk Server 2020 – 20 days, 20 posts: Unzip File Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts: Unzip File Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts – day 10. Following my last blog post here is another component migrate to BizTalk Server 2020 that makes part of my BizTalk Pipeline Components Extensions UtilityPack project: Unzip File Pipeline Component.

Unzip File Pipeline Component

The Unzip File Pipeline Component for BizTalk Server can be used in a Received pipeline (Disassemble stage), and it allows you to receive a compress (zip/gzip) file and extract its contents into different XML messages.

  • The capabilities are like those available in any compression software such as WinZip or 7-zip:
  • This component doesn’t require any configurations.

You do not need to add a custom pipeline component to be used by the BizTalk Runtime to the Global Assembly Cache (GAC).

Unzip File Pipeline Component for BizTalk Server 2020

The component doesn’t require any configuration.

What is BizTalk Pipeline Components Extensions Utility Pack?

BizTalk Pipeline Components Extensions Utility Pack is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in received and sent pipelines, which will provide an extension of BizTalk out-of-the-box pipeline capabilities.

The project is available on the BizTalk Server Open Source Community repository on GitHub (https://github.com/BizTalkCommunity), and everyone can contribute with new pipeline components that can be extended or improve the existing BizTalk Server capabilities.

BizTalk Pipeline Components Extensions Utility Pack: Unzip File Pipeline Component for BizTalk Server 2020

At the moment this project is available for:

  • BizTalk Server 2020;
  • BizTalk Server 2016;
  • BizTalk Server 2010;
  • BizTalk Server 2006-2009

Where to download it?

You can download BizTalk Pipeline Components Extensions Utility Pack from GitHub here:

BizTalk Pipeline Components Extensions Utility Pack

The post BizTalk Server 2020 – 20 days, 20 posts: Unzip File Pipeline Component for BizTalk Server 2020 appeared first on SANDRO PEREIRA BIZTALK BLOG.

App Services and APIM App Registration process in Azure AD

App Services and APIM App Registration process in Azure AD

Hi All,

This post is to describe and take through the steps to set up App Registrations for App Services and API Management using OAuth 2.0 with Azure Active Directroy.

  1. App Registration in AAD for securing App Services (WebApps and FunctionApps) using OAuth 2.0 with Azure active directory

This App registration is for securing the App Services, and should be used by the consumers (registered on AAD) to access the respective WebAPIs and Functions.

  1. Sign into Azure Portal.
  2. Select App registration
  3. Select New Registration.
  4. Name the Application as “nonprod-<team>-appsvc-appreg” , where <team> will be organisational domain  etc.
  5. Click Register.

Create App Registration

6. In the newly created App Registration, select “Manifest” property.

Select Manifest APIMClientID (2)

7. In that section set the following oAuth2 permission in the manifest modifying “oauth2Permissions” element of JSON representation.

“oauth2Permissions”: [
{
“adminConsentDescription”: “Allows another AAD registered application to access the App services”,
“adminConsentDisplayName”: “Access App Services”,
“id”: “8jca6c61-0762-7b99-a1e1-e4bdf7e6e165”,
“isEnabled”: true,
“lang”: null,
“origin”: “Application”,
“type”: “User”,
“userConsentDescription”: “Allows another AAD registered application to access the App services”,
“userConsentDisplayName”: “Access App Services”,
“value”: “user_impersonation”
}
],

8. “Save” the App Registration changes. This also creates a scope associated to the App Registration.

9. Similarly follow the same for Staging and Production as per shown tabular column.

Non – Production
Staging
Production
App Service App Registration Name

Note:- This App Registration Client ID can be used when consuming any of the Web Apps and Function Apps in HIP

nonprod-<team>-appsvc-appregstage-<team>-appsvc-appregprod-<team>-appsvc-appreg
oauth2PermissionsUse the above snippet as it is.

Id as given above.

Use the above snippet as it is.

Replaced Id with correct Id

Use the above snippet as it is.

10. Add user accounts as “App Owner” to this App Registration

2) App Registration in AAD for API Manager (APIM) to secure APIs using OAuth 2.0 with Azure active directory

This App registration is for the APIM which creates the necessary roles that should be used by the consumers to access the APIs.

  1. Select App registration
  2. Select New Registration.
  3. Name the Application as “nonprod<companyname>-apim-appreg
  4. Click Register.
  5. Select “Expose an API” and set “Application ID URI”

APIMClientID_ExposeAPI

6. Select Manifest.

Select Manifest APIMClientID (2)

7. In that section set the following oAuth2 permission in the manifest modifying “appRoles” element of JSON representation as shown below.

“appRoles”: [
{
“allowedMemberTypes”: [
“Application”
],
“description”: “Allow client apps to perform read and write operations on <name>.v1 API.”,
“displayName”: “<name>.v1.ReadAndWrite”,
“id”: “da9a6ab2-bc67-4213-102c-07d5749ea2s3,
“isEnabled”: true,
“lang”: null,
“origin”: “Application”,
“value”: “<name>.v1.ReadAndWrite”
},
{
“allowedMemberTypes”: [
“Application”
],
“description”: “Allow client apps to perform read operation on <name>.v1 API.”,
“displayName”: “<name>.v1.Read”,
“id”: “8ceee182-dcd0-48b3-af69-1a7924dc9d15”,
“isEnabled”: true,
“lang”: null,
“origin”: “Application”,
“value”: “<name>.v1.Read”
}

]

}

8. Similarly follow the same for Staging and Production as per shown tabular column.

Non – ProductionStagingProductionAPIM – API Roles
APIM App Registration Namenonprod-<companyname>-apim-appregstage-<companyname>-apim-appregprod-<companyname>-apim-appreg

<name>.v1.ReadAndWrite

<name>.v1.Read

<name>.v1.ReadAndWrite

9. Add user accounts as “App Owner” to this App Registration.

BizTalk Server 2020 – 20 days, 20 posts: Local Archive Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts: Local Archive Pipeline Component for BizTalk Server 2020

BizTalk Server 2020 – 20 days, 20 posts – day 9. This week I decide to start migrating a project that I enjoy and use them in several of my clients: BizTalk Pipeline Components Extensions UtilityPack. And the first component that I choose was: Local Archive Pipeline Component.

Local Archive Pipeline
Component

BizTalk Server Local Archive pipeline component it’s a pipeline component that can be used for archiving incoming/outgoing message from any adapters. It will provide the following capabilities :

  • It can be used in any stage of a receive pipeline or send pipeline;
  • It can be used in multiple stages of a receive pipeline or send pipeline;
  • It provides an option for you to specify the location path for where you want to save the message: local folder, shared folder, network folder.
  • It can be used from any adapter:
    • If the adapter provides the ReceivedFileName property promoted like the File adapter or FTP adapter the component will take this value in consideration and save the message with the same name;
    • Otherwise, it will use the MessageID, saving the file with the MessageID has its name without extension.

The component has two parameters:

  • PerformBackup: True/False value to enable or disable the archive functionality;
  • Folder: Local (or shared) folder for where you want to perform the archive of the message;

What is BizTalk Pipeline Components Extensions Utility Pack?

BizTalk Pipeline Components Extensions Utility Pack is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in received and sent pipelines, which will provide an extension of BizTalk out-of-the-box pipeline capabilities.

The project is available on the BizTalk Server Open Source Community repository on GitHub (https://github.com/BizTalkCommunity), and everyone can contribute with new pipeline components that can be extended or improve the existing BizTalk Server capabilities.

BizTalk Pipeline Components Extensions Utility Pack: Local Archive Pipeline Component

At the moment this project is available for:

  • BizTalk Server 2020;
  • BizTalk Server 2016;
  • BizTalk Server 2010;
  • BizTalk Server 2006-2009

Where to download it?

You can download BizTalk Pipeline Components Extensions Utility Pack from GitHub here:

BizTalk Pipeline Components Extensions Utility Pack

The post BizTalk Server 2020 – 20 days, 20 posts: Local Archive Pipeline Component for BizTalk Server 2020 appeared first on SANDRO PEREIRA BIZTALK BLOG.

March 30, 2020 Weekly Update on Microsoft Integration Platform & Azure iPaaS

March 30, 2020 Weekly Update on Microsoft Integration Platform & Azure iPaaS

Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?

Integration weekly update can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.

 

Microsoft Announcements and Updates

 

Community Blog Posts

 

Videos

 

Podcasts

 

How to get started with iPaaS design & development in Azure?

Feedback

Hope this would be helpful. Please feel free to reach out to me with your feedback and questions.

BizTalk Server 2020 – 20 days, 20 posts: Installing BizTalk Server 2020 in a Standalone Machine Guide

BizTalk Server 2020 – 20 days, 20 posts: Installing BizTalk Server 2020 in a Standalone Machine Guide

BizTalk Server 2020 – 20 days, 20 posts – day 8. Microsoft announced a few months ago, the release of Microsoft BizTalk Server 2020 – the 11th major release of BizTalk Server –and, as usual with previous versions, I updated my installation and configuration manual for BizTalk Server 2020. This whitepaper will explain in detail – a step-by-step guideline – how to install and configure Microsoft BizTalk Server 2020 on a standalone environment running Windows Server 2019. This information will help you plan the installation and configuration of BizTalk Server 2020, 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).

Installing BizTalk Server 2020 in a Standalone Machine Guide

Assumptions and out of scope

We will be assuming that the operating system: Windows Server 2019 and the latest critical Windows updates from Microsoft already have been installed.

In this scenario, we will be performing a full installation of Microsoft BizTalk Server 2020. The following components will be installed:

  • Enterprise Single Sign-On (SSO).
  • BizTalk Group.
  • BizTalk Runtime.
  • Business Rule Engine.
  • BAM Tools and Alerts.
  • BAM Portal.
    • BAM Portal it is deprecated, but we still can use it.
  • BizTalk EDI/AS2 Runtime.
  • REST APIs (Management data APIs)
    • Management data APIs are endpoints that let you remotely update, add, and query the status of different artifacts in your BizTalk Server environment. The endpoints are added using REST and come with a swagger definition.
  • BizTalk TMS.
    • BizTalk TMS is a service that refreshes authentication tokens used by BizTalk. It is a prerequisite for the Office 365 adapters.
  • Microsoft BizTalk Adapters for Enterprise Applications.

What’s in store for you?

This whitepaper will give you a detailed understanding of the following:

  • Preparing the Prerequisites and Installation for BizTalk Server 2020
  • Installing and Configuring BizTalk Server 2020
  • BizTalk Server 2020 Environment Optimization
Installing BizTalk Server 2020 in a Standalone Machine Guide

Where I can download it

You can download the whitepaper here:

I would like to take this opportunity also to say thanks to Tom Canter for being a reviewer of this installation guide and Lex for all the work to publish this resource.

I hope you enjoy reading this paper and any comments or suggestions are welcome.

You can also find, download and enjoy several other free whitepapers of my own here:

The post BizTalk Server 2020 – 20 days, 20 posts: Installing BizTalk Server 2020 in a Standalone Machine Guide appeared first on SANDRO PEREIRA BIZTALK BLOG.

Data Purging in BizTalk Server and BizTalk360

Data Purging in BizTalk Server and BizTalk360

The term purge means removing or erase something completely. Data purging is a process or activity mostly heard with Database technologies, i.e. Cleaning up of Database.

There are many different techniques and strategies for data purging, which is often contrasted with data deletion. Deletion is more of a temporary process whereas Purging removes data permanently which in turn frees up the storage and/or memory space for other uses.

Purging process lets you archive the data even though it removed from the main source permanently, giving us an option to retrieve from the archive in case there is a need for it. The deleting process also removes data permanently but doesn’t necessarily involve keeping a backup and generally involves on insignificant amounts of data.

Why Data Purging is Necessary?

For any organization and in any environment, it is important to keep track of database growth and execute the data purging policies whenever necessary.

When the database size grows, the SQL server needs more memory and CPU to read data from the tables, which slows down the database operation.

Besides that, there is also the risk that, due to the growing database(s), you are running out of disk space.

Database Administrators spend a lot of their time dealing with the problem of database processes consuming too much disk space. So, it’s very important to monitor the database size to ensure the database is not seizing the memory and CPU for smooth operation. Also, it’s essential and necessary to automate the purging process.

Data-Purging

The Purge Process

The purge process moves data between and deletes data from three categories of data or data sets:

  • The current data set, which contains data that needs to be available to users. Users can change or review the data.
  • The history data set, which contains data that can only be reviewed or aggregated by users. Users can’t change the data.
  • The archived data set, which contains data that falls out of the availability threshold but needs to be stored to meet legal or business requirements. This data is archived, so it can’t be changed, reviewed, or aggregated by users.

Available Options for Purging

In any BizTalk environment, to purge the SQL database there are two main categories of options available. The most common way is

  1. SQL Server Jobs (by using SQL Agent)
  2. Windows Services

SQL Server Jobs

The purpose of the SQL Agent is to serve as a job scheduler. Many experienced DBAs use jobs running inside the SQL Agent to perform routine tasks such as backups, updating statistics, and rebuilding indexes as needed.

Scheduling jobs is one of the core SQL Server functions. Many businesses have numerous SQL Server jobs scheduled that perform any number of different tasks from database maintenance jobs like backup and index rebuilds to running queries and kicking off ETL (Extract. Transform and Load) tasks.

Sql-Server-Jobs

What is SQL Agent?

The SQL Agent is the SQL Server subsystem that’s responsible for automatic task scheduling. The SQL Agent is available in all of the editions of SQL Server except the SQL Server Express edition. Taking advantage of SQL Agent enables you to automate many of your routine IT database infrastructure tasks.

Advantages of SQL Agent

The SQL Server Agent gives you a more specific focus around scheduling specific types of jobs around SQL Server. As the Agent is a part of the cluster resource when a failover occurs as long as the cluster has been set up correctly the SQL Agent job will start once the SQL Server Service has started.

Windows task Scheduler is a component of Microsoft Windows that provides the ability to schedule the launch of programs or scripts at pre-defined times or after specified time intervals. SQL Server Scheduler is created for Agent job scheduling, so using SQL Server Scheduler to schedule the job is the best method.

Disadvantages of SQL Agent

If the Agent is not running at the time a job is supposed to run, when the Agent is started it will run the job at the next scheduled time. It will not automatically run the job.

SQL Server Agent jobs that are running at the time of a failover event on a SQL Server failover cluster instance do not resume after failover to another failover cluster node. Jobs that begin but fail to complete because of a failover event are logged as started but do not show additional log entries for completion or failure. SQL Server Agent jobs in these scenarios appear to have never ended. So if your server failover, you need to run this job again in the other node.

Disadvantages-Sql-Agent

Windows Services

Definition of Windows Services

Windows Services is a core component of the Microsoft Windows operating system and enables the creation and management of long-running processes.

Advantages

Unlike regular software that is launched by the end-user and only runs when the user is logged on, Windows Services can start without user intervention and may continue to run long after the user has logged off. The services run in the background and will usually kick in when the machine is booted. An option ideal for use on servers when long-running functionality is needed without interference with other users on the same system.

Data Collection in BizTalk360

BizTalk360 consists own database. Collecting the necessary data is important to be able to represent the data in BizTalk360. As many of you aware that BizTalk360 is a one-stop solution for BizTalk monitoring. So for monitoring and analytics purposes, we do collect different data from various environments (it collects the information from BizTalk, SQL Server, and Tracking database) and stored in the BizTalk360’s database for future references.

  • Advanced Event viewer
  • Analytics

They are the sparkling examples for the data collection within BizTalk360 when you have configured/added more environments (for each environment there will be multiple machines say example a simple multi-server environment consists of 2-BizTalk servers for HA, 1-SQL server. For cluster servers and Load balancing you can imagine the numbers). However, you don’t want unlimited growth of your database.

BizTalk360 comes out of the box with the “Data Purging” feature to able to manage the size of the BizTalk360 database.

Monitor BizTalk360 Database Growth

There might be a situation where the BizTalk360 database grows due to the amount and size of event log data and performance counter data. The BizTalk360 purging policy will take care of the data growth, but it is also important to monitor the size regularly to ensure BizTalk360 is working seamlessly.

Data Purging in BizTalk360

As mentioned, BizTalk360 comes out of the box with the ability to purge the historic data by setting up the purging duration (days to be monitor) after the specified period. The Administrators/Superusers can set up the “Purge duration” under “Settings -> BizTalk360 Health -> Data Purging”. This will control the database growth and hence the performance of BizTalk360 will not get affected.

Purge Mechanism in BizTalk360

BizTalk360 uses Windows Services to trigger the purging. BizTalk360 owns two services,

  1. Monitoring service
  2. Analytics service

Out of those, purging was created as a sub-service of Monitoring service.

Purge-Mechanism-BizTalk360

In a regular interval time (for every 2 hours) the “Purge Data – a sub-service of Monitoring service” will purge the data by hitting the Stored Procedures for purging available in the BizTalk360 database.

We have chosen the windows service to utilize the advantages of Sub-service and the services that can be started and paused. As most of you aware that the BizTalk360 services should be always running to collect the data and perform few operations, so we would like to effectively use the service rather than creating SQL job and monitor separately. Also, if any exception appears for the sub-service, the exception will be captured from the log files and it will be shown adjacent to the respective sub-service in the UI.

Based on the values set in the data purge duration, for every 2 hours, the data will be purged using the stored procedures with the trigger of Purge Data sub-service.

Until BizTalk360 v5.0, there was a single stored procedure (sp_b360_admin_PurgePolicies) called from our monitoring service which takes care of all the purging. In v6.0, we improved the logic by splitting it into multiple stored procedures (sp_b360_admin_PurgePolicies) which takes care of various parts of data purging.

How to Check if Purging Service Status is Healthy?

Purge Data – a sub-service of Monitoring service from BizTalk360 Settings -> BizTalk360 Health -> Monitoring Service Status -> Purge Data, will help you to understand and find the information that the purging is running for every 2 hours.

If you can compare the started time and Last execution time, for every 2 hours the execution should happen. Even if there is an exception, the exception will be captured in the log files and it will be shown adjacent to the purge data sub-service.

Purging-Status-Healthy

Additional Capabilities

Along with the purging service within BizTalk360, there are few more options to check if the database size is under the expected size.

  1. Using the Database size monitoring feature, users can monitor the database and log file size of BizTalk and BizTalk360 databases, by simply configuring the error and warning threshold values for the database and log file sizes. By this when the database size exceeds the expected size, you will receive an alert from BizTalk360.
  1. Use Database Query monitoring to Monitor the size of a database.

Whenever the database grows beyond expectations, the first thing to be checked is the standard database reports. This will ensure which table occupies more space. Then we can act on the purging policy and change it according to the business needs and data flow.

What are the Stored Procedures Involved in Purging?

Till the latest release, there were 14 Stored Procedures involved in Purging for 32 tables. Each stored procedure will purge multiple tables. Let’s see what the stored procedures are used for purging the corresponding tables.

  1. sp_b360_admin_PurgePolicies_MBVReports – It will purge the BizTalk Health Check tool execution results.
  • b360_mbv_Executions
  1. sp_b360_admin_PurgePolicies_GovernanceAudit – It will purge the complete Governance and Audit tables, which is related to BizTalk and BizTalk360 activities.
  • b360_governance_Application
  • b360_governance_HostInstance
  • b360_governance_ServiceInstance
  • b360_governance_rules
  • b360_governance_Server
  • b360_governance_ESBException
  • b360_audit_History
  1. sp_b360_admin_PurgePolicies_AlertNotification – Will purge the Alert notifications which is sent out from BizTalk360 like Email, SMS and Notification channel.
  • b360_alert_History_Email
  • b360_alert_History_SMS
  • b360_alert_History_System
  • b360_historical_Alert_Maintenance
  1. sp_b360_admin_PurgePolicies_EventLog – Will purge the data of event logs that are already collected from different machines.
  • b360_ev_EventLogData
  1. sp_b360_admin_PurgePolicies_Throttling – Will purge the data collection for Throttling analyzer
  • b360_td_Throttling
  • b360_td_ThrottlingDataCollectionError
  1. sp_b360_admin_PurgePolicies_MonitoringDashboard – The alarm status update of the monitoring dashboard will be purged
  • b360_alert_MonitorExecution
  1. sp_b360_admin_PurgePolicies_MonitoringLogState – will purge the monitoring log state, monitoring will put the entries of current and expected state.
  • b360_alert_Monitor_LogStateCheck
  1. sp_b360_admin_PurgePolicies_MonitoringLogThreshold – will purge the monitoring threshold log, monitoring will put the current, warning and error levels.
  • b360_alert_Monitor_LogThresholdCheck
  1. sp_b360_admin_PurgePolicies_ProcessMonitoring – will purge the data of all types of data monitoring like Process, Message box data, Tracking data, BAM, EDI, ESB, Logic Apps, and Event log data monitoring.
  • b360_st_ScheduleActivity
  • b360_st_ProcessMonitorResults
  • b360_st_DataMonitorResults
  • b360_st_DataMonitorTaskActionResults
  1. sp_b360_admin_PurgePolicies_PerformanceAnalyzer – The data collected for the Analytics module will be purged
  • b360_perf_PerfCounter_Data
  • b360_perf_PerfCounter_Expanded
  1. sp_b360_admin_PurgePolicies_LiveFeed – Live feed data will be purged
  • b360_broadcast_Message
  1. sp_b360_admin_PurgePolicies_TrackingData – The data collected for the Analytics module will be purged.
  • b360_dta_Counter_Data
  • b360_dta_Counters_Expanded
  1. sp_b360_admin_PurgePolicies_DBCache
  • b360_admin_Cache
  1. sp_b360_admin_PurgePolicies_BizTalkReporting – The data collected for the Analytics – Reporting module will be purged.
  • b360_BizTalkDB_DiskUsages
  • b360_BizTalkDB_Tbl_sizes
  • b360_Report_History_Email
  • b360_report_ReportArchive

Purge Settings in BizTalk360

Let’s see how purge helps. The default purging settings in BizTalk360 can be seen in the below screenshot;

Purge-Setting-BizTalk360

Based on the values/duration provided by the purging policy, the data will be purged. We can see that the purging duration for Data Monitoring is 2 months. Hence the historical data for 2 months will be present in the BizTalk360 database.

Purging needs to be done to remove the historical data, thereby making the database healthy. BizTalk360 purges the data by running the stored procedure in the specified duration specified in the settings. The purging settings can be altered by the customers according to their business needs and data flow. If a large volume of data flows through the ports, they can set the purge duration to a minimum value so that data growth is controlled.

The customer configured 3 environments in a single installation of BizTalk360. In each environment, there were 5 servers available due to BizTalk and SQL server. Hence there were 15 servers to collect data for Advanced Event Viewer and they have set-up 30days to monitor. The Database has grown almost to 300 GB because of the number of servers to monitor.

We have recommended the customer to decrease the purge duration from 1 month to 2days and TRUNCATE the particular table (Since the table belongs to BizTalk360 and it is historical data, it won’t affect the BizTalk server). After modifying the purge duration, the data were reduced to 2 GB started working as expected.

Can the Purging be done Manually?

BizTalk360 will purge the data automatically. Sometimes the data collection may be out/beyond our expectation and that leads to a huge volume of data.

Our first suggestion is to reduce the number of purge days. So that the data will be reduced.

Until v6.0, the stored procedure sp_b360_admin_PurgePolicies was used and in recent versions of BizTalk360, you can use the stored procedure sp_b360_admin_CleanupDB to manually purge the database in case of large database growth.

When the number of records in the database grows quickly, call to the stored procedure(s) can start to get timed out from the monitoring services. This will result in data not being cleared and resulting in unusual database growth.

Conclusion

Considering the feedback provided by our customers we have improved and fine-tuned the collection of the data into the BizTalk360 database.

Whenever the database grows beyond expectations, the first thing to be checked is the standard database reports. This will ensure which table occupies more space. Then we can act on the purging policy and change it according to the business needs and data flow.

If you have any questions, contact us at support@biztalk360.com. Also, feel free to leave your feedback in our forum.

The post Data Purging in BizTalk Server and BizTalk360 appeared first on BizTalk360.