Third Party Tools:
As demonstrated, SSMS may not be a good tool for SQL Azure. There are couple of third party tools that I have been working with:
Omega Web Client
Omega Web Client has a great set of functionalities. It has a great object explorer. You can access this tool online at https://onlinedemo.cerebrata.com/SQLAzureClient/default.aspx and therefore no installation is needed.
Below is the main view of the UI that you will see from this tool.
SQL Azure Manager
SQL Azure Manager is another tool which supports SQL Azure. However there are quite number of draw backs which will be discussed later.
You can download this tool from http://hanssens.org/tools/sqlazuremanager/.
The SQL Azure Manager UI is much better than SSMS or the Omega web client.
Though SQL Azure Manager has a much better UI than SSMS 2008 or Omega, it is still lacking a lot of features in the object explorer. For example, it shows the views in object explorer, but does not allow the user to create the alter script for a view. Also, it does not display stored procedures or functions in the object explorer and can only show data of tables with the dbo schema.
How to Test SQL Azure
So how do you know whether all the T-SQL syntaxes are working with SQL Azure? For the purpose of this example we will use the TSQL from the AdventureWorks database.
First you need to create a database. You need to be logged in to the Master database to create a database.
CREATE DATABASE AdventureWorks2008
After creating the database, you need to log into the database with the created database credentials.
Schema, UDF and UDT
Schema , UDF and UDTs will be created without any issues. However, if you generate the script from the AdventurkWorks2008 database there will be entries for stored procedure sp_addextendedproperty.
Table & DML Triggers
There are a few limitations in the CREATE TABLE statement when it comes to SQL Azure which are highlighted in the following image.
You will need to remove these tags and columns to create tables in SQL Azure. As you can see in the above image, ROWGUIDCOL, xml and hierarchyid data types are not supported.
Triggers will be created but you shouldn’t use columns with more than two parts.
If you run this statement you will receive following error.
Deprecated feature ‘More than two-part column name’ is not supported in this version of SQL Server.
In the Update statement above, RevisionNumber is referred as [Purchasing].[PurchaseOrderHeader].[RevisionNumber] which will not be supported by SQL Azure.
Views don’t have major issues except that the above mention column types are not supported.
Store procedures behave same as views.
DDL Triggers can be created but with a major limitation – the EVENTDATA() function is not supported in SQL Azure (the EVENTDATA() function used to capture event information).
SSIS & SQL Azure
SQL Server Integration Services (SSIS) is used to integrate data between different data sources.
The first thing to note is that SQL Azure currently does not support OLE DB. The normal recommendation for SSIS is to use the OLE DB Source or Destination to access SQL Server. However, if you want to work with SSIS and SQL Azure, you must use the ADO.NET Source and Destinations. This is fine for 2008, but if you are using SSIS 2005, there is no ADO.NET Destination, so you will have to implement your own through a script component. This can be done by creating a source or destination and then using the SSIS control flow tasks and data flow tasks.
After it will be the standard way of write SSIS packages.
When using SSIS for SQL Azure, tables without a clustered index are not supported hence you are required to create a clustered index.