This blog post is about using Windows Azure AppFabric cache service together with EPiServer CMS. EPiServer CMS supports pluggable cache, replacing the standard EPiServer CMS cache component. …
Daniel Berg’s blog about ASP.NET, EPiServer, SharePoint, BizTalk
One of the feature that came with the WCF-SQL Adapter was the ability to XML polling from SQL. Anyone that remembers the original SQL adapter from BizTalk remembers the requirement for all the data returned from SQL to have the FOR XML AUTO added to the end of the query and the XMDATA added to add the Store Procedure call to your BizTalk project.
Typically when you poll data out of SQL it is a single table or the output of a stored procedure, this limited the type of data that could be returned, the example is that it was difficult to return the order header and all the detail lines with a single polling statement. The XML polling feature of the WCF-SQL adapter allow you to create your own XML document in SQL and return it to BizTalk via a polling statement.
In this blog post I am going to show how to do this.
I will be using the Sample AdventureWorks database and returning and Order XML document.
Install the AdventureWorks SQL sample database from http://sqlserversamples.codeplex.com/
Install the BizTalk 2010 LOB Adapter SDK and the LOB Adapter Pack (in not already installed)
Create a SQL Stored Procedure to return the desired data, using the xmlnamespaces and for xml path. You will need to decide when you create your stored procedure whether it will be returning a single Order or a batch of Orders, there will be not difference to the XML generated in the stored procedure just a difference in how you select the records to return. The stored procedure that I have created returns up to 5 Orders per call.
(complete SQL Stored Procedure available in download below)
Grant the BizTalk Application User group access to the AdventureWorks database and execute privileges to the GetSalesOrdersToProcess stored procedure.
Execute the stored procedure to create a sample XML document and save it to order.xml
Create a BizTalk Project (I am using Demo.WCFSQL.Polling for the solution name and AdventureWorks.Schemas for the project name)
In the BizTalk project add a generated item and select generated schema, in the generated schema dialog select Well-Formed XML, then select the order.xml file saved above. This will create an order.xsd in your BizTalk project. You may also need to update any numeric fields to the correct data types, the generated schema wizard sets number to the smallest data type that fits the sample data.
If you decide when creating your stored procedure to return multiple orders per execution, you will need to create and orders.xsd envelope schema, like this:
And set the Body XPath to the root node Orders.
Strong name your BizTalk project and set the deployment application name (I am using Demo.WCFSQL.Polling)
This is the complete Solution
Build and deploy your solution.
Expand the application that you deployed your solution into (I am using Demo.WCFSQL.Polling)
You now need to create the one-way WCF-SQL receive port and receive location (I am using AdventureWorks.Orders.Polling as the receive port name and AdventureWorks.Orders.Polling.WCFSQL as the receive location name)
The receive location can either be created as a WCF-Custom with the sqlBinding or as a WCF-SQL adapter (I am using the WCF-Custom with the sqlBinding)
After selecting WCF-Custom, click Configure.
On the General Tab type in the Address (URI), this consist of the protocol, server name, database name and Inbound Id like below:
mssql:////?InboundId=Orders (my example looks like: http://biztalkbill.commssql://.//AdventureWorks?InboundId=Orders)
On the Binding Tab, select sqlBinding in the Binding Type drop down list
Set the following setting
| Key | Value | Comment |
| inboundOperationType | XmlPolling | |
| polledDataAvailableStatement | select count(*) from [Sales].[SalesOrderHeader] where [status] = 5 | This would be specific to your situation, this statement queries your table to see if any records are available to retrieve |
| pollingIntervalInSeconds | 30 | how often to try to retrieve data |
| pollingStatement | exec [dbo].[GetSalesOrdersToProcess] | this calls the stored procedure to retrieve data |
| xmlStoredProcedureRootNodeName | Orders | this is only necessary if you chose to bring back more than one order per execution |
| xmlStoredProcedureRootNodeNamespace | http://AdventureWorks.Schemas.Orders | this is only necessary if you chose to bring back more than one order per execution |
Note: useAmbientTransaction defaults to True, if MSDTC is not configured between BizTalk and the source SQL Server you will have to set this to false
Click Ok, to close the WCF-Custom Transport Properties dialog
Select the appropriate Receive handler and set the Receive Pipeline to XMLReceive, then click OK to close the Receive Location Properties
For this example we are just going to send the XML message directly out to a file location
You now need to create a one-way send port (I am using AdventureWorks.Orders.Send.FILE for the send port name)
Select a file location for the output XML messages (I am using C:\Working\Demo.WCFSQL.Polling\Out\%MessageID%.xml)
On the Filters tab, set the following filter: (this is for my example, yours may differ)
BTS.ReceivePortName == AdventureWorks.Orders.Polling
Click Ok to close the Send Port Properties dialog
Right click on the Application and select start
You will notice with my example, 5 files are create at a each polling interval in the directory and that each file represents an order.
Note: there are over 31,000 orders in the AdventureWorks database.
I hope this example give you the information that you need to get started with XmlPolling with BizTalk and the WCF-SQL Adapter.
Code for this Sample
At design time we have, included in Visual Studio, 3 features that allow us to test and validate the maps: Test Map: Tests the selected map. Validate Map: Validates the map Debug Map: If a map is compiled successfully, Debug Map launches the XSLT debugger. It allows you to step through the generated XLST, just […]
Blog Post by: Sandro Pereira
I’m very pleased to have been awarded a Microsoft MVP for the third year in a row.
With new versions of SQL, VS and Windows shipping in 2012, and a bunch of very interesting things happening in Azure, it’s set to be a good year.
Node.js is a server-side JavaScript platform “for easily building fast, scalable network applications”. It’s built on Google’s V8 JavaScript engine and uses an (almost) entirely async event-driven processing model, running in a single thread. If you’re new to Node and your reaction is “why would I want to run JavaScript on the server side?”, this is the headline answer: in 150 lines of JavaScript you can build a Node.js app which works as an accelerator for WCF REST services*. It can double your messages-per-second throughput, halve your CPU workload and use one-fifth of the memory footprint, compared to the WCF services direct.
Well, it can if: 1) your WCF services are first-class HTTP citizens, honouring client cache ETag headers in request and response; 2) your services do a reasonable amount of work to build a response; 3) your data is read more often than it’s written. In one of my projects I have a set of REST services in WCF which deal with data that only gets updated weekly, but which can be read hundreds of times an hour. The services issue ETags and will return a 304 if the client sends a request with the current ETag, which means in the most common scenario the client uses its local cached copy. But when the weekly update happens, then all the client caches are invalidated and they all need the same new data. Then the service will get hundreds of requests with old ETags, and they go through the full service stack to build the same response for each, taking up threads and processing time. Part of that processing means going off to a database on a separate cloud, which introduces more latency and downtime potential.
We can use ASP.NET output caching with WCF to solve the repeated processing problem, but the server will still be thread-bound on incoming requests, and to get the current ETags reliably needs a database call per request. The accelerator solves that by running as a proxy – all client calls come into the proxy, and the proxy routes calls to the underlying REST service. We could use Node as a straight passthrough proxy and expect some benefit, as the server would be less thread-bound, but we would still have one WCF and one database call per proxy call. But add some smart caching logic to the proxy, and share ETags between Node and WCF (so the proxy doesn’t even need to call the servcie to get the current ETag), and the underlying service will only be invoked when data has changed, and then only once – all subsequent client requests will be served from the proxy cache.
I’ve built this as a sample up on GitHub: NodeWcfAccelerator on sixeyed.codegallery. Here’s how the architecture looks:
The code is very simple. The Node proxy runs on port 8010 and all client requests target the proxy. If the client request has an ETag header then the proxy looks up the ETag in the tag cache to see if it is current – the sample uses memcached to share ETags between .NET and Node. If the ETag from the client matches the current server tag, the proxy sends a 304 response with an empty body to the client, telling it to use its own cached version of the data. If the ETag from the client is stale, the proxy looks for a local cached version of the response, checking for a file named after the current ETag. If that file exists, its contents are returned to the client as the body in a 200 response, which includes the current ETag in the header. If the proxy does not have a local cached file for the service response, it calls the service, and writes the WCF response to the local cache file, and to the body of a 200 response for the client. So the WCF service is only troubled if both client and proxy have stale (or no) caches.
The only (vaguely) clever bit in the sample is using the ETag cache, so the proxy can serve cached requests without any communication with the underlying service, which it does completely generically, so the proxy has no notion of what it is serving or what the services it proxies are doing. The relative path from the URL is used as the lookup key, so there’s no shared key-generation logic between .NET and Node, and when WCF stores a tag it also stores the “read” URL against the ETag so it can be used for a reverse lookup, e.g:
| Key | Value |
| /WcfSampleService/PersonService.svc/rest/fetch/3 | “28cd4796-76b8-451b-adfd-75cb50a50fa6” |
| “28cd4796-76b8-451b-adfd-75cb50a50fa6” | /WcfSampleService/PersonService.svc/rest/fetch/3 |
In Node we read the cache using the incoming URL path as the key and we know that “28cd4796-76b8-451b-adfd-75cb50a50fa6” is the current ETag; we look for a local cached response in /caches/28cd4796-76b8-451b-adfd-75cb50a50fa6.body (and the corresponding .header file which contains the original service response headers, so the proxy response is exactly the same as the underlying service). When the data is updated, we need to invalidate the ETag cache – which is why we need the reverse lookup in the cache. In the WCF update service, we don’t need to know the URL of the related read service – we fetch the entity from the database, do a reverse lookup on the tag cache using the old ETag to get the read URL, update the new ETag against the URL, store the new reverse lookup and delete the old one.
Running Apache Bench against the two endpoints gives the headline performance comparison. Making 1000 requests with concurrency of 100, and not sending any ETag headers in the requests, with the Node proxy I get 102 requests handled per second, average response time of 975 milliseconds with 90% of responses served within 850 milliseconds; going direct to WCF with the same parameters, I get 53 requests handled per second, mean response time of 1853 milliseconds, with 90% of response served within 3260 milliseconds. Informally monitoring server usage during the tests, Node maxed at 20% CPU and 20Mb memory; IIS maxed at 60% CPU and 100Mb memory.
Note that the sample WCF service does a database read and sleeps for 250 milliseconds to simulate a moderate processing load, so this is *not* a baseline Node-vs-WCF comparison, but for similar scenarios where the service call is expensive but applicable to numerous clients for a long timespan, the performance boost from the accelerator is considerable.
* – actually, the accelerator will work nicely for any HTTP request, where the URL (path + querystring) uniquely identifies a resource. In the sample, there is an assumption that the ETag is a GUID wrapped in double-quotes (e.g. “28cd4796-76b8-451b-adfd-75cb50a50fa6”) – which is the default for WCF services. I use that assumption to name the cache files uniquely, but it is a trivial change to adapt to other ETag formats.
If you are dealing with large maps, they can become very complex and therefore very difficult to maintain and read. To minimize this problem, BizTalk server provides two main features to aid in the readability and maintainability of maps: Grid Pages Link Labels Grid Pages You can segment groups of links in to different grid […]
Blog Post by: Sandro Pereira
Troubleshooting the WCF LOB SDK 2010 installation when you think you have done everything correctly…
When we perform a transformation in the message there are 5 basic functionalities that typically arise: Simple mapping of a given value (direct copy) Concatenation of values Conditional selection Custom scripts Add new values (data) Simple mapping of a given value (direct copy) This is the most basic operation of all, where we intend to […]
Blog Post by: Sandro Pereira
Since the 1st occurrence of this issue (Issues with Tracking causing high CPU usage on BizTalk SQL Server), I have had 2 other clients with similar issues, each with different root causes. The key finding about these issues is that it is not obvious what the problem is from the initial problem determination, one of the recent issues showed up as a throttling issue for database size, but when running the Message Box Viewer tool, there was no errors that indicated the root cause, but there were warnings around the size of on of the tracking_1_x tables.
The underlying issue with the 2nd occurrence of this issue was a failed upgrade from 2006 R2 to 2009, although the upgrade seemed to work correctly, the user running the upgrade did not have sysadmin privileges on the BizTalk SQL Server, as required in the install instructions. This lack of permission lead to the service account for the Tracking host not being given permission on the tables and stored procedures that are used doing the movement of tracking data from the message box database to the tracking database. Once the permissions were update (cross checked permissions from a successfully upgraded 2009 BizTalk SQL Server), tracking data started to be moved from the message box database to the tracking database.
The 3rd occurrence is still under investigation, but the symptoms are the same, High CPU on the BizTalk server and large number of records in the tracking_1_x tables in the message box database.
From now on, one of my 1st checks for a poorly performing BizTalk system will be check the tracking_1_x tables and the TDDS_StreamStatus table. I will be publishing a SCOM monitor to detect the tracking data not being copied and hopefully a rule for message box viewer that will clearly identify this issue.
I’m currently working on broadening the content in the Windows Azure Service Bus Developer Guide. One of the features I have been looking at over the past week is the support for transactional messaging. When using the direct programming model and the WCF interface some, but not all, messaging operations can participate in transactions. This allows developers to improve the reliability of messaging systems. There are some limitations in the transactional model, transactions can only include one top level messaging entity (such as a queue or topic, subscriptions are no top level entities), and transactions cannot include other systems, such as databases.
As the transaction model is currently not well documented I have had to figure out how things work through experimentation, with some help from the development team to confirm any questions I had. Hopefully I’ve got the content mostly correct, I will update the content in the e-book if I find any errors or improvements that can be made (any feedback would be very welcome). I’ve not had a chance to look into the code for transactions and asynchronous operations, maybe that would make a nice challenge lab for my Windows Azure Service Bus course.
Messaging entities in the Windows Azure Service Bus provide support for participation in transactions. This allows developers to perform several messaging operations within a transactional scope, and ensure that all the actions are committed or, if there is a failure, none of the actions are committed. There are a number of scenarios where the use of transactions can increase the reliability of messaging systems.
Using TransactionScope
In .NET the TransactionScope class can be used to perform a series of actions in a transaction. The using declaration is typically used de define the scope of the transaction. Any transactional operations that are contained within the scope can be committed by calling the Complete method. If the Complete method is not called, any transactional methods in the scope will not commit.
|
// Create a transactional scope. using (TransactionScope scope = new TransactionScope()) { // Do something.
// Do something else.
// Commit the transaction. scope.Complete(); }
|
In order for methods to participate in the transaction, they must provide support for transactional operations. Database and message queue operations typically provide support for transactions.
Transactions in Brokered Messaging
Transaction support in Service Bus Brokered Messaging allows message operations to be performed within a transactional scope; however there are some limitations around what operations can be performed within the transaction.
In the current release, only one top level messaging entity, such as a queue or topic can participate in a transaction, and the transaction cannot include any other transaction resource managers, making transactions spanning a messaging entity and a database not possible.
When sending messages, the send operations can participate in a transaction allowing multiple messages to be sent within a transactional scope. This allows for “all or nothing” delivery of a series of messages to a single queue or topic.
When receiving messages, messages that are received in the peek-lock receive mode can be completed, deadlettered or deferred within a transactional scope. In the current release the Abandon method will not participate in a transaction. The same restrictions of only one top level messaging entity applies here, so the Complete method can be called transitionally on messages received from the same queue, or messages received from one or more subscriptions in the same topic.
A transactional scope can be used to send multiple messages to a queue or topic. This will ensure that all the messages will be enqueued or, if the transaction fails to commit, no messages will be enqueued.
An example of the code used to send 10 messages to a queue as a single transaction from a console application is shown below.
|
QueueClient queueClient = messagingFactory.CreateQueueClient(Queue1);
Console.Write(“Sending”);
// Create a transaction scope. using (TransactionScope scope = new TransactionScope()) { for (int i = 0; i < 10; i++) { // Send a message BrokeredMessage msg = new BrokeredMessage(“Message: “ + i); queueClient.Send(msg); Console.Write(“.”); } Console.WriteLine(“Done!”); Console.WriteLine();
// Should we commit the transaction? Console.WriteLine(“Commit send 10 messages? (yes or no)”); string reply = Console.ReadLine(); if (reply.ToLower().Equals(“yes”)) { // Commit the transaction. scope.Complete(); } } Console.WriteLine(); messagingFactory.Close();
|
The transaction scope is used to wrap the sending of 10 messages. Once the messages have been sent the user has the option to either commit the transaction or abandon the transaction. If the user enters “yes”, the Complete method is called on the scope, which will commit the transaction and result in the messages being enqueued. If the user enters anything other than “yes”, the transaction will not commit, and the messages will not be enqueued.
The receiving of multiple messages is another scenario where the use of transactions can improve reliability. When receiving a group of messages that are related together, maybe in the same message session, it is possible to receive the messages in the peek-lock receive mode, and then complete, defer, or deadletter the messages in one transaction. (In the current version of Service Bus, abandon is not transactional.)
The following code shows how this can be achieved.
|
using (TransactionScope scope = new TransactionScope()) {
while (true) { // Receive a message. BrokeredMessage msg = q1Client.Receive(TimeSpan.FromSeconds(1)); if (msg != null) { // Wrote message body and complete message. string text = msg.GetBody<string>(); Console.WriteLine(“Received: “ + text); msg.Complete(); } else { break; } } Console.WriteLine();
// Should we commit? Console.WriteLine(“Commit receive? (yes or no)”); string reply = Console.ReadLine(); if (reply.ToLower().Equals(“yes”)) { // Commit the transaction. scope.Complete(); } Console.WriteLine(); } |
Note that if there are a large number of messages to be received, there will be a chance that the transaction may time out before it can be committed. It is possible to specify a longer timeout when the transaction is created, but It may be better to receive and commit smaller amounts of messages within the transaction.
It is also possible to complete, defer, or deadletter messages received from more than one subscription, as long as all the subscriptions are contained in the same topic. As subscriptions are not top level messaging entities this scenarios will work.
The following code shows how this can be achieved.
|
try { using (TransactionScope scope = new TransactionScope()) { // Receive one message from each subscription. BrokeredMessage msg1 = subscriptionClient1.Receive(); BrokeredMessage msg2 = subscriptionClient2.Receive();
// Complete the message receives. msg1.Complete(); msg2.Complete();
Console.WriteLine(“Msg1: “ + msg1.GetBody<string>()); Console.WriteLine(“Msg2: “ + msg2.GetBody<string>());
// Commit the transaction. scope.Complete(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } |
The restriction of only one top level messaging entity being able to participate in a transaction makes some useful scenarios unsupported. As the Windows Azure Service Bus is under continuous development and new releases are expected to be frequent it is possible that this restriction may not be present in future releases.
The first is the scenario where messages are to be routed to two different systems.
The following code attempts to do this.
|
try { // Create a transaction scope. using (TransactionScope scope = new TransactionScope()) { BrokeredMessage msg1 = new BrokeredMessage(“Message1”); BrokeredMessage msg2 = new BrokeredMessage(“Message2”);
// Send a message to Queue1 Console.WriteLine(“Sending Message1”); queue1Client.Send(msg1);
// Send a message to Queue2 Console.WriteLine(“Sending Message2”); queue2Client.Send(msg2);
// Commit the transaction. Console.WriteLine(“Committing transaction…”); scope.Complete(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } |
The results of running the code are shown below.
When attempting to send a message to the second queue the following exception is thrown:
No active Transaction was found for ID ’35ad2495-ee8a-4956-bbad-eb4fedf4a96e:1′. The Transaction may have timed out or attempted to span multiple top-level entities such as Queue or Topic. The server Transaction timeout is: 00:01:00..TrackingId:947b8c4b-7754-4044-b91b-4a959c3f9192_3_3,TimeStamp:3/29/2012 7:47:32 AM.
Another scenario where transactional support could be useful is when forwarding messages from one queue to another queue. This would also involve more than one top level messaging entity, and is therefore not supported.
Another scenario that developers may wish to implement is performing transactions across messaging entities and other transactional systems, such as an on-premise database. In the current release this is not supported.
There are some techniques that developers can use to work around the one top level entity limitation of transactions. When sending two messages to two systems, topics and subscriptions can be used. If the same message is to be sent to two destinations then the subscriptions would have the default subscriptions, and the client would only send one message. If two different messages are to be sent, then filters on the subscriptions can route the messages to the appropriate destination. The client can then send the two messages to the topic in the same transaction.
In scenarios where a message needs to be received and then forwarded to another system within the same transaction topics and subscriptions can also be used. A message can be received from a subscription, and then sent to a topic within the same transaction. As a topic is a top level messaging entity, and a subscription is not, this scenario will work.