23

now I'm looking on Azure and want to migrate on this one. But I'm not sure that don't get a problems on this way. Could you explain what is the difference? And what I defenetly can't do with SQL Azure?

David Makogon
  • 69,407
  • 21
  • 141
  • 189
Artyom
  • 439
  • 1
  • 5
  • 9
  • I wrote this Google query to check for the unsupported features with references to SQL mentioned in the MSDN Azure documentation http://www.google.com/custom?q=site%3Amsdn.microsoft.com%2Fen-us%2Flibrary%2Fwindowsazure%2F+%22SQL%22+%22not+supported%22&btnG=Search – mvark Sep 30 '13 at 11:38

3 Answers3

24

SQL Azure is a subset of SQL server.
That means some features present in SQL server won't be present in SQL Azure.


So it has a lot of unsupported features
http://msdn.microsoft.com/en-us/library/ee336253.aspx

And some other limitations
http://msdn.microsoft.com/en-us/library/ff394115.aspx



Azure does not support CLR stored procedures (extended stored procedures neither)
https://feedback.azure.com/forums/217321-sql-database/suggestions/401015-support-clr-stored-procedures

And stored procedure support in general is questionable http://blogs.msdn.com/b/sajid/archive/2010/04/22/restrictions-of-stored-procedures-in-sql-azure.aspx

And it does not have scheduled tasks (or procedures)
Scheduled Tasks with Sql Azure?

And it does not support fulltext indexing either
https://feedback.azure.com/forums/217321-sql-database/suggestions/405464-support-full-text-indexing
SQL Azure - Substring Searches?

You can't do cross-database referencing (not anymore, see https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/)
http://www.mygreatwindowsazureidea.com/forums/34685-sql-azure-feature-voting/suggestions/402636-cross-database-reference?ref=title

Spatial data support is missing as well (not anymore)
http://www.keepitsimpleandfast.com/2009/12/main-differences-between-sql-azure-and.html

It doesn't support filestream
https://feedback.azure.com/forums/217321-sql-database/suggestions/413287-implement-filestream-for-blobs-into-azure-blob-sto

There is no profiler for SQL azure
https://feedback.azure.com/forums/217321-sql-database/suggestions/431943-profiler-for-sql-azure

The database won't automagically increase it's max allowed size
SQL Azure: What will happen if size of my SQL Azure get 5GB?

And there is no full support for SSMS
http://www.mygreatwindowsazureidea.com/forums/34685-sql-azure-feature-voting/suggestions/476408-full-management-studio-support?ref=title

It doesn't support ASP.NET sessions (not anymore, but you need to mark your session classes with the Serializable attribute)
http://www.mygreatwindowsazureidea.com/forums/34685-sql-azure-feature-voting/suggestions/472024-add-support-for-asp-net-sessions-in-sql-azure?ref=title

Then, there is no way to send emails
sending email from sql azure

This is how you connect to it, using SSMS:
http://www.mssqltips.com/tip.asp?tip=1950


Addendum: And it's not possible to have a database larger than 150 1000 GB
(500 GB per 03. April 2014)
(1000 GB per 19. February 2016)
Overcoming Windows Azure Sql Database 150 gb size limitation





And it's cost model is 'cloudy' at best
http://www.microsoft.com/windowsazure/pricing/
http://searchwindevelopment.techtarget.com/news/1507649/Windows-Azures-hidden-compute-costs
Real World Windows Azure Costing Examples, Anyone?
http://www.brentozar.com/archive/2009/07/sql-azure-pricing-10-for-1gb-100-for-10gb/

Forget that 9.99 value, it's not the truth.
It's only the rent for the data - you also need to rent the rest.
Azure's pricing is complicated
And at least 80$ a month.
http://www.microsoft.com/windowsazure/pricing-calculator/

On a year, that's 960 $ you spend on renting (mimimum, and you won't have too much for this).

Basically, just browse this:
http://www.mygreatwindowsazureidea.com/forums/34685-sql-azure-feature-voting
and this
https://stackoverflow.com/search?page=2&tab=relevance&q=sql%20azure%20unsupported
for a quick reference of the related problems.

Never mind that you store your (or your customers) sensitive data on someone else's server (taxation, patent filings, offers, customer lists, your database schema, etc.).

Community
  • 1
  • 1
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
  • 1
    Spatial data is supported, and the Universal Providers support SQL Azure as a session store, out-of-the-box. You can run SQL Azure independently of any Compute, accessing it from any host anywhere. So don't forget that $9.99 value. Regarding db size: you run an ALTER DATABASE to set max size (up to 150GB), and the db grows/shrinks as necessary, where you pay for the tier your data reaches, amortized daily. And don't forget the fact that all data in SQL Azure (and Azure Blobs, Tables, and Queues) are triple-replicated within the data center. – David Makogon Feb 02 '12 at 02:11
  • 1
    So what CAN it do? Looks pretty lame to me. – Rosdi Kasim Jun 18 '14 at 03:47
  • 1
    @David Makogon: What happens if your database exceeds 150 GB (easily possible when putting images in the db) ? – Stefan Steiger Mar 12 '15 at 13:22
  • @StefanSteiger - this particular answer is over 3 years old. DB size is now up to 500GB per database. As far as exceeding that: You'd need to have multiple databases. As far as storing images in your db: That's a topic for another day (and out of scope here... :) ). – David Makogon Mar 12 '15 at 14:27
  • @David Makogon: We are currently in the process of developing a document management application for the swiss postal services. This means storing of documents (versioning, no deletes) (doc, docx, xls, xls, ppt, pptx, pdf, pdfa, epub, p, djvu, ods, odt, odp, txt, dwg, jpg, jpeg, png, gif, webp, bmp, svg, webm, 3gp, avi, 7z, zip, rar, tar, dxf, dwg, wav, ogg, mp3, html). I am right at this moment importing 260 GB of documents into this database, Those 260 GB are less than 50% of what's to come. While storing those documents in another db would be possible, the wisdom of that is questionable... – Stefan Steiger Jun 12 '15 at 06:54
  • @David Makogon: PS: And before depicting multiple databases as a solution, you'll first have to develop foreign keys that can be applied over multiple databases. After all, data integrity is what a database is really all about, otherwise one could store all the data in a few JSON/XML files on the hard drive, and just have a web-service queuing & executing the read-write operations... As for not storing images in a database: Yes, we did precisely that, and good luck when you need to show an image in a report (you know, that (quirksmode-HTML 4/3.2) SSRS-thing of that company you work for). – Stefan Steiger Jun 12 '15 at 07:09
  • @StefanSteiger - Like I said in my prior comment, this is not the place to debate database design. But since you brought it up again, I'll say this: SQL Server is *not* the place you should be storing binary content such as what you listed. That content can easily be stored in blob storage, which gives you 500TB of headroom. Binary content isn't queryable, and it makes zero sense to store it in SQL Database. – David Makogon Jun 12 '15 at 11:02
  • @David Makogon: I wouldn't be that sure about that. When I have the data at 2 different places, I need to a) configure in "main application" where that place is (and nobody ever does that) b) other applications cannot read the config values of "main application" c) i need to ensure the integrity of the backup (database in sync with file-storage) d) I need ACID compliance (one user can query, while another deletes or updates, and transaction rollback if anything isn't in order) e) with BLOB storage as the backend, you need to ensure the write-operation succeed(MongoDB is BAD) – Stefan Steiger Jun 12 '15 at 12:00
  • @StefanSteiger - This is why these discussions don't belong in StackOverflow comments. Good luck with your app. – David Makogon Jun 12 '15 at 12:57
  • @David Makogon: You're right, it doesn't. But thanks anyway - though I hope I don't need it ;) It's going productive in 2 months. I realized I should really have used filestream - it's really the only reasonable choice in this case. Maybe bringing filestream to Azure would be a really good idea for your customers with such a scenario. I checked, it's not yet there. I also see cross-database refencing has been implemented in Q4 2015 - corrected that now. – Stefan Steiger Jan 20 '16 at 10:39
1

SQL Azure is a cloud based service:

Microsoft® SQL Azure™ Database is a cloud-based relational database service built on SQL Server® technologies. It provides a highly available, scalable, multi-tenant database service hosted by Microsoft in the cloud. SQL Azure Database helps to ease provisioning and deployment of multiple databases. Developers do not have to install, setup, patch or manage any software. High availability and fault tolerance is built-in and no physical administration is required. SQL Azure Database supports Transact-SQL (T-SQL). Customers can use existing knowledge in T-SQL development and a familiar relational data model for symmetry with existing on-premises databases. SQL Azure Database can help reduce costs by integrating with existing toolsets and providing symmetry with on-premises and cloud databases.

SQL Server 2008 is server based (local or hosted)

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
1

This link specifies Transact-SQL constructs that are not supported in Azure, or partially supported:

http://msdn.microsoft.com/en-us/library/ee336281.aspx

For example, unsupported: http://msdn.microsoft.com/en-us/library/ee336253.aspx

Partially supported: http://msdn.microsoft.com/en-us/library/ee336267.aspx

Finally, here's a nice reference to deployment: http://social.technet.microsoft.com/wiki/contents/articles/developing-and-deploying-with-sql-azure.aspx

If you have a specific question, please ask.

Hope that helps.!

Kieren Johnstone
  • 41,277
  • 16
  • 94
  • 144