Using Rails 2.x with MS SQL Server 2005

Question

Does anybody here have positive experience of working with MS SQL Server 2005 from Rails 2.x?

Our developers use Mac OS X, and our production runs on Linux. For legacy reasons we should use MS SQL Server 2005.

We're using ruby-odbc and are running into various problems, too depressing to list here. I get an impression that we're doing something wrong.

I'm talking about the no-compromise usage, that is, with migrations and all.

Thank you,

Answer 1

Have you considered using JRuby? Microsoft has a JDBC driver for SQL Server that can be run on UNIX variants (it's pure Java AFAIK). I was able to get the 2.0 technology preview working with JRuby and Rails 2.1 today. I haven't tried migrations yet, but so far the driver seems to be working quite well.

Here's a rough sketch of how to get it working:

1. Make sure Java 6 is installed
2. Install JRuby using the instructions on the JRuby website
3. Install Rails using gem (jruby -S gem install rails)
4. Download the UNIX package of Microsoft's SQL Server JDBC driver (Version 2.0)
5. Unpack Microsoft's SQL Server driver
6. Find sqljdbc4.jar and copy it to JRuby's lib directory
7. jruby -S gem install activerecord-jdbcmssql-adapter
8. Create a rails project (jruby -S rails hello)
9. Put the proper settings in database.yml (example below)
10. You're all set! Try running jruby script/console and creating a model.

    development:
      host: localhost
      adapter: jdbc
      username: sa
      password: kitteh
      driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://localhost;databaseName=mydb
      timeout: 5000

Note: I'm not sure you can use Windows Authentication with the JDBC driver. You may need to use SQL Server Authentication.

Best of luck to you!

Ben

Answer 2

Instead of running your production server on Linux have you considered to run rails on Windows? I am currently developing an application using SQL Server and until know it seems to run fine.

These are the steps to access a SQL Server database from a Rails 2.0 application running on Windows.

The SQL Server adapter is not included by default in Rails 2. It is necessary to download and install it using the following command.

gem install activerecord-sqlserver-adapter
--source=http://gems.rubyonrails.org

Download the latest version of ruby-dbi from

http://rubyforge.org/projects/ruby-dbi/

and then extract the file from ruby-dbi\lib\dbd\ADO.rb

to C:\ruby\lib\ruby\site_ruby\1.8\DBD\ADO\ADO.rb.

Warning, the folder ADO does not exist, so you have to create it in advance.

It is not possible to preconfigure rails for SQL Server using the --database option, just create your application as usual and then modify config\database.yml in your application folder as follows:

development:
adapter: sqlserver
database: your_database_name
host: your_sqlserver_host
username: your_sqlserver_user
password: your_sqlserver_password

Run rake db:migrate to check your installation. If everything is fine you should not receive any error message.

Answer 3

I would strongly suggest you weigh up migrating from the legacy database. You'll probably find yourself in a world of pain pretty quickly. From experience, Rails and legacy schemas don't go too well together either.

I don't think there's a "nice solution" to this one, I'm afraid.