My first experiences with SQL Azure, the SQL Server database in the cloud.

In one of my previous blog post: Getting Started with SQL Azure, create your first SQL database in the cloud. I described what you need to do to create a SQL Azure database. Overall I was suprised what is already possible. I'm looking forward to the next releases to see the progress in SQL Azure. This document will describe my first experiences with using SQL Azure.

1. Getting started and creating a database is easy to do. It is really straight forward to create and connect to your SQL Azure environment.
2. SQL Azure firewall services functionality is restricted. Access to my database can be blocked on IP version 4 level and user level. With IP address spoofing you can still access the SQL Azure server. In most production environments direct access to the SQL Server is blocked.
3. Firewall does not support IP version 6 adresses. In the coming years there is a shortage of IP version 4 adresses. To support the current growth of all devices which connect to the internet, IP version 6 is developed.
4. You need to create a firewall exception for TCP port 1433. Otherwise you get a connection error: Cannot connet to: Could not open a connection to SQL Server. (Microsoft SQL Server, Error: 53). This can conflict with current firewall rules. Connectivity to the SQL Azure database depends on the network from which you connect to it. In the past the IT department of my company blocked TCP port 1433 because of the SQL Slammer virus. My internet provider at home did not block this port.
5. Your application must support encryption when connecting to SQL Azure. This is good. You don't want send your data unencrypted over the internet.
6. Only support for SQL authentication. From a technical perspective I can image why Integrated Security is not supported. However, according the Books Online of SQL Server it is strongly recommended from a security perspective to use Integrated security to connect to the database. If you have the username and password of the SQL account, you can do what you want. At this moment there are not other ways of establishing more secure connections to the database, for instance making use of certificates. See next screen cast of the current SQL Azure security model.
7. Maximum database size is 1 GB for the Web edition and 10 GB for the Business edition. 10 GB of data is relative small size for a database.
8. Database connection will be closed due to the following conditions: Excessive resource usage, Long running queries, long running single transactions, idle connections. In the current release (10.25.9085) idle connections and long running queries or transactions are closed after five minutes. Automatic reconnect to the server did not work. Only the second execution is working. One example, I execute a simple query in SSMS on my SQL Azure database successfully. After that I do something else for 10 minutes, for instance a coffee break. After 10 minutes I execute the second simple query. Now I got an error: Msg 10053, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) This is not user friendly.
9. RESTORE and ATTACH database statement is not supported. The SQL Azure platform supports high availability, however a restore is sometimes needed for other purposes than a hardware failure like user or application mistakes. What can a user do if he made a mistake in deleting some transactions? In these scenarios you need to restore a copy of your database.
10. As an alternative to start testing with an local copy of your SQL database in the cloud, you can use SQL Azure Migration Wizard. Please read this blog if you got BCP upload errors with the SQL Azure migration wizard. I used this wizard to upload a copy of on on premise SQL database to my SQL Azure Server.
11. SQL Profiler is not supported. This is a tool I really misses. I use the SQL Profiler to understand what happens on my SQL server, to see what kind of queries are generated by my applications and to analyze my queries to improve performance. On the SQL Azure server I have an additional reason to use the SQL Profiler. In the pricing model for SQL Azure you have to pay for the data transfer sizes of your queries. The SQL Profiler can give you insight in the data transfered from the server to the client.
    







12. I understand from a technical point of view why tools like SQL profiler are currently not supported. They are builded for the on premise SQL Server environments. With the SQL Azure services we have a distinction between the physical and logical administration in which access to the physical components are not allowed. However, from a user perspective these tools are really needed to have. Without these tools the SQL Azure server is a black box. You put something in and you get something out but have no idea how this is done. Especially when you have to pay for the things you get out. You want to be in control what you need to pay to avoid suprises afterwards when you receive the bill.
13. I succeed to run my .NET application with a SQL Azure database. This .NET application is build to run on an on premise SQL Server. Because SQL Azure does not support the security implementation of my .NET application I made some changes in the run time of this .NET application. This was acceptable for this test because I was interested to see how SQL Azure was running with an application which generates a lot of different queries. After changing some stored procedures, I could login to my application and use all functionality.
14. I'm interested to know how much of my data will remain in the procedure cache and the data cache. I have no idea what amount of memory is allocated to my databases. For instance, is it usefull to use parameterized queries? Without the profiler you can't figure this out. 

Overall, I think this is a good start with this first version of SQL Azure and looking forward to see if some of my missing functionalities and tools are included in the upcoming releases.
Enjoy it.