SQL Azure: Development Tools

There are number of tools available to build your SQL Azure database. In this post I will describe three of them including SSMS (SQL Server Management Studio), SSDT (SQL Server Data Tools), and the SQL Azure Database Manger.

1) SQL Server Management Studio
SQL Server Management Studio, a.k.a SSMS, and its predecessor Query Analyzer have been the tools of choice for SQL Server database developers that prefer a GUI over a command line. SQL Server 2008 R2 includes support for SQL Azure in SSMS. Naturally, the version of SSMS that ships with SQL Server 2012 also supports the management and development of SQL Azure databases.

While the SSMS 2012 user interface looks a bit different than previous versions because it is now hosted in the WPF-based Visual Studio shell, connecting to a database is the same as connecting from SSMS 2008 R2.

Once connected, the Object Explorer tree looks very similar to an on-premises SQL Server database, although you'll notice that the server icon looks a bit different. Additionally, some folders such as Server Objects and Replication as well as the SQL Server Agent node are missing as this functionality is not currently available on the SQL Azure platform. Refer to MSDN for a list of the SQL Server features not currently supported on the SQL Azure platform.   

Now that we have provisioned a SQL Azure server instance as well as created a SQL Azure database, let's create a table. Just like a regular SQL Server database, it's as simple as expanding the database node in Object Explorer, right-clicking the Tables folder and selecting New Table.... For some reason Microsoft has chosen not to provide the familiar table designer user interface. Instead it displays a CREATE TABLE script template. Below is a screen shot of a modified template used to create a PolicyTransactionFact table in our InsuranceDW database. All we had to do was name the table and add columns and data types. Once you have created your table definition you can either hit the F5 key on your keyboard or click the Execute button. The screen shot below was taken after executing the CREATE TABLE script.

Creating other types of SQL Azure objects is just as easy and is extremely similar to creating objects in on on-premises SQL Server database.

2) SQL Server Data Tools
SQL Server Data Tools (SSDT) provides a declarative approach to database development that is more in line with Microsoft's Visual Studio-based application development paradigm. It is hosted within the Microsoft Visual Studio shell and is free for download on MSDN.

 Microsoft would like SSMS to remain as an Administrative tool for SQL Server instances and databases but would like development work to be done in Visual Studio by way of SQL Server Data Tools projects. Additionally, starting with SQL Server 2012, SSDT replaces BIDS as the development environment for SSIS, SSAS, and SSRS. 

The first step in SSDT-based database development is to create a SQL Server Database Project. Make sure you install SSDT prior to attempting to create this project type as it is not available with the default installation of SQL Server 2010.

Once you've created the SQL Server Database Project right-click the top-level project node in Solution Explorer and select Properties. Here is were we set the Target Platform to SQL Azure to make sure that our build output is compatible with the SQL Azure platform.

Once the project is created and the platform appropriately set we can begin to create database objects. Just as we did with SSMS, we'll create a table. As you can see below, SQL Server Database Projects provide a table designer so that you can create tables using a GUI as well as writing SQL.

Since SSDT is a declarative development environment we don't actually create physical objects on a server as we develop them in code. Once the developer is content with their development they must Publish the current SSDT representation of the database schema to the database server. This is done by right-clicking on the top-level database project node and selecting Publish.

At this point we are presented with a dialog requesting the publishing/deployment information.

Once the connection information and database name are entered hit the Publish button. The results of the publish can be seen in the Data Tools Operations window.

SSDT SQL Server Database Projects are extremely powerful and can play an integral role in DLM (database lifecycle management) and ALM (application lifecycle management) processes. I provide an argument for using SQL Server Database Projects' predecessor Visual Studio Database Project in my blog post titled Visual Studio 2010 Database Projects and also explain the evolution to SQL Server Database Projects in my blog post titled The Future of Visual Studio Database Projects.

3) SQL Azure Database Manager

The last database development tool I'll discuss is the web-based SQL Azure Database Manager. To use the Database Manager you'll need to log into the Azure Platform Portal and navigate to the Database section by clicking on the Database Link.

Now navigate the menu tree and select the appropriate database server.

Now click the "Manage" link. 

This will take you to the Database Manager log on screen. Enter your credentials and log on. You should see  the screen below.

Click the "Design" link in the lower left-hand corner of the screen.

Notice that we're in the Tables section and that we see a list of our tables. Click "New Table" to create a new table.

The Create Table screen should be self-explanatory. You can add/remove columns, set their data types, select primary key columns, set nullability, etc. When you're done, click "Save" and your new table will be added to the database.

Summary
As you've seen, anyone can create a SQL Azure database (whether you want to pay or not) and use any one of the three tools we discussed to create and maintain database objects. SQL Azure isn't right for everyone, but for some organizations and situations it can be a very powerful tool. Hopefully this post will at least convince you to get your feet wet in case you find yourself in one of these situations so that you will have had enough exposure to SQL Azure to know what to do next. Good luck!

SQL Azure - Very Cool - Very Big Deal Breakers

THIS POST REQUIRES AN UPDATE WITH CHANGES MADE TO SQL AZURE

For anyone that doesn't know what SQL Azure is, it's essentially SQL Server hosted in Microsoft Data Centers and accessible via the internet. Throw the word cloud in there if you want the executives and sales guys to listen. A competing product would be Amazon's SimpleDB.

SQL Azure is cool. No doubt about it. In a matter of minutes I can provision a SQL Server database somewhere in the ether and connect to it from my machine using the same familiar tools like SQL Server Management Studio and Visual Studio. Reporting Services has a SQL Azure connection type and SSIS allows for SQL Azure sources and destinations using ADO.NET type connection managers.

The benefits of having your database be a SQL Azure database are almost in lockstep with anything "in the cloud." You don't need your own hardware, you get high availability, no software patching/updating, etc. All that stuff becomes someone elses responsibility. Additionally you get scalability and you pay for only what you need. In the traditional, on-premise model of hosting your own database(s), if you have a business where you need to support peak load that only occurs a few days or weeks out of the year you have to pay for that horsepower in both hardware and software licensing costs. Move those same databases to SQL Azure and now you have a platform that scales to your needs on-demand and you only pay for the resources consumed. There's no doubt in my mind that this model will become ubiquitous just as virtualization has become over the years.

So let's move all of our SQL Server databases to SQL Azure! Well, not quite yet. There are some major limitations that will keep 99% of "real" applications off of this platform in the near-term. My biggest "deal-breakers" are listed below.

Database Size Limitations
SQL Azure databases are limited to 5GB for Web Edition databases and 50GB for Business Edition databases. Of course there are plenty of databases that might fall within these limitations but I wouldn't want to risk my job on deploying to this platform only to hit this limitation and have a catastrophe on my hands.

No Windows Authentication
The platform currently supports only SQL Server logins. Intuitively that makes sense since the server doesn't live in your domain but this can become painful if you're an organization that has standardized on using Windows authentication. It might also mean major rework of existing applications that rely on user-based permissions.

No local restore of backups
This one kills me and I think many others as well. While you can now restore a database to another SQL Azure database you cannot bring a backup to your local environment and restore it on a local server. I imagine this could make things rather difficult. Microsoft recommends trasferring your database locally by using SSIS. No thanks.

No Replication
For a lot of organizations this might not matter but if you are an organization that relies heavily on replication you simply can't move a subscriber or publisher out to the cloud.

No SSIS, SSRS, or SSAS
These services are not available on the SQL Azure platform. I would expect that they will be at some point but if I was a betting man I wouldn't put any money on them coming anytime soon. That doesn't mean you can't use these services on local servers while consuming Azure databases but this could hurt your bottom line. I know plenty of organizations that run any combination of these services on one physical machine without issue. So now if you're paying for the hardware to support SSRS why pay a subscription fee to move your database out to the cloud? Maybe you can make some arguments around high availability or scalability for you relational engine but if you're running them on the same server to begin with then these are probably not a concern.

Lack of Profiler
You can't attach a profiler instance to a SQL Azure database. That could get rather dicey when trying to track down all sorts of issues. Seems scary to me. There might be alternative but I'm not aware of them.

Bottom line: SQL Azure is cool, it will mature to the point that it will begin to become adpoted, but it ain't quite ready from prime time.