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.

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

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.

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.

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;

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.

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

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.

Windows Azure Service Bus Topics and Queues

A topic that I think BizTalk professional find appealing in Windows Azure is the Service Bus (part of Windows Azure AppFabric). Service Bus provides secure messaging and connectivity capabilities that enable building distributed and disconnected applications in the cloud, as well hybrid application across both on-premises and the cloud.

One of new features is topics and queues. Clemens Vasters did a talk on Windows Azure Service Bus Topics and Queues during Windows Build and an advanced one, which you find on his blog.

You can work with this new feature through Windows Azure AppFabric SDK V1.5 - September 2011. The version 1.5 SDK includes an updated Service Bus assembly that includes support for durable, or “brokered” messaging features, such as queues, topics, and subscriptions. These features require the .NET Framework version 4.0. (Note: If you have a dependency on the .NET Framework version 3.5, you should download or continue to use version 1.0 of the AppFabric SDK.)

If you have virtual machine available or laptop following requirements are applicable:

• Account Requirements Before running the samples in the SDK, you must have one or more service namespaces. To create a Windows Azure AppFabric service namespace, you have to register or login Windows Azure Platform Portal.
• Runtime Requirements The Windows Azure AppFabric SDK samples are available as Visual Studio 2010 solutions (C# and VB) and require Microsoft .NET Framework 4.0 higher to run. The SDK is supported on Windows Server 2008, Windows 7, Windows Vista, Windows Server 2003, and Windows XP operating systems. For Windows Server AppFabric integration, IIS7.5 is required.

Documenting BizTalk Environment and BizTalk Solution

When it comes to documentation with BizTalk it can be challenging task. What do and what do I not document and how do I document it. In my view there are three types of documentation when it comes to BizTalk in my opinion. That is:

• BizTalk Environment (Design) document
• Functional Design of BizTalk Solution
• Technical (Design) of BizTalk Solution

BizTalk Environment (design) documentation you can described the different BizTalk environment Development, UAT and Production. In this type of document you can roughly describe the following:

• Purpose;
• Design Decisions regarding environments;
• Considerations/Issues;
• Landscape (how does it fit with in IT-Landscape in Enterprise);
• MS BizTalk and SQL Server editions;
• SQL Server configuration (sizing disk, clustering e.a.);
• ICT Policy (Back up, Anti-virus, Patching, e.a.) applied in Enterprise;
• Operating Systems;
• Security Settings;
• Backup and Restore (procedures e.a.);
• BizTalk Group Configuration (clustering, availability e.a.);
• Host configuration;

To add you in documenting the environment or take into consideration (i.e. reference material) is MSDN Microsoft BizTalk Server 2010 Operations Guide.

Functional Design you describe how solution will work, which service it will call, what type of message’s it excepts, processes and sends. You can use notation symbols found in EAI Patterns site and create a functional model for your BizTalk solution (see example below).

 

You can also use UML like state diagram to depict process that is implemented in BizTalk solution (see picture below).

Besides diagrams, you will also document specifications for messages (data types, restrictions, e.a.), security settings, protocols, message patterns, and so on.

Finally Technical (design) documentation of BizTalk solution and its configuration is fairly simple as you use BizTalk documenter for it. This is tool found on codeplex that can automatically generate a document (compiled chm, and even HTML for 2010 version).You can start documenter and following screen will be shown.

Click Next, Select if you want to completely document entire configuration of specific BizTalk application.

Click Next, you can select (this is new with BizTalk 2010 documenter) SSO configuration e.a.

Click Next, choose output options.

Finally you can click Generate Documentation and documentation will generated for you in desired format.

 

As you can see working with documenter is pretty straight forward and type of documentation can be realized with minimal effort. You can keep this documentation easy up to date when changes are made to BizTalk solution. Versioning your solution and its documentation is very important here! Documentation of BizTalk environment will take more effort and will be done in MS Word with perhaps drawings from Visio (there are BizTalk stencils, see post by Sandro). Functional documentation regarding your BizTalk solution will also require some effort, but vital before implementing your solution. Usually MS Word and Visio are used here as well.

Comments are welcome as I am interested in your view on documenting BizTalk.

Cheers!

BizTalk Server 2010 Patterns by Dan Rosanova

Currently I am in Toronto staying at friend enjoying some free time before heading to San Francisco, and SoCal for the Windows Build Conference. Still I find some time to write this post, which I wanted to as BizTalk Server 2010 Patterns book is there for pre-order. A new BizTalk book written by Dan Rosanova, two time MVP BizTalk Server, and over a decade over of experience delivering solutions on Microsoft platforms in different branches. It is published by PACKT and reviewed by myself, fellow MVP Randal van Splunteren and Rene Brauwers, basically an all Dutch BizTalk Technical Review team.

I enjoyed reviewing it and found that the author has put in tremendous effort together with a lot of people at PACKT and fellow reviewers to be able to publish such a fine book. I realize it may sound/read like I am shamelessly plugging/promoting this book, but I really mean it. Therefor I am going to tell you why I mean it and why as an professional integration professional, consultant or architect in Microsoft space need to buy this book.

First of all it is a great book, and in first part it extensively details the important parts of BizTalk, how to structure your solution, XLANG, architecture and its components like BAM, artefacts (i.e. orchestrations, pipelines, maps), operation architecture (i.e. scalability, availability), (unit) testing and messaging concepts. The second part the focus shifts from theory to practice and will be very appealing for professionals that develop BizTalk solutions. This part delivers you the skills to error handling, WCF-SQL Adapter capabilities, BRE (i.e. policies, vocabularies and rules), dealing with flat files and bindings, BAM, SQL Broker (Notifications), SharePoint, orchestration patterns (i.e. conveys), handling zombies and some other concepts.

Second reason is that this book neatly fits in with other BizTalk books published by PACKT like :

• Microsoft BizTalk 2010: Line of Business Systems Integration
• SOA Patterns with BizTalk Server 2009

Third and final reason you will be a better BizTalk professional or architect, when applying the knowledge found in BizTalk Patterns book and others. Who wouldn’t want that!

Book is available through Amazon and PACKT is self, so go to either of one and order it!

Cheers, and now I am off to diner.

Proef Ordina! Aangenaam kennis te maken

Proef Ordina! Zomer editie geeft je donderdagavond 22 september een kijkje in de keuken van Ordina. Je bent bij Ordina thuis uitgenodigd! In een ongedwongen sfeer kun je kennismaken met Ordina als werkgever en ontdekken wat ‘jouw toekomst bij Ordina’ is.

Want bij Ordina is de zomer nog niet voorbij! Samen maken we deze avond tot een groot succes: barbecue, inhoud en een spetterend optreden van talenten van de Voice of Holland Pearl, Rafaella en Bart.

Tijdens de avond krijg je onder andere een kijkje in de keuken van de Excellence Centers van Ordina. Zowel professionals als leden van het management zijn aanwezig om je te informeren over de diensten en producten die wij aan onze klanten leveren. Via demo’s, presentaties en gesprekken hoor en zie je wat voor moois er allemaal gebakken wordt in de keuken van Ordina.

Kijk voor meer info op http://www.proefordina.nl/

What new Training Kit BizTalk 2010

There is a new training kit for BizTalk 2010. It contains:

Hands On Labs

• Creating BizTalk Maps with the new Mapper
• Consuming a WCF Service
• Publishing Schemas and Orchestrations as WCF Services
• Integrating with Microsoft SQL Server
• Integrating using the FTP Adapter
• Developers - Create a Role and Party-based Integration Solution
• Exploring the New Settings Dashboard
• Monitoring BizTalk Operations using System Center Operations Manager 2007 R2
• Administrators - Create a Role and Party-based Integration Solution

Presentations and Videos

• Introduction and New Features Overview
• The New and Improved Mapper
• Updated Adapter Features
• Trading Partner Management
• Configuring Performance and Application Settings
• Monitoring BizTalk Using Operations Manager

BizTalk 2010 Environment

You will be needing a BizTalk 2010 environment to do the labs and for that you have a couple of options:

a) Create an environment yourself if you access to resources like MSDN

b) Download the virtual disk contain BizTalk environment. You will need Window 2008 Hyper V for that. Workaround is that after downloading the VHD you convert it to VMDK and mount in VMWare workstation.

c) Download the BizTalk Administration Kit Virtual Hard Disk also containing training for administrators. Apply same workaround if you want it in VMWare workstation.

Success!

BizTalk RFID and StreamInsight Talk

Coming Monday I will present together with my colleague Wouter on BizTalk RFID and StreamInsight (Complex Event Processing). It is what my company calls a “Innoveer jij mee sessie” and these talks are about innovation. I did similar talk a few months back on BizTalk AppFabric Connect. Talks are in Dutch and hosted at or Headquarters in Nieuwegein. It is open for public and you are free to register here.

If you cannot make it the talk following resources will cover what we will talk about:

• TechEd 2010 StreamInsight Session and Demo
• Microsoft StreamInsight: Introduction to Complex Event Processing with SQL Server 2008 R2 StreamInsight
• The Eco System for RFID in BizTalk (google download PDF)

If you can make it and looking forward seeing you at presentation.

Cheers!

Learn about the BizTalk Business rules Engine

One of the strong points of the BizTalk Server Integration suite is the inclusion of a very versatile, flexible and extendable Business rules engine.

This versatility, however, is the same thing that makes it widely underused in deployments using BizTalk Server. Even today, being on the market in various versions for more than 6 years, I see people often struggle with it.

The root problem: the same flexibility and versatility comes with a rather steep learning curve to master.

To help you get started, Microsoft has packed a hands-on learning guide into a Virtual Lab. You can get to it here.

Try it, you won't be disappointed.

BizTalk 2010 : Line of Business System Integration

Packt Publishing has released another great BizTalk resource by title of Microsoft BizTalk 2010: Line of Business Integration.

Authors have done an excellent job detailing integration with Dynamics (CRM,AX), SalesForce, SharePoint, and SAP. There was not much documentation on integration with Dynamics for instance and most it was out dated. Currently there a couple of BizTalk books out there and all they focus mainly on out-of-box features and capabilities. None of them really talked about capabilities offered by BizTalk Adapter Pack that has been out there a couple years now. Line-Of-Business adapters in this package enable integration with SAP, Oracle, Oracle eBusiness Suite, Siebel and and SQL Server. This book does deal with integration with a couple of these LOB systems.

Besides that it shows how to integrate with SharePoint, and Dynamics two other Microsoft Products, which may seem that that easy to integrate with. It isn't, as it can be quite complex, but with guidance provided in this book it will make life easier for you. If professionals like yourself are facing issues on current projects with these systems, or need guidance on how prepare yourselves before going to do integration project, or want more background/knowledge than this book will be extremely useful for you.

I myself have bought the book and promoted it during a talk on Sales Force integration I gave to BizTalk Dutch User Group as it was a great resource for preparation and leveraging samples.