Vladimir Sarić wrote this on September 4, 2012

ActiveRecord and Azure

On our most recent Rails project we had the unexpected experience of using Microsoft’s SQL Server database on Azure, as it was already being run and populated by a separate team.

On this journey, we encountered some obstacles, but also learnt a lot. Here I’m going to give you a short overview of what needs to be done to get your app with MS SQL Server database up and running.

Gems

The two gems that we needed for this to work are:

• TinyTDS
• ActiveRecord SQL Server Adapter

TinyTDS

The TinyTDS gem is used for connecting, querying and iterating over results from the Microsoft SQL Server databases. It uses the FreeTDS’s DB-Library and needs to be present on the system for TinyTDS to compile against.

Even though we recently moved our CI service Semaphore away from Heroku, most of our client projects are still successfully hosted there. So, we decided to host this project there too.

Unfortunately, we found that there are a number of issues (altough they might be solved by now) with getting TinyTDS working on Heroku, primararly because of the need for FreeTDS library. One way to overcome this issue is to use TinyTDS’s rake-compiler and mini_portile to compile and package a native gem. We decided to go against this path and use a Linode box with Ubuntu Server 12.04 LTS instead. That way, all we needed to get FreeTDS on the box was to run:

sudo apt-get install freetds-dev freetds-bin tdsodbc

ActiveRecord SQL Server Adapter

The ActiveRecord SQL Server Adapter gem, as the name says, is an ActiveRecord adapter for the Microsoft SQL server. Unlike the TidyTDS gem, it requires no external library to compile against and it has no strict gem dependencies outside of ActiveRecord. The default connection mode is dblib which uses the TinyTDS gem.

Installation

Gem installation is standard, just add the gems to the Gemfile and run bundle install:

gem 'tiny_tds'
gem 'activerecord-sqlserver-adapter', '~> 3.1.0'

Setting up the database connection

As with other Rails apps, this is done in config/database.yml. Here’s an example:

production:
  adapter: sqlserver
  mode: dblib
  dataserver:
  host: mydb.database.windows.net
  port: 1433
  database: DATABASEPRODUCTION
  username: vladimir@mydb
  password: mypass
  timeout: 5000
  azure: true

development:
  adapter: sqlserver
  mode: dblib
  dataserver:
  host: mydb.database.windows.net
  port: 1433
  database: DATABASEDEVELOPMENT
  username: vladimir@mydb
  password: mypass
  timeout: 5000
  azure: true

test: &test
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

cucumber:
  <<: *test

As you can see, in the test environment you can still use the SQLite database, but you have to be careful with pure SQL queries (i.e. User.find_by_sql), since the syntax differs between the SQL Server and SQLite.

Possible Issues

In the intro I mentioned that we encountered some obstacles, here I’ll list some and the steps we took to overcome them.

Migrations and Clustered Index

Just to be clear, you can generate and run migrations like on any other Rails app. But if you are also in a situation where the DB is being run by a separate team, you might want to co-ordinate with them on this.

As written in the ActiveRecord SQL Server Adapter wiki all the tables need to have a clustered index:

All tables need a clustered index. This means that if you use :id => false in your schema statements for things like join tables you will get a “Tables without a clustered index are not supported…” message when inserting data. The solution is easy, just add a :primary_key field of some type, since they are automatically clustered. Or you can create your own clustered index to match your own needs.

The [schema_migrations] table also requires an index and it isn’t added automatically by Rails. We solved this by creating and running the following migration:

class PrepareSchemaMigrations < ActiveRecord::Migration
  def up
    execute <<-SQL
      CREATE CLUSTERED INDEX [idx_schema_migrations_version] ON [schema_migrations] ([version])
    SQL
  end

  def down
  end
end

Models and table names

ActiveRecord has a convention for naming classes, tables and fields. With that It also expects applications to follow certain naming conventions. By default classes (models) use the singular form, tables use the plural form, primary keys are stored in the id attribute and foreign keys are stored in the table_id attribute.

If these, for some reason, differ in your database, you can specify them in the model:

class User < ActiveRecord::Base

  self.table_name = "User"
  self.primary_key = :id

  has_many :books, :foreign_key => :userId

end

Attribute names

This is more of a tip then an actual issue. If your database has a column, whose name is, for example, CamelCased, instead of Rails’ standard snake_cased, don’t worry. The following statement, even though it looks a bit strange to what we are used to in the Rails world, will work just fine:

user.update_attributes(:FirstName => "Vladimir", :LastName => "Saric")

Conclusion

All in all, once the basic setup is done and you get passed some issues, working with the MS SQL Server database is perfectly acceptable.