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!

Synchronizing a Local Database with the Cloud using SQL Azure Sync Framework

I’ve been looking at different aspects of Windows Azure over the past few months, and have been having a lot of fun getting a grip on Cloud Computing and Microsoft’s Cloud Computing Platform. One of my past posts gives a step-by-step tutorial on how to connect to SQL Azure using Microsoft SQL Server 2008 R2 (November CTP). If you haven’t already done so I would suggest reading Making Data Rain Down from the Cloud with SQL Azure to follow along with this tutorial.

I received an email a few days ago from Apress Books suggesting me the book to the left due to my previous purchase of Introducing Windows Azure.  Both books cover the Windows Azure Storage Services API, and SQL Azure. The Introducing Windows Azure book covers Azure .NET Services, which was renamed to the AppFabric Services which is covered in Windows Azure Platform. This name change was announced during the first keynote [View Keynote] of PDC 2009. Along with Codename “Dallas” [Data as a Service], Microsoft Pinpoint Marketplace.

Codename Dallas is built on top of Windows Azure and SQL Azure. Dallas will supply developers with Data as a Service, as part of the Open Data Movement in the United States. If this doesn’t get you excited about Microsoft’s Cloud Computing Platform, I’m not sure what will.  To ensure that you can develop on the cloud and use the SQL Azure Database Platform to your advantage read forward as I’ll be showing you how to Migrate a database to the cloud with SQL Azure Data Sync, which is an extension of the Microsoft Sync Framework.

 

To begin you will have to download the Microsoft Sync Framework 2.0 Software Development Kit (SDK) and the Microsoft Sync Framework Power Pack for SQL Azure November CTP (32-bit) and if you don’t already have a SQL Azure Account you can get one here at the SQL Azure Data Sync Developer Quick Start page.

Once you have your SQL Azure Account, you will need to provision a new database in the cloud and change the firewall settings so the local computer can connect to and modify the database.  These steps are provided in Making Data Rain Down from the Cloud with SQL Azure. Now that we’ve got our SQL Azure firewall database set up we’re ready to sync a local database into the cloud.

When you first fire up SQL Azure Data Sync you will see this Introduction screen. [Hopefully future releases of this tool will allow you to check a box to skip this screen, like other SQL Server Tools.]

SQL-Azure-Data-Sync-Intro

Hitting next on the Introduction screen will take you to the SQL Azure configuration screen where you will need to enter the details for your SQL Azure Account. For this step you will need to login to your SQL Azure account to figure out what server your database server is hosted on. Fill out the database information required, like you see below.

SQL-Azure-Data-Sync-Setup-AzureDB

A good thing to note here is that this database should not be created in the cloud already, the data sync tool will be creating the database from the local instance you will be setting the details up for in the next step. So lets click next and enter the information for our local Database.

SQL-Azure-Data-Sync-Setup-LocalDB

Once you have both databases configured [and tested] you’re ready to select the tables you wish to synchronize or create in the cloud database.

SQL-Azure-Data-Sync-Tables

Due to the fact Relational Databases have Foreign Keys to link data from one table to the next you may need to synchronize the database in a particular order to maintain data integrity. If you have data in your tables and your tables are related to one another be sure to place the Primary Key table, before the Foreign Key table. Here is the screen that will allow you to order your tables in order they should be synchronized.

SQL-Azure-Data-Sync-Tables-Order

After you have reorganized the order in which your tables are to be synchronized, you will be shuffled off to a Summary page. This page outlines the Database Server, and asks if it is to create a 1GB Database, or a 10GB Database in the cloud.

SQL-Azure-Data-Sync-Summary

After Reviewing the information and selecting the database size that should be created. Click on the Process button to Copy the local Microsoft SQL Server database to SQL Azure.

SQL-Azure-Data-Sync-Process

SQL-Azure-Data-Sync-Progress

Once this process is complete the Microsoft SQL Server has been created on the specified SQL Azure Database server. The Synchronization is carried out by SQL Server Agent, be sure to copy down the Job name from the Finish Setup screen.

SQL-Azure-Data-Sync-Results

You may need to Configure SQL Server Agent before the first time the Synchronization is run.

SQL Azure Data Sync is a great way to deploy your current databases into the cloud. You can use the cloud as a method of backing up your current infrastructure or you can start expanding into the cloud as a more economical solution of expanding your current data centers. As we count down to the New Year it seems fitting to think about moving into the future of Technology. Happy New Years!

Setting up the Development Storage Service

If you are setting up your Development Environment for Windows Azure and want to avoid installing SQL Server Express. I didn’t want to use SQL Server Express because I’ve already installed SQL Server 2008 R2 CTP so I can interact with my SQL Azure Database in the Cloud.

From the command line, Navigate to the Windows Azure SDK directory.

cd "C:\Program Files\Windows Azure SDK\v1.0\bin\devstore"

Then you will Launch the Development Storage Initialization Tool [DSInit.exe]. You will need to set the SqlInstance [/sqlinstance:] that you wish to create the tables on, and you will need to force [/forceCreate] the tool to create the tables.

DSInit.exe /sqlinstance:<YourDatabaseName>/forceCreate

This will launch the Initialization Tool.

Development Storage Initialization
If you receive the error message below you it is most likely because the instance name was improperly set. Ensure to be use only the InstanceName do not include periods or slashes.

Development Storage Initialization Error

After your Development Storage is set up make sure you  go into your Development Fabric and Start the Development Storage Service.

Happy Coding!