Working with Azure WebJobs with Hybrid SQL Connection

[polymathic9999]

I have been using SqlDependencyEx with a console App successfully. I get the notifications, the actual data changes etc.

I have to move this executable to an Azure WebJob, and need to work with an on-prem SQL server database for now. Would SqlDependencyEx still work in this scenario?

Awesome project, thanks.

Swanand

[dyatchenko]

Hi,

Thanks for using the project. If you use Azure SQL PaaS then it won't work because this version of SQL server is very restricted. And it doesn't have Service Broker which is used for notifications. Service Broker is the only solution to have some sort of notification in SQL server. Thus, none of the existing notification solutions will work there. They may implemented it already, but I'm not sure. I checked it about a year ago and it didn't work.

However, you can use an Azure IaaS VM and configure SQL server there.

Thanks, Dmitrii

On 16 Dec 2016, at 23:05, polymathic9999 notifications@github.com wrote:

I have been using SqlDependencyEx with a console App successfully. I get the notifications, the actual data changes etc.

I have to move this executable to an Azure WebJob, and need to work with an on-prem SQL server database for now. Would SqlDependencyEx still work in this scenario?

Awesome project, thanks.

Swanand

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[polymathic9999]

Thanks for your quick reply.

The database is a regular SQL server 2014 instance on premises, it is NOT an Azure SQL database. As I mentioned, I used a console app on my local machine and was able to receive notifications.

I am trying to move the console app (exe) to an Azure WebJob, so I have to use a hybrid SQL connector to connect to an on-premise database. Theoretically, it should work.

The reason we are not using a VM is cost. Maybe it will change if this is the only way.

Thanks again.

Best regards Swanand

On Fri, Dec 16, 2016 at 12:39 PM, Dmitriy Dyatchenko < notifications@github.com> wrote:

Hi,

Thanks for using the project. If you use Azure SQL PaaS then it won't work because this version of SQL server is very restricted. And it doesn't have Service Broker which is used for notifications. Service Broker is the only solution to have some sort of notification in SQL server. Thus, none of the existing notification solutions will work there. They may implemented it already, but I'm not sure. I checked it about a year ago and it didn't work.

However, you can use an Azure IaaS VM and configure SQL server there.

Thanks, Dmitrii

On 16 Dec 2016, at 23:05, polymathic9999 notifications@github.com wrote:

I have been using SqlDependencyEx with a console App successfully. I get the notifications, the actual data changes etc.

I have to move this executable to an Azure WebJob, and need to work with an on-prem SQL server database for now. Would SqlDependencyEx still work in this scenario?

Awesome project, thanks.

Swanand

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/dyatchenko/ServiceBrokerListener/issues/36#issuecomment-267690660, or mute the thread https://github.com/notifications/unsubscribe-auth/AXeOc7E6M3xzBYeDpYTo7ewc5K5Ad2yCks5rIvcPgaJpZM4LPf6L .

[Siliconrob]

Your data is on premise, but your compute is off premise? You do know that latency will kill you in this situation and compute should be as close to data as possible to avoid network issues and costs.

Natural evolution of your console app to something that runs all the time is a windows service. Check out TopShelf

[dyatchenko]

If there are no updates I'm closing it.

[polymathic9999]

Thanks Dmitriy.

Sorry, your email was kind of lost in my inbox and I couldn't reply earlier. I did exactly as you suggested, the windows service solutions works just fine.

Had another question regarding permissions: I granted the following to the user (used in my connection string).

GRANT CREATE PROCEDURE TO [servicebroker]; GRANT CREATE SERVICE TO [servicebroker]; GRANT CREATE QUEUE TO [servicebroker]; GRANT REFERENCES ON CONTRACT::[DEFAULT] TO [servicebroker]; GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [servicebroker]; GRANT CONTROL ON SCHEMA::[dbo] TO [servicebroker]; GRANT CREATE MESSAGE TYPE TO [servicebroker];

However, I was not getting notifications. Once I gave the sysadmin role, I got the notifications. Am I missing something here? Are these permissions not sufficient (without sysdamin)?

Thanks Swanand

On Sat, Dec 24, 2016 at 5:01 PM, Dmitriy Dyatchenko < notifications@github.com> wrote:

Closed #36 https://github.com/dyatchenko/ServiceBrokerListener/issues/36 .

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/dyatchenko/ServiceBrokerListener/issues/36#event-904632850, or mute the thread https://github.com/notifications/unsubscribe-auth/AXeOc21TPxOzSbTGMfZ5iRslDGKPExpMks5rLcBygaJpZM4LPf6L .

[dyatchenko]

Hi,

This is strange - there are unit tests which check permission issues. Are all your tables in dbo schema?

Thanks, Dmitrii

[polymathic9999]

Yes, they are.

Thanks Swanand

On Wed, Jan 4, 2017 at 10:26 PM, Dmitriy Dyatchenko < notifications@github.com> wrote:

Hi,

This is strange - there are unit tests which check permission issues. Are all your tables in dbo schema?

Thanks, Dmitrii

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/dyatchenko/ServiceBrokerListener/issues/36#issuecomment-270573415, or mute the thread https://github.com/notifications/unsubscribe-auth/AXeOc6ZUc4b4pSRSWdWeIRUeD16aw1fRks5rPI0ZgaJpZM4LPf6L .