This blog post will show you how easy it is to set up the famous Adventureworks database using the Microsoft Azure Cloud.

Before we start, a little background:

A couple of days ago, I was at a customer site and needed a simple database in order to show a .NET framework demo. I wanted to demonstrate an end-to-end sample that consisted of loading data from an SqlServer database by means of the Entity Framework (EF) and hooking the loaded data on a Windows Presentation Foundation (WPF) front end. The requirement for the database were really simple. I just needed a DB that would run on Microsoft SqlServer, contain a couple of tables with relations and some data in them.

I remembered the good old Adventureworks database that is a perfect fit for any database related product demo. There are various possibilities on how to get the Database on http://www.codeplex.com.

I decided to go for the out-of-the-box-ready solution that provides an *.mdf and an *.ldf file that can be attached to an Sql Server, using the following link: http://adventureworksdw2008.codeplex.com/

However, as usual, it was not as easy as I hoped it would be. 🙁

When I tried to start the SqlServer on my box, using the SqlServer Configuration Manager, I got an error message stating that “The remote procedure call failed. [0x800706be].

It looked like my Sql Server installation was somehow broken. I fiddled around for about 20 minutes and decided that I would just reinstall the SqlServer and not waist more time. Unfortunately, the SqlServer is a piece of software that, in my humble opinion, is not installed in 5 minutes time. As soon as the installer completed, it triumphantly told me that everything installed perfectly EXCEPT FOR THE DATABASE ENGINE!!! *grrrrrr*. That surely pissed me off. Annoyed and disappointed I went to sleep. (Needless to say, it was already very late at night.)

That left me in the awkward situation that I would have to show up at the customer site in the morning and fix my database problem sometime between the getting out of bed and the start of the course that I was about to teach. *sigh*

But then, IT HIT ME!

Since I am a huge fan of Microsoft Azure and deeply appreciate the technology behind it and how it facilitates a developers life, I turned my back on the local SqlServer installation on my notebook and looked up into the skies or, I must say, the cloud. And, alas, the Azure cloud really saved my day!!! Literally 5 minutes later, I had my db running and could even take a coffee break before the course started. *Tataaa*.

In the next section, I will demonstrate how amazingly easy it is to get the Adventureworks db running on Microsoft Azure. Keep in mind that the process that I am going to describe replaces all of these tasks:

• Providing a virtual or physical machine
• Downloading an SqlServer Installer
• Organizing a License
• Installing the SqlServer
• Configuring the SqlServer
• Downloading and Installing Adventureworks

Running Adventureworks on Microsoft Azure SQL

The following tutorial is divided into four parts:

1. Prerequisites
2. Provisioning an Azure Sql Database Server
3. Getting and Deploying the Scripts
4. Verifying the Installation

Let’s get started:

Step 1: The Prerequisites

You need 3 things in order to reproduce this example:

1. A Microsoft Azure Subscription
If you do not have one, you can get a free trial subscription for one month and 200 CHF at:
http://azure.microsoft.com/en-us/pricing/free-trial/
2. A computer with an installation of the .NET framework in version 4.
If you run Windows 8, you are set. If you run an earlier version and .NET 4.0 is not installed, you can get it at the Microsoft Download Center: http://msdn.microsoft.com/en-us/vstudio/aa496123
3. The Azure db must be accessible from your client computer. This is a configuration setting in the Azure Management Dashboard. Details can be found below.

Note that currently there are two Azure Portals. The old one under https://manage.windowsazure.com and the new one under https://portal.azure.com. For simplicity, I am going to use the old one.

Step 2: Provisioning an Azure Sql Database Server (Old Portal/manage.windowsazure.com)

After you have created your Microsoft Azure account, go to the management portal: http://manage.windowsazure.com and log in.

On the portal, select SQL DATABASES from the navigation on the left hand side:

Once you are on the SQL DATABASES page, click SERVERS on the top navigation:

Finally, click the ADD button at the bottom:

The CREATE SERVER wizard will pop up, fill in the fields and confirm. Select a region that is close to your location.

After some time, it took about 30 seconds for me, your database server is ready to go! Click the little arrow on the right of the server name to proceed to the server settings:

On the following page, click CONFIGURE and then click the arrow on the right, where it says “ADD TO THE ALLOWED IP ADDRESSES”. This configuration is vital since it allows your local machine, where the browser is running, to access the database server. IMPORTANT: Do not forget to hit the save button, once completed!

Note the server name and the credentials that you specified and we can go on with the next step. Alternatively, you can navigate to the DASHBOARD page and copy the MANAGE URL from there.

Step 3: Getting and deploying the scripts

Navigate to http://msftdbprodsamples.codeplex.com/releases/view/37304 and download the “AdventureWorks2012ForWindowsAzureSqlDatabase” file. Save and extract the zip file.

Open a CMD prompt with administrative privileges and navigate to the directory where you unzipped the package and there into the \AdventureWorks directory.

Enter the following command:

CreateAdventureWorksForSqlAzure.cmd <servername> <username> <password>

Important Hints!
Make sure you enter the name as “username@servername”. Providing the username only can lead to problems during the installation. Additionally, you have to use the full server name, as in: zvnhp88skk.database.windows.net.
Example:

CreateAdventureWorksForSqlAzure.cmd zvnhp88skk.database.windows.net mme@zvnhp88skk.database.windows.net mypassword

After roughly 5 minutes, the script should show “Installation Completed”. That’s it, your database is up and running.

Step 4: Verifying the installation

You can use any SqlServer tool, such as Visual Studio or the SqlServer Management Studio to connect to your DB and verify that the data is there. I would like to show a very simple approach using SqlCmd.exe. SqlCmd is shipped with Visual Studio and the easiest way to use sqlcmd.exe is by using the Visual Studio Command Prompt. Run the following command inside the VS command prompt:

sqlcmd.exe –S <servername> –d <db name> –U <username> –q <query>

or, in our case:

sqlcmd.exe –S zvnhp88skk.database.windows.net –d AdventureWorks2012 –U mme@zvnhp88skk –q “select top 10 firstname, lastname from person.person”

If you see 10 records that contain names, the data is there and we are done!

Important hint: Always keep in mind that Microsoft Azure is based on a pay-as-you go policy and you will be charged on a per-minute base for your database instance! If you don’t need it, shut it down!