Making The Leap

Recently I had to finally bite the bullet and get my Linux VM’s talking with MS SqlServer. After much trial and error and umpteen tutorial sites, I have finally figured out how to do this…..and the amazing thing is that is dead simple.

First off, all of this was executed as root:

yum install freetds-devel unixODBC-devel

This will install not only these packages, but the freetds and unixODBC ones as well and install the libraries ‘libtdsS.so’ and ‘libtdsodbc.so’ in the /usr/lib folder

vim /etc/freetds.conf

- in the global section change “tds version” to 8.0

vim /etc/odbcinst.ini

—add an entry for FreeTDS

[FreeTDS]
Description     = ODBC for SqlServer
Driver          = /usr/lib/libtdsodbc.so
Setup           = /usr/lib/libtdsS.so
FileUsage       = 1

once this has been done, you should be able to connection up to your SqlServer machine

tsql -S <server-ip> -U <username> -P <password>

you will now be in a shell and can test is by doing this

use <actual database name>
go
select * from <actual table name>
go

Normally, at this point, I install Ruby Enterprise Edition. I use this as my only ruby installation on the machine. I won’t go into how to install it because http://www.rubyenterpriseedition.com/ has great documentation already.

Next, download ruby-odbc from http://www.ch-werner.de/rubyodbc/

Once downloaded, do the following

tar zxvf ruby-odbc-0.xxxx #replace the xxxx with the current version
cd ruby-odbc-0.xxxx 
ruby extconf.rb
make
make install

Next, grab the gems you’ll need

gem install dbi
gem install dbd-odbc
gem install rails-sqlserver-2000-2005-adapter -s http://gems.github.com

That’s it! At this point you can make a new rails app and modify the database.yml file to look similar to this:

development:
  adapter: sqlserver
  mode: odbc
  dsn: Driver=FreeTDS;Server=10.x.x.x;Database=dbBoring;Uid=sa;Pwd=password1

There is an option to edit the file /etc/odbc.ini and define your dsn’s there, but that didn’t feel too dry to me so I define them only in the database.yml file.