First summary of previous articles:
Part 1: We defined SQL Azure and discussed advantages of SQL Azure
Part 2: We created an Azure account and created our very first SQL Azure database
Part 3: We discussed about the provisioning and the billing model of SQL Azure
Part 4: We discussed the SQL Azure architecture
Part 5: We discussed the SQL Azure security model
Part 6: We discussed how to migrate Databases to SQL Azure
Part 7: We discussed how to improve performance of SQL Azure DB and options for planning backup and restore strategies.
Part 8: We discussed administrative tasks related to SQL Azure.
Now, in this module, we are going to discuss about developing SQL Azure applications. If you come from a SQL Server world, then there are only few differences in developing SQL Azure applications that you need to know. In this module, we would discuss them primarily from a Database Developer perspective. Let us discuss them one by one:
Partially supported and not supported TSQL Statements
All TSQL statements that are supported by SQL Server are not supported by SQL Azure. So when you are developing SQL Azure applications (or ideally before you decide to include SQL Azure as part of your technology solution), you need to be aware of these partially supported and not supported TSQL statements. Now, note that SQL Azure is rapidly evolving and so in next service updates, support for a particular TSQL statement may be added. However, you need to be aware of the contemporary set of TSQL statements that are supported by SQL Azure.
Let us see few unsupported TSQL statements (As of Nov 2011)
- BULK INSERT
- FREETEXT etc.
Complete list of partially supported and unsupported commands can be found here: http://msdn.microsoft.com/en-us/library/windowsazure/ee336281.aspx
The reason I pointed you to the list is, since SQL Azure is rapidly evolving and you may soon find support for features that are not supported right now. Therefore, if I had list the unsupported features here, then this post would become obsolete in few months.
SQL Server features not supported by SQL Azure
Now SQL Azure does not support some SQL Server features yet. To name a few:
- SQL Server Agent jobs (you can connect to SQL Azure via SQL Server Agent running locally but SQL Server Agent is not a feature of SQL Azure)
- Full Text Search
- Transparent Data Encryption
- Service Broker
- Common Language Runtime etc.
Complete list of SQL server features that are not supported by SQL Azure can be found here: http://msdn.microsoft.com/en-us/library/ff394115.aspx
Knowing about it is crucial especially if you are planning to migrate an existing SQL Server application to SQL Azure. In this case, you need to aware of these limitations. In addition, keep an eye on service updates. Therefore, as soon as the support for the feature that you wanted is added, you can migrate your SQL Server application to SQL Azure.
You also need to be aware of general limitations of SQL Azure. List is here: http://msdn.microsoft.com/en-us/library/windowsazure/ee336245.aspx
Here, you must also realize that SQL Azure is meant to be a “black box”. By this, I mean that since it is a cloud based multitenant solution, it will abstract underlying details from the user. So for instance, you will not have access to manage your DB files reside or say you will not have access to manage the system resources on which your SQL Azure DB runs or you do not access to TempDB or some only some systems views are available. You need to be aware of things that are “hidden” to you and support for such features/commands may not be added.
The key take-away from this part of the module is that while developing SQL Azure applications, you need to be aware of the unsupported and partially supported TSQL commands. You also need to be aware of SQL Server features that are not supported by SQL Azure.
Tools that have support to connect to SQL Azure:
- SQL Server Management Studio 2008 R2 or above supports SQL Azure
- Manage SQL Azure via Azure management portal
(In Part 7 and Part 8, we discussed some use-cases for above tools with SQL Azure)
- SQL Server Data Tools (SSDT) supports SQL Azure. SSDT allows database developers to perform the database design tasks within Visual studio – so you can build SQL Server (Also SQL Azure) databases without leaving visual studio. Learn more about SSDT here: http://msdn.microsoft.com/en-us/data/gg427686 or read a MSDN magazine article about SSDT here: http://msdn.microsoft.com/en-us/magazine/hh394146.aspx
For the people from BI world:
- You can use SQL Azure as data source for SSRS running locally (SQL Server Reporting Services). We will talk about SQL Azure reporting in next part. It is valid for SSAS (SQL Server Analysis Services). Just note: The support for SQL Azure with these BI tools is for SQL Server 2008 R2 and above.
- You can use ADO.Net source and destination components in SSIS (SQL Server Integration Services) to connect to SQL Azure.
Complete list can be found here: http://msdn.microsoft.com/en-us/library/windowsazure/ee621784.aspx
How can clients or Applications connect to SQL Azure?
A client/application can connect to SQL Azure via ADO.Net or ODBC connection library. And so apart from .Net apps, SQL Azure can also be connected from Java or PHP app. You need to use the following version of the connection libraries:
- Data provider for SQL Server from .Net framework 3.5 SP1 or above
- Entity Framework from .Net framework 3.5 SP1 or above
Complete list can be found here: http://msdn.microsoft.com/en-us/library/windowsazure/ee336245.aspx#dlaps
As of now, connecting to SQL Azure via OLEDB is not fully supported.
So the take away for this module is that you need to study the things that are not supported by SQL Azure.