28 June 2011

Windows Azure Service Bus and Line of Business Integration with AppFabric Connect

I’ve been working on a demo to present at a Microsoft practise meeting which is going to be held in a few days at the consulting firm where I’m employed. What started out as a simple demo turned into an enhanced solution so I thought I might as well wrap things up by a blog post to document what I’ve done.

This blog post covers the following .NET middleware technologies:

  • Windows Azure Service Bus
    • Relay Service
  • Windows Server AppFabric
    • Monitoring / Persistence
    • Distributed Cache
  • AppFabric Connect
  • WCF Routing .NET 4.0
  • Windows Workflow 4.0

The source code for the solution can be downloaded here.

The scenario is as follows: image

1. AzureLOB.SubmitOrderClient - A WPF application is used by consumers to fill out and submit order requests via he Windows Azure Service Bus to an on-premises IIS hosted WCF Service. I’ve taken the client application from the Windows Azure Training Kit (Connecting a WCF Service in IIS 7.5 to the Service Bus), but modified it to suit this scenario.


2. AzureLOB.IISHostedWcfService / AzureLOB.WcfRoutingService – A WCF Service hosted in IIS must be configured to use Windows AppFabric Auto-Start feature in order to start receiving messages from the Service Bus in the cloud (the Relay Service). Wade Wegner explained in detailed in his blog post; Host WCF Services in IIS with Service Bus Endpoints, so this is out of scope for this article.


Upon receiving a request from the Service Bus in IIS, the request is simply forwarded to a WCF Routing Service. Based on the quantity ordered (Content Based Routing), the WCF Routing Service is responsible for routing the request to one of two WCF Workflow Services. You can read more about WCF Routing in this blog posts; WCF Routing Service Deep Dive: Part II–Using Filters, by Richard Seroter.

The routing service routes messages based on the following filter expressions in web.config.

  <filter name="OrderAcceptedFilter" filterType="XPath" filterData="//custom:Quantity &lt;= 100" />
  <filter name="OrderApprovalFilter" filterType="XPath" filterData="//custom:Quantity > 100"/>

You may have noticed that there is room for improvement here. I would rather have the WCF Routing Service exposed to the Service Bus directly, without having a service fronting it, however I haven’t manage to solve this just yet.

3, AzureLOB.OrderAccepted.WorkflowService – Order requests with quantity less or equal to 100 are routed to an “accepted” Workflow Service. This WCF Workflow Service sends a response back to the client, via the Service Bus, indicating the id and status (ACCEPTED) of the order.


The workflow saves its state to the Windows Server AppFabric Persistence Store just before sending the response back to the client. Later, if an exception occurs during the life cycle of the workflow, for instance if the downstream system is temporary unavailable, it is possible to resume the workflow from this execution bookmark once the downstream system is back up again.


Enable AppFabric Persistence for Workflow in IIS.


Resuming a suspended workflow instance from the AppFabric Dashboard in IIS.


The workflow ends by committing the ACCEPTED order to the database.


4. AzureLOB.OrderApproval.WorkflowService – Order requests with quantity greater than 100 are routed to a long-running WCF Workflow Service. Again this workflow persists itself to the AppFabric Persistence Store before sending the id and status (PENDING) of the order back to the client.


Orders routed to the order approval workflow must manually be finalised. The long-running workflow initially submits the order with a PENDING status to the database.


Using correlation the workflow then waits until a subsequent order approval message (submitted by the Approver in the diagram) is received.


Pending order awaiting approval.


You can read more about correlation in workflows here: Correlate Multiple Receive Activities.

5 – 6. Both WCF Workflow Services use AppFabric Connect to communicate (insert and update) with the database. You can read more about AppFabric Connect here: BizTalk + WF/WCF, Better Together.

7. AzureLOB.OrderManagementClient – The Approver sends a request to a WCF Service to retrieve a pending order for approval.  


8 – 9. AzureLOB.CachingService – The caching service is a WCF Service that utilises the AppFabric Distributed Cache to maintain a collection of cached pending order objects. The WCF Service initially makes a call to check if any pending orders exist within the cache. If no pending orders are found the WCF Service populates the cache with a collection of all pending orders retrieved from the database. The client application will subsequently only receive cached objects until no pending orders are present within the cache. The WCF Service will then need to make a new call to retrieve orders with status PENDING from the databases and populate the cache.


When using the AppFaric Cache you should keep in mind that the database is always your source of truth and that the cache cannot be relied on as a secure store provider, it is a non-transaction cache. When designing something like the above you should take into account that a possible unexpected failure might occur within your solution.

10. AzureLOB.OrderManagementClient – A pending order is either accepted or declined and submitted to a correlating workflow instance to be finalised.

11. AzureLOB.OrderApproval.WorkflowService – The workflow updates the order to its final status (ACCEPTED/DECLINED) in the database. Ultimately, this implies that the end of the long-running workflow and closing the pending order.


Please understand that this is a demo. Some portions of the solution might be overkill or implemented in an easier way. For instance there is really no reason for having two workflows in this scenario. What I’m trying to demonstrate here is the ability to do content-based routing in WCF and show how backend Line of Business systems like SQL Server, SAP, Oracle, and Seibel can be fronted with workflow services to ensure reliable messaging. Using AppFabric Connect the demo can easily be extend to send all accepted orders to an Oracle database, for instance.

I’m playing around with the idea to push the order messages to an AppFabric Queue instead of using a Relay Service and perhaps make use of the Azure AppFabric June CTP to ”cloudify” the solution (some question marks here and there but I’m sure it’s solvable).

What you need to run the demo:

  • A Windows Azure Account and a registered AppFabric Service Bus Namespace
  • Visual Studio 2010
  • SQL Server 2008 or later with Chinook database. A colleague of mine is using the Chinook database to do a demo on SQL Azure and we thought it would be nice to use the same database. You can download the Chinook database here, you then need to execute the SQL script in the AzureLOB solution to create the Order table.
  • IIS 7.5
  • Windows Azure AppFabric SDK V1.0
  • Windows Azure SDK 1.4
  • Windows Server AppFabric with Persistence Store and Caching Service configured
  • BizTalk Server 2010 with AppFabric Connect installed

13 April 2011

Archiving BAM Data

BAM is intended to provide information about the status of active business processes; it’s not recommended to keep historical data in the BAMPrimaryImport database. Instead, old data should be moved out of BAMPrimaryImport and into the BAMArchive database. Each time the bm.exe is use to deploy a new BAM activity, a SQL Server Integration Services (SSIS) package called BM_DM_<Activity> is created.

The SSIS package have two functions:
  • It partitions the data in BAMPrimaryImport.
  • It moves old data from BAMPrimaryImport into the BAMArchive database.
Data is partitioned by creating new tables in BAMPrimaryImport to contain activity data. When the archive package runs, it partitions the activity tables by creating additional tables called bam_<activity>_<GUID> and bam_<activity>_<GUID>_ Relationships. Partitioning data into separate tables is a performance improvement, especially for real-time aggregations. It also simplifies the archival process. When the package runs, it looks for partitions that are available to archive, and processes the entire partition.

Caution If you write code against the BAM Primary Import database, it’s essential to use the views, not the underlying tables. If you write code directly against the underlying tables, BAM data may disappear when the job runs and partitions the table.

A partition is available to archive if all scheduled views that depend on the partition have been processed, and the partition is older than the online window for the activity. By default, the online window for an activity is six months. The online window can be changed by modifying the values of the OnlineWindowTimeUnit and OnlineWindowTimeLength columns in the bam_Metadata_Activities table in BAMPrimaryImport.

It’s important to bear in mind that the SSIS package only archives partitions older than the online window. As an example, imagine an activity where the archive package has never been run. There is a year’s worth of data in the BAMPrimaryImport database, but there are no partitions. The online window is the default: six months. Now consider what happens if you schedule the archive package to run each day. The first day the package runs and creates a partition table in BAMPrimaryImport. This partition table contains a year’s worth of data. However, there are no partitions older than six months, so no data is removed from BAMPrimaryImport. The next day, the package runs again, and a second partition table is created. This partition will contain one day’s worth of data. The partition table created the previous day is only 24 hours old, and so it will be left in BAMPrimaryImport. If the online window remains six months, it will be a full 6 months after the job is scheduled before any data is removed from BAMPrimaryImport, but then a full year of data will be removed in one day.

There are two design decisions to make when setting up archiving: the length of the online window and how often the archive job runs. The length of the archive window is driven by business needs for historical data. The archive job should be run on a regular basis. Running this job daily or weekly is a good choice.

4 February 2011

Windows SharePoint Foundation 2010, BizTalk - WSS Adapter Web Service and the BAM Portal

In this post I’m giving a detailed description on how the BizTalk - Windows SharePoint Services adapter Web service can be hosted on the same machine as the BAM Portal. I will walk through the process of installing Windows SharePoint Foundation 2010 and creating a SharePoint Web Site that will contain the BizTalk - Windows SharePoint Services adapter Web service. This kind of setup is practical on a developer machine and allows working with both BAM development and SharePoint integration in an isolated environment.

BizTalk Server requires Windows SharePoint Services (WSS) to support the Windows SharePoint Services adapter Web service.  BizTalk Server 2010 supports two versions of WSS:

  • Windows SharePoint Foundation 2010
  • Windows SharePoint Services 3.0 with SP2
How to install Windows SharePoint Services 3.0 with SP2 is described in detailed by Sandro Pereira, BizTalk 2010 Installation and Configuration – Install and Configure Windows SharePoint Services (Part 6).
Steef-Jan Wiggers has written Installing Windows SharePoint Services Web Service Adapter BizTalk Server 2010/SharePoint Foundation 2010. This post describes a Windows SharePoint Foundation 2010 Standalone installation and does not demonstrate how to have the BizTalk - WSS Adapter Web Service and the BAM Portal hosted on the same machine.
Also worth looking at is the BizTalk 2010 Installation and Configuration Guide - Development and Test Build whitepaper published by St. Louis Microsoft Connected Systems Working Group. This whitepaper describes a Server Farm installation of Windows SharePoint Foundation 2010 but again not with BizTalk - WSS Adapter Web Service and the BAM Portal hosted on the same machine. The whitepaper suggests installing BAM components which gives you the BAM API and BAM databases to store events, and then creating your own portal in SharePoint, ASP.NET, etc. to display BAM information. However I believe for many projects the best choice is to provide the BAM Portal to data consumers, and then consider building a custom solution to view the data later when the users ask for additional functionality that the portal can’t provide.
Windows SharePoint Services support in BizTalk Server 2010 with Windows SharePoint Foundation 2010 is summarized in the following table:

Windows SharePoint Services version
WSS adapter
BAM portal
SharePoint Foundation 2010
64-bit only
32-bit only.

I’ve copied the table directly from the Installing BizTalk Server 2010 on Windows Server 2008 R2 and 2008 installation guide. There is some really useful information in Appendix A so make sure to read the documentation carefully.

I’m running a Windows Server 2008 R2 x64 machine and I’m connected to Active Directory. I have BizTalk Server 2010 already installed and BAM Portal hosted in Default Web Site. Application Pool Defaults is set to 32-bit mode in IIS. Windows SharePoint Foundation 2010 requires IIS and ASP.NET to run in 64-bit mode. To do this, follow these steps:
1.    Click Start, Administrative Tools, and then click Internet Information Srvices (IIS) Manager.
2.    In the Connections pane, expand the server name, and then click Application Pools.
3.    In the Actions pane, click Set Application Pool Defaults…

4.    On the Application Pool Defaults dialog box, in Enable 32-Bit Applications, select False.

Install Windows SharePoint Foundation 2010

1.    Browse to http://www.microsoft.com/downloads/en/details.aspx?FamilyID=49c79a8a-4612-4e7d-a0b4-3bb429b46595&displaylang=en. Download the SharePointFoundation.exe installation package to a location on your hard disk.
      2.    Locate the package you just downloaded and double-click the package to start the setup.
3.    Click Install software prerequisites.

4.    Click Next.

5.    Accept the terms and click Next.

  6.    Click Finish.

7.    Click Install SharePoint Foundation.

8.    Accept the terms and click Continue.

9.    Click Server Farm

10.    Select Complete and click Install Now.

11.    Click Close.

12.    Click Next.

13.    Next you will see the popup below. Click Yes.

   14.    Select Create a new server farm, then click Next.

15.    Add the server name of BizTalk SQL Server, specify database access account and then click Next.

16.    Enter your Passphrase and then click Next.

17.    Accept the default port, select NTLM and then click Next.

18.    Review and then click Next.

19.    Click Finish.

20.    Select No, I don’t wish to participate and then click OK.

21.    Click Start the Wizard

22.    Fill out the form and click Next

23.    Click Skip.

24.    Click Central Administration and then Manage web applications.

25.    If a default web application has been created on Port 80 highlight the application and Delete.

26.    Select Delete content database and Delete IIS Web sites, then click Delete.

27.    Click on New.

28.    Next we'll create a new Web Application called "SharePoint – 90" in IIS so that the WSS site does not interfere with the BAM Portal living in the Default Web Site. We also modify the port to be "90" which means any requests to the WSS site will need to have ":90" in the URL. When posting or retrieving documents to/from SharePoint from BizTalk his port number will need to be included in the Receive Location/Send Port configuration.

29.    Note that “:90” makes up part of the URL. We can also choose to create a WSS dedicated App Pool called “SharePoint – 90” so it is clear when determining which IIS Web Site uses this App Pool.

30.    On Database Name and Authentication options, appended “90” onto the Database Name that will be used for storing Content and choose “Windows authentication” option.

31.    Leave rest of the form with default options and click OK.
32.    We have now created the “shell” for this Web Application, but we don’t have any content in this Web Application. Click Application Management link and then Create site collections.

33    Give the site a Title and a Description (if so desired).

34.    If desired, create a URL for the new top-level Web site, I left mine with the default URL. In the Template Selection option select Team Site and indicate users to administrate the site, then click OK.
35.    Team site has now been created. Click on the link to verify that it was created successfully.

36.    Looking in Internet Information Srvices (IIS) Manager we should see the Default Web Site and the SharePoint Web sites.

37.    At this point make sure that you can browse the BAM Portal. If not make sure that the Defaul Web Site is started (In IIS Manager right click Default Web Site, Manage Web Site and click Start).
Setup BizTalk WSS Adapter Web Service

Once Windows SharePoint Foundation 2010 is installed it is time to run through the BizTalk install. As mentioned, I already have BizTalk installed on my machine, so I am simply going to modify my existing installation.
1.    Start Microsoft BizTalk Server 2010 installation wizard and click Install Microsoft BizTalk Server 2010.

2.    Select Modify.

3.    The BizTalk install process will determine if WSS is installed and if so we have the ability to check the “Windows SharePoint Services Adapter Web Service” check box and install the BizTalk Adapter Web Service.

4.    Click Finish.

5.    Select to configure the SharePoint Adapter.

6.    Check “Enable Windows SharePoint Services Adapter on this computer”. Use the drop down list to select the WSS Site where you would like the adapter to be installed. Click Apply Configuration. Make sure to add the user of the Host instance, which will operate this adapter, to the “SharePoint Enabled Hosts” group.

7.    Click Next.

8.    Click Finish and exit the Microsoft BizTalk Server Configuration wizard.

9.      If you launch IIS and navigate into the WSS Site (SharePoint 90) that you created you will find that BizTalk – WSS Adapter Web Service is now installed. The BizTalk WSS Adapter will leverage this Web Service when calling SharePoint.

It is important to note that this post describes the installation of the BizTalk - WSS Adapter Web Service on a developer machine. You only need WSS installed on your BizTalk servers if you plan to host WSS sites on your BizTalk servers. If you plan to connect to remote WSS or MOSS servers you do NOT need WSS installed on your BizTalk Servers. The BizTalk Windows SharePoint Service Adapter Web Service must be installed on the server where Windows SharePoint Services is installed. However by following the steps described in this post you can have a SharePoint site hosted on your development machine along with the BAM Portal. This setup allows working with both BAM development and SharePoint integration in an isolated environment.