What is the difference between SQL Azure and SQL Server 2008?

Question

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?

Answer 1

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.).

Answer 2

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)

Answer 3

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.!