September 15, 2017 at 8:58 am
Hi,
I'm just playing around with hosting an Azure database in the cloud (not on a virtual SQL server).
What I'd like to know is if it's possible to connect this thing to MySQL (the MySQL instance is on AWS).
Normally I'd just install the MySQL drivers and created a linked server, can I do this on Azure, or do I need to build a full SQL server on the cloud?
I'm not really interested in using the REST API - it's too resource intensive on both sides of the equation, I just want to connect via ODBC and fire an occasional ETL query, so the request would originate on the Azure instance and just OPENQUERY across (or call an SP on the MySQL side, I'm not too fussy).
Thanks
Rich
September 15, 2017 at 9:47 am
Linked servers aren't supported in Azure SDB.
Are you trying to perform distributed queries? I assume you don't want to do ETL?
September 15, 2017 at 10:04 am
Right, that saves me a lot of pain Steve, thankyou. I'm sure Microsoft mention this somewhere but they haven't gone out of their way to make it obvious 😉
I have some queries defined in MySQL. I want the results of these queries to be stacked up in a table. It is ETL at a very basic level, I just want to use the last load date in the table as a parameter in the MySQL query.
So, I need to fire queries on a timer based on the the last load date of the current data it holds (and no, I don't even know if I can do that on Azure yet).
Call me a dinosaur but linked servers and ODBC are a really cheap and reliable way to achieve this. Is there something else I could be looking at for doing this?
Basically I've got 2 weeks to build a very quick and dirty data mart that'll feed out to Excel/Power BI and leave them with something I can support remotely, they're a startup so I need to keep the billing to the absolute minimum. I'm already familiar with the data so it's really just the mechanics I'm concerned with at the moment.
September 15, 2017 at 10:17 am
It's documented. There are a set of features in SQL Server that make less sense at the databaase level. Linked Servers are instance level, so some architectural change is needed to the database to get these to work. They're part of the same type of work that would get us contained jobs as well. Fingers crossed someone does this.
For your situation, I'd look at the serverless stuff. Not sure what this is in AWS, but it's there. In Azure, it's Azure Functions. You should be able to write a query on a timer that gets from MySQL, inserts to SQL Server. I'd think that wouldn't be too hard.'
Maybe this: https://stackoverflow.com/questions/37215069/azure-functions-database-connection-string
or this: https://stackoverflow.com/questions/44620692/unable-to-connect-to-azure-mysql-database-through-azure-function-c-sharp
September 15, 2017 at 11:10 am
Thanks Steve,
Hmmmm... Had a demo from MS about the serverless stuff last week,it's pretty sexy but I was seriously unimpressed with the results, their function created data that wasn't even included in the input parameters - I think you'd have to build a big framework around it to ensure data integrity.
My own view (which could well be wrong and makes me feel old) is that if you want data integrity you're better off directly querying an RDBMS for the foreseeable future, people keep trying to sell me on these magical systems that it's impossible to test because latency. They're great, but as far as they could assure me you're really just pinging stuff into the ether with little in-built reassurance that it'll work. MS will give you a reassurance on data consistency after it's stored but as far as whether it'll actually collect the data in the first place you're on your own.
The trouble, much like the problem with web APIs, is that programmers think this stuff is bloody great, but very few of them can explain how we're supposed to test it, or why we should be shooting everything from a DB, through the application layer, to the api, to the receiving api, through the application layer again and merged/versioned/UID tracked back to the receiving DB when there's a perfectly good standard fine honed over 30 years which gives you a timed snapshot which enforces uniqueness at a point in time and can be optimised against the DB.
But maybe they've never had to reconcile back to the management accounts....
I think I'll just stick an SQL instance on a desktop for now 😉
September 15, 2017 at 1:36 pm
LOL, I can understand that. The serverless stuff is interesting for some things. Not sure I'd use it for ETL either, but you certainly could just query/update things. Tracking how it works, testing, debugging, all hard things. I don't have a great solution if you want all Azure. Data Factory might be overkill. A VM in the cloud you spin up and down to move things seems like too much as well.
Ultimately you need some process/app that gets data and inserts/updates, right? I'd be tempted to build some process that just grabs data and moves it to some staging on your SQL instance and then another process to merges this into your system. Not sure where the best place to put this is, but maybe there's something on the AWS side that can read and then push to ASD with an ODBC driver.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply