SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connect Azure to MySQL


Connect Azure to MySQL

Author
Message
richard.gardner87
richard.gardner87
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 7
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)

Group: Administrators
Points: 397800 Visits: 20439
Linked servers aren't supported in Azure SDB.

Are you trying to perform distributed queries? I assume you don't want to do ETL?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
richard.gardner87
richard.gardner87
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 7
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 Wink

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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)

Group: Administrators
Points: 397800 Visits: 20439
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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
richard.gardner87
richard.gardner87
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 7
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 Wink
Steve Jones
Steve Jones
SSC Guru
SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)SSC Guru (397K reputation)

Group: Administrators
Points: 397800 Visits: 20439
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search