Where can I store my Data on Windows Azure?

So, You’re building a cloud app on Windows Azure are you? Where are you going to store your data? This is a question that comes down to architecture, scale and options. Considering I don’t know your architectural decisions, or how scalable your particular application, I guess that leaves me with only one topic to talk about, What are your options for storing data on Windows Azure?

Windows Azure is an open cloud platform which enables you to quickly build and deploy your applications using your existing skillset. This is reflected in the data options which are available for use on Windows Azure which are also hosted on Windows Azure.

Relational Databases on Windows Azure

Some Applications (or Developers) can’t live without a trusty relational database. We’ve got that.

MySQL

This option surprises many developers, but Windows Azure does have MySQL databases available for use.

MySQL is provided by a partner ClearDB who maintains multi-master and multi-master with multi-replica MySQL configurations. ClearDB combines advanced replication techniques, advanced clustering technology and a web service interface to provide MySQL Databases for your consumption.

If you create a Windows Azure Web Site using the Create with Database or From Gallery option in the Windows Azure Management Portal a Mercury sized database is being provisioned on your behalf.

MySQL Sizing Options

Pricing available on the Windows Azure Store on the ClearDB Website.

Package Name

Database Size

Max. Connection limit

I/O Performance

Mercury 20MB

4

Low
Venus 1GB

15

Moderate
Saturn 5GB

30

Moderate
Jupiter 10GB

40

High

MySQL on a Linux Virtual Machine

In addition to the ability to leverage a third-party service for using MySQL on Windows Azure, you can also leverage the new Virtual Machines feature. Follow the steps for setting up MySQL on an OpenSUSE Virtual Machine on Windows Azure.

Pricing for Linux Virtual Machines are available on the Windows Azure Pricing page.

SQL Database

Microsoft has created a cloud equivalent of SQL Server known as Windows Azure SQL Database (or SQL Database for short). SQL Database uses the same TDS Protocol as SQL Server which means all standard libraries or drivers [like the SQL Server Driver for PHP] work against SQL Database for Connectivity and Querying.

SQL Database Sizing Options

Pricing available on Windows Azure Pricing page under Data Management.

Database Size
100 MB
1 GB
10 GB
150 GB

As you can see above, SQL Database offers sizing tiers which acts as a starting point for your database size, additional database sizing between the tiers is offered at a discounted database rate as described in the SQL Database section of the Windows Azure Pricing page.

You may have also noticed that the maximum database size is 150GB, once you reach a database of this size it becomes necessary to shard your data using a technical known as SQL Federation.

SQL Server on a Windows Virtual Machine

If you need a large database (Scaled Vertically) or rely on a feature that is not currently present in SQL Database, it is possible to install SQL Server in a Windows Virtual Machine. Depending on the version of SQL Server you would like to use there are two great tutorials: Provisioning a SQL Server Virtual Machine on Windows Azure [which installs SQL Server 2012 from an image in the gallery] or Creating and Uploading a Virtual Hard Disk that Contains the Windows Server Operating System [which explains how to create a reusable operating system image, you’ll need to install SQL Server before running sysprep the VHD]

Unstructured Data (NoSQL) on Windows Azure

Unstructured Storage is becoming all the rage, which is understandable due to its ability to massively scale.

Windows Azure Table Storage

Windows Azure Table Storage is one of the three abstractions of Windows Azure Storage. Table Storage is an Entity-Attribute-Value model data store with a 100TB per account limit. Each subscription has 5 storage accounts by default, and more storage accounts can be added by calling customer support.

An entity in Windows Azure Table Storage is comprised of 3 required properties partition key, row key & timestamp and up to 252 additional properties. Properties can be one of the following data types Int32, Int64, String, Guid, DateTime, Double, Boolean or Byte Array.

MongoDB on Windows Azure

There are a few different ways that MongoDB is supported on Windows Azure which include in a Cloud Service, on a Virtual Machine or as a Service.

MongoDB in a Cloud Service

The creators of MongoDB, 10gen, have created a MongoDB installer which will install a MongoDB replica set in a Windows Azure Cloud Service. To do this they leverage a worker role in which their installer leverages a startup script to install the MongoDB Server. It also utilizes Local Storage as a cache before committing new transactions to Blob Storage.

To use the MongoDB in a Cloud Service configure and Deploy the MongoDB Installer.

MongoDB in a Virtual Machine

Obviously MongoDB can be installed on a Virtual Machine and can be exposed to the internet. There are two tutorials on installing a standalone instance of MongoDB on Windows Azure: Install MongoDB on a virtual machine running CentOS Linux in Windows Azure and Install MongoDB on a virtual machine running Windows Server 2008 R2 in Windows Azure.

If you would like to set up a highly available MongoDB replica set on Windows Azure, follow the instructions laid out in MongoDB on Windows Azure VM – Linux Tutorial or MongoDB on Windows Azure VM – Windows Installer,

MongoDB as a Service

Another option for MongoDB on Windows Azure is MongoDB as a Service provided by MongoLab. This service is [at time of writing] in preview and provides 500MB of storage for FREE. The MongoLab Preview is currently only available in the East US datacenter.

Preview Warning

We are pleased to be able to offer free databases running on Windows Azure. Be aware that both Windows Azure and MongoLab’s presence on Windows Azure are still in preview mode, and that these databases are not appropriate for production use. For example, we may need to take them down for maintenance from time to time without advance warning.

image

CouchDB on Windows Azure

Apache CouchDB is a JSON document database exposed over HTTP.

CouchDB comes with a suite of features, such as on-the-fly document transformation and real-time change notifications, that makes web app development a breeze. It even comes with an easy to use web administration console. You guessed it, served up directly out of CouchDB! We care a lot about distributed scaling. CouchDB is highly available and partition tolerant, but is also eventually consistent. And we care a lot about your data. CouchDB has a fault-tolerant storage engine that puts the safety of your data first.

BigCouch by Cloudant

Cloudant has expanded the base implementation of CouchDB in a new incarnation they are calling BigCouch. BigCouch as a Service is offered in the West US datacenter in a cluster Cloudant refers to Lagoon.

The Cloudant Data Layer collects, stores, analyzes and distributes application data across a global network of secure, high-performance data centers, delivering low-latency, non-stop data access to users no matter where they’re located.

BigCouch is chalked full of goodies including secondary indexes, lucene-based full text search, as well as built in replication and synchronization.

Cassandra on Windows Azure

Looking to build the next Netflix or Twitter? Run an elastic, decentralized and fault-tolerant Cassandra cluster on Windows Azure. Learn how to harness the power of Cassandra while a Node.js application in the tutorial Running Cassandra with Linux on Windows Azure and Accessing it from Node.js.

Choose your own Adventure

Have a favorite data store of your own? Why not try running it on Windows Azure? If you do, leave a comment below with a link off to a blog post explaining how you got it running.

Stay Cloudy my Friends…

SQL Azure Powershell for Developers on the Run!

In my last post I Announced the Windows Azure Powershell Extensions a new project that I will be iterating over when I run into tasks that are common in everyday Windows Azure Scenarios.

Being a Developer on the run, going from City to City or Coffee shop to Coffee shop, the absolute first Extension came to me naturally. Quickly create firewall rules for where ever you are by using Add-RoamingFirewallRule, when you’re ready to go remove the setting with Remove-RoamingfirewallRule.

Let’s take a look at how to use these scripts. [If you haven’t already installed the Windows Azure Powershell Cmdlets, download the Windows Azure Powershell Cmdlets]

Adding Functions to your Powershell Profile

Functions are only helpful in powershell if their accessible, if it takes you too long to set up a script there’s a good chance you will never use it. Let’s take a brief look at making sure your Powershell environment is up and running.

Open Powershell.

Create a Powershell profile

new-item -path $profile -itemtype file -force
Notepad $profile

Now that your profile has been created, and opened in notepad it’s time to copy the Add-RoamingFirewallRule and Remove-RoamingFirewallRule functions from the Windows Azure Powershell Extensions Project on GitHub and paste them into the open notepad document. After you’ve finished save and close the file. Restart Powershell.

Using Add-RoamingFirewallRule & Remove-RoamingFirewallRule

Let’s first take a look at the help files that are provided with the script.

Get-Help Add-RoamingFirewallRule

image

Get-Help Remove-RoamingFirewallRule

image

You’ll notice that both Add-RoamingFirewallRule and Remove-RoamingFirewallRule both require an EnvironmentsCsv parameter which is a path to a CSV file containing Subscription and Management Certification information.

Creating the Subscription CSV File

The CSV file ensures that you don’t need to login to the Windows Azure Portal anytime you’re looking for your SubscriptionIds [Just keep the CSV file up-to-date]. The file requires the header in order to map to the internal variables in the function. The first value is the SubscriptionId for your Windows Azure Account, the second value is the Thumbprint for a Certificate [installed in CurrentUser\My] which has been uploaded as a Management Certificate using the Windows Azure Portal. Save this CSV file in a memorable location.

SubscriptionId,Thumbnail
BD1DD4FC-E866-473A-8665-760BE7B007B0,204E4F82C76FD2586234E7064FEC1EAEB0709507
E969F512-56ED-4CF3-B59D-A60A5D394527,204E4F82C76FD2586234E7064FEC1EAEB0709507

Adding SQL Azure Firewall Rules with Add-RoamingFirewallRule

To create a Roaming Firewall Rule you simply need to call Add-RoamingFirewallRule and pass in a Rule Name and a Path to the Subscription.csv file [created above].

Add-RoamingFirewallRule 'Starbucks' C:\WindowsAzure\Subscription.csv

image

After taking a look at the Windows Azure Portal you notice that a Firewall Rule will be created for every SQL Azure Server within each of the subscriptions that were defined in the CSV file.

Removing SQL Azure Firewall Rules with Remove-RoamingFirewallRule

To remove the setting once you’re done hanging out at Starbucks, simply call Remove-RoamingFirewallSetting passing in the same Rule name and the path to your Subscriptions.csv file.

Remove-RoamingFirewallRule 'Starbucks' C:\WindowsAzure\Subscription.csv

image

Magically the firewall rule disappears!

That’s a Wrap

I hope that this helps speed up your day when working with SQL Azure at different locations or encourages you to get out of the office from time to time. If you have any requests or notice a bug please file an issue on github.

Happy Clouding!

Migrating Large Databases from On-Premise to SQL Azure

Recently, I was working on a project that required a site migration from a Shared Hosting server to Windows Azure. This application has been up and running for sometime and had acquired quite a substantially sized database.

During the course of the project I ran across a few road blocks which I wasn’t expecting, due to the experience gained in my previous blog entries: Migrate a database using the SQL Azure Data Sync Tool and Scripting a database for SQL Azure (Issues explained in previous link resolved with launch of SQL Server 2008 R2). Hopefully the following tricks will help you along your data migration.

Using Import/Export in SSMS to Migrate to SQL Azure

In addition to the SQL Azure Data Sync Tool, it is possible to use the existing Import/Export Wizard in SQL Server Management Studio to migrate data to SQL Azure. There are a number of things to keep in mind while using the Import/Export Tool:

SQL Server Native Client to .NET Data Provider for SqlServer

SQL Azure doesn’t fall under the typical SQL Server Native Client 10.0 Product SKU, this means that you’ll have to use the .NET Data Provider to migrate your data. The configuration screen for the provider is very intuitive, but there are two key settings that should be changed from their default values, Asynchronous Processing (set to true) and Connection Timeout (increase to 1500).

SQL-Azure-SMSS-IE-Tool

Without changing the Timeout value the data migration would error out after creating the fist few sets of rows. Making this an Asynchronous process was beneficial when exporting multiple tables at a time.

Work-around for SSIS Type: (Type unknown …) Error

There is a chance when you go to run the migration that you will encounter an error as described in Wayne Berry’s [@WayneBerry] blog post entitled “SSIS Error to SQL Azure with varbinary(max)” on the SQL Azure Blog.

As Wayne explains in his post, there are a number of XML files which contain data mapping information used by the Import/Export Wizard in order to map the data from the source database to the proper data type in the destination database.

Database Seeded Identity Insert Issue

I’m not sure why this happened, but when using the Import/Export even with Identity Insert on, the ID [Identity] Column was not Inserting the correct values. To get around this I used the ROW_NUMBER to generate new Identities and rebuilt the foreign key tables.

There is a lot of chatter on the Forums and other blog posts that say that BCP with the –E switch is the most effective way to do exact copying (with Identity Columns).

For more information:

Cost Effective Approach

A good thing to keep in mind while preparing your database for migration is that transactions as well as data transfer costs are applied to Queries to (and from) SQL Azure. With this in mind it would be best to set up a scenario where you would test your data migration to ensure the data migration would be performed in the least number of attempts as possible.

Happy Clouding!

Reach for the Sky… Script a Database for SQL Azure

While there are many different ways to get your database up and running into the cloud. You could read up on SQL Azure Sync Framework in my post titled “Synchronizing a Local Database with the Cloud using SQL Azure Sync Framework”.

However, nothing feels more comfortable to a developer than something that familiar. After a little bit of investigating while preparing for my talk at Confoo on SQL Azure, I managed to find a post on the MSDN website that Explains what is needed in order to use the Generate Scripts Wizard in SQL Server Management Studio.

Create the Transact-SQL Script

  1. In Object Explorer, right-click the database, point to Tasks, and select Generate Scripts.

  2. In the Script Wizard dialog box, click Next to get to the Select Database step. Select School, select Script all objects in the selected database, and then click Next.

  3. In Choose Script Options, set the following options:

    • Convert UDDTs to Base Types = True
    • Script Extended Properties = False
    • Script Logins = False
    • Script USE DATABASE = False
    • Script Data = True

    SQL Azure does not support user-defined data types, extended properties, Windows authentication, or the USE statement.

  4. Click Next, click Next, and then click Finish. The Script Wizard generates the script. Click Close when the script is completed.

  5. In the generated script, delete all instances of "SET ANSI_NULLS ON".

  6. Each CREATE TABLE statement includes a "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" clause. Delete all instances of that clause.

  7. Each CREATE TABLE statement includes the "ON [PRIMARY]" clause. Delete all instances of that clause.

The reason you need to apply these changes to the script is that SQL Azure currently doesn’t support all the features of the currently release of SQL Server 2008.  There are plans to start to incorporate some of the features that are in this outline, including the USE statement.

Hopefully this will make your life easier on your move to the cloud. Until then, Happy Coding!

Making data rain down from the Cloud with SQL Azure.

Hopefully you’ve had the chance to get a token [Check for a token here] to check out Microsoft’s Upcoming Cloud computing Platform Windows Azure. I’ll be posting some more information about Windows Azure in the coming weeks, but while i am creating a new website for Guelph Coffee and Code I thought I would bring you some information on connecting to SQL Azure in order to get your database up into the cloud.

Even though it is possible to connect to a database using SQL Server 2008, I’d suggest getting the Nov CTP for SQL Server 2008R2.  Just incase you don’t want to download a CTP here’s a hint as to how you can connect to your database with SQL Server 2008. The main reason I suggest SQL Server 2008 R2 is due to the fact that the Object Explorer of the current version of SQL Server is not compatible with SQL Azure.

Oh, I see you took my hint and downloaded R2, excellent choice. Now that you have your SQL Server Management Studio set up, lets take a look at some of the configurations that we will have to do in SQL Azure in order to connect to our database. [At the time of this blog post SQL Azure was available only to those who requested a token to access it. This post assumes you have access to SQL Azure.]  Here are the steps to take to get to the SQL Azure Cloud Configuration Console.

You should now be in an area that contains an interface much like this one:

Database_Console

As you see I’ve already Created a Database, it’s a very simple process where you define a name and a size [1GB or 10GB] and click on OK to create it.  In order to allow your Azure applications or to Manage your database using your SQL Server Management Studio you will have to allow connections through the Firewall, to change the firewall settings click on the ‘Firewall Settings’ tab.

Database_Firewall_AllowMSServices

In order for your Azure applications to be able to connect to your SQL Azure Database you will need to check the ‘Allow Microsoft Services access to this server’ checkbox. This adds the ‘MicrosoftServices’ Definition in the Rules grid.  If you don’t intend on using the SQL Azure database with a Windows Azure Application, keep the box unchecked.  To Allow you to access SQL Azure using SSMS you will have to Add a Rule.

Database_Firewall_AllowIPRange

The SQL Azure team did an excellent job making the Rules to be simple to configure.  Enter a name, which is a Label to Identify the IP Range you are allowing to connect.  Then specify an IP Range that you wish to allow access to this database.  You’ll notice that the application displays your IP Address, so if you only wish to add your IP simply copy and paste the IP address into both the Start and End textboxes. Then Click Submit to Save the changes. Repeat this step if you would like to be able to connect to your SQL Azure database from Multiple locations (Work, and Home).

One last piece of information you’ll need from the Azure Services site is the Connection String for your new Database. To get the Connection String, Navigate back to the Databases Tab. Select your new database from the list and click on the ‘Connection Strings’ button.

Database_ConnectionString

From here you can either select the entire Connection String and Copy using the Right Click context menu, or click on the ‘Copy to clipboard’ link (which may cause a Security pop-up in your browser) [Note: Choose the appropriate Connection String for your Application. I have only tested the ADO.NET Connection String in SSMS].  Paste your Connection String somewhere it is easy to work with, I prefer using ‘Sticky Notes’ a feature of Windows 7, but you can use notepad if you’re more inclined to do so.

If you haven’t done so Already, Fire up your installation for SQL Server 2008 R2 Management Studio. [The SQL Server instance in the cloud uses the TCP port 1433 so you will have to make sure that your Firewall will allow outbound traffic on TCP port 1433.]  As you normally would, Select Connect to Server from the Object Explorer.

SSMS_ConnectToServer

Fill out the Server name, Login and Password information [Note: The Server name, and login are contained in your Connection String that you pasted safely into Sticky Notes or Notepad.]  Your password you would have set while accessing the SQL Azure Menu for the first time. At this point it gets a little bit different than your typical connection to the database. If you were to connect now, you will only have access to the ‘master’ database which you do not have the ability to add tables to, not to mention you’ve created a nice new database to house your data for your current application. Here’s the trick to make sure you connect to the proper database on your SQL Azure account:

  • Click on Options at the bottom of the Connect to Server Screen.
  • Select the Additional Connection Parameter Tab
  • Paste in your trusty connection string [waiting idly by in Sticky Notes]
  • Change the password in the Connection String from myPassword to your actual SQL Azure password
  • Hit the Connect button to Connect to the database.

Now that we’re up and running lets add a few tables.  Being a .NET Developer myself I know that one of the existing .NET Features I’m going to want to leverage will be the ASP.NET Membership Provider. After giving the command-line utility a try, I found out that SQL Azure does not support a number of Commands that are contained in the Utility.  This includes use [cannot select other databases in the cloud], GRANT your login cannot GRANT or DENY privileges on the tables or stored procedures, nor is it able to Query against the master table.

No I wouldn’t bring this up just to let you down, here is a work around. Open the Visual Studio 20008 Command Prompt, and type the following:

aspnet_regsql.exe -A mpr -sqlexportonly <filePath>

-A: Application Services Options

  • P – Profiles
  • M – Membership
  • R – Role Manager

Then Obviously replace <filePath> with a Path where you can Save the SQL Output like “C:\SQL\ASP_NET_MemberShip_Tables.sql”.

This will allow you to run the SQL file against the database in the SSMS Query window. It will throw warnings about the invalid keywords but the tables and stored procedures get created regardless.

I wish you luck with your Deployments to Windows Azure, Stay Tuned for more blog posts on Developing on the Windows Azure Platform.