Understanding the current Integration Technologies Puzzle (cloud and on-premise)

by | Jun 30, 2013 | BizTalk Community Blogs via Syndication

In the recent months there were overwhelming amount of improvements in the Microsoft integration space both on-premise and in the cloud and it’s very difficult to keep up with the various technologies  and how they all relate to each other.  In this article we are going to take a brief look at each one of […]

The post Understanding the current Integration Technologies Puzzle (cloud and on-premise) appeared first on BizTalk360 Blog.

Blog Post by: Saravana Kumar

Encountering BizTalk configuration difficulties when moving from a clustered master secret server back to a single server – Cannot perform encryption or decryption because the secret is not available from the master secret server

Encountering BizTalk configuration difficulties when moving from a clustered master secret server back to a single server – Cannot perform encryption or decryption because the secret is not available from the master secret server

by | Jun 28, 2013 | BizTalk Community Blogs via Syndication

I recently encountered a problem at a customer site where it was a common practice to clone their production BizTalk Server 2009 environment into its own isolated environment for staging and testing purposes. The production environment made use of a failover cluster to ensure high-availability of the enterprise single sign on master secret. However in […]
Blog Post by: Johann

Hybrid Scenarios using BizTalk Adapter Service

by | Jun 28, 2013 | BizTalk Community Blogs via Syndication

What is BizTalk Adapter Service?

BizTalk Adapter Service allows an application in the cloud to communicate with an on-premise Line-of-Business (LOB) system behind a firewall. Using the LOB adapters in BizTalk Adapter Pack (BAP), a BizTalk Adapter Service application can execute LOB operations to the following on-premise LOB systems:

  • Microsoft SQL Server
  • Oracle Database
  • Oracle E-Business Suite
  • SAP
  • Siebel eBusiness Applications

BAS Scenario

There is an on-premise SQL Server that stores Customer data, including Name, Shipping Address, Order History, and Payment Methods. You want to develop a purchasing application. When the order is being placed, this application retrieves the Name, Shipping Address, Order History, and Payment Methods from this SQL Server database. The goal is to host the application in the cloud and have it retrieve the customer data from this on-premise SQL Server.

BizTalk Adapter Service is your answer. You continue to maintain your on-premise SQL Server and allow the cloud to host your application. You can execute any LOB operation, like DELETE, INSERT, SELECT, UPDATE, from your cloud application.

For further information please refer here.

BizTalk Adapter Service Architecture

BAS Architecture provides information on BizTalk Adapter Service Architecture.

BizTalk Adapter Service Setup installation

Pre-Requisites

Missing Pre-Requisites can cause issues in BAS installation. Please make sure you install all the required Pre-Reqs.

Here’s the list of pre-requisites for BAS. There’s a smaller list if the user is installing only runtime/tools but excluding that from this section. 

  1. Windows Server AppFabric v1.0 or higher  
  2. IIS 
  3. IIS Windows Authentication feature 
  4. BizTalk Adapter Pack 64 bit 
  5. BizTalk Adapter Pack 32 bit 
  6. ASP.NET 

Note: For Win 8/ Win Server 2012 machine, ASP.NET4.5 has to be enabled at 2 separate places. 

    1. Windows 8:  
      1. Control Panel -> Programs -> Turn Windows Features On or Off –> Internet Information Services –> World Wide Web Services –> enable under Application Development Feature.  
      2. Control Panel -> Programs -> Turn Windows Features On or off –> enable under .NET Framework 4.5 Advanced Services. 
    2. Windows Server 2012: 
      1. Server Manager -> Add Roles and Features -> Server Roles Web Server (IIS) -> Web Server -> Enable under Application Development Feature. 
      2. Server Manager -> Add Roles and Features -> Features -> enable under .NET Framework 4.5 Features 
    3. For other Lower Versions: 
      1. For windows Server machine: Server Manager -> Roles -> Web Server (IIS) -> Add Role Services -> enable under Application Development Features  
      2. For Client machine: Control Panel -> Programs -> Turn Windows Features On or Off -> Internet Information Services –> World Wide Web Services –> Application Development Features  

       7. ISAPI Extensions 

    1. For windows Server machine: Server Manager -> Roles -> Web Server (IIS) -> Add Role Services -> enable under Application Development Features 
    2. For Windows 8/7: Control Panel -> Programs -> Turn Windows Features On or Off –> Internet Information Services –> World Wide Web Services –> Application Development Features 

       8. ISAPI Filters 

    1. For windows Server machine: Server Manager -> Roles -> Web Server (IIS) -> Add Role Services -> enable under Application Development Features 
    2. For Windows 8/7: Control Panel -> Programs -> Turn Windows Features On or Off –> Internet Information Services –> World Wide Web Services –> Application Development Features 

While anything missing from above list would prevent installation from succeeding, there are some additional pre-requisites for BAS to work correctly. The list includes 

  1. CGI 
  2. HTTPACTIVATION  

If the user is running into issues installing BAS, we should ask the user to run the setup with extended logging option “msiexec /i BizTalkAdapterService .msi /l*vx logFile.txt“. The log file will have more details on the exact cause of the failure. 

Troubleshooting

Common errors observed and reason for the same 

  • The setup fails unexpectedly, the log file shows the following error: “Error 26201. Error -2147467259: failed to create SQL database: BAService, error detail: unknown error”  

In the setup, it prompts for the SQL server/instance name, Make sure it correctly maps to the SQL server on your local machine/ the server you are targeting. 

  • The setup fails unexpectedly, the log file shows the following error: “CustomAction AddIsapiCgiFilter returned actual error code 1603 (note this may not be 100% accurate if translation happened inside sandbox)” 

This error generally prompts on Windows 8, 32-BIT machine. Please ensure ASP.NET 4.5 is installed on the machine. Please Install ASP.NET 4.5 and try re-installing the BAS setup. 

  • The setup fails unexpectedly, the log file shows the following error: ”Calling CustomAction SBConnectCA!Microsoft.ApplicationServer.Integration.AFConnect.Program.AddIspiCgiFilter 

Enable ISAPI/CGI filters: Enter 

ERROR: System.UnauthorizedAccessException: Filename: redirection.config 

Error: Cannot read configuration file due to insufficient permissions” 

Please try running the setup with Administrator Privileges. Right Click on the Setup file and “Run as Administrator” 

Recommended Installations

  • I have installed all the pre-requisites and the setup installed successfully. But still I cannot host any .svc files in my browser. Why? 

Please make sure that these three windows features are turned on your machine. These are required to serve the WCF .svc files. 

  • HTTPACTIVATION 
  • CGI 
  • WINDOWS PROCESS ACTIVATION 

Step by step procedure to connect to a SQL server from BizTalk Services Project

1)      After you have the BAS (BizTalk Adapter Service) setup installed in your machine. In Visual Studio, you can see a Server Explorer Component “BizTalk Adapter Services”

 

 

2)      Right click on BizTalk Adapter Service and click on “Add BizTalk Adapter Service…”

   

                Specify the server name on which your SQL data exists.

                If you are targeting the local system specify “localhost” in the highlighted.

 

3)      After establishing the connection you can see the following 5 LOB types.

           

Right click on the LOB type you want to connect to, SQL for instance.

 

4)       Right click -> Add SQL target..

Connection Parameters:

 

 

It throws this wizard and helps you in creating services for your on-premise LOB systems that can be accessed from the cloud.

 

Here specify the respective Sql Server database details:

Server: The server you want to target

Instance: The sql server instance you have in your machine, ex: sqlexpress

Catalog: The Name of the Database you want to access.

 

After entering these details Specify the respective credentials and click next..

 

5)      Operations:

Here I can select all the operations I can expose.

Consider the following EmpData table, here I want to expose all the 4 operations “Delete”, “Insert”, “Select”, “Update”.

Click on the desired operation and add it.

 

 

 

6)      Runtime Security:

This is the critical part. This is the runtime security.
The authentication that we specified in the Connection parameters is just the design time security. But this authentication specified here will be validated when we try connect to on-prem sql database. This Is the credentials that will be verified when a message come from the cloud.

 

We have 4 different options:

  1. FIXED USER: This is the SQL authentication that we provided in the machine.
  2. FIXED WINDOWS CREDENTIALS: You can provide a Domain, Username and Password to connect to the SQL Database.
  3. CUSTOM SOAP HEADER: The client as a part of message is going to sent a UserName and Password to connect to Sql Database. This will come as part of the SOAP header.
  4. MESSAGE CREDENTIAL: Client will send username and Password in WS-Security header of the message.

Select the respective secutiry type and click next.

 

 

7)      Deployment:

A single LOB relay can host multiple LOB targets.

If you have an existing LOB Relay you can use that. You can select an existing relay.

Or

You can create an entirely new relay.

 

 

 

If you want to create a new realy specify the 4 required fields: Namespace, Issuer name, Issuer Secret, Relay Path.

 

You need to provide a sub path to identify your target.

it shows the runtime URL based on the details we enter:

Format: “https://LOBrelayNamespace.windows.net/lobrelayRelativePath/TargetSub-Path”

 

Click next.

 

8)      Summary

It shows a summary of details you entered cross check them and click create to create the relay on the service.. Congratulations J

 You can see the LOB Target in the server explorer.

 Now create a BizTalk Services project and In the MessageFlowItinerary.bcs file just drag the required LobTarget..

 

 

 

Any message sent to the runtime Address path, shown in the propeties of the LobTarget properties, will be targeted to the On-Premise SQL DataBase you are connected to.

 

BizTalk Adapter Service PowerShell Cmdlets

PowerShell is a Windows command-line shell typically used by system administrators. The BizTalk Adapter Service Runtime Server utilizes the cmdlet (command-let) ability of PowerShell to expose the LOB Relay and LOB Target entities. Using PowerShell, BizTalk Adapter Service Administrators can manage the BizTalk Adapter Service Runtime Service and these LOB entities. The BizTalk Adapter Service PowerShell cmdlets can be executed directly on the BizTalk Adapter Service Runtime server or on any other server.

For further information on BAS PowerShell Cmdlets please refer here.

Blog Post by: BizTalk Blog

Debugging EAI Bridges with Tracking and Tracing – I

by | Jun 28, 2013 | BizTalk Community Blogs via Syndication

 Windows Azure BizTalk Service is a managed service which provides integration capabilities for the Windows Azure Platform to extend on-premises applications to the cloud, provide rich messaging endpoints on the cloud to process and transform messages, and help organizations integrate with disparate applications, both on cloud and on-premises. The core entity of BizTalk Services is a bridge that does all the processing. You may need to see what all operations were executed on the message inside the bridge or in case of a fault what went wrong. For debugging and troubleshooting of bridges in EAI scenarios, we have two broad types of information:

 1.  Operational Tracking – stage execution and message properties information

2.   Tracing – activity  execution information stored in Azure WADLogsTable.

 For most scenarios, Operational tracking should provide enough information to troubleshoot the issue. Tracing contains unstructured data and will contain a lot of information which is not directly related to message processing. But it is useful to troubleshoot those hard to fix issues, and most likely, if you report an issue to Microsoft support, they will ask for these traces.

 In this post we’ll look at Tracking and Tracing will be covered in detail in the next post of this series.

 

Getting started

To get started you would need to create a BizTalk Services Deployment and Download the SDK.

Let’s take a simple scenario where you have an input purchase order message that has to be sent to one of the multiple warehouses based on the city. The warehouse information per city is stored in a database table as shown below:

City

Warehouse

Bellevue

W1

Redmond

W2

Cincinnati

W3

 

For this we are going to use BizTalk Services One-Way Bridge receiving messages from an FTP server and sending to one of the two Sql LOB destinations as shown in Figure 1. You can use the sample project attached to this post. More details on the project are given below.

In a new BizTalk Services Project, an FTP source is dropped onto the design surface in the .bcs file and configured. Then we drop a one way bridge and two Sql LOBs (go here for more details on how to do this). We would need to promote some properties that can be used in the route filters to choose the destination. 

 So we promote two properties on the input – ‘City’ using Xpath and ‘Warehouse’ using SQL Azure Database Lookup on ‘City’. The bridge is connected to the destinations using connectors and the route filters are configured with conditions like Warehouse=’1’ as shown in the Figure.

 

Figure 1 Sample Scenario showing a bridge routing to SQL LOBs using filter condition WareHouse=’10’

 Once the above project is successfully deployed to the Azure BizTalk Service, let’s say a message is dropped to the ftp folder with value of city being ‘timbaktu’. No new rows get added to the destination sql table and we need to debug the issue. For this we’ll make use of the tracked data available to us via the BizTalk portal. 

1 Tracking View in BizTalk Services Portal

When you drop an http message to a bridge and get a fault back or in case of FTP/SFTP sources when no output is seen in the destination, then the first place to be visited for troubleshooting is the Tracking section of the BizTalk Portal. Go here for details on how to reach this tracking section. You would see two types of events – informational and error.  The tracked data in the view can be filtered using advanced search options available in the search textbox dropdown.

 

 Figure 2 BizTalk Portal showing the tracking section. 

2 Track records  

  Track records capture the information of message processing events like route, artifact retrieved, custom code and faults. The records show up in the BizTalk portal tracking section as seen in Figure 2. Select any row and click “Details” button on the bottom to see the more information for the track record as shown in Figure 3.

 

Figure 3. Track record showing the route stage processing information along with the promoted properties.

 Each message has a unique RequestId associated with it which is returned back to the client and can be cited for reference to the Microsoft support in case of any error.  TrackId is same as RequestId and is regenerated when a message moves to another pipeline as part of chaining  or EDI disassemble.

 Debugging our sample

 Let’s revisit our scenario and look at the tracking section of the portal for errors. We see an Error event here with activity information as “dataLookupPropertyExtract : Activitystatus” and clicking on the Details button we see a track record showing Error as “Lookup returned no results”. This means that our city matched no warehouse in the database and hence lookup failed.   You can see the track record containing a property with name as ‘City’ but none with name as ‘Warehouse’.

 Taking another example, let’s say the city is changed to ‘Cincinnati’ and the file is dropped to FTP again. This time too no new rows appear in the destination table. In order to find out what happened we go to the tracking portal which shows an error “No Filter matched for the message”.  Looking at the details of this track record we see that the value of Warehouse property does not match the filter conditions of any of the destinations and hence this error as shown in Figure 4.

 

   

 Figure 4.a Fault Track records from the Route stage showing the promoted properties   

Figure 4. b. TrackSubmitted Fault record from FTP source

You can even drop the messages directly to the bridge using message sender tool and in this case you would receive a fault back with the same error.

Now let‘s look at some more aspects of tracking.  

3  Tracking configuration

 

Figure 5. Track Properties on the bridge in the BizTalk Service Project

By default only a fixed set of records like route success, custom code calls and faults are tracked. In order to add more information to be tracked, the design surface in the BizTalk Service project exposes a property “Track Properties” on each Bridge. Click the ellipses against this property to launch the dialog as shown in Figure 4. You can choose one or more of the below options in the dialog:

  1. Track Message Processing events – emits informational records to the tracking database. For more details refer to section 5.
  2. Track all message properties – select all or some of the promoted properties to be shown in the track records.

 

4  Tracking Database

The tracking information shown in the portal is backed by the tracking database. When you created the BizTalk Service Deployment, you would have created this database in Azure where all the tracking related data goes.

The information in the database can be used for viewing pipeline and source track records. You can write analytics based on the tracked data like total number of incoming messages, the no. of successful messages as well as the promoted properties.

The tracking database exposes a GetTrackRecords stored procedure that can be used for retrieving the track records as well as automation of failure notification. The main tables in the database are:

 

Table

Comments

EndpointAddressMap

Contains mapping of Bridge Endpoint Name to Endpoint Id which is used in the PipelineTrackRecords table.

PipelineTrackRecords

Contains the track records with names of stages, activities and the status of each.

TrackRecordRecordProperties

Contains detailed status for events with Parent Id corresponding to Id of PipelineTrackRecords record.

TrackRecordMessageProperties

Contains user defined and default promoted properties like X_PIPELINE_REQUESTMESSAGETYPE and X_PIPELINE_MESSAGETYPE

 Figure 6. Tracking Database example showing contents of PipelineTrackRecords Table.

 

5   Pipeline and Source Track Records 

Track Records can be emitted by the Pipelines as well as FTP and SFTP Sources. Pipelines can emit below information and error events based on the configuration:

     1.  Pipeline started

     2.  Pipeline completed

     3.  Stage started – emitted for all the Enabled Stages like xmlvalidator, requestMessageExtractor, requestMessageTransform, transformedResponseEnrich, route, sendReply and others.

     4.  Stage completed

     5.  Artifact Retrieved – for artifacts like schemas and transforms

     6.  Custom – emitted from custom code inspectors when they are set.

     7.  Route – show the route status for the message ie faulted or success.

     8.  Faulted – when any error is thrown like lookup failed, validate failed

 

With “Track All Message Processing Events” option set to true all the above events get saved in the tracking database, but the BizTalk Portal Tracking section will only show last four events. For EAI scenarios, FTP and SFTP Sources emit below events to the tracking database.

1. TrackSubmitted – emitted when the message is submitted to the pipeline

2. TrackProcessed – emitted when the message is deleted from the source. Faulted when delete fails as in case of read only user.

3. TrackStatus – emitted on source start and stopped. Faulted when service cannot connect to the FTP server.

4. TrackRetrieved – error event is emitted when the file cannot be retrieved from the server in cases like it being a subfolder inside the FTP folder. Error looks like ‘The remote server returned an error: (550) File unavailable (e.g., file not found, no access)’.

5. Poll Error – emitted when one of the above errors happen. The poll interval in increased to 1.5 times on each poll error in order to give time to rectify the issue.

6. Listing error – emitted when the listing of the files fails eg when the configured FTP folder is deleted 

The Tracking portal will only show error events for the sources.

6  Summary

In this post we have gone through the details of tracking portal and the tracking database and looked at the various track records useful for debugging. And if that seems insufficient to investigate the issue further, for instance with custom code, when tracking portal does not show the error stack traces. Then you can go to the traces stored in the WADLogsTable of the monitoring store. We shall see how to do this using Tracing in the next blog.

Blog Post by: BizTalk Blog

Visio 2010 Stencil for BizTalk Server Updated with 63 new modern flat shapes

Visio 2010 Stencil for BizTalk Server Updated with 63 new modern flat shapes

by | Jun 27, 2013 | BizTalk Community Blogs via Syndication

This week I published 73 modern flat shape that will help you representing BizTalk Server physical architectures, Integration architectures (Cloud or Hybrid scenarios) or solutions diagrams in Visio 2013. However I found out that many people are still using Visio 2010, so I thought it would be a good idea to provide these new shapes […]
Blog Post by: Sandro Pereira

Where is my BizTalk Server 2013 Developer Edition?

by | Jun 27, 2013 | BizTalk Community Blogs via Syndication

One of the question we normally hear in the community and with various BizTalk customers is "Where is my Developer Edition for BizTalk Server 2013". In the past versions of BizTalk Server (2009, 2010 etc),  there was  a special edition for developers used primarily for development purposes. Prior to BizTalk Server 2010, the cost for […]

The post Where is my BizTalk Server 2013 Developer Edition? appeared first on BizTalk360 Blog.

Blog Post by: Saravana Kumar