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.
The two gems that we needed for this to work are:
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.
Gem installation is standard, just add the gems to the Gemfile and run
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.
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.
[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
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")
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.