Sunday, October 30, 2011

BizTalk Server 2010–Tools

I have created an article for TechNet Wiki called BizTalk Server 2010 – Tools and it is a compilation of all tooling available through Codeplex or Microsoft Download Center. These tools are not provided out of box with BizTalk Server 2010, but can be of tremendous help in debugging, logging, testing, troubleshooting, documenting solution, and performing health checks and benchmarking of your BizTalk environment. The number of tools listed and of codeplex projects pretty much covered in the article. Thanks to Colin Meade the Orchestration Profiler can be of used with BizTalk 2010. If you feel there is a tool that I have missed, which can be beneficial in any way, please feel free to add comment to the article.

Cheers !

Sunday, October 23, 2011

BizTalk Server TechNet Wiki Articles

The TechNet Wiki is a library of information about Microsoft technologies written by the community for the community. Through TechNet Wiki you will find the following BizTalk Server 2010 articles:

Hopefully you find one of more article that are useful for you to learn about capabilities of BizTalk or new ways to integrate with BizTalk.

Cheers!

Thursday, October 13, 2011

BizTalk User Group Sweden : My debut as International Speaker

Next week a new BizTalk User Group Meeting in Sweden is scheduled with two sessions:

  • A Lap Around BizTalk Adapter Pack
  • A Lap Around BizTalk360

The first session will be done by me and it will be first time I will do a session in English in another country. The other session is done by Saravana on BizTalk360. It will be second time both me and Saravana will share the same stage. Last time was June this year for the Dutch BizTalk User Group.

There are still seats left for this event, so if you want you can attend; it is in Stockholm and I expect many if it not all from Sweden. My session will be talk about BizTalk Adapter Pack, its evolution how it became the pack that is shipped along with BizTalk 2010, its origin, alignment with Microsoft Platform, Cloud and an integration scenario with Oracle. Saravana will take you through his BizTalk360 product showing a lot of its capabilities and how you can leverage them in a BizTalk production environment.

For those who will attend I am looking forward to meet you and present my story on the BizTalk Adapter Pack.

Cheers!

Monday, October 10, 2011

BizTalk360 new V2.5 release–Message Box Viewer Integration

Saravana has released new version of BizTalk 360, a version 2.5 release with following important features/enhancements:

  • Role Based Security
  • Knowledge Base Repository
  • Message Box Viewer Integration
  • Message Detail (Content/Context)
  • Alert/Notification Enhancements

It shows that BizTalk360 has more monitoring and troubleshooting capabilities. In this blog post I will focus on troubleshooting capability: MessageBox Viewer Integration.

Many of you have heard of the BizTalk MsgBoxViewer tool that has been around for some time. It has been created by Jean-Pierre Auconie. The tool runs a set of tests and queries against the MessageBox database of your BizTalk Server. The results are displayed in the tool and can also be exported as HTML reports including links.

image

I wondered how this integration works with BizTalk360.After installing BizTalk360 V2.5 I looked for the feature. I clicked Settings –> Configure MBV Integration.

image

You will see that you need to install the MessageBox Viewer first as licensing restrictions prohibit bundling with BizTalk360. On BizTalk360 the steps are explain for configuring MessageBoxViewer. If you do not follow these steps you will see error like below:

image

and nothing will appear in BizTalk360 Message Box Viewer – Reports!

image

If you do have accept the EULA, but still see no reports than check if key below exists.

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\MBVConsole\EULA]
"EULAAccepted"="YES"

In my x64 environment it set the following key after accepting EULA of MBVConsole (and not the above):

[HKEY_LOCAL_MACHINE\SOFTWARE\MBVConsole\EULA]
"EULAAccepted"="YES"

After configuration you set schedule (day(s) and time(s)) and the click Save.

image

I set schedule on everyday at evening/night on 21:00 – 06:00 (as a test, later I rescheduled to only 21:00). And soon as MBV has run you will see report appear like below.

image

There is more besides having a report and that is BizTalk360 also allows you to send emails based on MBV report thresholds. For instance if in the last run MBV reports a couple of critical errors (let’s say 5), you can configure BizTalk360 to send a notification!

image

This is just one of new features of BizTalk360. There is more to explore through Feature Tour.

Cheers!

Thursday, October 06, 2011

BizTalk Adapter Pack 2010 WCF-Custom with sqlBinding Table Operations through Messaging solution

Last year I did a post on table operations using WCF-SQL Adapter as part of Adapter Pack 2.0. This post was targeted for BizTalk 2009 and I now like to do an update targeting BizTalk Server 2010. With BizTalk Server 2010 (DVD/ISO) comes the adapter pack that bundles old (non-WCF based) Enterprise Adapter for BizTalk with new (WCF-based) adapters to support compatibility with different versions of Line Of Business systems (SAP, Siebel, Oracle, Oracle eBusiness Suite). The BizTalk Adapter Pack 2010 includes support for new versions of the following line-of-business applications and databases: SAP 7, Oracle EBS 12.1 and SQL Server 2008 R2. The Adapter Pack also provides support for Visual Studio 2010 and .NET Framework 4. For documentation see MSDN BizTalk Server Adapter Pack 2010 Documentation and interactive poster.

Installation is straight forward and made simpler through following steps in a wizard and adjusted to installing on x64 (you can read that in post “Installing BizTalk Adapter Pack 2010 on x64”). After installation you will need to add adapter manually if you want to use SQL adapter through a WCF-SQL port.  The SQL adapter can be also used in BizTalk as a WCF-Custom port. If you want to use the SQL adapter through a WCF-Custom port, you do not need to add the WCF-Custom port to the BizTalk Server Administration console, because the WCF-Custom port is added to the BizTalk Server Administration console by default. When you use Consume Adapter Service Tool in BizTalk Project it will create xml schema’s based on operation contract (inbound or outbound) and WCF-Custom binding file.

To perform operations on SQL Server through messaging by using the WCF-Custom adapter, you must perform a set of design-time and run-time tasks using Visual Studio and BizTalk Server Administration console respectively. The following high level tasks are required to create a message based solution:

  • Create BizTalk project, and generate schema.
  • Create messages instances.
  • Build and deploy the BizTalk project.
  • Configure the application.
  • Start the application.
BizTalk Project and generate schema (‘s)

First, we need the XML schemas, which represent our data source. By installing the WCF LOB Adapter SDK, you will have a metadata browser built into Visual Studio. NET at your disposal. You can access this from a BizTalk project by right-clicking the project, choosing Add then Generated Items and selecting Consume Adapter Service.

image

This action opens a new window that provides us the interface to connect to, browse, and select database objects for service interaction. The very first thing we need to do here is choose the sqlBinding as the service binding, and then configure a connection string. The simplest working connection string consists of an Initial Catalog value for the database, and a valid Server name entry. Note that the adapter now uses a connection string in the form of a URI instead of the traditional Data Source=;Initial_Catalog=;User_ Id=;Password= style.

image

Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories.

image

On the root "/" character a range of un-typed generic functions are displayed, which can be exploited. Click on tables and you will see a number of Tables, select Products.

image

Click the Delete, Insert, Select and Update statement in available categories and operations. Click the Add button to add the operations. After clicking the Ok button at the bottom, schemas (and a single binding file) is added to our associated BizTalk project in Visual Studio.NET.

image

The binding is required later on, when solution is deployed and ports have to be configured for SQL Server (i.e. Send Port). This binding has to be edited for each operation (Select, Update, Insert, and Delete), because there is no corresponding logical ports as this a complete message based solution (if you keep original binding file and import in than you will run into error described in this post Why does the Adapter say ‘Action is not understood’ even though I am using the binding file generated by the ‘Consume Adapter Service’ wizard?). I choose to create four new binding files, each a copy of original, but all with a single operation in BtsActionMapping.

image

Create message instances

Next step is to generate messages from generated schema, which can be routed from Recieve location in Receive Port to a send port with WCF-Custom Adapter. Generating the first message is easy, as you can right click on the TableOperation.dbo.Products.xsd and select Generate Instance and an instance of top element is created (e.g. Select).

image

You will get a instance of message as depicted below:

image

By opening up the TableOperation.dbo.Products.xsd in file-editor you can move the next operation to top, so you can then in Visual Studio generate the next instance for an operation or you can use XML-Editor inside Visual Studio (closing schema, right click and select open with… and then select XML Editor) to move elements around. In end you will have four xml instance for TableOperations, save to file as xml (type all files, encoding UTF-8!).

Build and deploy the BizTalk project

Only schemas are required for a messaging only scenario with WCF-SQL Adapter to perform operations on SQL Server. Therefore after assigning a strong name and application name to BizTalk project it can be build and deployed.

Configure the application

After solution has been deployed it is time to configure the ports. FILE ports are easy and focus here will be configuring the WCF-Custom Send Ports. Through BizTalk Management Console one can browse to BTS.SQL.Table.Operations application and right click the application and select Import Bindings … and navigate to file called WcfSendPort_SqlAdapterBinding_Custom_Select.bindinginfo.xml, select and click Open. You will notice that a Send Port will be created automatically. You can repeat the import for binding:

  • WcfSendPort_SqlAdapterBinding_Custom_Delete.bindinginfo.xml
  • WcfSendPort_SqlAdapterBinding_Custom_Insert.bindinginfo.xml
  • WcfSendPort_SqlAdapterBinding_Custom_Update.bindinginfo.xml

Next steps are setting filters on WCF-Send Port and File Send Port. Configuration is as follows:

image

Start the application

In the end your messaging solutions can be depicted as below. After configuration you can start BizTalk application. Schema below gives an overview what will happen once drop message in folder (e.g. In folder).image

I hope with this post I was able to show all operations (CRUD) on table. The sample from MSDN shows only SELECT operation and here I have extended on that, showing all operations and how to setup WCF-Custom Adapter using sqlBinding and steps to do, before you can execute all operations on a SQL Table.

You can find sample for this post through MSDN Code Gallery: BizTalk Table Operations – Messaging

Cheers!

Monday, October 03, 2011

BizTalk Operations Using a Web Console

One of features that BizTalk lacks is a Web UI for operating BizTalk. There is a BizTalk Administration Console that enables BizTalk operators and Administrators to deploy and manage BizTalk Server applications and group(s). The console is basically a Microsoft Management Console (MMC) you can start from Server where BizTalk is installed or remotely on a different machine, but it is not web based. To be able to manage BizTalk from a Web UI has been a desired by BizTalk professionals for quite a while (as in years!).

I think many of you by now have heard or seen BizTalk360, a web based (RIA) BizTalk monitoring/support tool for Microsoft BizTalk Server. BizTalk360 is product created by Saravana Kumar a fellow BizTalk MVP and it is a commercial tool that gives you remote access to BizTalk Group, and gives a nice web based (Silverlight) UI! I have seen this tool in early stages during MVP summit in Redmond last February and it has evolved into great tool, feature rich with an excellent User Experience (see latest post by Kent BizTalk: Adding BizTalk 360 to your Environment). BizTalk360 can be an alternative to SCOM using BizTalk management packs.

There is another alternative if you desire a Web based UI, but do not or wish to use a product like BizTalk360. That is BizTalk Web Console you can download since this week from Codeplex and it was developed by Abdul Rafay another fellow BizTalk MVP.

As I did not see it in action I was interested to see what it can do. According to description it allows administrators/operators to perform the following operations from the browser:

  • Stop/Start/Restart Host Instances
  • View Status of Service Instances by applying queries to the group.
  • Stop/Start/Unenlist Orchestrations, Send Ports and Send Port Group
  • Stop/Start Receive Locations

I download the tool and installed it on my VM with BizTalk 2010 according to the release notes. I then opened up the BTS2010WebConsole in my browser and started some operations like Stop/Start/Restart Host Instances. That worked without a problem.

image

I then tried some others operations and they worked fine too.

image

These basic operations are available from the browser and the UI is very basic and straight forward. The number of operations is still limited though and resembles a subset of all capabilities the BizTalk Administration Console offers.It is a start and for those who want to have custom web based access to their BizTalk group they can leverage this project to their own needs. If you do have enough budget in your organization and prefer to have an off the shelve product than BizTalk360 is definitely a product you should consider!

Sunday, October 02, 2011

Polling SQL Azure Database Table with WCF-SQL Adapter

I have seen polling related questions sometimes on the BizTalk forums and I myself occasionally have come across a scenario’s where I had to use polling. In past I have read a couple of posts on polling like the one from Richard (TypedPolling) and Armen (Polling). Now with SQL Azure I wondered if it is possible to poll a SQL Azure Table.

If you have an account with Windows Azure Portal than you can create 1 Gb database online. You can go for a free trail (90 days), or through MSDN account (you receive quite a lot of benefits with Windows Azure), or commercially (see pricing). In a previous post I showed some steps how to create a new SQL Azure database.

You can create a database through Windows Azure Portal or SQL Management Studio. If you connect to SQL Azure through Management Studio you need to use SQL Server Authentication, fill in the server name, and credentials.

image

Once connected you will in the left pane see Azure database you connect to.

image

When you click New Query a query windows will appear on the right. Here I executed the following statement:

USE [master]
GO

CREATE DATABASE [CustomerOnlineDb]
GO

In left pane you will see new database in databases tree. Select that database and click New Query again, a new query pane will appear. Here I executed the following statement:

CREATE TABLE [dbo].[CustomerAddress](
    [CustomerAddressID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [MiddleName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [FullName] [nvarchar](50) NOT NULL,
    [CompanyName] [nvarchar](50) NOT NULL,
    [FullAddress] [nvarchar](50) NOT NULL,
    [PostalCode] [nvarchar](50) NOT NULL,
    [City] [nvarchar](50) NOT NULL,
    [StateProvince] [nvarchar](50) NOT NULL,
    [Country] [nvarchar](50) NOT NULL,
    [Polled] [bit] NOT NULL
CONSTRAINT [PK_CustomerAddress] PRIMARY KEY CLUSTERED
(
    [CustomerAddressID] ASC
)
)

GO

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Catherine','R.','Abel','Catherine R. Abel', 'Professional Sales and Service','57251 Serene Blvd','91411','Van Nuys','California','United States',0)
GO

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Frances','B.','Smith','Frances B. Smith', 'Area Bike Accessories','6900 Sisk Road','95354','Modesto','California','United States',0)
GO

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Margaret','J.','Adams','Magaret J. Adams', 'Bicycle Accessories and Kits','Lewiston Mall','83501','Lewiston','Idaho','United States',0)
GO

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Samuel','N.','Agcaoili','Samuel    N. Agcaoili', 'Vinyl and Plastic Goods Corporation',' 25800-130 King Street West','M4B 1V5','Toronto','Ontario','Canada',0)
GO

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Robert','E.','Ahlering','Robert    E. Ahlering', 'Fun Toys and Bikes','6500 East Grant Road','85701','Tucson','Arizona','United States',0)
GO

I then executed SELECT * FROM dbo.CustomerAddress TSQL Statement and check if there is data.

image

I have set up now a database in SQL Azure with one table containing five records. The next step is to create a BizTalk project and having a xml schema and WCF-Custom Receive Port Binding generated for table in my SQL Azure database I wish to poll. I configured the URI to connect to SQL Azure (security, connection string and adapter binding properties), choose inbound operations and from available categories Polling. When I click Ok a DataSetSchema.xsd and Polling.xsd is generated together with WcfReceivePort_SqlAdapterBinding_Custom.bindinginfo.xml file. I deployed project after signing and giving appropriate name for BizTalk application.

image

Notice that with the Consume Adapter Service Wizard you have ability to choose between three type of inbound operations: Polling, TypedPolling and Notification. This means you can with:

  • Polling operation a data set as part of the polling message returned.
  • TypedPolling operation a strongly-typed polling message returned.
  • Notification operation a notification message returned.

Important step after deployment of schema’s is importing the custom binding file and setting some properties (i.e. pollDataAvailableStatement, pollingStatement). Importing a binding is a straightforward operation inside BizTalk Administation Console. After the receive port and location is created you can double click receive location and then Configure Type (WCF-Custom). URI is present in General Tab and can be left as is. In next tab you see binding information of sqlBinding. In polledDataAvailableStatement I have the following statement: SELECT COUNT(*) FROM dbo.CustomerAddress and for pollingStatement: SELECT * FROM dbo.CustomerAddress WHERE Polled = 0; UPDATE dbo.CustomerAddress SET Polled=1 WHERE Polled=0;

image

I also changed useAmbientTransaction property to False (for operations where the adapter client does not write any data to the SQL Server database, such as a Select operation, you might not want the additional overhead of performing the operations inside a transaction. In such cases, you can configure the SQL adapter to perform operations without a transactional context by setting the UseAmbientTransaction binding property to false.)

In the tab called Other I configured the credentials filling in user name and password for the account to access database on SQL Azure. After that you are ready for receive side. As I just wanted to poll data and send it to file, I also created a Send Port that uses FILE Adapter. I configured the adapter to send data to folder with a filter that subscribes to message type http://schemas.microsoft.com/Sql/2008/05/Polling/#Polling.

As you can I basically have messaging solution now that polls data from SQL Azure database table and routes the polled data as a single message to a folder on-premise (i.e. my virtual machine). Once I start the application I see after a few seconds a xml file containing data.

image

If I perform a query inside my SQL Management Studio I noticed the column Polled is 1 for every record.

image

With WCF-SQL Adapter I am able to poll a SQL Azure table and not just polling, but calling a Stored-Procedure or table operations are also possible using WCF-SQL adapter, available through BizTalk Adapter Pack 2010. I conclude that you can use WCF-SQL adapter for on-premise SQL Server and its cloud variant SQL Azure!

BizTalk Server 2010 Unleashed

This weekend I received my copy of the new BizTalk Server book BizTalk Server 2010 Unleashed.

This book, from the hands of elite members of the BizTalk community Brian Loesgen, Charles Young, Jan Eliasen, Scott Colestock, Anush Kumar and John Flanders is the second edition of a series that started with BizTalk Server 2004 unleashed and which was then concidered to be The Book when working with BizTalk Server 2004.

Although I have not yet had the chance to read it front to back, I am already delighted to see some very exciting and usefull chapters in the Advanced Topics section of the book.

The book deals with the topics of the Business Rules engine and the ESB Toolkit not only from a technical perspective but adds conceptual insights too. This alone should be reason enough to get the book at your earliest convenience.

In chapters 10 and 11 the business rules engine is explained to a level of detail I have never seen in one single place before. It is the coherent collection of snippets of information to be found all over the internet but also adds value by explaining some theoretics behind Rule-based programming.

In chapter 12, you will find the ESB Toolkit described. Again, it offers a complete overview of the technology and some basic insights into the concept of ESB, the added value of the ESB Toolkit and what you are supposed to use it for. Unfortunatly, despite being well written and well explained, it still is very theoretic with little to no handles for developers developing interest to get started. This is in contrast with most other chapters and may very well be the biggest shortcoming of the book. However, this still does not disqualify it at any time as a must-have for BizTalk consultants of any given level.