Query Notification SQL Azure BizTalk WCF-SQL: Possible?

Posted: October 4, 2011  |  Categories: BizTalk BizTalk Server 2010 SQL Azure Uncategorized
Tags:
You can download the entire article as a PDF document.
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

You can download the entire article as a PDF document.
Query Notification SQL Azure BizTalk WCF-SQL: Possible?

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.

You can download the entire article as a PDF document.
Query Notification SQL Azure BizTalk WCF-SQL: Possible?
#1 all-in-one platform for Microsoft BizTalk Server management and monitoring
Author: Steef-Jan Wiggers

Steef-Jan Wiggers is all in on Microsoft Azure, Integration, and Data Science. He has over 15 years’ experience in a wide variety of scenarios such as custom .NET solution development, overseeing large enterprise integrations, building web services, managing projects, designing web services, experimenting with data, SQL Server database administration, and consulting. Steef-Jan loves challenges in the Microsoft playing field combining it with his domain knowledge in energy, utility, banking, insurance, healthcare, agriculture, (local) government, bio-sciences, retail, travel, and logistics. He is very active in the community as a blogger, TechNet Wiki author, book author, and global public speaker. For these efforts, Microsoft has recognized him a Microsoft MVP for the past 8 years.

turbo360

Back to Top