MS SQL setup for Rails 2.3.3 on Mac OSX and Plesk 8.6

==Summary

  These instructions detail the configuration of an MS SQL database connection
  (and the associated software required) for Ruby on Rails or ActiveRecord. They
  rely heavily on input from a variety of blogs (noted with asterisks).

  I tested these during the setup of a dev workstation on a Mac Pro with OS X
  and a Preview server running CentOS and Plesk 8.6.0.

  It seems that ADO doesn't work (or is a nightmare to get working) on a Linux
  or Mac based machine, so this solution uses ODBC. This solution likely differs
  significantly from the configuration needed for a Windows box.

  NOTE - The ODBC driver files "odbc.ini" and "odbcinst.ini" turned out to be
         rather picky on not having leading spaces.

  Prerequisites (may work with newer versions):
    * Ruby 1.8.7 
    * Rails 2.3.3
    * Software for doing a "make, make install"
      * On Plesk, this may already be available
      * I seem to recall adding software from the OSX disk a while back for this
    * (Mac) MacPorts

==Primary resources:

  * http://blog.opensteam.net/past/2009/1/28/rails_ms_sql_on_mac/
  * http://toolmantim.com/articles/getting_rails_talking_to_sqlserver_on_osx_via_odbc
    (Good for testing procedures, stop at "Add the FreeTDS ODBC Driver")

==Ruby Stuff

  * http://github.com/rails-sqlserver/2000-2005-adapter/tree/master
  (This solution seems to not work with ADO, so it needs an ODBC driver, FreeTDS)

  $ sudo gem install dbi --version 0.4.1
  $ sudo gem install dbd-odbc --version 0.2.4
  $ sudo gem install activerecord-sqlserver-adapter

==Ruby ODBC Bindings

  * http://www.ch-werner.de/rubyodbc/README

  $ cd /usr/local/src
  $ curl -O http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9997.tar.gz
  $ tar -zxvf ruby-odbc-0.9997.tar.gz 
  $ cd ruby-odbc-0.9997
  $ ruby extconf.rb
  $ make
  $ sudo make install

==Install UnixODBC

  (On Plesk, SKIP THIS -- already has version 2.2.11)
  * http://unixodbc.darwinports.com/
  $ sudo port install unixodbc  
  
==Install FreeTDS (an ODBC driver-type thing)

  (Mac Setup)
    * http://toolmantim.com/articles/getting_rails_talking_to_sqlserver_on_osx_via_odbc
    * http://blog.opensteam.net/past/2009/1/28/rails_ms_sql_on_mac/
    * http://blog.singletoned.net/2009/07/connecting-to-ms-sql-server-from-python-on-mac-os-x-leopard/

    $ sudo port install freetds +mssql

  (Plesk Setup)
    * http://webscholar.net/2008/02/19/php-mssql-freetds/
    * http://freetds.org/

    # cd /usr/local/src  
    # wget "ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz"
    # ./configure --prefix=/usr/local/freetds
    # make
    # make install
    # cd /usr/local/freetds/bin
      (Above...switch to directory containing binary for testing)
    
==Test FreeTDS

  $ man tsql
  $ tsql -H SQL_SERVER_ADDR -p 1433 -U USERNAME -P PASSWORD

==Test DSN (temporary use of freetds.conf)

  Add a temporary server entry to the FreeTDS config file, which can be found 
  at:
  
  (MAC)   /opt/local/etc/freetds/freetds.conf
  (PLESK) /usr/local/freetds/etc/freetds.conf

    [A_DSN]
      host = HOST_ADDRESS
      port = 1433
      tds version = 8.0 # for SQL2000

  $ tsql -S somesqlserver -U USERNAME -P PASSWORD
 
  Once done, delete entry from freetds.conf

==Configure FreeTDS DSN entries

  (MAC)   Edit /Library/ODBC/odbcinst.ini: 
     1  [ODBC Drivers]
     2  tds = Installed
     3  
     4  [tds]
     5  Driver = /opt/local/lib/libtdsodbc.0.0.0.so
     6  Setup  = /opt/local/lib/libtdsS.1.0.0.so

  (Plesk) Edit /usr/local/freetds/ODBC/odbcinst.ini:
     1  [ODBC Drivers]
     2  tds = Installed
     3  
     4  [tds]
     5  Driver = /usr/local/freetds/lib/libtdsodbc.so
     6  Setup  = /usr/local/freetds/lib/libtdsodbc.so

  (MAC)   and /Library/ODBC/odbc.ini:
  (Plesk) and /usr/local/freetds/ODBC/odbc.ini:

     1  [ODBC]
     2  Trace = 0
     3  
     4  [A_DSN]
     5  Driver = tds
     6  Description = ODBC connection via FreeTDS
     7  Trace = No
     8  Server = hostname_or_ip 
     9  Database = DATABASE_NAME
    10  Port = 1433

==Configure UnixODBC to use FreeTDS driver and DSN

  * http://piao-tech.blogspot.com/2008/02/using-activerecord-with-microsoft-sql.html

  (Use the Plesk path "/usr/local/freetds/ODBC/" below if needed)
  $ sudo odbcinst -i -d -f /Library/ODBC/odbcinst.ini
  $ sudo odbcinst -i -s -l -f /Library/ODBC/odbc.ini

==Rails 'database.yml' file example

  1  development:
  2    adapter: sqlserver
  3    username: USERNAME
  4    password: PASSWORD
  5    mode: ODBC
  6    dsn: A_DSN

==Rails 'environment.rb' file

  * http://github.com/adzap/rails-sqlserver-adapter/tree/master

  1  config.gem 'dbi', :version => '0.4.1'
  2  config.gem 'dbd-odbc', :version => '0.2.4', :lib => 'dbd/ODBC'
  3  config.gem 'activerecord-sqlserver-adapter', :version => '2.2.19'