stuart's posterous

I have a few battle scars from working with MSSQL via *nix/OSX, it always seems to take 17.8x longer than you plan for it to take just to install everything and getting it working. And despite installing and configuring this set up about 5 times, I end up hitting some wall of some sort. So in an effort to lower my blood pressure I’m going to give a quick run down on what has worked for me.

Many many thanks go out to all the hundreds of people who have lost sleep over this process before me (PHP, Phython & Ruby coders alike). From their bitchin' and stupid questions, I wouldn’t have even got it configured. So in memory of their tortured souls:

First install Homebrew

Homebrew’s standard install does not install with pointers to unixODBC, FreeTDS automatically uses the first one it finds, and in this case (OSX) will be iODBC. Normally this wouldn’t be an issue but the version of iODBC that comes with OSX needs to be updated in order to play nicely with FreeTDS.

I will be investigating using an update version of iODBC, but have been using UnixODBC for the sake of being like my production environment.

So first lets modify Homebrew’s FreeTDS brew to add a flag if unixODBC has been installed.

Go into the terminal and enter:

brew edit freetds

Copy and paste the code belong over the current contents def install method.

args = ["--prefix=#{prefix}",
        "--with-tdsver=7.0",
        "--enable-msdblib",
        "--mandir=#{man}"]

args << "--with-unixodbc=/usr/local" if Formula.factory('unixodbc').installed?

system "./configure", *args
system 'make'
system 'make install'

Once you’ve saved the changes we can install all the components

brew install unixodbc freetds ruby-odbc

Remember, unixODBC must be installed before FreeTDS as the ./configure will not be done with the unixODBC path and will compile with iODBC libs instead.

To test that this works, you can enter tsql -C into the terminal and you can see how FreeTDS was compiled. At the bottom you should see unixODBC: yes.

Another test that should be done is that FreeTDS can actually talk to your server. Again using the tsql tool attempt to connect.

tsql -S [ip_address] -U [user_name] -P [password] -D [database]

Without getting all schmancy, I usually test with something simple like SELECT 1 and then entering GO on the next line.

Ok the easy part is done. Now lets configure the DSN.

At the time of writing I couldn’t get a DSN-less connection working which from a Rails development perspective and for the sake of application portability I prefer that approach. This however works fine on my Debian dev & production environments I will be investigating further in a quest to get this to work but for now the methods below worked without a hitch.

Open up /usr/local/etc/freetds.conf in your favouritist text editor (mine being TextMate)

[SQLSERVERNAME]
 host = IPADDRESS
 port = 1433
 tds version = 7.0

Obviously changing the “SQLSERVERNAME” to something you like, lower case usually (not sure it matters).

Next open up /usr/local/etc/odbc.ini

[DSNNAME]
Driver = /usr/local/lib/libtdsodbc.so
Server = IPADDRESS
Database = DATABASENAME
client charset = UTF-8  #needed only on osx

DSNNAME is the name that you will use in your database.yml so make it something nice.

Open up your database.yml file and use this an example for your environments:

development:
  adapter: sqlserver
  mode: odbc
  dsn: DSNNAME
  username: USERNAME
  password: PASSWORD

Now last but not least. You gonna need a couple of gems to be installed for Rails to talk to ODBC and also wrap activerecords interpretations into MSSQL friendly commands.

I use bundler, if you’re not using it, you’re doing it wrong!

Add these gems to your Gemfile:

gem 'dbd-odbc', :require => false
gem 'activerecord-sqlserver-adapter', :require => false

It seems DBI is not longer required with newer versions of activerecord-sqlserver-adapter. Which is nice.

And thats it. Do your bundle install and rock and roll.