Uzure SQL gotchas & limitations - definitive list? Also Replicaiton vs Data sync
Hi, one of our customers wants to move of their custom SQL Server DB into Azure DB. We're trying from a SQL Server developers point of view to identify pitfalls and limitations.
I'm finding it hard to find definitive answers on a number of question. the biggest issues are that MSs own "list of limitations" from this link:
https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/
does not elaborate at all on what "partially supported" means.
the second issue is a lot of articles are already out of date. For example I found one document from jan 2016 - only 4 months old, but this states triggers are not supported at all. this is already out of date and inaccurate.
Has anyone got a definitive list of Azure limitations, can expand on what "partially supported" means and any up to date do's/donts or case studies where you should NOT use Azure SQL?
I'm taking the mentality that Azure SQL is basically like classic shared database hosting - you can do stuff in your own DB and that's it. Am I correct to be thinking this way - I don't want to be telling the customer to move to Azure SQL if they cannot actually make the application work !
Specific interest to me is:
1) what is not supported, a full explanation of "partially supported". for example it's not overly comforting that CREATE TABLE is listed under "partially supported"!!
2) What should you do if there is a large reporting requirement? should you use 2 separate Azure SQL serves, one from transactions, one for reporting? should it be one DB and let Azure scalability manage the load? if it is 2 different DBs, do you use
data sync to achieve this? will it work with none trivial (2 million row) tables OK?
3) Index management - where are index rebuilds/re organisation managed?
4) are geographic indexes Ok in azure SQL?
5) if certificates are not supported how should a password field for example be encrypted?
thanks in advance. it's not for lack of trying to find these answers myself, I am not sure I’m reading correct or up to date information and might also be missing new terminology, features, etc.
I'm finding it hard to find definitive answers on a number of question. the biggest issues are that MSs own "list of limitations" from this link:
https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/
does not elaborate at all on what "partially supported" means.
the second issue is a lot of articles are already out of date. For example I found one document from jan 2016 - only 4 months old, but this states triggers are not supported at all. this is already out of date and inaccurate.
Has anyone got a definitive list of Azure limitations, can expand on what "partially supported" means and any up to date do's/donts or case studies where you should NOT use Azure SQL?
I'm taking the mentality that Azure SQL is basically like classic shared database hosting - you can do stuff in your own DB and that's it. Am I correct to be thinking this way - I don't want to be telling the customer to move to Azure SQL if they cannot actually make the application work !
Specific interest to me is:
1) what is not supported, a full explanation of "partially supported". for example it's not overly comforting that CREATE TABLE is listed under "partially supported"!!
2) What should you do if there is a large reporting requirement? should you use 2 separate Azure SQL serves, one from transactions, one for reporting? should it be one DB and let Azure scalability manage the load? if it is 2 different DBs, do you use
data sync to achieve this? will it work with none trivial (2 million row) tables OK?
3) Index management - where are index rebuilds/re organisation managed?
4) are geographic indexes Ok in azure SQL?
5) if certificates are not supported how should a password field for example be encrypted?
thanks in advance. it's not for lack of trying to find these answers myself, I am not sure I’m reading correct or up to date information and might also be missing new terminology, features, etc.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Documentation I'm afraid I would still "disagree" here: especially as the Azure doc references the full SQL doc The hyperlinks I checked (from the Azure page you posted) all point to pages that are Azure specific, except were the features are fully supported, and the documentation on those pages further describes some Azure specific issues as well. At the top of the pages, you will see a green checkmark against the SQL version which the documentation page refers to. "A side by side comparison" is actually very complicated due to differences in the syntax and all. We can agree to disagree :) :) :)
Reporting I see. I had assumed you are just referring to the reporting workload on the Azure SQL instance :). Thanks for clarifying that one.
As to documentation being out of date, I also agree. Microsoft products are evolving a lot faster than the documentation ... I've been banging head against the wall a lot with ASP.NET Core... many times, names are just renamed slightly and you only find out after a fair amount of Binging! Or there simply isn't any documentation at all.
All the best :) :)
Reporting I see. I had assumed you are just referring to the reporting workload on the Azure SQL instance :). Thanks for clarifying that one.
As to documentation being out of date, I also agree. Microsoft products are evolving a lot faster than the documentation ... I've been banging head against the wall a lot with ASP.NET Core... many times, names are just renamed slightly and you only find out after a fair amount of Binging! Or there simply isn't any documentation at all.
All the best :) :)
ASKER
for point 1 - I have read the documentation and I dont think it's particularly clear, especially as the Azure doc references the full SQL doc. As you say, a side by side comparison seems to be the only way to see what is missing, but this could lead to misinterperation.
with regard to reporting I was talking more about SSRS. There is mention of "reporting" pricing on Azure SQL, but I have since found a good article that explains SSRS is not compatible with Azure SQL as a datasource and to use a full SQL instance on an Azure VM. At which pint my question there is answered. You do need a separate serve for SSRS.
again though, a lot of articles relating to this are fairly dated and inaccurate.
thanks for the info on the other points.