What is SQL Azure ?

The MSDN documentation (http://msdn.microsoft.com/en-us/library/ee336279.aspx) describes it as:

Microsoft SQL Azure extends SQL Server capabilities to the cloud. SQL Azure offers a relational database service called Microsoft SQL Azure Database, and using SQL Azure, you can easily provision and deploy relational database solutions. Benefits include manageability, high availability, scalability, a familiar development model, and a relational data model. Additional SQL Azure services are planned.

Practically, SQL Azure is a SQL Server 2008 working on a Microsoft server farm, but with some differences from the normal SQL Server. To get detailed overview of SQL Azure we will suggest to read http://blogs.msdn.com/b/sqlnativeclient/archive/2010/02/12/using-sql-server-client-apis-with-sql-azure-vversion-1-0.aspx.

The customers may purchase one of the Windows Azure service plans (http://www.microsoft.com/windowsazure/offers/). Then create a database and use it as a normal SQL Server. So, we have purchased "Introductory Special" plan, as we only need to test AnyDAC for Delphi with SQL Azure.

AnyDAC and SQL Azure

After some changes, we got the AnyDAC for Delphi QA suite passing without issues on the SQL Azure for all AnyDAC components and layes. That means, that AnyDAC data access VCL library has very good compatibility level across all SQL Server brands.

At first we have disabled MARS connection, which was enabled by default. Now, if you need it, you have to specify MARS=Yes as a connection definition parameter. At second, all our test and demo SQL Server tables now have CLUSTER indexes. Without such index, INSERT operation on a SQL Azure table will fail. Other changes was rather minor internal changes.

To connect to SQL Azure, we have used the following connection definition (login info is changed):

[SQLAzure_Demo]
DriverID=MSSQL
Server=tcp:nasdfert6.database.windows.net
Database=Northwind
User_Name=addemo@nasdfert6
Password=asd123zxc
Encrypt=Yes
MetaDefSchema=dbo
MetaDefCatalog=Northwind 

Worth to mention:

• You have to use "tcp:" prefix in Server parameter. This is because only TCP/IP protocol is supported by SQL Azure.
• You have to use "@server" suffix in User_Name parameter. This is probably because the farm has a lot of users from other SQL Azure accounts. 

Delphi and SQL Azure

Some of you may remember, that Microsoft was pushing ADO as a recommended connectivity for SQL Server. Then they switched to ADO.NET as a new recommendation. So, the funny thing - SQL Azure does not support ADO, but supports ODBC and ADO.NET !

So, what this means for Delphi users ? We have tried to connect using dbGo components and SQL Native Client Provider 10.0 to the SQL Azure - it failed with the error message:

Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match..

So, this means, that dbGo with SQL Native Client Provider and dbExpress VCL components cannot be used as a connectivity to SQL Azure. Probably, the same may be applied to ZeosDBO and DevArt SDAC, as they are using OLEDB technology for their SQL Server drivers. Anyway, the ODBC based solutions should work, including dbGo / Microsoft ODBC provider and Open ODBC dbExpress driver.

Thanks

We want to thank Sebastian May for his great idea to support SQL Azure and for the links, which helped us a lot. Thank you, Sebastian !