Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

On-Premises SQL Server Integration with Azure Application Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 1:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:03 AM
Points: 47, Visits: 363
We have an application (called Umbraco) hosted on Azure. We also have an on-premises SQL Server database. We'd like to integrate the two. Specifically, we want to push or pull data from the on-prem DB to Umbraco's database.

My approach was to use Azure's Data Sync to synchronize my on-prem DB with custom tables I'd add to the Umbraco DB. I would then build a process to poll the tables for changes and then push those changes through Umbraco's API.

My co-worker doesn't like this approach because he doesn't like the idea of constantly polling the tables to find changes. He wants to use a CLR proc in the on-prem DB to call the Umbraco API. I'm concerned about this approach primarily because of security concerns.

Are one of these ideas better? Are there other ideas?

Any help is much appreciated!

Thanks!!
Post #1585668
Posted Tuesday, June 24, 2014 8:55 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 15,501, Visits: 27,887
As much as you can, I'd try to keep any queries against the SQL database up in the Azure cloud. Any, regardless of if you write them or you use the API, that pull data down locally, are going to be costly. So, if you can create a service or function that does the polling within Azure, you'll be better off.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1585728
Posted Wednesday, June 25, 2014 6:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:03 AM
Points: 47, Visits: 363
Thanks Grant! I appreciate the help. So would you say that polling isn't necessarily a bad idea in and of itself?

P.S. Your books have been a huge help! I carry your tuning book in my work bag.
Post #1585851
Posted Wednesday, June 25, 2014 6:28 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 15,501, Visits: 27,887
I wouldn't say polling is bad, no. Not at all. It's one way to get things done. How you poll, where you poll, how often you poll, those are the tougher questions to answer.

And thanks. Glad it's helpful.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1585858
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse