Getting started with Microsoft SQL Azure Database

Getting started with Microsoft SQL Azure Database

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.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in August 2010

The “elastic” cloud database

”Cloud computing platforms have two primary benefits: ubiquity and elasticity. If either or both of these benefits are something you've had difficulty achieving through other means, then a move to the cloud can start to look attractive.”
-- Don Jones, Microsoft MVP

Read more: When (and why) to move SQL Server to the cloud

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:

Figure 1: The SQL Azure Database portal (click to enlarge)

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:

Figure 3: SQL Server Management Studio view (click to enlarge)

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:

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.

 

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top