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.
Next inside online database you can create a new table and add columns, data type e.a.
Click Save after you are done. If you click table you see newly created table.
Next step is to have data table. Click New Query and paste your insert data query.
Click Execute.
If you perform a select statement you will get following result.
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.
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:
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.
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.
Query Notification SQL Azure BizTalk WCF-SQL: Possible?
