Rails 3 and SqlServer 2008

2011-03-08 · sqlserver, rails, ruby, freetds

For the next project at work I have to interface Microsoft SqlServer 2008 from my rails applications. The setup is quite simple. First we need unixODBC. Normally Mac OS X ships with iODBC, but since I use linux servers, I wanted to use the layer that's going to be used on my production systems aswell.

Lets start!

brew install unixodbc

Next, we need FreeTDS, which is a free implementation of the Tabular Data Stream protocol.

brew edit freetds

Add --with-unixodbc=/usr/local to the ./configure line. Then:

brew install freetds

Then I created ~/.freetds.conf and added:

[development_db]
host = <Database IP>
port = 1433
tds version = 8.0
client charset = UTF-8

Edited /usr/local/etc/odbcinst.ini and added:

[FreeTDS]
Description = TD Driver (MSSQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
FileUsage = 1

And ~/.odbc.ini

[development_db]
Driver= /usr/local/lib/libtdsodbc.so
Servername=development_db
Server=<Database IP>
Port=1433
Database=<database>
TDS_Version=8.0

To use the odbc connection, I just needed to install two ruby gems. Just add the following to your Gemfile.

gem "activerecord-sqlserver-adapter"
gem "ruby-odbc"

I ran bundle install and configured my config/database.yml

adapter: sqlserver
mode: odbc
username: <login>
password: <password>
database: <database>
dsn:  development_db