Ruby on Rails on Windows Azure with SQL Azure

Article
02/26/2010

I was recently talking to a customer about the possibility of moving a web site from Linux to Windows Azure. The hosting costs of the application are not excessive, and the customer is happy with the service received. Nevertheless they were very interested in exploring the hosting costs and potential future benefits of the Windows Azure platform.

The web site was developed using Ruby on Rails®, an open-source web framework with a reputation for "programmer happiness and sustainable productivity". The site also makes use of MySQL and memcached.

Most of the necessary jigsaw pieces are already in place:

Tushar Shanbhag talked about running MySQL on Windows Azure at the PDC last October.
Simon Davies blogged about getting up and running with Ruby on Rails on Windows Azure, and released a Sample Cloud Project.
Dominic Green recently discussed memcached on this blog.

We calculated that we could make savings in the hosting costs simply by moving to Azure. However, it soon became apparent that if we could replace MySQL with SQL Azure, then this would provide a number of additional benefits:

Further reduce hosting costs. MySQL would require a worker role, hosted on its own dedicated node. This would cost around $1200/year whereas hosting the 400Mb database on SQL Azure would cost $9.99 per month.
Reduced complexity and management. The MySQL accelerator shows how to deploy in a worker role, and includes sample code for managing and backing up the database. However this is all custom code which would increase the total cost of ownership of the solution. Using SQL Azure would greatly simplify the architecture.
Business Intelligence. The application currently has a custom Business Intelligence module, developed using Flash. Moving to SQL Azure would allow the customer to take advantage of roadmap features to provide clients with a much more sophisticated Business Intelligence module, while again reducing total cost of ownership.

The only missing piece in this jigsaw is the connection of a Ruby on Rails application to SQL Azure.

Ruby on Rails already works with SQL Server 2000, 2005 and 2008, while SQL Azure is a subset of SQL Server 2008 functionality. The adapter is available on GitHub. I decided to test this out at the weekend. Luckily I found this old article. After a certain amount of ‘fiddling and hacking’, I managed to get it working:

See the Application on Windows Azure

Actually, was incredibly simple. The finished application required almost no code amendments, and I only encountered one problem that would definitely need to be resolved before considering a production deployment.

My development environment is Windows 7 Enterprise, Visual Studio 2010 Beta 2 and Windows Azure November SDK, but other than the SDK this should work with most versions of our tools. It is not my intention to provide a step by step list of instructions on how to do this, but the outline process is described in the following sections:

Develop a Working Ruby on Rails Database Application

It is very simple to set up a Rails development environment and create a basic database application using SQL Server. This should take no longer than 10 minutes!

1. Get up and running with Ruby on Rails.

2. Create a basic Rails database application. I created a blog.

3. Test the application by browsing to https://localhost:3000.

4. Install the SQL Server Adapter for Rails:

gem install activerecord-sqlserver-adapter --source=https://gems.rubyonrails.org

5. Test against your local SQL Server, with ODBC. You will need to edit the database configuration for your application. This is defined in <myapp>\config\database.yaml. For SQL Server authentication this would be:

 development:

 adapter: sqlserver

  mode: ODBC

  dsn: Driver={SQL Server};

        Server=<your_server>;

      Database=<your_database>;

      Uid=<your_uid>;

        Pwd=<your_pwd>;

6. Restart your web server and test by browsing to https://localhost:3000 again. This should exhibit the same behaviour as before, but create your database in SQL Server.

I originally got this working with ADO and the SQLNCLI driver on the development fabric, but encountered problems on Windows Azure. I found this article and without investigating closely decided to switch to ODBC. ADO requires an additional Ruby component as described here.

Develop a Cloud Solution

Simon Davies has already created a skeleton solution for deploying a Ruby on Rails application to Windows Azure. The solution was developed with Visual Studio 2008, but it converted to Visual Studio 2010 without a problem. Download and unzip Simon's solution.

Windows paths are limited to 260 characters, which is why the project names are so short in the solution. It would be sensible to unzip the solution to the root of a drive. The Windows Azure Development Fabric also tends to generate very long paths. Jim Nakashima discusses this in more detail here.

Copy ruby\bin \and ruby\lib from your Ruby installation into the Ruby directory in the solution. Then copy your rails project directory into the main RR directory. My rails project directory is called 'blog'.

Open the project in Visual Studio. There are a large number of files in the ruby\lib directory, so if Visual Studio is slow to respond, open the .csproj file in a text editor and remove all the specific project references to individual files. This could be specific to VS2010 Beta 2. Simon also suggested that

You can then include the following in the file to ensure that the directory contents are treated as 'content-copy if newer':

 <Content Include="Ruby\**\*.*">

  <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>

</Content>

<Content Include="blog\**\*.*">

  <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>

</Content>

You then need to make some changes to the configuration setting in the default solution. Most of these settings are contained within two files that are part of the cloud service project:

Within ServiceConfiguration.csdef you specify the port that the application will listen on:

 <Endpoints>

 <InputEndpoint name="Server" port="80" protocol="tcp" />

</Endpoints>

You can set this to port 8080 to simplify local testing.

Within Service Configuration.cscfg you can make a number of other settings:

 <?xml version="1.0"?>

<ServiceConfiguration serviceName="RW"

                      xmlns="https://schemas.microsoft.com/ServiceHosting/2008/10/ServiceConfiguration">

  <Role name="RR">

    <Instances count="1" />

    <ConfigurationSettings>

      <Setting name="DiagnosticsConnectionString" value="UseDevelopmentStorage=true" />

      <Setting name="StorageAccount" value="UseDevelopmentStorage=true" />

      <Setting name="RubyFolder" value="Ruby" />

      <Setting name="AppFolder" value="blog" />

    </ConfigurationSettings>

    <Certificates/>

  </Role>

</ServiceConfiguration>

“Instances” specifies the number of instances of this role to start. This is currently set to 1. Increasing this to 2 will double your hosting costs, but load will be automatically distributed across your roles by the Windows Azure Load Balancer.

The DiagnosticsConnectionString and StorageAccount entries specify that the application will use the development fabric. The values for RubyFolder and AppFolder should be set to the folders into which you copied the ruby bin and lib directories, and your Ruby on Rails application directories respectively.

You should now be able to run your application, simply by pressing F5. This will build and pack your application, and launch the development fabric. It may take a few seconds to start all the relevant processes, but eventually you will see an instance of mongrel (or WEBrick) running in the development fabric:

You can now test your application by navigating to https://localhost:8080. Mine looks like this:

Converting to SQL Azure

Cloud Applications are incredibly flexible: you can host your database locally, with your log files in Windows Azure or any number of other combinations. This means that we can convert just the database portion of the application in SQL Azure - and it is remarkably simple to do.

If you don’t have one, you'll need to create a SQL Azure database. There is a lot of help and general information available on how to do this: my Azure Developer Portal looks like this:

You will need to allow access to your server from 'Microsoft Services' (this will eventually be used to connect from Windows Azure), and also from your local machine for testing.

Then change your configuration to point at SQL Azure. This is done by amending database.yaml as indicated below:

 development:

 adapter: sqlserver

  mode: ODBC

  dsn: Driver={SQL Server};

        Server=...

In this case you specify:

The SQL Azure Server - <your_server>.database.windows.net
The database name - in this case: ruby
Your SQL Azure user name - <Uid>@<Your_Server>
Your SQL Azure password - whatever you specified when establishing the service

Run your application. At this point it will fail.

This is because SQL Azure is a distinct version of SQL Server and the SQL Server Adapter detects the version and compares it to the supported version list (2000, 2005, 2008). In order to get around this, I amended <your_solution>\RR\Ruby\lib\ruby\gems\1.8\gems\activerecord-sqlserver-adapter-2.3.1\lib\active_record\sqlserver_adapter.rb. I added the following code to the database_version function:

 if @database_version["Azure"] == "Azure"

@database_version = "Microsoft SQL Server 2008 (SP1)

 - 10.0.2531.0 (X64) Mar 29 2009 10:11:52

Copyright (c) 1988-2008 Microsoft Corporation 

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> 

(Build 7600: ) " 

end

This simply detects whether the current database contains the string "Azure". If it does, I switch in the string that describes SQL 2008. This is clearly not suitable for a production deployment, but for proof of concept it is fine.

Next, create your SQL Azure database. This is done through the Azure Developer Portal. It cannot be done in any other way. (I then connected to my local SQL Server database; scripted out the tables and created the tables in the new database using SQL Server Management Studio 2008 R2). (Install SQL Server 2008 R2 client tools: older versions will not work). I believe Rails may create the required tables automatically.

Then, when you run the application again, you should be running from the development fabric, while connecting to SQL Azure.

Note: If you get specific network connection errors, this is because SQL Azure reserves the right to close or refuse connections to maintain Quality of Service across the service as a whole (you are just one client). The errors will be displayed by Ruby on Rails as follows:

If you get this error, simply refresh the page or use the back button and retry.

This is important, because if you would like to use this set-up in production, you will need to amend the SQL Server Adapter to be fault tolerant - and to retry on encountering failure. This is a common design pattern for cloud applications. (If running in production, it would also make sense to run against a local SQL Server instance and study the query execution plans produced by the adapter - it is likely that some tuning would pay dividends).

Once your application is working, it can then be deployed to Windows Azure.

Deployment to Windows Azure

Eventually, I had to make a small change to Simon's solution. It (quite rightly) tries to access the web server, and if it cannot, the worker role is automatically recycled. While this is a very good thing, the database errors described above mean that the cloud application is perpetually recycled. To save time, make this change in WorkerRole.cs now:

  //start the process and check to make sure that its running every 60 seconds

            while (true)

                if (!ProcessIsRunning())

                    LogError("Process is not running");

                    StartProcess();

                // Check to make sure that the server can serve a request

                // nick - suppress this.  SQL Azure connection loss causes role recycle if it happens here....

                //if (!GetWebPage())

//{

                //    LogError("Web Server returned too many errors - requesting recycle");

                //    RoleEnvironment.RequestRecycle();

//}

                Thread.Sleep(60000);

Obviously, this could be done in a different way, or the code could be re-instated once the suggested changes to the SQL Server Adapter are implemented.

I will assume you have appropriate services set up to host the application on Windows Azure. You will need both a Storage Account and a hosted service:

Then it is a simple matter of updating your configuration settings for deployment to the Azure platform. All of these settings are stored in Service Configuration.cscfg you can make a number of other settings:

 <?xml version="1.0"?>

<ServiceConfiguration serviceName="RW"

                      xmlns="https://schemas.microsoft.com/ServiceHosting/2008/10/ServiceConfiguration">

  <Role name="RR">

    <Instances count="1" />

    <ConfigurationSettings>

      <!--

        <Setting name="DiagnosticsConnectionString"

                 value="UseDevelopmentStorage=true" />

        <Setting name="StorageAccount"

                 value="UseDevelopmentStorage=true" />

-->

      <Setting name="DiagnosticsConnectionString"

               value="DefaultEndpointsProtocol=https;AccountName=YOURACCOUNT;AccountKey=YOURKEY" />

      <Setting name ="StorageAccount"

               value="DefaultEndpointsProtocol=http;AccountName=YOURACCOUNT;AccountKey=YOURKEY" />

      <Setting name="RubyFolder" value="Ruby" />

      <Setting name="AppFolder" value="blog" />

    </ConfigurationSettings>

    <Certificates/>

  </Role>

Run the application again. Navigate to https://localhost:8080. The application should still work. Now it is running in your Development Fabric, but with all storage and databases in Windows Azure.

If you changed your application to use port 8080 above, then don’t forget to set it back to port 80 now:

 <Endpoints>

    <InputEndpoint name="Server" port="80" protocol="tcp" />

</Endpoints

Select the Cloud Service project in the solution in Visual Studio, right-click and select publish. When publication is complete an Explorer window will open automatically, and the Windows Azure Developer portal will open in the browser. In the publish directory there will be two files:

These are the files that you will use to deploy your application to Windows Azure. While you can upload them directly to the developer portal, RW.cspkg is large - it contains the Ruby binaries and library. (You could reduce this by finding files that are not needed and setting to 'Do Not Copy' in Visual Studio).

Such a large file is best handled by a recoverable upload in case of network failures. There are a number of tools emerging that enable you to manage your Azure blob storage from your development machine. I used one of these tools to upload the files to blob storage:

I then deployed my application:

Then, after some interesting graphics and pretty coloured lights, your application is available on Azure:

Click on the Web Site URL to test your application on Windows Azure. https://rubysqlazure.cloudapp.net.

Summary

I am surprised at how easy it is to get a Ruby on Rails application running on Windows Azure and using SQL Azure for database storage. It is a strong example of Windows Azure Interoperability. It is certainly a cleaner solution than hosting database binaries in a worker process.

Of course, for a production deployment some work would be required - but the changes that would be needed would be localised to the SQL Server Adapter - a single ruby script.

Having this option available for deploying Ruby on Rails applications to Windows Azure has the potential to significantly reduce the total cost of ownership, and to use the elasticity and availability inherent in the Azure platform to significantly increase the quality of service of the applications at the same time. This would be invaluable for applications that are currently failing to meet their availability targets or those that have very high peak load variations.

Additional roles can be added as and when required simply by updating the <Instances count="1" /> in configuration, and this can be done dynamically on the Windows Azure Development Portal - without redeploying the application.

Written by Nick Hill

Comments

Anonymous
August 29, 2010
Hi there Nick, This is a wonderful article and I'm trying to follow and get a rails app running against SQL Azure. I've got my rails app running against sqlite. I'm running ruby 1.8.7 with the following gems installed: -- gem install rails --version 2.3.8 gem install activerecord-sqlserver-adapter --version 2.3.8 -- Running gem install activerecord-sqlserver-adapter --source=http://gems.rubyonrails.org gave me an error: ERROR: Could not find a valid gem 'activerecord-sqlserver-adapter' (>= 0) in any repository Once rails and the sqlserver active record gem was installed, I updated my database.yml and started up my server but got the following error: Please install the sqlserver adapter: gem install activerecord-sqlserver-adapter (no such file to load -- active_record/connection_adapters/sqlserver_adapter Can you please help? Hani
Anonymous
August 31, 2010
Hi there Nick, I tried the above methods and received a "no such file to load -- odbc". I've instead tried with IronRuby. Using iron ruby to connect to a local instance works fine but does not work against SQL Azure. Any ideas? Here's my database.yml development: mode: ADONET adapter: sqlserver host: nrvk7uv6tl.database.windows.net database: mydb username: myuser password: mypassword Ameer Deen
Anonymous
September 01, 2010
Hi Hani, Thanks for your comment. It is a while ago since I did this but I seem to recall that certain versions of ruby behaved diffferently when installing GEMS. I was using ruby 1.8 with active record 2.2.2 and the SQL Adapter 2.3.1. Have you read this article? www.ruby-forum.com/.../134660. I am quite busy at present but will try to take a look over the weekend. Nick
Anonymous
September 16, 2010
Hi, It takes long time to include Ruby Folder into the solution and package file become to much huge. Are there any way to solve these problems?
Anonymous
October 02, 2010
Ameer, did you make the appropriate firewall settings on SQL Azure?
Anonymous
October 12, 2010
Why not host it on Heroku? Are there any benefits on Azure over Heroku?

Share via

Ruby on Rails on Windows Azure with SQL Azure

Comments

Additional resources