SQL Server Reference Guide

SQL Azure is an Edition of SQL Server "in the cloud." It's actually a "database as a service," which I probably need to explain a little. The concept of a database as a service is fairly simple — when you need to access a database, you simply connect to it and run your queries. There's nothing to install, nothing to patch, and nothing to manage — sort of.

A database is an interesting service to provide. On the one hand, it's just as I mentioned — a database ready when you need it. For SQL Azure, The installation is handled, the upgrades and patches are handled, the system balances its own load, and High Availability is helped with a three-copy-commit built right in. But it's not simply a matter of a developer writing code against a database. There are still tasks for the database designer and the database administrator. Databases must be properly designed and created, server-side code needs to be written, backups need to be considered, and there are sizing (and thus cost) considerations, as well as proper security design and so on.

To do all of those things you need a way to access the system and control it. That's done through the use of various Microsoft and 3rd-party tools. In this overview I'll cover your available options, and how you can leverage those in your current environment.

General List of Tool Options

I'll start with the general list of tools you can use. This isn't meant to be an exhaustive list, since you can do most any task in Transact-SQL (T-SQL) that you can graphically, so any program you write that sends T-SQL is a management and development tool for SQL Azure:

• The Windows Azure Portal — Sets up subscriptions, create databases and objects, simple T-SQL Editor
• SQL Server Management Studio — Connect to database, runs T-SQL, create database objects graphically
• Command-Line Tools — SQLCMD and PowerShell (but not the PowerShell provider as of this writing) can be used, along with various solutions that can send commands using the SMO 10.5 model
• Visual Studio 2010 and Higher — Data structure design and creation, object browsing and selection
• Third Party Tools

I'll dig into each of these further in a moment.

I won't cover the list of tools you can use to import and export data from SQL Azure, since that's a specific task and warrants its own article. You do have a lot of options, however, and I'll cover those more fully in another article, along with scenarios where you can use each. Here's a partial list of your options:

• SQL Server bcp (Bulk Copy Program)
• SQL Server Integration Services
• Microsoft Data Sync
• 3rd party tools
• SQL Azure Export in SQL Azure Labs

The Windows Azure Portal

Probably the most up-to-date tool to use is the Windows Azure Portal, where you're able to access all of your subscription information, work with your billing, and get an overall view of the system. It's also available anywhere you have an Internet connection, on any system that can run a Silverlight plugin, including PC's, Macs and Unix systems. What it lacks in features for development it makes up for in access.

You connect to the portal by going to http://windowsazure.com and selecting the "Log in to Portal" link at the top of the page. From there, you can see your subscriptions.

Figure 1

From here you can create an entire new "Server," which is actually just a logical construct to aid in bundling the billing and security areas. Clicking on the "Databases" icon on the left brings up the management window specific to SQL Azure.

Figure 2

Here you can see the name of your server and administrator login (hidden in this case for my screenshot), the number of databases you have and their "edition" (which equates to size), and the region of the world your databases are hosted in. You can control the firewall rules here, since by default no one can connect until you allow them a connection in to the system. When you do that, you can also see the IP address on the system where you're hitting the portal from — which is a nice touch.

You can also create new databases and drop old ones here, and change the password for the administrator of the account.

Clicking the "Manage" button at the top brings you to the SQL Azure Database Manager — what used to be called "Project Houston."

Figure 3

After you enter your login credentials — and note that you enter a specific database name, not just the server name — you're brought to the object explorer portion of the tool. You can see the tables, views and stored procedures associated with the database, and the graphic in the middle rotates to show some basic information.

Clicking the "New Query" button brings up a panel where you can enter T-SQL code.

Figure 4

There's no color-coding here, Intellisense or code-correction, just a simple text box. You  can save and load queries from here as well.

Back in the main tab of the SQL Azure Database Manager, you can create and design a new table in a graphical way.

Figure 5

Other than billing and subscription information, I tend not to use the portal for SQL Azure very often. I normally choose either SQL Server Management Studio (SSMS) or more often command-line tools.

SQL Server Management Studio

In SSMS version 2008 R2 (even the Express Edition), you can connect to and work with a SQL Azure database.

Figure 6

There are a couple of things to keep in mind here. The first is that you will need to specify the full name of the SQL Azure system, as it is shown in the Windows Azure Portal. For the login, use the format username@servername.

You'll need SQL Server Authentication, and the system you're connecting from will need to have a Firewall rule opened. You can do this through the Portal or use T-SQL commands.

You'll also need to specify the database to connect to. I use the system database master, since I have access to that database, and the nice thing about SSMS is that it will show all of the databases you have access to in one window.

Figure 7

Once inside, you'll find a familiar environment. There are, however, a few things that don't quite work the same. You won't find a "Properties" page anywhere, there are no Standard Reports, and in a Query Window you won't see advanced features like Intellisense enabled.

Even with these limitations, this is the tool most database administrators will use. You can right-click the various objects to see more of what does and does not work, but for the most part you can think of this as a good editing and visualization tool. It's a great place to work with both on-premise and SQL Azure databases, but you'll need to bone up on your T-SQL syntax to do more of the management tasks that you are used to doing graphically.

Command-Line Tools

I like to use the command-line tools because I can get information without having to open a graphical user interface. Using this blog entry, for instance, I can get the daily average and monthly billing — something that is very important to track. You have two main options for working with SQL Azure from the command line.

First, you can use PowerShell. I've explained PowerShell in other articles, and from those you'll recall that there are actually two ways of using PowerShell with SQL Server. The first is that you can invoke the PowerShell Provider for SQL Server, or include the Server Management Object (SMO) libraries in a PowerShell script.

For the  SQL Server Provider for PowerShell, you'll notice that you don't have the ability to right-click the database name in SSMS and "Start PowerShell Here." You can, however, cheat a little and use the SQLPS command in the operating system and then use the invoke-Sqlcmd command-let. This allows you to run a query against SQL Azure:

Invoke-Sqlcmd -Query "SELECT * FROM sys.databases;"  -ServerInstance "MySQLAzureServerName.database.windows.net" -Username  "YouUserNameIncludeTheQuotes" -Password " YourPasswordncludeTheQuotes  "

But to me this seems a little "heavy." I would rather just use native PowerShell and invoke the SMO model 10.5 — which of course must be installed (comes with the client tools for SQL Server 2008R2), or as I've done here with the SQL Native Client ADO call  and then treat it like any database query:

# Connect and run a command using SQL Native Client, Returns a recordset
# Create and open a database connection
$sqlConnection = new-object System.Data.SqlClient.SqlConnection "Server=tcp:YourServerName.database.windows.net;Database=master;User ID=YourUserName;Password=YourPasswordName;Trusted_Connection=False;"
$sqlConnection.Open()
# Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "SELECT @@VERSION" 
# Execute the Command
$sqlReader = $sqlCommand.ExecuteReader()
# Parse the records
while ($sqlReader.Read()) 
{
write-host $sqlReader[0] 
$intRow = $intRow + 1
}
# Close the database connection
$sqlConnection.Close()

Or any number of ways to query a SQL Server database using the Native Client.

Of course, if you have the client tools installed, you can use SQLCMD, which is what I use quite often as well — just treat the server and database like any other server, with the exception of the server and login name:

sqlcmd -S YourServerName.database.windows.net -U YourUserName@YourServerName.database.windows.net -d master

This prompts for a password and drops you in the interactive mode of SQLCMD. You can also run queries from a file, pipe output files and more. Remember, you need to specify the database name on the connection — SQL Azure does not currently support the USE DATABASE construct.

Visual Studio 2010 and Higher

If you're a developer, you can use Visual Studio 2010 and higher to design datasets that hit a SQL Azure database.

Figure 8

Those classes can be bound to SQL Azure, and you can send queries using the Query window in the Tools menu.

Figure 9

Third-party Tools

Many companies are making toolsets for SQL Azure connections, monitoring and management. I've tested a few of them out here.

Cerebrata has graphical tools for Windows Azure, and their Powershell command-lets can connect to and work with a SQL Azure system as well. You can find them here: https://www.cerebrata.com/Products/AzureManagementCmdlets/Details.aspx?t1=0&t2=5

Redgate makes software for on-premise SQL Server, and they are supporting SQL Azure with their data compare and other tools. : http://www.red-gate.com/products/cloud/ The one I've been using lately is including QueryAnywhere - http://www.queryanywhere.com/ which is a little better than the management Portal for querying the databases.

Figure 10

Quest Software also makes tools for on-premise SQL Server and many other databases as well. They have the popular TOAD product, and although I've tested it with Windows Azure Table storage I have not tried the SQL Azure connections yet. They are also making more and more tools for Azure. http://www.quest.com/toad-for-cloud-databases/

Although not specifically for managing SQL Azure, there are a couple of other tools I want to mention here. The first is the Microsoft Assessment and Planning (MAP) Toolkit. I've written about this tool before, and it's really worth investigating if you want to evaluate a database to see how much work it will be to bring it to SQL Azure. It's been upgraded so that it is Azure-Aware. http://technet.microsoft.com/en-us/solutionaccelerators/gg581074

There's also an interesting CodePlex (Microsoft's Open-Source-like site) free tool that you can use to migrate databases, called the SQL Azure Migration Wizard: http://sqlazuremw.codeplex.com/

If SQL Azure is in the mix for your planning, check out these tools to see which fits best in your day to day routine.