BizTalk Server Tips and Tricks: How to Backup (other) BizTalk Custom Databases

BizTalk Server Tips and Tricks: How to Backup (other) BizTalk Custom Databases

During my sessions about BizTalk Server Tips and Tricks, I normally ask: What RosettaNet, ESB or UDDI have in common? And the answer is: they all are BizTalk optional features that are not part of the primary installation process, you need to execute “secondary” installation processes to add theses features. These installation processes will create BizTalk custom databases for supporting all of these new optional features. But the big questions here are: do you think that these databases are being backed up? And if not, how to backup (other) BizTalk Custom Databases?

Do you think that these databases are being backed up?

To respond this first question, the answer is: No!

Because these BizTalk custom databases (we are calling “custom databases” because they are supporting optional features that are not part of the primary installation process) are not installed by default with BizTalk Server, they are not included in the default list of databases to be marked and backed up by the Backup BizTalk Server job. The default list of databases that are, normally, being backed up by the Backup BizTalk Server job are:

  • BAMAlertsApplication
  • BAMPrimaryImport
  • BizTalkDTADb
  • BizTalkMgmtDb
  • BizTalkMsgBoxDb
  • BizTalkRuleEngineDb
  • SSODB

How to Backup (other) BizTalk Custom Databases?

If you want the Backup BizTalk Server job to back up these additional BizTalk custom databases, you must manually add the databases to the Backup BizTalk Server job.

You can achieve this by:

  • Taking Windows Explorer and browse to the “Schema” directory on the BizTalk installation folder, normally:
    • C:Program Files (x86)Microsoft BizTalk Server <version>Schema
  • Run “Backup_Setup_All_Tables.sql” and next “Backup_Setup_All_Procs.sql” against all your BizTalk custom databases that you want to back up. This creates the necessary table, procedures,  roles and assigns permissions to the stored procedures.
  • After that you need need to modify the adm_OtherBackupDatabases table, in the BizTalk Management (BizTalkMgmtDb) database, to include a row for each of the new BizTalk custom databases
    • Type the new server and database names in the corresponding columns, as shown in the following tab
      • DefaultDatabaseName: The friendly name of your custom database.
      • DatabaseName: The name of your custom database.
      • ServerName: The name of the computer running SQL Server.
      • BTSServerName: The name of the BizTalk Server. This value is not used, but it must contain a value nonetheless.

To complete the process, you, mandatory, need to force Backup BizTalk Server (BizTalkMgmtDb) job to perform a full backup of the databases, otherwise you will receive the following error:

  • BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013)

To do that you need:

  • Execute the “sp_ForceFullBackup” stored procedure present in the BizTalkMgmtDb database.

The next time you run the Backup BizTalk Server job, it will back up all your BizTalk custom databases.

Note: I will not recommend you to add any of your application support custom databases to the Backup BizTalk Server job since they may interfere with the execution times of this job. If the Backup BizTalk Server job starts to take a long time to execute, it may also affect the overall performance of the BizTalk platform.

Stay tuned for new BizTalk Server Tips and Tricks!

Check out the first blog of the series BizTalk Server Tips and Tricks: Enabling BAM Add-In for Excel 2016.

Author: Sandro Pereira

Sandro Pereira is an Azure MVP and works as an Integration consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

Backup BizTalk Server (BizTalkMgmtDb) job failed with BACKUP LOG cannot be performed because there is no current database backup

Backup BizTalk Server (BizTalkMgmtDb) job failed with BACKUP LOG cannot be performed because there is no current database backup

Recently, I was writing a new article “BizTalk Server Tips and Tricks: How to Backup (other) BizTalk Custom Databases” (that will be release soon as a guest post in BizTalk360 Blog) that explain you how you can configure the Backup BizTalk Server (BizTalkMgmtDb) job to back up additional BizTalk custom databases (RosettaNet, ESB Toolkit, …), when I got an error message on the job saying: BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214). The full error was this:

Date 6/5/2017 2:30:00 PM
Log Job History (Backup BizTalk Server (BizTalkMgmtDb))

Step ID 3
Server BTS02

Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name MarkAndBackupLog
Duration 00:00:01
Sql Severity 16
Sql Message ID 3014
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message

Executed as user: domain/username. Processed 1 pages for database ‘BAMPrimaryImport’, file ‘BAMPrimaryImport_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 1 pages in 0.037 seconds (0.118 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 12 pages for database ‘BizTalkDTADb’, file ‘BizTalkDTADb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 12 pages in 0.051 seconds (1.790 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 9 pages for database ‘BizTalkMgmtDb’, file ‘BizTalkMgmtDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 9 pages in 0.054 seconds (1.283 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 67 pages for database ‘BizTalkMsgBoxDb’, file ‘BizTalkMsgBoxDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 67 pages in 0.101 seconds (5.129 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 1 pages for database ‘BizTalkRuleEngineDb’, file ‘BizTalkRuleEngineDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) BACKUP LOG successfully processed 1 pages in 0.051 seconds (0.086 MB/sec). [SQLSTATE 01000] (Error 3014). The step failed.

Backup BizTalk Server (BizTalkMgmtDb) job failed BACKUP LOG cannot be performed because there is no current database backup.

Cause

If you take attention to the error message, you will notice that the job failed in the third task: MarkAndBackupLog, that is responsible for performing the backup’s the BizTalk Server databases logs.

You need to remember that by default Backup BizTalk Server (BizTalkMgmtDb) job only makes a full backup of the databases once a day and each 15 minutes a backup of the databases logs. However, to be able to perform a back up of the log from a particular database, it needs that a previous full backup has been performed and register in the “adm_BackupHistory” table in the BizTalkMgmtDb database. You can validate by executing the following script:

USE [BizTalkMgmtDb]

SELECT DISTINCT [DatabaseName]
FROM [BizTalkMgmtDb].[dbo].[adm_BackupHistory]

In my case, I was configuring an additional database to be backed up via the Backup BizTalk Server (BizTalkMgmtDb) job, and at that time, the full backup of the BizTalk Databases had already occurred.

Solution

To fix the problem of BACKUP LOG cannot be performed by the Backup BizTalk Server job, you need to:

  • Execute the “sp_ForceFullBackup” stored procedure present in the BizTalkMgmtDb database.
USE [BizTalkMgmtDb]

EXEC sp_ForceFullBackup

The next time you run the Backup BizTalk Server job, it will back up all BizTalk databases including all your BizTalk custom databases and it will be able them to execute the third task: MarkAndBackupLog, and therefore execute successfully the job once again.

Fix: Backup BizTalk Server (BizTalkMgmtDb) job failed BACKUP LOG cannot be performed because there is no current database backup.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Microsoft Integration (Azure and much more) Stencils Pack v2.5 for Visio 2016/2013 is now available

Microsoft Integration (Azure and much more) Stencils Pack v2.5 for Visio 2016/2013 is now available

Once again, my Microsoft Integration Stencils Pack was updated with new stencils. This time I added near 193 new shapes and additional reorganization in the shapes by adding two new files/categories: MIS Power BI and MIS Developer. With these new additions, this package now contains an astounding total of ~1287 shapes (symbols/icons) that will help you visually represent Integration architectures (On-premise, Cloud or Hybrid scenarios) and Cloud solutions diagrams in Visio 2016/2013. It will provide symbols/icons to visually represent features, systems, processes and architectures that use BizTalk Server, API Management, Logic Apps, Microsoft Azure and related technologies.

  • BizTalk Server
  • Microsoft Azure
    • BizTalk Services
    • Azure App Service (API Apps, Web Apps, Mobile Apps and Logic Apps)
    • API Management
    • Event Hubs
    • Service Bus
    • Azure IoT and Docker
    • Virtual Machines and Network
    • SQL Server, DocumentDB, CosmosDB, MySQL, …
    • Machine Learning, Stream Analytics, Data Factory, Data Pipelines
    • and so on
  • Microsoft Flow
  • PowerApps
  • Power BI
  • Office365, SharePoint
  • DevOpps: PowerShell, Containers
  • And much more…

Microsoft Integration Stencils Pack v2.5

The Microsoft Integration Stencils Pack v2.5 is composed by 13 files:

  • Microsoft Integration Stencils v2.5
  • MIS Apps and Systems Logo Stencils v2.5
  • MIS Azure Portal, Services and VSTS Stencils v2.5
  • MIS Azure SDK and Tools Stencils v2.5
  • MIS Azure Services Stencils v2.5
  • MIS Deprecated Stencils v2.5
  • MIS Developer v2.5 (new)
  • MIS Devices Stencils v2.5
  • MIS IoT Devices Stencils v2.5
  • MIS Power BI v2.5 (new)
  • MIS Servers and Hardware Stencils v2.5
  • MIS Support Stencils v2.5
  • MIS Users and Roles Stencils v2.5

These are some of the new shapes you can find in this new version:

Microsoft Integration Stencils Pack v2.5

You can download Microsoft Integration Stencils Pack for Visio 2016/2013 from:

Microsoft Integration Stencils Pack for Visio 2016/2013 (10,1 MB)
Microsoft | TechNet Gallery

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk Server: Teach me something new about Flat Files (or not) video and slides are available at Integration Monday

BizTalk Server: Teach me something new about Flat Files (or not) video and slides are available at Integration Monday

Last Monday I presented, once again, a session in the Integration Monday series. This time the topic was BizTalk Server: Teach me something new about Flat Files (or not). This was my fifth session that I deliver:

And I think will not be the last! However, this time was different for many aspects and in a certain way it was a crazy session… Despite having some post about BizTalk Server: Teach me something new about Flat Files on my blog, I didn’t have time to prepare this session (sent to a crazy mission for a client and also because I had to organize the integration track on TUGA IT event), I had a small problem in my BizTalk Server 2016 machine in which I had to switch to my BizTalk Server 2013 R2 VM, interrupted by the kids in the middle of the session because the girls wanted me to have dinner with them (worthy of being in this series)… but it all ended well and I think it was a very nice session with two great real case samples:

  • Removing headers from a flat file (CSV) using only the schema (without any custom pipeline component)
  • And removing empty lines from a delimited flat file, again, using only the schema (without any custom pipeline component)

For those who were online, I hope you have enjoyed it and sorry for all the confusion. And for those who did not have the chance to be there, you can now view it because the session is recorded and available on the Integration Monday website. I hope you like it!

Session Name: BizTalk Server: Teach me something new about Flat Files (or not)

BizTalk Server: Teach me something new about Flat Files

Session Overview: Despite over the year’s new protocols, formats or patterns emerged like Web Services, WCF RESTful services, XML, JSON, among others. The use of text files (Flat Files ) as CSV (Comma Separated Values) or TXT, one of the oldest common patterns for exchanging messages, still remains today one of the most used standards in systems integration and/or communication with business partners.

While tools like Excel can help us interpret such files, this type of process is always iterative and requires few user tips so that software can determine where there is a need to separate the fields/columns as well the data type of each field. But for a system integration (Enterprise Application Integration) like BizTalk Server, you must reduce any ambiguity, so that these kinds of operations can be performed thousands of times with confidence and without having recourse to a manual operator.

In this session we will first address: How we can easily implement a robust File Transfer integration in BizTalk Server (using Content-Based Routing in BizTalk with retries, backup channel and so on).
And second: How to process Flat Files documents (TXT, CSV …) in BizTalk Server. Addressing what types of flat files are supported? How is the process of transforming text files (also called Flat Files) into XML documents (Syntax Transformations) – where does it happen and which components are needed. How can I perform a flat file validation?

Integration Monday is full of great sessions that you can watch and I will also take this opportunity to invite you all to join us next Monday.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Step by step configuration to publish BizTalk operational data on Power BI whitepaper

Step by step configuration to publish BizTalk operational data on Power BI whitepaper

After some request by the community and after I publish in my blog as a season of blog posts, Step by step configuration to publish BizTalk operational data on Power BI is available as a whitepaper!

Recently, the Microsoft Product team released a first feature pack for BizTalk Server 2016 (only available for Enterprise and Developer edition). This whitepaper will help you understand how to install and configure one of the new features of BizTalk Server 2016:

  • Leverage operational data – View operational data from anywhere and with any device using Power BI, works and how we can configure it.

BizTalk operational data on Power BI whitepaper

What to expect about Step by step configuration to publish BizTalk operational data on Power BI

This whitepaper will give a step-by-step explanation of what component or tools you need to install and configure to enable BizTalk operational data to be published in a Power BI report.

Table of Contents

  1. About the Author
  2. Introduction
  3. What is Operational Data?
  4. System Requirements to Enable BizTalk Server 2016 Operational Data
  5. Step-by-step Configuration to Enable BizTalk Server 2016 Operational Data Feed
    1. First step: Install Microsoft Power BI Desktop
    2. Second step: Enable operational data feed
    3. Third step: Use the BizTalk Server Operational Data Power BI template to publish the report to Power BI
    4. Fourth step: Connect Power BI BizTalkOperationalData dataset with your on-premise BizTalk environment

Where I can download it

You can download the whitepaper here:

I would like to take this opportunity also to say thanks to my amazing team of BizTalk360 for the proofreading and for once again join forces with me to publish for free another white paper.

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

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

My sessions at TUGA IT 2017 | May 18th–20th | Lisbon ,Portugal

My sessions at TUGA IT 2017 | May 18th–20th | Lisbon ,Portugal

We are 3-days away of Tuga IT 2017 to return to Lisbon! And you are still on time to register for this event here. This year, in addition to organizing the integration track, I will also present 2 sessions, one focused on PowerApps and Microsoft Flows on SharePoint/Office365 track, and the second focused on Azure Enterprise Integration features: Logic Apps.

You can check the full event agenda here.

Here are the title and abstract of the sessions I will deliver at TUGA IT 2017

How can PowerApps and Microsoft Flow allows your power users to quickly build Enterprise Mobile Apps

How can PowerApps and Microsoft Flow allows your power users to quickly build Enterprise Mobile Apps will occur May, 19th at SharePoint/Office365 track between 9 AM to 10 AM.

Every organization faces constant pressure to do more with less. While technology is often the key to operating more effectively and efficiently, cost and complexity have often prevented organizations from taking maximum advantage of the potential benefits. The growth of SaaS (software as a service) has lowered barriers – no need to deploy servers or to install and configure complex software systems. Just sign up and go.

Microsoft Flow and Microsoft PowerApps will help these people (normally business users) achieve more.

We know not every business problem can be solved with off-the-shelf solutions. But developing custom solutions has traditionally been too costly and time-consuming for many of the needs teams and departments face, especially those projects that integrate across multiple data sources or require delivery across multiple devices from desktop to mobile. As a result, too many technology needs end up unsolved or under-optimized. We piece together spreadsheets, email chains, SharePoint or/and manual processes to fill in the gaps.

TUGA IT 2017: PowerApps

PowerApps and Microsoft Flow are both aimed squarely at these gaps. They give people who best understand their needs and challenges the power to quickly meet them, without the time, complexity and cost of custom software development.

In this session, we will look at these two new offering from Microsoft: PowerApps and Flow. What are they? How can I use it? But special we will walk through and create from scratch some live demos showing how to create Enterprise Mobile Application that easily connects with all your enterprise platforms like Office36, SharePoint Online, Dynamic CRM, on-premise SQL, Social Networks and much more and also how they can automate some common tasks using the new Microsoft Flow.

The Speaker Nightmare: Eval Forms, OCR, Logic Apps & Power BI

The Speaker Nightmare: Eval Forms, OCR, Logic Apps & Power BI will occur May, 20th at Enterprise Integration track between 10:20 AM to 11:30 AM

An evaluation form is something that a speaker love and hates, especially, if the results are processed in real-time and public available. If the result was excellent, then it is extremely rewarding, other times, it may “hurt” the speaker who has made himself available to share his knowledge and has been evaluated negatively. Sometimes the attendees are unfair in their evaluations, like, to basic in a 100-level session (these types of sessions are supposed to be basic or introductory) or sometimes the speaker had a bad day (it happens with everyone).

TUGA IT 2017: SmartDocumentor

(this demo will be in real time)

I speak from personal experience, is these last 6 years that I have been doing speaking at community events, in Portugal and abroad, I already been evaluated in all ways: badly, reasonable, good and excellent, sometimes I saw in, the same sessions, attendees with different profiles evaluate me badly and excellent. The key point for the speaker is:

  • All feedback is good, either negative or positive, he can learn to improve itself, if that’s the case, or that I specific topic is not good for a certain audience
  • He only needs to give his best! We cannot please everyone, and the goal is to fill happy with yourself and your performance.

I love evaluation forms and I love for them to be public available, even better if they are public available during the event. Because, at least, it will give during the event a good topic of conversation for people that do not know each other and it will keep the conversation flowing (naturally), people normally are affray or shy to start a conversation between unfamiliar persons, this is a good ice break.

In this session, I will show and explain a real live demo on how we can easily build a robust solution for process evaluation forms, using an OCR software and easily integrate them with Power BI to present the results in an interactive and beautiful way. But most important: How you can educate your enterprise Developers and IT pros users to easily extend capabilities for power users, who understand their business challenges the best, and allow them to use their familiar tools like: OCR software (SmartDocumentor) to process Evaluation forms and quickly build and deliver Power BI solutions to build Interactive Data dashboards. And at the same time integrate these tools, platforms or systems and a very quick and robust way using integrate feature on Azure, like, Logic Apps, API Apps and Azure Functions. How to start from a simple solution and evolve them enabling new functionalities.

Registration For TUGA IT 2017

TUGA IT 2017 will take place in Microsoft Portugal’s offices, in Lisbon, on May 18-20, 2017. It will feature 3 days of breakout sessions and full-day workshops by world-class speakers where IT Professionals can spend 3 amazing days checking the future of IT and also take the time to network with top-level speakers and other IT Professionals.

Registration for TUGA IT 2017 is a few euro’s or even free if you do require lunch (the fee is there to reduce waste and prevent having an abundance of food).

You can register here and I will see you there in Lisbon!

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

TUGA IT 2017 is almost here, don’t miss it!

TUGA IT 2017 is almost here, don’t miss it!

After the success of last year, Tuga IT 2017 is one week away from returning to Lisbon! From May 18th until the 20th, a variety of world class speakers (local and international) that will present a huge variety of “fresh” and “hot” topics about Microsoft Data Platform, SharePoint, Office 365, Enterprise Integration, Agile Methodologies, Open Source Technologies, Azure and much more. And this is only possible thank to the collaboration of all of the 9 communities taking part in Tuga IT 2017 event organizers or track owners.

Once again, I was invited to lead the Integration track and yes, we will once again have again a dedicated Enterprise Integration track! The integration track on Saturday the 20th will be packed with amazing sessions by Azure MVP’s Nino Crudele, Steef-Jan Wiggers, Eldert Grootenboer, Tomasso Groenendijk, myself and Ricardo Torre.

Enterprise Integration Track Agenda

8:30 Registration

9:00 How to face the integration challenges using the open source by Nino Crudele [Azure MVP]

“The open source space offers a lot of different options and opportunities in order to face the endless challenges that we normally need to face in the integration of technologies.

During this session, we will explore all the best open source options to cover the most advanced and complex requirements.

We will explore new options to extend the actual technologies and how to use the open source in conjunction with technology stacks like, pure .Net Framework, BizTalk Server and Microsoft Azure to solve complex integration scenarios.

During the session, we will also examine the best options available in the market and in the open source space.”

10:10 Coffe break

10:30 The speaker nightmare: Eval Forms & OCR & Logic Apps & Power BI by Sandro Pereira [Azure MVP]

“An evaluation form is something that a speaker love and hates, especially, if the results are processed in real-time and public available. If the result was excellent, then it is extremely rewarding, other times, it may “”hurt”” the speaker who has made himself available to share his knowledge and has been evaluated negatively. Sometimes the attendees are unfair in their evaluations, like, to basic in a 100-level session (these types of sessions are supposed to be basic or introductory) or sometimes the speaker had a bad day (it happens with everyone).

I speak from personal experience, is these last 6 years that I have been doing speaking at community events, in Portugal and abroad, I already been evaluated in all ways: badly, reasonable, good and excellent, sometimes I saw in, the same sessions, attendees with different profiles evaluate me badly and excellent. The key point for the speaker is:

  • All feedback is good, either negative or positive, he can learn to improve itself, if that’s the case, or that I specific topic is not good for a certain audience
  • He only needs to give his best! We cannot please everyone, and the goal is to fill happy with yourself and your performance.

I love evaluation forms and I love for them to be public available, even better if they are public available during the event. Because, at least, it will give during the event a good topic of conversation for people that do not know each other and it will keep the conversation flowing (naturally), people normally are affray or shy to start a conversation between unfamiliar persons, this is a good ice break.

In this session, I will show and explain a real live demo on how we can easily build a robust solution for process evaluation forms, using an OCR software and easily integrate them with Power BI to present the results in an interactive and beautiful way. But most important: How you can educate your enterprise Developers and IT pros users to easily extend capabilities for power users, who understand their business challenges the best and allowing them to use their familiar tools like OCR software to process Evaluation forms and quickly build and deliver Power BI solutions to build Interactive Data dashboards. And at the same time integrate these tools, platforms or systems and a very quick and robust way using integrate feature on Azure, like, Logic Apps, API Apps and Azure Functions. How to start from a simple solution and evolve them enabling new functionalities.”

11:50 Cloud Integration: so many options! by Steef-Jan Wiggers [Azure MVP]

Traditional integration has changed with the rise and evolution of cloud computing. And connectivity between systems on premise will evolve to connections with services and solutions in the cloud. Data is everywhere, applications are everywhere and people are everywhere with their devices always connected. In this world integration is key. Azure offers us various ways to implement integration solutions to provide connectivity to the business to their data. This talk will be a journey of options you have when building these types of solutions!

13:00 Lunch

14:00 Using DocumentDB to make your API App high performant and secure it with API Management by Tomasso Groenendijk [Azure MVP]

APIs are becoming more important for organizations and people even talk about the API economy but how can you create your own API, expose it globally and make a profit with it? In this session, Tomasso is going to show how you can use API Apps and DocumentDB to create a high performant API and how to use API Management in combination with Web Apps to expose the API.

15:10 Sponsor Session

15:30 Cofee Break

15:50 BizTalk 2016 in a hybrid world by Ricardo Torre

The integration landscape has definitely evolved to be a hybrid, significant on-premise investment has been accumulated over the years while at the same time cloud computing brought new challenges and new ways of implementing integration. Let’s navigate through the innovations in both worlds and how BizTalk and the cloud currently live together.

17:10 Azure IoT Hub Deep Dive by Eldert Grootenboer [Azure MVP]

Azure IoT Hub gives us the possibility to manage and secure and do bi-directional communication with billions of IoT devices. In this session, we will dive into all these possibilities, to show how you can easily set up a robust and hyper-scalable solution for your IoT needs.

18:20 Closing

More information on the official website: http://tugait.pt/2017.

Invitation for a social game.

A social SPEAKER game… there might be other games (and awards) but that is still under NDA!

Speaker game has 1 objective: getting you the MAXIMUM social experience, getting you to LEARN about your fellow speakers, about the event and about THE ATTENDEES.

The winner/s will be determined by the collection of the maximum number of points and we shall determine the winner on Saturday evening … (the prize, again, is still under NDA) and here is how you can start collecting points on the game card will receive at the airport/event:

  • Get to know 5 people at the event and write down their name, company, it area & specialty: 5 points
  • Vote your favorite Tuga IT 2017 speaker (you will need to attend that session): 1 point
  • Write down the number of countries that Tuga IT 2017 speakers represent: 1 point
  • Write down the number of countries that Tuga IT 2017 attendees represent: 1 point
  • Name all Tuga IT 2016 sponsors: 1 point
  • Name the person who was the most influential in your career: 1 point
  • Name 3 IT Celebrities (MVPs/Speakers/PMs) who started outside of IT: 1 point
  • Name the creation dates of the involved User Groups: 1 point per correct answer (there are 9 groups in total)
  • Name the youngest speaker of Tuga IT 2017: 1 point
  • Discover how many MVPs/Microsoft MVPs/Oracle Aces do we have at the Tuga IT 2017? 1 point

And even more for the social part:

It might be worth playing… you know… you never lose in trying.

Registration For TUGA IT 2017

TUGA IT 2017 will take place in Microsoft Portugal’s offices, in Lisbon, on May 18-20, 2017. It will feature 3 days of breakout sessions and full-day workshops by world-class speakers where IT Professionals can spend 3 amazing days checking the future of IT and also take the time to network with top-level speakers and other IT Professionals.

Registration for TUGA IT 2017 is a few euro’s or even free if you do require lunch (the fee is there to reduce waste and prevent having an abundance of food).

You can register here and I will see you there in Lisbon!

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk Server 2016 Feature Pack 1: How to publish BizTalk operational data on Power BI – Step-by-step configuration (Part 3)

BizTalk Server 2016 Feature Pack 1: How to publish BizTalk operational data on Power BI – Step-by-step configuration (Part 3)

To close this sequence of posts about BizTalk Server 2016 Feature Pack 1: How to publish BizTalk operational data on Power BI – Step-by-step configuration, in this post we will see how we can connect Power BI BizTalkOperationalData dataset with our on-premise BizTalk environment.

Step-by-step Configuration to Enable BizTalk Server 2016 Operational Data Feed

Fourth step: Connect Power BI BizTalkOperationalData dataset with your on-premise BizTalk environment

The final step is to install and configure a Power BI Gateway to connect your on-premise BizTalk Server environment.

With the On-premises data gateway, you can keep your data fresh by connecting to your on-premises data sources without the need to move the data. Because it installs and runs on your computer, it is best to install it on a machine that can be left running all the time.

Here are a few things to consider before installing the gateway.

  • If you are installing on a laptop, and your laptop is turned off, not connected to the internet, or asleep the gateway won’t work and the data in the cloud service will not be synchronized with your on-premises data.
  • If your machine is connected to a wireless network, the gateway may perform more slowly which will cause it to take longer to synchronize the data in the cloud service with your on-premises data.

(source: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/)

To do that we need to:

BizTalk operational data: Power BI Gateway download

  • Run the downloaded .EXE file – PowerBIGatewayInstall.exe – from your hard drive.
  • In the “Security Warning” screen, click “Run”

BizTalk operational data: Power BI Gateway Install

  • In the “Start your on-premises data gateway installation” screen, click “Next”

BizTalk operational data: Power BI Gateway Install Welcome

  • · In the “Choose the type of gateway you need” screen, select “On-premises date gateway (recommended)” and then click “Next”
    • On-premises data gateway: Multiple users can share and reuse a gateway in this mode. This gateway can be used by Power BI, PowerApps, Flow or Logic Apps. For Power BI, this includes support for both schedule refresh and DirectQuery
    • Personal: This is for Power BI only and can be used as an individual without any administrator configuration. This can only be used for on-demand refresh and schedule refresh. This will launch the installation of the personal gateway.

BizTalk operational data: Power BI Gateway Install type of gateway

  • In the “Reminder before you install” screen, click “Next”

BizTalk operational data: Power BI Gateway Install warning

  • In the “Getting ready to install the on-premises data gateway” screen, review or change the installation path, select the option “I accept the terms of use and privacy statement” and then click “Install”

BizTalk operational data: Power BI Gateway Getting ready

  • If the install is successful, then the “Almost done” screen will appear. The next step is to sign into your Power BI account to finish the configuration of the gateway. Enter your email account and then click “Sign in”

BizTalk operational data: Power BI Gateway Almost done

  • The next step is to specify that we want to register a new gateway on our environment.
    • Select the option “Register a new gateway on this computer” and then click “Next”

BizTalk operational data: Power BI Gateway Register new gateway

  • Finally, the last step is to configure the Gateway settings by defining a Gateway name and recovery key. To finalize the configuration, click “Configure”

BizTalk operational data: Power BI Gateway Configure gateway

When we click configure, the Gateway is confirmed and added to the PowerBI setup for our account.

The only thing the is missing to finalize the process is to add a data source to the gateway that we just created previously on https://app.powerbi.com portal. For that we need to:

  • Login in https://app.powerbi.com portal
  • Select the gear icon in the upper-right corner and then click “Manage gateways”.

BizTalk operational data: Power BI manage gateways

  • You can add a new data source by selecting a gateway and click “…” (3 dots) button and then click “Add data source”.

BizTalk operational data: Power BI manage gateways Add Data Source

  • In the “Data Source Setting” panel set the following configuration:
    • Data Source Name: set a name for your data source, example: BizTalkOperationalDataSource
    • Data Source Type: From the Data Source Type list, you must select “OData”.
    • URL: set the URL that we defined on the “BizTalkOperationalData.pbit” template:
      • http://localhost/BizTalkOperationalDataService
    • Authentication Method: This can either be Windows or Basic. You would want to choose Basic if you are going to use SQL Authentication instead of Windows Authentication. Then enter the credentials that will be used for this data source.

BizTalk operational data: Power BI manage gateways Add Data Source information

  • And then click “Add”.

Now let’s associate this gateway and data source with our BizTalkOperationalData dataset. To do that you need to:

  • Select your Workspace, in my case “My workspace” and then select the tab “dataset”

BizTalk operational data: Power BI BizTalkOperationalData dataset

  • On our BizTalkOperationalData dataset, click “…” (3 dots) button and then select “Settings”

BizTalk operational data: Power BI BizTalkOperationalData dataset settings

  • On our BizTalkOperationalData dataset, expand the tab “gateway connection”. There you will be able to already see a gateway available under “Use a data gateway”.
    • Select the option “Use a data gateway” and click “Apply”.

BizTalk operational data: Power BI BizTalkOperationalData dataset use data gateway

And now you will be able to successfully refresh manual your dataset or specify a scheduling refresh mechanism.

BizTalk operational data: Power BI BizTalkOperationalData dataset refresh

Related Links

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk Server 2016 Feature Pack 1: How to publish BizTalk operational data on Power BI – Step-by-step configuration (Part 2)

BizTalk Server 2016 Feature Pack 1: How to publish BizTalk operational data on Power BI – Step-by-step configuration (Part 2)

Following the sequence of my last post: BizTalk Server 2016 Feature Pack 1: How to publish BizTalk operational data on Power BI – Step-by-step configuration (Part 1), here we will see how we can enable BizTalk operational data feed and how we can use the BizTalk Server Operational Data Power BI template provided by Microsoft to easily publish the report to Power BI.

Step-by-step Configuration to Enable BizTalk Server 2016 Operational Data Feed

Second step: Enable BizTalk operational data feed

To enable BizTalk Server 2016 operational data feed, you should:

  • Run Windows PowerShell as Administrator by:
    • Press the “Windows key” to open the Start menu and type “PowerShell”
    • Right click on “Windows PowerShell” option and select “Run as administrator”.

BizTalk operational data: run PowerShell as administrator

  • Browse to the BizTalk Server 2016 is installation folder, normally:
    • C:Program Files (x86)Microsoft BizTalk Server 2016
  • And run the following command. Be sure to update your website, domainuser, password, and domaingroup with your values:
    • Service: The service to be configured: “Management” or “OperationalData”.
    • WebSiteName: The existing Web Site where the service will be configured. The default value is “Default Web Site”
    • ApplicationPool: The Application Pool to be used by the service. If exists, will not be created. The default value is “DefaultAppPool”
    • ApplicationPoolUser: Configures the application pool to run as this user identity. Must have BizTalk Server Operator or higher privileges.
    • ApplicationPoolUserPassword: Password for the ApplicationPoolUser
    • AuthorizationAccount: List of authorized Groups or Users to use this service

FeaturePack.ConfigureServices.ps1 -Service operationaldata -WebSiteName ‘<Default Web Site>’ -ApplicationPool <operationalDataServiceAppPool> -ApplicationPoolUser <domain><user> -ApplicationPoolUserPassword <password> -AuthorizationRoles ‘<domain><group>, <domain><user>’

If everything go well, after you run the script, if you browse the new IIS Application by:

  • Open your web browser
  • Go to http://localhost/BizTalkOperationalDataService

You will see a JSON file – BizTalkOperationDataService.json – being download.

BizTalk operational data: BizTalkOperationDataService

Third step: Use the BizTalk Server Operational Data Power BI template to publish the report to Power BI

To access the Power BI Template file, and use the provided visualization from Microsoft, use the following steps:

  • Browse to “OperationalDataService” folder under the BizTalk Server 2016 is installation folder, normally:
    • C:Program Files (x86)Microsoft BizTalk Server 2016OperationalDataService

BizTalk operational data: OperationalDataService Power BI Template file

  • And double click to open the “BizTalkOperationalData.pbit” file.
  • When prompted from Power BI Desktop, paste the http://<BizTalkServer>/BizTalkOperationalDataService URL that you created for your OData feed.
    • Normally: http://localhost/BizTalkOperationalDataService

BizTalk operational data: OperationalDataService Power BI Template OData URL

  • And then click “Load” to populate the fields in your Power BI report.

BizTalk operational data: OperationalDataService Power BI Template OData Load

The Template file automatically generates the information and tables available from the OData feed.

BizTalk operational data: OperationalDataService Power BI Template

The operational data is exposed through the computer and can be accessed and executed by other applications based on permissions.

Publishing your reports to the Power BI service is fast and easy. Once the data have been load you need to:

  • Save the file by selecting the menu “File” and click “Save”
  • And then select the “Publish” button on the Home tab in Power BI Desktop, and the process begins.

BizTalk operational data: OperationalDataService Power BI Template Publish

  • The first step, of course, is to provide your Power BI account

BizTalk operational data: OperationalDataService Power BI Template Publish Autentication

  • After you authenticate on Power BI, a list of destinations that you have access will be presented so you can select the destination to where you want to publish BizTalk Operational Data report.
    • Select your destination, in my case, “My workspace”
    • and then click “Select”

BizTalk operational data: OperationalDataService Power BI Template Publish destination

  • The then the publishing process will start

BizTalk operational data: OperationalDataService Power BI Template Publish starting

  • Once the upload is complete, a dialog box tells you the publishing process succeeded and a link is provided in that dialog to take you directly to your report in the Power BI service, in a web browser.

BizTalk operational data: OperationalDataService Power BI Template Publish Complete

  • If you access to PowerBI.com you will see that you will have BizTalk Operational Data (BizTalk Operational Data report) report available.

BizTalk operational data: OperationalDataService Power BI online report

And if you click on it, you will be able to see the data that was load and publish by the Power BI desktop.

BizTalk operational data: OperationalDataService Power BI online report Data

But the most important question is: Are this data being updated?

And the answer is: No. For that, you will need additional steps.

Related Links

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk Server Tips and Tricks: Enabling BAM Add-In for Excel 2016

BizTalk Server Tips and Tricks: Enabling BAM Add-In for Excel 2016

I have been writing a blog post series in BizTalk360 Blog called “Thinking outside the box (or not)” and I will continue to do that in the future. In the meanwhile, I thought of creating another blog post series about one of my favorites topics: “BizTalk Server Tips and Tricks” where I will address some common, and (I hope!) useful BizTalk Server Tips and Tricks (and Workarounds) for developers, administrators or business users. In this blog, let’s get started with “How can we enable BAM Add-In for Excel 2016?”

Add-ins provide optional commands and features for Microsoft Excel. In this case, the BAM Add-In for Excel 2016 provides the tools that you need to create a BAM observation model. In other words, it will allow you to define what data to collect from business processes and to define the way in which business users will view the collected data. A BAM observation model consists of:

  • BAM activities: a BAM activity defines milestones and data of interest.
  • BAM views: a BAM view defines the dimensions and measures based on the data of interest and milestones that present information to a particular audience.

By default, add-ins are not immediately available in Excel, so you must first install and (in some cases) activate/enable these add-ins to be able to use them.

Assuming that you already have BAM and Excel installed, for enabling the BizTalk Server 2016 BAM Add-In for Excel 2016, we need to:

  • Open Excel 2016, click the “Customize Quick Access Toolbar” button present in the Quick Access Toolbar and then click the “More Commands…” option near the bottom of the drop-down menu.

BizTalk Server 2016- Enabling BAM Add-In for Excel 2016

  • Alternatively, you can also click the “File” tab and then click “Options”.
  • This will open the Excel options dialog box and from there you can select the Add-Ins category. From the Add-ins list, you should select “Business Activity Monitoring
    • Note: Do not yet exit the “Excel Options” box by clicking “OK”

BizTalk Server 2016- Enabling BAM Add-In for Excel 2016 options

  • After selecting “Business Activity Monitoring”, in the bottom of the window in the “Manage: Excel Add-Ins” section, click “Go…”.
  • This will bring up an “Add-Ins” dialog box where you need to check the “Business Activity Monitoring” Add-In option.

BizTalk Server 2016- Enabling BAM Add-In for Excel 2016 Business Activity Monitoring

  • Finally, click “OK”.

Having done this, the BAM Add-In will now be accessible through the “Add-ins” tab and you can edit your BAM Activities and Views.

BizTalk Server 2016 - Enabling BAM Add-In for Excel 2016 enabled

Stay tuned for new tips and tricks!

Author: Sandro Pereira

Sandro Pereira is an Azure MVP and works as an Integration consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.