SQL Azure new user interface - Project Houston

jahlen — Thu, 02 Sep 2010 10:27:00 GMT

"Project Houston" is the new user interface for SQL Azure. If you have tried SQL Azure you know that the user interface on the web portal is very brief. You can't do almost anything from there (except create new database and setup security). To do anything with your SQL Azure database you need to use the "non-cloud" tools like Management Studio (in SQL Server 2008 R2).

The new user interface is a Silverlight application. It's got a fresh new look:

You can use it to design and edit the contents of your database objects. However it is not the fastest (I managed to hang it when I opened a too large table).

So what is it useful for? I'd say it's useful for situations where you don't have access to Management Studio (or similar tool) or people who'd be scared of Management Studio. I think the idea is good that a cloud database should also have a cloud based user interface, although in most cases it will be more convenient to use the traditional user interfaces.

SQL Azure - some tips & tricks

jahlen — Sun, 20 Dec 2009 05:34:00 GMT

In this blog post I'd like to share some of my experience working with SQL Azure so far. First a background on what SQL Azure really is and then I will give you some of my tips and tricks.

What is SQL Azure

Microsoft SQL Azure Database is a cloud-based relational database service that works much like an ordinary stand-alone instance of Microsoft SQL Server. It support most of the functionality in the SQL Server 2008 relational database engine, including SQLCLR support. It doesn't have the Business Intelligence components like Integration Services, Analysis Services, Reporting Services. Also it doesn't support typical DBA features, but that's logical since the whole idea of SQL Azure is that you should not need any DBA. Microsoft promises a 99.9% availability during a calendar month.

Currently SQL Azure is free to evaluate if you have an invitation. In production, the pricing will be a fixed cost per month which depends on edition and a data transfer cost. The web edition is limited to 1 GB database size and currently costs $9.99 / month. The business edition allows up to 10 GB database size and costs $99.99 / month. SQLCLR is supported only by the business edition. Data transfer costs is dependent on location, but costs less than a dollar per gigabyte.

Below is a screenshot from the SQL Azure management web site. There you create your databases and configure your firewall settings. This web site is located at http://sql.azure.com/.

Connecting to SQL Azure

Once you have created your SQL Azure databases, you need to enable access to the in the firewall settings. After doing that and waiting a couple of minutes for the changes to take effect, you can connect to your database. You can click on the Connection Strings button to get the necessary information to connect.

Previously there was no support for any graphical user interface to SQL Azure. When I started looking at SQL Azure I used the SQLCMD command line utility to execute my SQL commands. Thankfully now you can download the November CTP of SQL Server 2008 R2 and from there connect to SQL Azure through SQL Server Management Studio.

Below is a screenshot of the connection dialog where I connect to my SQL Azure database. Only SQL Server Authentication is supported. You need to type in your login in the format username@server. Also it is a good idea to click on Options and on the Connection Properties tab select your desired database in the Connect to database drop down list.

Scripting

Typically you develop databases locally in SQL Server 2008 and then deploy the SQL Azure. As mentioned earlier, SQL Azure doesn't support typical DBA features like filegroups settings. That means you need to limit the scripting options when copying objects from a local SQL Server 2008 database to SQL Azure. Luckily there is a new settings in the November CTP of SQL Server 2008 R2 where you can choose database engine type in the Generate Scripts... wizard. See the screenshot below:

By setting "Script for the database engine type" to SQL Azure Database, you exclude the features that are not compatible with SQL Azure.

Copying data

If developing in a local SQL Server 2008 environment, you may want to copy data between your environment and SQL Azure. Basically there are two ways in Management Studio to do this.

Either you can use the Generate Scripts... wizard and through the Types of data to script option make it generate INSERT STATEMENTS for your data.

My preferred way however is to use the Import and Export data wizard. That wizard build an SSIS package in the background and executes to copy your data. It supports a variety of data sources like SSIS does.

The key to use the Import and Export data wizard is to use the right database provider. Currently it seems that SQL Azure only works with the .Net Framework Data Provider for SqlServer and not with the default provider suggested by the wizard. Below is a screenshot of my settings.

Encryption

Encryption is mandatory in all connections to SQL Azure. Usually you do not need to worry about it as it is automatically set by the server upon connecting.

Connection closing

The save resources, SQL Azure closes connections after they have been idle for some time. In Management Studio, this is not a big problem. You can just retry your queries. However it causes more problems in your applications.

Here is my theory what happens. By default, Windows and web applications use connection pooling to improve performance. Connection pooling means that connections are reused instead of being opened and closed. When your application closes a connection, it actually stays open in the connection pool. When it's been idle long enough it get's killed by SQL Azure. Guess what happens when your application tries to connect? Yes, it gets the save connection that's dead.

Your could either disable connection pooling or add try/catch error handling to retry your queries when they fail. I have successfully used the try/catch method to get rid of all problems. That should be the preferred method since disabling connection pooling would hurt your performance much more.

Collations

There is no way to set collation on database level. I've tried! Upon creation, the databases are set to SQL_LATIN1_GENERAL_CP1_CI_AS. For people like me who write applications in other languages than English, this is an annoyance.

What to do? The good news is that SQL Azure supports collations on column level even if I'd prefer a global setting on database level instead. Also you can do a collation cast in your queries, for example:

SELECT LastName
FROM MyTable
ORDER BY LastName COLLATE Finnish_Swedish_CI_AS ASC

Final words

The core of SQL Azure seems to be relatively complete, but it still lacks quite a bit in the user interface support. That makes it useful for experienced SQL Server users. However, I think there soon will be much better support from SQL Server 2008 R2 Management Studio.

SQL Azure is one of three cloud platforms from Microsoft. The other two are Windows Azure and the AppFabric. I've developed a Windows Azure application that uses my SQL Azure database. Doing that I missed the possibility to host them in the same Affinity Group. That should be a key requirement to get optimal performance from your cloud applications.

Nyhetskoll - my contribution to the Windows Azure Developer Challenge

jahlen — Fri, 13 Nov 2009 10:43:00 GMT

I submitted today my contribution to the Swedish Windows Azure Developer Challenge. It's an application, Nyhetskoll, where you can automatically monitor the Swedish newssites for keywords (such as persons, companies or your interests).

The application runs on Windows Azure and it uses SQL Azure för storage and query logics.

I started writing the application last weekend and after having spent a few evening finishing it in time for the deadline, today at 12:00, I was disappointed that they extended the deadline by two months. So much for my hurry!

Well, it made me really explore the Windows Azure platform. It looks promising for the future, but still I would say it is far from stable. Several times when I deployed it got stuck in the "Initializing" phase. It will be very interesting to see what happens at the official release the 19th of November during the PDC.

Feel free to visit Nyhetskoll at:
http://nyhetskoll.cloudapp.net

Johan Åhlén : SQL Azure

SQL Azure new user interface - Project Houston

SQL Azure - some tips & tricks

Nyhetskoll - my contribution to the Windows Azure Developer Challenge