Query Notification SQL Azure BizTalk WCF-SQL: Possible?

In a previous post I discussed polling a SQL Azure Table using WCF-SQL Adapter. I got one comment back from Mikael Sand with question: Does SQL Azure support notifications? Well there is one way to find out and that is give it a try to find out if it is possible. As inspiration I looked at a post by Richard Seroter Query Notification Capability in WCF SQL Adapter and see if I can try a similar scenario using SQL Azure.

This time I created the database through Azure Management Portal and I then selected the created database and choose manage. You will see that a new browser tab will appear and you be prompted to fill credentials to connect.

image

Next inside online database you can create a new table and add columns, data type e.a.

image

Click Save after you are done. If you click table you see newly created table.

image

Next step is to have data table. Click New Query and paste your insert data query.

image

Click Execute.

image

If you perform a select statement you will get following result.

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 have notifications from. I configured the URI to connect to SQL Azure (security, connection string and adapter binding properties), choose inbound operations and from available categories Notification.

image

When I click Ok a Notification.xsd is generated together with WcfReceivePort_SqlAdapterBinding_Custom.bindinginfo.xml file. I deployed project after signing and giving appropriate name for BizTalk application.

Important step after deployment of schema’s is importing the custom binding file and setting some properties (i.e. Notification Statement). Importing a binding is a straightforward operation inside BizTalk Administration 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 Notification Statement I have the following statement:

image

I also changed inboundOperationType to Notification. 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/Notification/#Notification.

As you can I basically have simple solution now that will send notifications from SQL Azure database table and routes these notifications as message to a folder on-premise (i.e. my virtual machine). At least that is what I except, but I am getting the following error.

image

Error gives me no information what exactly is wrong or point me to any directions. I do not think for now that this capability is supported by SQL Azure. I would prefer polling for SQL Azure as implementation was easy without any problem. With notification I spend some time to figure out the problem, which I could not find in timely manner. To be continued ….

[Update] Thanks to Saravana I know now SQL Broker is not supported and so Notification capabilitity is not possible with SQL Azure.

Comments

Saravana Kumar said…
Hi Steef,

Under the hood this is how the query notification service works. When you create the receive port/location a listener is hosted inside the adapter. The SQL Query notification, when it matches the subscription send the notification message to the listener. All these things are part of SQL Service broker.

This document clearly states http://msdn.microsoft.com/en-us/library/ee336250.aspx SQL Service broker is not supported (yet) at SQL Azure.

Anyway you are being adventurous and trying something new (as always). I won't be surprised if you come up with a hack :-)
9:03 AM
Steef-Jan Wiggers said…
Thanks Saravana you confirmed my suspicision. So I can continue this experiment as soon as broker is supported. Still polling is possible, but notification pattern is something I would perfer.
10:44 AM
Mikael Sand said…
Thanks guys. I think I've said it before. Azure is not quite done yet. It's getting there but for now I'll wait.

Although it has been pointed out to me that the cost of transactions and data transfer is not the main bulk of cost when running on Azure.
11:39 PM
Post a Comment

Popular posts from this blog

DTAP Strategy: Pricing and Licensing

Image
To implement BizTalk solutions it is a good practice to have an DTAP environment in place. DTAP stands for Development, Test, Acceptance and Production. Development and test environments are equal (mirrored) and same account for acceptance and production. Deploying DTAP environments is easy, because there are many factors influencing outcome of DTAP. One factor is pricing and licensing costs. BizTalk is available in different editions and since BizTalk depends on operating system and database there are some configuration options to. Here is an overview of possible configurations for DTAP environments, hence there is a nice BizTalk Feature Dependency Matrix. Besides features, there are non-functional requirements to reckon with like fail-over, availability and scalability. On Microsoft Pricing and Licensing FAQ section of BizTalk I found this on non functional side. In a large BizTalk Server 2006 R2 deployment, two key considerations ensure redundancy in all areas of the architect
Read more

Table Operation on Oracle 11g XE with OracleDbBinding

Image
Last year I did a post on table operations on SQL Server Table with WCF-SQL Adapter from BizTalk on SQL. Now I like to do similar thing on table in Oracle 11g Express database using the Oracle Binding from BizTalk Adapter Pack 2010. Oracle 11g Express Edition is available through OTN. For downloading software you will need to have an account! It is important that when accessing Oracle you have appropriate client installed. This can sometimes be a hassle if you do not know, which client you need. When you trying to connect to Oracle you will get error messages like: Connecting to the LOB system has failed. Could not load file or assembly 'Oracle.DataAccess, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.. This can be pretty annoying, but there is documentation that can help you going the right direction. In installation documentation I read that supported versions are: Oracle d
Read more

Integration, the community blasting into 2017!

Image
In the past, now and tomorrow I will keep saying that integration is relevant, even in this day and age of digitalization. Integration professionals will play an essential role in providing connectivity between systems, devices and services. Integration drives the digitalization forward by connecting everything with anything. Integration skills are still in demand. A LinkedIn study revealed a top 10 with integration and middleware in top 5 with Cloud, Data Science, UX and Web. And the expectation for 2017 is that this will be similar. Our toolbox is expanding from on premise tooling to cloud services; we have WCF, BizTalk Server, MSMQ to Logic Apps, API Management and Service Bus. Formats ranging from flat file, EDI, XML to JSON, several protocols open and proprietary, tooling from mappers to BizTalk360. It can be challenging yet much more exciting. My conclusion is more options, more fun, at least in my view. In February I, will be travellin
Read more