If you've been doing any Microsoft development then you've most likely encountered Microsoft SQL Server. If you don't plan to use Microsoft SQL Server with your Rails projects, you can safely skip this section.
Rails has built-in support for this as long as your database design allows it. There are a few issues you should be aware of before starting:
SQL Server lets you name tables using mixed-case, dashes, and other strange characters. Rails simply can't handle those well, so if you can't rename your tables, you may have to get creative. Table and column names should be defined using lowercase letters and underscores.
Views work just like tables but you're limited when it comes to doing updates.
When you use Rake to migrate your database, created tables are owned by the user that you specify in database.yml, not by the database owner.
Stored procedures aren't easy to pull off but can be done provided you are careful in how you construct the return values from your procedure. I've had success executing procedures that return a single recordset.
There are two methods to connect your Rails applications to a Microsoft SQL Server database: an ODBC connection or an ADO connection.
I am going to assume that you have access to Microsoft SQL Server 2000 or higher and that you can already connect to that server using Enterprise Manager on your development machine. I also assume you've got a database created for your Rails application and that you've granted a user access to that database.
Setting up an ODBC connection is simple, especially if you've done one before.
Open the Control Panel and choose the Administrative Tools icon.
Select Data Sources (ODBC).
Ensure that User DSN is selected and choose Add.
When asked to select a driver, choose SQL Server from the list and select Finish.
Fill in the name for the ODBC data source, select the server you wish to use, and select Next.
When asked how SQL Server should verify the authenticity of the login ID, choose With SQL Server Authentication as shown in Figure 24 and choose Next.
Change the default database to the database for your application and choose Next.
Finally, select Finish on the final screen, leaving all settings on that screen alone. You'll be given a summary screen that will allow you to test the connection. When your connection test is successful, choose OK to save the settings.
Your database.yml file should then use this for the connection information:
Development: adapter: sqlserver mode: odbc dsn: MY_TEST_RAILS_APP username: my_rails_user password: my_rails_password
With that configured, you should now be able to run your required tasks.
Another way to connect to Microsoft SQL Server on a Windows machine is by using the ADO Ruby adapter. This method is a little more portable across machines because it doesn't require you to create a DSN first, but it does require that you modify the Ruby installation on each machine first.
The One-Click Ruby installer we used for this Short Cut includes the Ruby-DBI library but does not include the bindings for ADO. The real Ruby-DBI library at http://rubyforge.org/projects/ruby-dbi/ does contain the crucial file.[4]
Visit http://rubyforge.org/frs/?group_id=234&release_id=6448 and choose dbi-0.1.1.tar.gz.
Unzip the file to a temporary location like c: emp uby-dbi.
Once extracted, go to c: emp uby-dbi and locate the file ADO.rb within the libDBD folder.
Copy this file to c: ubylib ubysite_ruby1.8DBDADO. You will most likely need to create the ADO folder (see Figure 25).
That's it! Now you just need to configure your database.yml file to connect to SQL Server via an ADO connection. The syntax looks very similar to the syntax for MySQL:
production: adapter: sqlserver database: UWECpublic host: server.myhost.com username: my_rails_user password: my_rails_password
So Which Is Better, ODBC or ADO?
It's an interesting comparison. I've used ODBC connections for my applications when I want to connect to Microsoft SQL Server from a Linux-based production server. If I use ODBC on my desktop machine as well, then I won't have to change the database.yml file from ADO to ODBC on the Linux server (Linux servers can only connect to Microsoft SQL Server using ODBC).
However, when deploying an application on Windows, I have found that there can be some issues with permissions when using ODBC DSNs. Mongrel running as a service on Windows cannot access the DSN. In these cases, I've been able to just use the ADO method.
Some have hinted that the ADO method may be less stable, but I've noticed no problems so far. Both methods seem to perform equally well.
The best advice I can give you is to rigorously test your applications and determine the best solution for yourself.