How did Amazon achieve 100% MySql compatibility on the cloud?

Question

... I think they did it by actually supporting all those operators that it ordinarily would not like. 

Then, they implemented a mapping rule for each of those operators that have a cloud-equivalent;

and  implemented a noop for the ones that don't make cloud sense (like T-SQL's 'use DatabaseName').

Why would Sql Azure not do that? Post-gen doctoring of existing scripts is not very cool. Looks like hacking to me. Believe me. Completely removing that migration inertia is a great opportunity. 

I know there are plans to throw tools at it and let SQL Management Console do some kind of "Script for SQL Azure". That's definitely a way. But does not cover more agile scenarios where teams have a very mature set of scripts, mostly hand-crafted that routinely get applied to databases for each new version of the system (script-based deployments).

I say, let's learn from Amazon Relational Cloud's pragmatic approach. Let a Valid On-premise script compile well and build well within Sql Azure. Any objects it fails to create, let it just generate a warning and keep going, that's all.

---

Pita.O:
http://www.arizentax.com/
http://www.cgi.com

Answer 1

Well, fair points, but let's be honest its not an apples-to-apples comparison. SQL Azure and Amazon RDS both have their own particular set of differentiators and, of course, their limitations and hence you can't apply the same set of rules/standards to both. I'm not saying one is better than the other but they ARE different and eash suits different scenarios. I use the analogy of a car and a motorbike - they both get you from A to B but in very different ways and both have their pluses and minuses.

I'm interested in your opinion in response to that!

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 2

>> I'm interested in your opinion in response to that!

Well Jamie, I don't really have any technical statement to reply to. Are you asking me to respond to your generalized statements about how things are different and so can't be compared? Granted for one second that they're different, I believe that you certainly can "apply the same set of rules/standards to" Apples and Oranges: They should both be edible, for instance, is a fair expectation.

So, perhaps, you should take another whack at my observation and tell me why I cannot expect MSFT SQL Azure to learn something cool that their competition is doing so well and either meet or exceed it.

And by the way, what's so different b/w using an on-premise T-SQL script to create a cloud relational SQL database that does work ... compared to using an on-premise MySQL script to create a cloud relational MySql script.

---

Pita.O:
http://www.arizentax.com/
http://www.cgi.com

Answer 3

I have to join Pita's opinion with some constraints.
First I agree that it would be tremendous relief for developers to maintain only one SQL Script and deploy it wihout any fatal errors on both on-premise and Azure SQL.
However I will agree that there should be a mechanism which explicitly tells you if something is not supported. So you don't blindly deploy your script, but be aware of any issues you might have when deploying. And there shall be some distinguishment between what is a "Fatal error" and stops script execution and which is "warning" and allows contiuation of script execution.
For instance when you create tables and you are using "ALLOW_ROW _LOCKS ", it is not supported in Azure, but your table can be created ignoring this. However if your application logic relies in some way to the Allow_Row_Locks - you have to be explicitly warned and be aware of situation.
Let's take a look at other case - I am developing a location aware application where I wanted to use SPATIAL datatypes. I created my model locally in SQL 2008 and tried to migrate it to Azure - WOW. What would be my surprice if Azure blindly created a table and put varbinary datatype instead of Geography for instance?

I am recently not using MySQL very extensively, but I've been using it since it was 3.23 up do 5.1.something. And I can state that SQL Server is fare more mature RDBMS which has far more features than MySQL (or at least it used to, I don't know where did MySQL 6 go). But maybe it is far more safe to ignore MySQL not'supported features in the cloud than MS SQL.

And here is a trade-off - I don't want the server I am executing a script against to blindly ignore not supported features that I want, but I also want an easier way to maintain already generated deployment script. Also, what I would like after commercial launch would be a tool that will allow me to evaluate a T-SQL statements against SQL Azure without having to pay for SQL Azure to understand what is and what is not supported.

These are my comments on the topic.

Answer 4

Thanks, Anton, for your support. Certainly, Anton's views provide further clarification to my position with additional conceptual detail, while still keeping it conceptual.

The summary of our view, if I may is that the lexar/parser (or whatever gives SQL Azure  its juice) should "support" the full T-SQL lexicon. It could decide what to do with the unsupported portions. But whatever it does, produce a database similar to what it would've produced if the developer had post-gen hacked the script to weed out Non-Azure portions of the script.

Throwing tools at it is just treating symptoms and forces tool developers in the ecosystem (like TOAD) to spend needless time and funds upgrading for Azureness.

SQL Azure is good stuff and give the short time and pressure to make the turn around from ACE to TDS, I'll say kudos. But now, let's just have a single source of 'clean'. 

---

Pita.O:
http://www.arizentax.com/
http://www.cgi.com

Answer 5

Hi again,
One thing I can add is that I am really impatient to see DataDude compatible with Azure, eliminating the need for SqlAzure Migration Wizard!
I don't know if anybody reading this is aware of what DataDude is, but if hear it for the first time check out these links:

http://blogs.msdn.com/gertd/
http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en

It is amazing for it gives to teams wokring on a DataBase project!