Make SQL-Azure 100% T-SQL compatible
I script I maintain/exported from an on-premise site should just run on the cloud.
It doesn't matter if it implements some tokens as a No-OP or with warnings or notes. But it should compile unaltered and run on Azure.
In a DB as a service model, that Azure SQL database is, full t-SQL compatibility isn’t something that we can promise or deliver on in the short/medium term. While we are working on improvements, 100% is a very high goal.
We do have two great solutions for SQL in Windows Azure. SQL Server in a Windows Azure VM is the best choice for highest T-SQL compatibility. Azure SQL Database is the best choice when writing new apps, or when existing apps can be modified to run with the subset of T-SQL that Azure SQL Database supports.
If you have other feedback, email@example.com would be great
Neal Miller commented
If you generate a script using the Generate Scripts wizard in SQL Server Management Studio, there is an advanced option that generates SQL Azure-compatible T-SQL code. On the "Set Scripting Options" page, click the "Advanced" button, then find "Script for the database engine type", and select "SQL Azure database" from the dropdown. This helped me greatly, and does not require any changes to SQL Azure.
Michael-Rainabba Richardson commented
"SQL Server in a Windows Azure VM" isn't a "cloud solution" for any number of reasons and recommending it in the context of "Azure SQL Database" reminds me of the Scroogled commercials; it's a silly redirection.
Google has given us Cloud SQL (100% compatible MYSQL with real "cloud" features) and if there's any chance that I'm going to stick with MSSQL, I want the same feature-set. As it is, business needs (real growth) is forcing me to seriously consider Node + MySQL (or CouchDB) for a properly scalable architecture that won't require the administration team to be as large as the development team. That kind of appeal is what sold us on MS services 7+ years ago back when OSS was a nightmare to manage. Now we have an almost infinite number of choices that are far less costly, far less management intensive, far MORE performant and they scale better. Digital Ocean is a great example.
If I have to redesign in order to get cloud scalability, I'm going to do it the right way and get real cloud scalability at a lower cost with more agility. Let's not forget the massively larger tallant pool and reduced talant costs associated with these OSS platforms compared to "Azure compatible" technologies.
Ms better get with the program. This isn't their world anymore and acting like the world is going to bend over backwards to be able to use their systems (desktop, office, mobile, tablet) while spending more money to do it is only going to result in their complete loss of the market (as opposed to the nearly complete loss they now are facing).
Peter Obiefuna commented
@DBMan: You would have a point only if we no-op things that actually have a semantic equivalent in Azure. No-op with warning has been the way these things are done in other platforms. Using your example, NO-OP of USE DB in a single DB, single instance context would be perfectly reasonable.
Eg: how does the OS implement code that drives a hardware camera in versions of a phone that has no camera? It NO-OP.
Microsoft just kinda doesn't take the time to do it right the first time around. Little wonder they keep struggling.
I really question the wisdom of " Azure SQL Database is the best choice when writing new apps". Obviously, since there are large functionality gaps, the best choice is that one that best suits your application's needs. There are serious limitations to Azure SQL Database.
The T-SQL FREETEXTTABLE function is not supported for my sematic research method in my stored procedure.
Is there a solution to replace it ?
SELECT ... INTO should work in azure.
Ahsan Mumtaz commented
It wont be possible to have 100% T -SQL compatibilty, because customer do not have direct access to the database. As we have T-SQL for our traditonal SQL Server (on premises)to create/monitor filegroup/datafile/logfile. But in SQL Azure customer do not have access of these atrbiutes, so no point to have T-SQL for thse attributes
@DBman: & @Jamie: Thanks guys for your votes in support, especially Jamie, for whom this perhaps represents a significant rethink from the forum opinion on the same subject.
I've given a bit of thought to both comments; and am wondering ... perhaps, MSFT should
1. accept the conceptual idea of seeking to achieve full compatibility (not in the spirit of IE6, though)
2. Phase the implementation like:
(a) provide full syntactic compatibility for everything ... emit what you see fit for each opcode
(b) provide semantic compatibility (with appropriate and well documented semantic equivalences where an exact match does not make sense) for DDLs (except stored procs, functions and such like)
(c) Do the best you can (with community input) for DMLs, stored procs, functions and such like.
MSFT already knows this to be a problem. The first (conceptual) cut they announced was a tooling approach. "Make SQL Management Studio smarter". I rather say, make the LEXER/PARSER smarter. This is not syntax coloring! It's a language issue. It should be addressed in the language.
Rajib Bahar commented
I'll 3rd it. It'll help us learn the technology easily.
Jamie Thomson commented
I'm totally in favour of as much T-SQL compatibility as possible (and I will be giving this idea 3 of my votes) but we must also face the reality that T-SQL as it exists today is not 100% compatible with SQL Azure.
I only know of one feature that is not compatible with SQL Azure but I'm pessimistically assuming there are more. The feature that I know about is SELECT...INTO. Every table created on SQL Azure requires a clustered index and the reasons for that are rooted in the high-availability mechanisms that SQL Azure provides. SELECT ... INTO creates heaps, hence not compatible with SQL Azure.
So, not 100% compatible but its mightily close. Nevertheless, even given what I just said this still gets maximum votes from me!
I all for 100% T-SQL compatibility but it would be problematic to no-op unsupported T-SQL.
I assume that if my application sends a T-SQL statement to a server then the application probably depends on the correct execution of that statement to function correctly. If SQL Azure just ignores (no-ops) statements that are not supported then my applications may function syntactically but not semantically. For example, if you no-op the “use DB” statement my script will assume “use” worked but in reality I am still operating on the database before the execution of “use” – this may result in my scripts executing DDL on the wrong database.
I vote for 100% semantically correct T-SQL.