Monday 10 January 2011

Getting Started with SQL Azure

Towards the end of last year, I was the lead author on Microsoft Learning course 10337A: Updating Your Microsoft SQL Server 2008 BI Skills to SQL Server 2008 R2. While this is a course primarily for BI developers, we included a module on SQL Azure; and to make the course work in a classroom, we created a Silverlight-based simulation that students can use to walk through the steps required to set up and use a SQL Azure database. Additionally, Hilton Giesnow presents a useful video introduction to SQL Azure on the MSDN site.

However, the release cycle for cloud-based technology moves even faster than that of traditional software products, and predictably enough, the Azure team at Microsoft has created a new version of the Web portal used to manage Azure platform subscriptions – including SQL Azure. So, I thought it might be useful to provide a short walkthrough based on the latest portal (before they go and change it again!)

To start with, you need to sign-up for an Azure subscription at http://www.microsoft.com/windowsazure/offers/. It may take as long as a couple of days to provision your account, but mine took less than half an hour. After you’ve signed up, you’ll be able to access the Azure portal using your Windows Live ID:

Picture1

To create a SQL Azure server (which you can think of as a cloud-based computer running an instance of SQL Server – it’s not, but you can think of it like that!), click the New Database Server button in the toolbar (which I guess like everything else these days is probably more properly called the “ribbon”), which will display all of the Azure subscriptions your Windows Live ID is associated with that include SQL Azure services. In this case, I have a single subscription named CM Azure Subscription, which currently has no SQL Azure database servers defined in it.

Picture2

No you can select the subscription in which you want to create the SQL Azure server, and click the Create button in the toolbar/ribbon, which will start a wizard, the first step in which is to select the geographical location where you want the server to be hosted:

Picture3

After selecting a location, you need to specify the Administrator credentials for the server. There are some restrictions on login name and password complexity (for example, you can’t create a login named Administrator with the password password).

Picture4

Next, the wizard prompts you to specify the firewall rules that control connectivity to your server. By default, nothing (including any other Azure services you may have) can access your server, so you’ll typically want to enable access for Windows Azure services as shown here:

Picture5

Additionally, you’ll probably want to allow at least some computers to connect to the server across the Internet – even if initially this is limited to your own development workstation. To do this, you’ll need to add a firewall rule that specifies a range of IP addresses from which you want to enable connectivity. In this example, I’ve created a rule that allows connections from any computer on the Internet.

Picture6

After completing the wizard, your SQL Azure server is provisioned with a name that looks like a random stream of characters and shows up in the Database section of the Azure portal as shown here:

Picture7

Now that you have a SQL Azure server, you can select it in the Azure portal and create databases in it by clicking the Create button in the ribbon. Doing this results in a prompt for a name, edition, and size for your database. There are two editions available (Web and Business), each with their own range of possible sizes (and corresponding prices), so when you select an edition, the available sizes will reflect the sizes supported by that edition. In this example, I’ve created a 1GB Web edition database.

Picture8

After creating your database, you can view its properties in the portal as shown here:

Picture9

The latest version of the Azure portal includes a management tool that you can use to manage your SQL Azure database. To launch this, simply select the database you want to manage in the Azure portal and click Manage in the ribbon. The first time you do this you’ll be prompted to accept the terms and conditions for the management tool, and then the tool itself will open in a new browser window and prompt you to log in as shown here:

Picture11

You can log in using the administrator credentials you specified when you provisioned the SQL Azure server, and start the manage the database by using the management user interface as shown here:

Picture12

Additionally, if you have installed SQL Server Management Studio for any edition of SQL Server 2008 R2 (including the free Express edition), you can connect to your SQL Azure server and manage it by specifying the fully-qualified server name and SQL Server login credentials as shown here:

Picture13

Note that the server name takes the form YourSQLAzureServerName.database.windows.net, and the login name takes the form YourAdminLoginName@YourSQLAzureServerName.

SQL Azure servers are displayed in SQL Server Management Studio like this:

Picture14

From here, you can manage your SQL Azure database in a similar way to how you manage on-premise SQL Server instances, though you’ll find that there are some SQL Server features that are not supported by SQL Azure. You can also connect to your SQL Azure database from client applications in a similar fashion to on-premise SQL Server databases by specifying the fully-qualified SQL Azure server name in the connection string.

For more information about SQL Azure, see http://msdn.microsoft.com/en-us/library/ff937661.aspx.

del.icio.us Tags:

No comments: