RG021 – How to create a MS SQL Azure Database

By Max Ranzau

 

First you might be thinking; what the heck does this have to do with RES Software products?? Well, as you will find out with the currently available Release Candidate 2, RES Workspace Manager 2010 supports a brand new database type, more specifically Microsoft SQL Azure, which is a cloud based SQL database.

So, what’s the big deal? Easy: If you don’t want to host your own DB, Microsoft offers one at a fraction of the price. You don’t have to worry about high availability, disaster recovery or hardware. They put together a nifty little video on the right, which will explain in simple terms what it’s all about, looking from the generic database customer’s side of things. Also it highlights the difference between traditional hosted databases and one sitting in the cloud. The main site for Azure is here.

From the perspective of RES Workspace Manager, it’s quite simply briliant to be able to use a cloud based database. Number one, there’s the savings, number two – since Workspace Manager (unlike some other products who shall remain nameless) does not store User Settings in the datastore, but in the users homedirectory and, the database is cached locally, the RES Workspace Manager agent can run autonomously and performance is actually very good. This is despite you’ve just cut out a major part of the Workspace Manager infrastructure and offloaded it externally.

A couple of items before you get started:

  • You will need a Microsoft Live ID a.k.a MS Passport account. If you use MSN Messenger these are your sign-in credentials. If you don’t have any LiveID yet, go here to sign up.
  • You WILL need a major credit card (Visa, Amex or MC) to enroll for Azure. There is however a free* option
  • It may take up to 24 hours before your database is activated, so don’t plan on demoing it with out having signed up already.

*As mentioned above, there is a (almost) free option for trying out Azure. This is called the Windows Azure Platform Introductory Special. The trick is that if you start using it beyond a certain level, MS will start billing you for it. Currently per month you get:

  • 25 hours of a small computing instance (whatever that is)
  • ½ a Gig of storage
  • 10.000 transactions

There are some pricing figures available at this URL: http://www.microsoft.com/WindowsAzure/offers/popup.aspx?lang=en&locale=en-US&offer=MS-AZR-0001P. Tip: Change the higligted US in the url to your own country code (UK, DK, NL, etc.) to change the currency. Also you can read about the other database options here

In my opinion, the prices are actually very fair when you consider the database offering here includes full DR, HA, Backup, electricity, software patching, and all the other wonderfull things you get to deal with as a DBA. For example another 10k transactions costs you just 1 cent. So for demo/test/lab purposes, quit moaning – it won’t bankrupt you :-)

Okay, so let’s roll up the sleeves and get started. First thing we need to do is to sign up for the service. I’ve grabbed some screenshots from when I did the signup myself. The screenshots are available in the articles right hand side and will be enlarged when you click on them.

  1. Go to the signup page at http://www.microsoft.com/windowsazure/offers. Click the Buy button for the Windows Azure Platform Introductory Special.
  2. Login with your Microsoft  Live ID. If you don’t have one already, the same page will offer you the posibility to sign up for it. All you need is a valid email.
  3. You will now be redirected to the Microsoft Online Services Customer portal. If you haven’t been here before, it will prompt you for your real name, address, and phone.
  4. On the second screen, you will be prompted for information about your company, such as size, website and activity. That kind of stuff.
  5. Next you will be asked for the person owning the Service (the database). Since that’s probably you, make sure to tick the Copy my contact information.
  6. Last part of the profile wizard will just ask you to confirm before continuing to the next wizard where you will be billed.
  7. Your portal shopping cart will now be displayed. It should look like the one on the right. Make sure you tick the agree tickbox before you are able to click Check out.
  8. Next it’s  billing info. If you haven’t been here before, you need to define a new credit card. You can optionally specify a purchase number. The beancounters seem to like this.
  9. In the following form you get to choose creditcard type, number, expiry date and all the usual payment yadda-yadda. Remember to enter a valid billing address also.
  10. Next screen just asks for a confirmation, where you have to enter your full name and your title before proceeding. Your card is not yet validated at this point.
  11. You will be presented with a final confirmation. When you click Confirm Order, the credit card is validated and a confirmation message is sent to your Live ID email.
  12. If everything goes well, you get a confirmation number and you must now proceed to activation. In the meantime you will shortly receive two emails from msonlineservicesteam@microsoftonline.com. One is titled “Purchase confirmation of order NNNNNNNNNN” and the other is “Welcome to Windows Azure Platform Introductory Special“. You don’t have to do anything with these except keep them.
  13. The next step is activation. Here you must first specify a Subscription name. This is just a name for your reference for the database service you’ve just purchsed.
  14. Now you specify the administrative contact for the database. Assuming this is still going to be you, tick the Copy my contact information checkbox.
  15. A summary page, displaying the owner, administrator and the services provisioned will now be displayed. Click Finish to continue..
  16. A final (yes this is the last one) summary page confirms that you’ve activated. Click close. Within 24 hours (it was
  17. Now it’s just a matter of sitting back and waiting for the email. You will btw probably get it twice if you’ve signed up as both the owner and the administrator. The email will have something like “Your Windows Azure Platform Introductory Special subscription will automatically renew in 30 day(s)” in the subject.

Once you receive the email, the fun begins. Let’s create a cloud database server!

  1. Go to sql.azure.com and log in with your Live ID if prompted (it may be cached already)
  2. You will see an overview of the services you’ve purchased, but in here they are called projects. Click on the project name to start creating your database server.
  3. You will be asked to accept Terms of Service before continuing.
  4. Now pick the administrative username (only alphanumerics) and a min. 8-character password. Remeber these for later. Also select in what region of the world the server will be created. Choices at the time of writing were:  South Central US, Northern Europe (believed to be Amsterdam) and East Asia. Microsoft are planning many more datacenters, so this list should grow.
  5. The server will now be created. You will be presented with the administrative web console which looks like shown here on the right. The Master database is the first there to begin with.  Second, note that your database hostname is specified as: xxxxxxx.database.windows.net – Note: You will need this later too.
  6. The database server is firewalled per default, so in order for anything to connect to it, you must enable MicrosoftServices access on the Firewall Settings tab.  By ticking the box Allow Microsoft Services access to this server, a rule will be created allowing 0.0.0.0 – 0.0.0.0 (see graphic on right).
  7. In addition to this, you must use the Add rule button to create another entry which allows access to the (external) IP address which you are behind. Fortunatly the dialog box will display your external address, so it’s just a matter of picking any old name and filling in the ip address or range.
  8. When you’ve added your rule, the firewall configuraton should look like displayed on the right. Note that Microsoft states that firewall changes may take up to 5 min! (I think this is a disclaimer to accomodate anticipated future load. My rules were active within 10 sec.

Finally, back on the Azure database configuration tab, you may notice a button called Test Connectivity. I never managed to get it to work (maybe I shoudl read the manual someday), but supposedly it is supposed to help you test that the database. Don’t sweat it though, your database will fine.

Now we’re done with the Azure web part and it’s time to configure Workspace Manager 2010 to use the cloud database.

  1. Start the Workspace Manager console and navigate to Setup|Datastore|Connection.
  2. Click on the Create button and select Microsoft SQL Azure from the Type dropdown. This is important, as a regular SQL driver will try to connect to azure anyway, but it won’t work well.
  3. Fill in the database server name. This was the nnnn.database.windows.net hostname from step 5 above. Also fill in the credentials which you specified in step 4 in the previous section. Leave protocol encryption disabled (I’m not sure if Microsoft supports this yet at the time of writing.
  4. Next, you fill in the name of the Workspace Manager database you wish to have created on Azure. Note that the Azure host will not be contacted until the end of the wizard, where we are normally used to that it will be checked when you click next after the first
  5. Now you must chose the initial size of the database. You can chose between either 1 or 10GB. For demo and test purposes I would probably stick to 1GB. Remember you will get billed for your consumption acording to the previously discussed pricing figures.
  6. Enter the SQL credentials you want to have created for the new Workspace Manager database. It is  however important to note that there is a password policy in effect, so your  password will have to be minimum 8 chars.
  7. Select the edition of Workspace Manager 2010 to use. Remember you can change between Enterprise, Standard and MyWorkSpace editions as much as you want -  as long as you haven’t activated your Workspace Manager licenses yet. Here is a link to an article describing the different editions and their differences.
  8. When you click Finish on the next screen, Workspace Manager will start talking to the Azure host, creating the database, and adding all appropriate tables etc. The entire operation may take up to around 5-6 minutes, depending on where your datacenter is. The resulting database is around 2,5 MB
  9. Once completed, the console will reload. I’ve experienced I had to use the Connect button on the Connection node in order to establish the connection. Nevertheless I just reentered the data and voilá! Workspace Manager finds a valid database on the remote Azure datastore and then is connected.
  10. When the console has reloaded, notice the nice response time field, which is present in the Connection node. This would ordinarily be less than 1ms for a local, well performing database on the LAN. For a MS Azure database, the latency will likely be in the neighborhood of 150ms.

Again let’s not freak out over this, since Workspace Manager doesn’t really care due to the fact the agents will cache all config data locally.The only component which may be affected by the slower responsetime will be the console, as it is talking directly to the datastore.

Note1: As mentioned in step 10 above, a very nifty feature in the Workspace Manager connection configuration node, is that it displays the actual response time of the datastore. Not simply by ICMP ping, because that would tell you squat about the database servers actual performance. Instead Workspace Manager uses a quick “db-ping” null-operation to measure the response time of the actual database. The console only does this when you navigate to the Connection node. Bear in mind that leaving th console here, will continue pinging the database server likely count towards your transaction billing.

Note2: If you need to deploy these database settings throughout the entire estate, you might consider using DHCP Scope options for distributing Workspace Manager datastore connection information. This is available through the Connection String button in the Connection node. This is however a story for another day. In the mean time, hit F1 when you’re in the Connection node and search for Dynamic Datastore configuration for Agents. That should tell you all you need to know about this option.

Happy Cloud-Computing!

/Max

Addendum pr April 14th 2011: If you for one reason or another want to cancel your Azure subscription, it seems evident that you’re not able to do it on the portal. Don’t panic though. All you have to do is call the friendly folks at the MS Online support department and ask them to cancel the subscription. They will ask for your Live/MSN id and you’re good to go. The US phone number is 1 (866) 676 6546, but you can find all the international phone numbers on this page.

No Comments

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment


two + 9 =