Microsoft’s SQL Azure Database service recently became available to the public, providing a new way to store data...
in the cloud and access it from a variety of applications. Developers and DBAs familiar with SQL Server should come up to speed fairly quickly by combining their existing knowledge with new features and tools that can be used for development and administration.
It all starts with the fundamentals, however, so let’s look at how to create an Azure account, access it from applications and use SQL Server 2008 R2 client tools to work with SQL Azure.
Before you create an account, you should visit the SQL Azure pricing website and decide on the type of service you need. A good start would be to take advantage of the introductory special that will give you, at least for now, three free months of a basic SQL Azure account with a single Web Edition database up to 1 GB.
There are many pricing options and sizes to choose from, and you can go as large as 50 GB per database with a monthly charge of $499.95. There is also the lowest option of $9.99 per month for a 1 GB database, which is very affordable even after the introductory special expires. Note that data transfers are charged separately -- and in my opinion very reasonably -- at 10 cents per GB for inbound traffic and 15 cents for outbound traffic.
Once you create an account and select a pricing option, you can access the portal to manage your SQL Azure databases. The interface is pretty intuitive; after you select an administrative password and create a server you can start creating user databases. Keep in mind that you will be charged for each user database you create based on its size, but you will not be charged for the master database.
Here is what the portal looks like from my test account with one user database:
Once you create a server and database in the portal you will need to configure the firewall settings for the server. By default, no connections are allowed and you will not be able to connect to Azure unless you add at least one firewall rule. Each rule lets you add a range of allowable IP addresses. For your development account, you can add the range used by your company and possibly the IP range of your home Internet provider if you intend to work remotely. For production servers, you have to allow access from your Web server and other application servers.
SQL Azure databases are maintained using SQL Server Management Studio 2008 R2 (SSMS). The Azure portal provides the connection details you need to connect; just select “SQL Server Authentication” in the connection dialog and use the fully qualified server name from the portal along with the username and password you created there:
Figure 2: The SQL Azure connection portal
After SQL Server Management Studio connects to the cloud, you’ll see a view similar to that of the SQL Server Database Engine, but with fewer options:
The support for SQL Azure database administration and management is very limited, however, and the user interface (UI) doesn’t allow you to do much. For instance, when you right-click on different objects, few options exist aside from being able to delete them or script them out. In fact, using this interface is a lot like working with an older version of Oracle since there aren’t many administration features implemented in the UI, so you’d better at least know basic T-SQL syntax.
Moreover, SQL Server Management Studio generates scripts that aren’t valid in SQL Azure. For example, I created a table and scripted it out, but when I ran the script it failed because the PAD_INDEX option isn’t supported in SQL Azure. Also, the script generated the “USE DatabaseName” script, but switching database context isn’t allowed in SQL Azure. As you can see, SQL Server Management Studio needs some more work to make it more useful with SQL Azure management.
In fact, once you start working closely with SQL Azure you may be in for a disappointment if your expectations are set too high. While the technology is fully based on SQL Server, the current version doesn’t go much beyond the core database engine and T-SQL language since most of the surrounding technologies haven’t been migrated to SQL Azure yet. Some of the most significant SQL Server technologies missing from SQL Azure include:
- SQL Server Agent
- Table partitioning
- Database mirroring
- SQL Server Service Broker
- SQL Server Analysis Services (SSAS)
- Common Language Runtime (CLR)
- CLR user-defined types
- Transparent data encryption
- Sparse columns
- Full-text search
- Change data capture
- Data compression
- FILESTREAM data
The complete list is much longer, but SQL Azure documentation can provide you with more details. Note that due to all these limitations, it may not always be easy to migrate your existing applications to the cloud. Therefore, you may have to wait for future versions to get the features you need.
Regardless of these shortcomings, once you get used to the slightly different management options and learn what isn’t available you should be able to utilize your existing SQL Server skills and be comfortable working with SQL Azure. Later in this series, I’ll dive deeper into SQL Azure development, administration, data loading options and best practices.
About the author:
Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.