Active Record is an object-relational mapping (ORM) that makes it easy to work with databases. While there are other ORMs out there for Ruby, Active Record is very popular and I wanted to walk through using it with SQL Azure. Active Record can be installed by running gem install activerecord, however most people probably install it as part of the Rails installation. The examples in this post use Rails, so gem install rails is the command I used to install Rails, Active Record, and all the other bits that Rails comes with.

Next we need an adapter that Active Record can use to talk to SQL Azure. I’m using the SQL Server Adapter (gem install activerecord-sqlserver-adapter,) though I’m told that if you’re using JRuby that the activerecord-jdbc-adapter works as well.

The Active Record SQL Server adapter can connect to SQL Azure using either ODBC (through the Ruby-ODBC gem,) or dblib (through the TinyTDS gem.) While both allow connectivity, I’ll be using ODBC since the TinyTDS gem currently requires a manual build process to enable SQl Azure support. If you’re interested in using TinyTDS, I documented my experience building it with SQL Azure support at http://social.technet.microsoft.com/wiki/contents/articles/connecting-to-sql-azure-from-ruby-applications.aspx#tinytds. To install the Ruby-ODBC gem, use gem install ruby-odbc.

Now we just need to provision a new SQL Azure server, create a database, create a Rails application, and modify it to use our SQL Azure database.

Provisioning a SQL Azure Database Server

Perform the following steps to provision a new SQL Azure server:

1. In your browser, login to http://windows.azure.com and select New Database Server from the Common Tasks section of the ribbon.
   
2. In the left pane, select your subscription and then click the Create button in the Server section of the ribbon.
   
3. In the Create a New Server dialog, select a region and then click next.
4. Enter an administrator login and password, and then click next.
5. Click the Add button and enter the IP address of the machine you will be running the Ruby code on. Finally, click finish.

At this point you will have a new server with a gibberish name like fvwerou3.database.windows.net. You can use the Test Connectivity button on the ribbon at the top of your browser to test whether you can connect to the master database for this server. 

Create a database

Active Record can’t automatically create a database on the SQL Azure server that we’ve just provisioned, so we have to do this manually. To create a new database, select the Create button from the Database section of the ribbon.

Enter a new name and click OK. The defaults of Web edition and a size of 1gb should be sufficient for testing.

Create a Rails application

For a test application, I created a simple blog using the following commands at the command line:

rails new blog
cd blog
rails generate scaffold Post title:string body:text

To configure this application to use SQL Azure, perform the following steps:

1. Edit the Gemfile and comment out the gem ‘sqlite3’ entry. Add the following entries:

   gem ‘activerecord-sqlserver-adapter’
   gem ‘ruby-odbc’

2. Save the Gemfile and run bundle install to ensure that the new gems we added are installed.
3. Next, open the database.yml file from the blog/config directory and replace the existing development section with the following:

   development:
    adapter: sqlserver
    mode: ODBC
    dsn: Driver={SQL Server};Server=servername.database.windows.net;Uid=user@servername.database.windows.net;Pwd=password;Database=databasename

4. Replace the servername, username, password, and databasename with the values for your SQL Azure Server, database administrator login, password, and database name.
5. Save the database.yml file and run rake db:migrate to create the database structure.

At this point you willll receive an error staging that ‘Tables without a clustered index are not supported in this version of SQL Server’. To fix this error, go back to the Windows Azure portal in your web browser and perform the following steps:

1. Select your database and then click the Manage icon in the Database section of the ribbon.
2. When prompted to login, ensure that the Database name is correct and then enter the database administrator username and password. Click Log on to proceed.
3. Select the New Query icon from the ribbon, and enter the following statement in the query window:

   CREATE CLUSTERED INDEX [idx_schema_migrations_version] ON [schema_migrations] ([version])

4. Click the Execute icon on the ribbon to run this statement. Once this has completed, issue the rake db:migrate command again and it will succeed.

Run the Application

You can now start the blog web site by running rails s. Navigate to http://localhost:3000/posts to create and view posts, which will be stored into SQL Azure. If you return to the browser window where we added the clustered index, you should be able to select the ‘posts’ table and view the table structure, data stored in it, etc. 

NOTE

In writing this blog post I’ve noticed that something has changed between my the Rail 3.1RC code my work environment is using vs. the Rails 3.1 release code I installed on my test box. The RC4 code works fine with SQL Azure, but with the 3.1 release I receive the following error:

DBCC command ‘useroptions’ is not supported in this version of SQL Server

This is accurate; useroptions isn’t supported by SQL Azure. So if you're using a newer version of Rails and things are failing, you're not alone.  I'll investigate and see if I can find a resolution to this error and post an update here.