Platform Installation

Headings that have a tested after it have been confirmed to pass all the adapters test suite. If you have found that a method that is marked as untested does pass all the tests, please let us know and/or update the wiki.

Mac OS X

MacPorts 1.8/1.9 [tested]

The method for getting the SQL Server stack on Mac OS X is pretty much the same no matter which OS version you have. This article covers some of the finer points of Snow Leopard and x86_64 compatibility but can be applied to pretty much any version that MacPorts targets.

The Ultimate OS X Snow Leopard Stack For Rails Development:
x86_64, MacPorts, Ruby 1.8/1.9, SQL Server & More

MacPorts Base w/REE (Ruby Enterprise Edition) [tested]

Recently, I have built on the article above and written another on how to use Ruby Enterprise Edition with the SQL Server stack. The title is Installing REE With The Snow Leopard SQL Server Stack

Mac’s Native w/Custom unixODBC [untested]

Although untested and not for the faint of heart, if you want to use the Mac’s installed ruby with a custom version of unixODBC (perhaps installed into /usr/local) then Jacob Riff has written up his process for getting the stack up in Snow Leopard under this scenario. This also avoids the segfaults that iODBC causes.

Using ruby-odbc with unixODBC on Snow Leopard

Mac’s Native w/iODBC [tested]

Using the built in iODBC with Mac OS 10.6.0 will cause a segmentation fault with FreeTDS. A patch is available at iODBC.org

This package contains a bug-fix for the version of iODBC provided by Apple, installing in /usr; this is recommend for compiling interfaces such as Perl DBD::ODBC, the Ruby ODBC bridge and PHP:

Mac OS X (10.5 Leopard, 10.6 Snow Leopard) iodbc-usr-sdk.zip

penwellr: I installed FreeTDS on 10.6 with the above patch using MacPorts. After that I was able to establish a connection using a DSNless string such as the following settings:

production:
adapter: sqlserver
mode: :ODBC
username: sa
password: password_goes_here
database: database_goes_here
dsn: DRIVER=/opt/local/lib/libtdsodbc.so;SERVER=server_goes_here;DATABASE=database_goes_here;UID=sa;PWD=password_goes_here

Homebrew All The Way w/unixODBC [untested]

Hugh Evans wrote a simple article that details how far HomeBrew has come and possibly how simple it is to use now for the adapter.

Homebrew, FreeTDS and RubyODBC

Making Sure Your LOCALE setting is UTF-8

Jon Kinney wrote this article on making sure your local settings are correct for the FreeTDS connection to SQL Server.

Change locale on os x snow leopard for FreeTDS functionality

Ubuntu Installation Notes

Configure SQL Server 2008

By default the server does not listen on a TCP port. Enable it following
these instructions.

FreeTDS

This package implements the TDS protocol between SQL Server and the Ubuntu box.
The Ubuntu packages for this are:

sudo apt-get install freetds-dev tdsodbc

UnixODBC

This package implements and ODBC layer over FreeTDS The Ubuntu packages for
this are:

sudo apt-get install unixodbc unixodbc-dev

Configure FreeTDS

FreeTDS needs a configuration file named /etc/freetds/freetds.conf.

The example below has two entries. The first entry, [developer], tells FreeTDS how to
connect to a named SQL Server 2008 instance named DEVELOPER.

The second entry, [production], tells FreeTDS how to connect to the default SQL Server
instance. In this case no ‘instance’ parameter is required.

[developer]
  host = endor
  port = 1433
  instance = DEVELOPER # connect to a named instance
  tds version = 8.0
  client charset = UTF-8 

[production]
  host = endor
  port = 1433
  tds version = 8.0
  client charset = UTF-8

Test FreeTDS

Use the command line client sqsh to test your FreeTDS configuration.

sudo apt-get install sqsh

For example, to connect to the developer database and perform a count on the people
table do this:

sqsh -S developer -U database_username -P database_password

A sqsh prompt should open up.

> use project_development

> go

> select count(*) from people

> go

You should see the result of the count.

Configure UnixODBC

Tell UnixODBC where the FreeTDS driver is. In /etc/odbcinst.ini put the
following:

[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

Create the ODBC entries for you databases

ODBC DSN entries are defined in /etc/odbc.ini.

Note that the names you give these entries are the names you’ll use in your
rails database.yml file.

The template for an odbc.ini entry is:

[dsn] #this is the name you use for the 'dsn' field in your rails database.yml
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = myserver # This is the name of an entry in your /etc/freetds/freetds.conf file
Database = actual_database_name # This is the name of a database in your SQL Server instance.

My /etc/odbc.ini looks like this:

[project_development]
Driver = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = developer
Database        = project_development

[project_test]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = developer
Database = test

[project_production]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = production
Database = project_production

Test UnixODBC

You can test your ODBC configuration with the isql command. For example:

isql -v project_development database_username password
SQL> select count(*) from people;

returns the count of records in the people table.

Install ruby-odbc

ruby-odbc is the ruby binding to the UnixODBC library.
On Ubuntu install the package libodbc-ruby1.8

sudo apt-get install libodbc-ruby1.8

Install Required Gems

This adapter uses the dbi and dbd-odbc gems. It requires specific versions of
those gems. Follow the Installation instructions in the adapter’s README, located here:
README

Scroll down to the “Installation” section.

Setup your database.yml

The database.yml setup is pretty simple. There is a special case for the ‘test’
entry. If your test database name doesn’t match the DSN name for that database
you must explicitly set the database name by assigning to the database field.

production:
  adapter: sqlserver
  mode: odbc
  dsn: project_production
  username: dbuser 
  password: password
  encoding: utf8

development:
  adapter: sqlserver
  mode: odbc
  dsn: project_development
  username: dbuser
  password: password
  encoding: utf8

test:
  adapter: sqlserver
  mode: odbc
  dsn: project_test
  database: test #This must be the real name of the database on the server, not the ODBC DSN! Only required for test.
  username: dbuser
  password: password
  encoding: utf8

Test that the application can talk to the database

Enter the console and query a table using active_record:

script/console
> Person.count

One last bit of Rails hackery.

There are several places where Rails’ lib/tasks/database.rake assumes it is
installed on windows and calls oslq. The test:purge task does this so you can’t
run your tests from Ubuntu. My solution is to simply edit that task in place like this:

when "sqlserver"
-        dropfkscript = "#{abcs["test"]["host"]}.#{abcs["test"]["database"]}.DP1".gsub(/\\/,'-')
-        `osql -E -S #{abcs["test"]["host"]} -d #{abcs["test"]["database"]} -i db\\#{dropfkscript}`
-        `osql -E -S #{abcs["test"]["host"]} -d #{abcs["test"]["database"]} -i db\\#{RAILS_ENV}_structure.sql`
+        ActiveRecord::Base.clear_active_connections!
+        ActiveRecord::Base.connection.recreate_database(abcs["test"]["database"])
when "oci", "oracle"
       ActiveRecord::Base.establish_connection(:test)