SQL Azure available in West European Region and East Asia

As announced in one of my previous blogs, the release of SQL Azure Update 3 is now available. This release of SQL Azure is now also available in 2 new data centers. These data centers are located in the West European Region and the East Asia region. In this SQL Azure Update 3 the following new features are available:

• 50 GB Database Support – You can now store even more data in a single SQL Azure database as the database size has been increased to 50 GB. This will provide your applications increased scalability. For detailed pricing information on SQL Azure and how to create or modify your database to take advantage of the new size, see this blog post.  In general every GB of data will cost 10 $. For business edition started with 100 $.
• Spatial Data Support - SQL Azure now offers support for the Geography and Geometry types as well as spatial query support via T-SQL. This opens the Windows Azure Platform to support spatial and location aware applications.
• HierarchyID Data Type Support – The HierarchyID is a variable length system data type which provides you the ability to represent tree like structures in the database.

Enjoy it, and I'm already looking forward to the reporting server support, so we can make use of the all the maps in combination with the supported spatial data support.

New features in coming SQL Azure updates

In one of my previous blogs I already told about my first experiences with SQL Azure. 2 weeks after the launch of SQL Azure the first Service Updates was coming available. At the MIX 10 conference, the Microsoft SQL Azure team announced some new features in the upcoming releases.

Support for MARS
In SU2 (April) Support for Multiple Active Row Sets. Mutilpe Active Row Sets (MARS)  allows you to execute multiple batches in a single connection.

50GB Databases
In SU3 (June) 50 Gb Database will be available.  If you would like to become an early adopter of this new size option before SU3 is generally available, send an email to EngageSA@microsoft.com and it will auto-reply with instructions to fill out a survey. Fill the survey out to nominate your application that requires greater than 10 Gb of storage.

Support for Spatial Data
In SU3 (June) Support for spatial data. This feature will support the Geography and Geometry types as well as query support in T-SQL. This is a nice feature which opens the Windows Azure Platform to support spatial and location aware applications.

SQL Azure Labs
A new site called SQL Azure labs is launched. SQL Azure Labs provides a place where you can access incubations and early preview bits for products and enhancements to SQL Azure. The goal is to gather feedback on the features you want to see in the product. All technologies on this site are for testing and are not ready for production use. Some of these features might not even make it into production – it’s all based upon the feedback of the SQL Azure community. Be aware that these features are actively being worked on, you should not use them against any production SQL Azure databases.

It is great to see that the development on SQL Azure is moving on. What will be the next feature in the upcoming Service Update (SU). If you have ideas you can submit them on: http://www.mygreatsqlazureidea.com/

First service update for SQL Azure Database is live.

Photo credit: Jakob Montrasio

The first Service update for SQL Azure is live. This update is coming 2 weeks after the general availability of SQL Azure. It contains some bug fixes but more important it contains some new features based on the feedback they got. I'm happy to see that the SQL Azure team is looking seriously to the provided feedback. In my previous blog: "My first experiences with SQL Azure, the SQL Server database in the cloud" I talked about my experience with SQL Azure. Some things for improvement are already implementend in the first service update for SQL Azure like:

• First dynamic views to monitor the health of a database, diagnose problems, and tune performance. It is not complete but the start is made.
• Ability to move between editions. You can now upgrade your database fro 1 Gb to 10 Gb. 10 Gb is still the maximum size, but I think this is a matter of time.
• The idle connection timeout is increased from 5 to 30 minutes. This is much better.
• Improved algorithm for terminating long running transactions. Import and export of larger date is possible with having to break down you data in smaller chunks.

All details about this first service update of SQL Azure can be read here.

The first service update within 2 weeks, is a really good start. I hope that the SQL Azure team can keep on delivering this kind of service updates with this speed. I can't wait for the second service update.
Enjoy it.

SQL Server client API's with SQL Azure version 1

Today I found a really nice document of Steve Hale that describes some of the issues a developer should be aware of when writing ODBC, ADO.NET SQL Client, BCP and PHP code that connects to SQL Azure. It talks about:

1. Supported components
2. How to connect to SQL Azure
3. Creating and Connecting to a Database
4. Creating tables.
5. Unsupported features.
6. Deprecated functionality
7. Connection behaviour
8. Latency

Enjoy reading this article

My SQL Azure Bill: Usage statistics like bandwidth and databases

Image credit: KevinSpencer


By using SQL Azure you do not have to be concerned about your backups, maintaince of your SQL Server, creating a fallback scenario etc. You only have to pay for what you are using. This is nice, but what are you using? You do not want to be suprised when the first bill is received. By using SQL Azure you need to pay for:

1. Database usage. You will be invoiced for the number, type and duration of the database. Type of a database can be Business edition (Up to 10 Gb) or Web edition (Up to 1 Gb). Duration is the number of days the database was available on your SQL Azure server. 
2. Bandwidth usage. You will be invoiced for inbound and outbound data traffic.

Within your database 2 views are available to get insight information about the Database usage and Bandwidth usage. I can imagine that these views are not so user friendly for your controller, I think he has even no access to these views. But you have at least something.

Database usage view: SELECT * FROM SYS.Database_usage. This view contains information about the number of database of eachtype you have everyday. One database for 30 days will be invoiced for the same amount as 30 databases one day.

Bandwidth usage view: SELECT * FROM SYS.Bandwidth_usage. This view contains the amount of bandwidth usage (in kilobytes KB) per database, per hour, data moving into SQL Azure, data moving out of SQL Azure, data from within Windows Azure and data from outside Windows Azure.

With these usage statistics you can calculate your bill for the coming month.
More detail about billing information can be found here. More details about the billing rates can be found here.
Enjoy using SQL Azure and hopefully you will not be badly suprised about the billing amount.

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.

Main differences between SQL Azure and SQL Server 2008

SQL Azure is a service which makes the administration slightly different.  Unlike administration for an on-premise instance of SQL Server, SQL Azure abstracts the logical administration from the physical administration; As an database administrator you continue to administer databases, logins, users, and roles, but Microsoft administers the physical hardware such as hard drives, servers, and storage. Because Microsoft handles all of the physical administration, there are some differences between SQL Azure and an on-premise instance of SQL Server in terms of administration, provisioning, Transact-SQL support, programming model, and features.
 
Logical Administration vs. Physical Administration
To provide this level of physical administration, you cannot control the physical resources of SQL Azure. For example, you cannot specify the physical hard drive or file group where a database or index will reside. Because the computer file system is not accessible and all data is automatically replicated, SQL Server backup and restore commands are not applicable to SQL Azure. Many SQL Server Transact-SQL statements have parameters that allow you to specify file groups or physical file paths. These types of parameters are not supported in SQL Azure because they have dependencies on the physical configuration.

Because SQL Azure performs the physical administration, any statements and options that attempt to directly manipulate physical resources will be blocked, such as Resource Governor, file group references, and some physical server DDL statements. It is also not possible to set server options and SQL trace flags or use the SQL Server Profiler or the Database Tuning Advisor utilities.

SQL Azure does not support all of the features and data types found in SQL Server. Analysis Services, Replication, Reporting Services, and Service Broker are not currently provided as services on the SQL Azure.

Microsoft SQL Azure Database supports a subset of Transact-SQL for SQL Server 2008.

Transact-SQL Features Supported on SQL Azure
• Constants
• Constraints
• Cursors
• Index management and rebuilding indexes
• Local temporary tables
• Reserved keywords
• Stored procedures
• Statistics management
• Transactions
• Triggers
• Tables, joins, and table variables
• Transact-SQL language elements such as
o Create/drop databases
o Create/alter/drop tables
o Create/alter/drop users and logins
o and so on.
• User-defined functions
• Views, including sys.synonyms view

Transact-SQL Features Unsupported on SQL Azure
• Common Language Runtime (CLR)
• Database file placement
• Database mirroring
• Distributed queries
• Distributed transactions
• Filegroup management
• Global temporary tables
• Spatial data and indexes
• SQL Server configuration options
• SQL Server Service Broker
• System tables
• Trace Flags

SQL Azure Migration Wizard. BCP upload process failed: SQLState = 37000, NativeError = 40531

In SQL Azure the BACKUP and RESTORE functionality is not available. This makes it a little bit more complex to transfer existing data to the SQL Azure database. A way to upload data to SQL Azure is using BCP. The SQL Azure Migration Wizard makes use of BCP to upload your data. The SQL Azure Migration Wizard helps you migrate your local SQL Server 2005 / 2008 databases into SQL Azure. The wizard walks you through the selection of your SQL objects, creates SQL scripts suitable for SQL Azure, and allows you to migrate your data from:

• SQL to SQL Azure
• SQL Azure to SQL
• SQL Azure to SQL Azure.

This tool can do a lot. A nice screen cast in which the author: George Huey explains what the tool can do can be found here:

I practise with this tool and could create the SQL objects, like tables very easy. The migration wizard can also check your scripts for compatibility issues with SQL Azure. However the upload of data always failed with next error message:

Process starting ...
-- Success: CREATE NONCLUSTERED INDEX [JournalStatusEntryGuid] ON [dbo].[amutak] ...
Uploading data to "Deltabike.dbo.amutak
*
*****
Sorry, but BCP upload process failed:
SQLState = 37000, NativeError = 40531
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]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.

This error can be solved if you specify also the servername after the login name. @
instead of only

Enjoy uploading your data to SQL Azure.

Getting Started with SQL Azure, create your first SQL database in the cloud.

Microsoft® SQL Azure™ Database is a cloud-based relational database service built on SQL Server® technologies. As said by Microsoft SQL Azure should:

1. Provide 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.
2. Developers do not have to install, setup, patch or manage any software.
3. High availability and fault tolerance is built-in and no physical administration is required.
4. 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.
5. SQL Azure Database can help reduce costs by integrating with existing toolsets and providing symmetry with on-premises and cloud databases.

These statements sounds very promising. Therefor I started creating a SQL Azure database in the cloud to see the current status of the cloud-based relational database service.

First of all you need a SQL Azure account. Request an invitation code for yourself.  Within one day you will receive the invation code which enables you to create your own SQL Azure Server. To create a server you need to specify and Administrator Username and an Administrator Password.

Now your server is created with one database: Master.
To access this server you need to configure the firewall settings. Press on the Firewall Settings tab and click on checkbox "Allow Microsoft Services acccess to this server". Select the Rule MicrosoftServices and press the Edit Rule button. Now you can specify the range of IP adresses which are allowed to connect to your Azure Server. In my example I have configured all IP addresses.

Now we will create the first database. Select the Databases tab. Press Create Database, Specify a database name and the size of the database. You can choose between 1 Gb and 10 Gb.

Press the Test Connectivity button to test the connection. You MUST have the MicrosoftServices Firewall rule enabled in order to use this feature. Your database is created and you are ready to connect with the SQL Server Management Studio (SSMS). For the Object Explorer you have enter the Server name and Login credentials.

To open a new query you need to specify the database you want to connect. This canbe done in the connection propertie tab. The USE command is not supported for switching between databases. Establish a connection directly to the target database.

Now you are connected.

Enjoy using SQL Azure.