Tuesday, June 3, 2008

Connect to Microsoft SQL Server from Rails ActiveRecord -- a Moving Target?

Some time ago, I approached my boss regarding doing some internal R&D work using Ruby on Rails. I gave a demo.

It was received well, except when he found out that the preferred deployment system for Rails applications is not Windows, and that the preferred database backend is not Microsoft's SQL Server.

He recommended that I look into at least getting Rails (ActiveRecord, specifically) to talk to Microsoft SQL Server. I said that I would, but inside I cringed. From the research I had done then, it was clear to me that Microsoft SQL Server support was not considered a first-class citizen within portions of the Rails community.

Some other stuff came along, and the whole thing got derailed (no pun intended).

Fast forward to May (last Thursday/Friday to be exact), and I find myself returning to the topic of getting ActiveRecord to talk to Microsoft SQL Server. I found that the Wiki entry titled "HowtoConnectToMicrosoftSQLServer" had been updated for Rails 2.x. Nice.

So I followed the instructions and got FreeTDS 0.82 installed on my Mac via MacPorts. I even found instructions on how to get Mac OS X's ODBC Administrator to talk to FreeTDS, as well as installed the activerecord-sqlserver-adapter gem. When I wrapped up my work week, all that was left undone was to manually install the lib/dbd/ADO.rb file from the Ruby-DBI project.

Today, I found out that Rails 2.1 had been released over the weekend, containing some cool enhancements. But, to my dismay, the activerecord-sqlserver-adapter gem has disappeared from Hmm...

A bit more sleuthing revealed to me the existence of activerecord-odbc-adapter gem, which offers support for SQL Server. I guess I will have to shift gears and pursue it from this angle. Instead of FreeTDS, I will need to install the rb-odbc port. Thank goodness for MacPorts!

So, as of Rails 2.1, SQL Server connectivity is still second-class, and this exercise has been frustrating.

Is it supposed to be this hard?


YodaTravis said...

I am experiencing this problem right now. I have a greenfield project starting in the next week or so and I'm researching the best tools for the job. I know Ruby and Active Record would make this project go much smoother (my alternatives are c# or java) but if I can't even get Active Record working with our MS SQL backend then I'll be forced down another path. I would encourage anyone who has gotten Ruby/ActiveRecord/SQLServer working lately in a windows environment (This is for work and we are a windows shop.) to post examples here. Thanks in advance.

YodaTravis said...

btw, I found this site helpful. It explains the difference between the ADO and ODBC versions of the ActiveRecord/Ruby connection and also talks about the differences that exist between getting SQLServer access in a Windows versus Linux environment

Anonymous said...

I know this article is old news but I was able to install activerecord-sqlserver-adapter after running:

gem sources -a

YodaTravis said...

This is the new definitive way to setup Rails for Windows / sql server