Is RoR on SQLServer suitable for a commercial application?

Rails
1

Good Evening,

I need some advice. I am in a position at work where I’ve got the boss
pretty excited about rails. I think I could swing the Next Generation
version of our product to use rails as the platform, but I find myself a
little hesitant to do so until I’ve answered a few issues that I’m
hoping
people on this list could help me with.

Firstly, we are tied to SQLServer 2000. Our application contains a lot
of
stored procedures and triggers and a legacy table structure. So for
starters
we have always and are always likely to run on Windows.

  • Is Rails on SQLServer ready for the prime time?

  • Have the issues with primary key names been resolved (Used to need to
    call
    your key id or otherwise always include an explicit :order_by)?

  • Do transactions work properly (the SQLServer DBI code “looks” like it
    does
    each command in a transaction - I’ve had rake unable to do unit tests
    because it couldn’t set up the fixtures due to the connection already
    being
    in a transaction)?

  • Do migrations work? I’ve heard yes, but haven’t had the opportunity to
    test them.

  • Is performance acceptable? Is there much overhead in the SQLServer
    adapter? (I wouldn’t imagine so, but…).

  • Can ActiveRecord map a legacy database structure acceptably?

  • Can I work direct-db calls and ActiveRecord code? We have many
    informational queries and stored procedures that we will work with that
    will
    not map to objects.

  • Do triggers cause problems for ActiveRecord (I’ve seen suggestions
    that
    this might be the case on this list)?

  • Do the unit and functional tests work properly for SQLServer?

  • For any issues above - is there any work being done to address them?

I would dearly love to be able to use Rails for this project. It has so
many
compelling advantages over other alternatives (currently ASP.Net using
Delphi, or PHP) but the issues we may face with SQLServer are a concern.
I
am working on implementing a part of our system in Rails as a
proof-of-concept and would appreciate any help that this list could
offer me
to make this process faster and easier.

Kind regards,

Peter.

2

My apologies if this comes through twice. I haven’t seen it myself yet,
but
some later posting I’ve made has shown up. So I’m trying again.

---------- Forwarded message ----------
From: Peter S. [email protected]
Date: Jan 12, 2006 8:34 PM
Subject: Is RoR on SQLServer suitable for a commercial application?
To: [email protected]

Good Evening,

I need some advice. I am in a position at work where I’ve got the boss
pretty excited about rails. I think I could swing the Next Generation
version of our product to use rails as the platform, but I find myself a
little hesitant to do so until I’ve answered a few issues that I’m
hoping
people on this list could help me with.

Firstly, we are tied to SQLServer 2000. Our application contains a lot
of
stored procedures and triggers and a legacy table structure. So for
starters
we have always and are always likely to run on Windows.

  • Is Rails on SQLServer ready for the prime time?

  • Have the issues with primary key names been resolved (Used to need to
    call
    your key id or otherwise always include an explicit :order_by)?

  • Do transactions work properly (the SQLServer DBI code “looks” like it
    does
    each command in a transaction - I’ve had rake unable to do unit tests
    because it couldn’t set up the fixtures due to the connection already
    being
    in a transaction)?

  • Do migrations work? I’ve heard yes, but haven’t had the opportunity to
    test them.

  • Is performance acceptable? Is there much overhead in the SQLServer
    adapter? (I wouldn’t imagine so, but…).

  • Can ActiveRecord map a legacy database structure acceptably?

  • Can I work direct-db calls and ActiveRecord code? We have many
    informational queries and stored procedures that we will work with that
    will
    not map to objects.

  • Do triggers cause problems for ActiveRecord (I’ve seen suggestions
    that
    this might be the case on this list)?

  • Do the unit and functional tests work properly for SQLServer?

  • For any issues above - is there any work being done to address them?

I would dearly love to be able to use Rails for this project. It has so
many
compelling advantages over other alternatives (currently ASP.Net using
Delphi, or PHP) but the issues we may face with SQLServer are a concern.
I
am working on implementing a part of our system in Rails as a
proof-of-concept and would appreciate any help that this list could
offer me
to make this process faster and easier.

Kind regards,

Peter.

3

Hi,

SQL server is not ideal in that it tends to lag behind other
databases in terms of features and stability, but I’ve been using it
happily for months.

Windows as a hosting platform is a different story, and last I
checked was not good for production. Things may have moved on; others
may fill you in.

We bought a new linux box to serve Rails apps, after piloting on
Windows with IIS. Windows now runs the SQL server behind lighttpd,
and IIS is nowhere to be seen.

We also run Rails on (sometimes nasty) legacy tables, and make use of
non “id” primary keys. Some of them are even varchar.

SQLserver tables can be accessed using ‘nicened’ views for Rails if
it helps; this works best with a 1-1 mapping between views & tables
(multi-table views can’t generally be updated without INSTEAD OF
trigger voodoo). We do this in some places.

I forget about migrations, sorry … I only use them at home on
postgres, where they’re nice indeed. Not sure if this is becuase it’s
broken or I’m just silly.

Performance seems decent.

Unit tests work; i can’t run them all at once with Rake at present
(it barfs), and running the Rails (the framework) unit tests cause 3
failures with my configuration out of a few thousand tests. These are
all, IIRC, to do with large binary blobs or suchlike. Running the uni
tests for a given model works fine. Don’t do much in the way of
functional tests yet (sorry world) so can’t comment.

You can call stored procs, but you need to do a thing or two (eg
clean up the handle afterwards) to keep the DB connections tidy. This
may be cleaner since i last checked.

hope this helps.

My advice:

  1. sure, use SQL Server 2000. Be prepared for the occasional rough
    edge (it’s come a really long way).
  2. don’t use IIS.

cheers,
David

4

Just to add:

  • we use full test stack: unit, functional and even Watir tests with MS
    SQL server and can run it at once by rake (we also use this for
    Continous Integration with CIA = Continuous Integration Automater - part
    of Rails SVN)

  • the ODBC way of using sqlserver_adapter seems to be better/more
    stable/more mature than the ADO (or a few weeks ago seemed so)

  • the sqlserver_adapter is actively maintained (and I have also created
    a few little patches in pre-1.0 versions :wink:

  • performance seems to be very good, but we have quite small amount of
    users, so you will have to made some performance test by your own

  • also concerning the legacy schema mapping you will have to try it
    yourself

K.

5

Hi,

We have no problems running Rails app against MS SQL Server 2000 since
Rails 1.0 .

We don’t have a legacy schema (we buid a brand new app) and don’t use
any special SQL Server features and no stored procedures, so our
application runs also on MySQL without any modifications.

Also migrations works great for both DBs, which is a big plus.

The application is running on MS Windows 2000 server with Apache 2.0 +
FCGI also no problems yet.

We are also authorizing the users against Active Directory through LDAP
protocol (we have implemeted our own simple pure Ruby LDAP bind and
search operations).

As (now almost former) PHP and Java (Hibernate/Spring/Tapestry)
programmer, I must say that I’m much happier with Ruby and Rails :slight_smile:

Hope this helps,
Karel

6

Migrations on SQLServer work fine here (developing on a Mac, Rails ->
ODBC -> FreeTDS -> SQLServer, in case that’s important)

  • james
7

That’s gmail. It doesn’t show your own postings.

bakki

8

Thanks, everyone. I am greatly encouraged.

I will test out the areas I’ve had problems with in the past - from the
feedback you have all given it sounds like all the issues should be
solvable
now.

Then I can present to the boss why rails is the way to go.

Regards,
Pete.

9

Peter:

I’ve been developing an app to integrate with a legacy SQL Server
system. We set up our development environment to use Sqlite (and I
sometimes use MySQL as well) and are deploying to both a test
production and live production environment on SQL Server.

There has been some pain. First, because we are using non-railsified
legacy tables, that problem had to be addressed. I rewrote
“method_missing” in ActiveRecord::Base in a subclass that became the
parent class of my legacy models to deal with the UPPER CASE
attributes (translates lower case attribute names into the upper case
of the database column names). If I had to do it all over again
though, I’d just make views since the tables I’m using are read only.

Other pain has been from differing behavior between the database
adaptors. For example, using “left join” with two tables that have a
common column name caused the sql server adaptor to blow up–but both
SQLite and MySQL worked fine. I chased this as a bug in the SQL
Server adaptor for several hours before realizing I could use :select
=> .* to solve the problem. The biggest
problem isn’t the behavior, but that the database adaptors behave
differently. This means that rails code isn’t perfectly portable
between databases.

If you use singular table names and table sessions, be aware that the
auto generated sessions table will still be plural–make your own
singular one.

Based upon my experience of the past month, I’d say that make any new
stuff as railsified as possible, use railsified views on the legacy
stuff if that’s feasible, and if you use any other dbs in development
assume differences in the database adaptors.

Jamie

10

Good luck!

And don’t be afraid to study/dig into Rails sources if you find
something unclear or strange. That’s a really good way how to learn all
much better and quicker. And you will soon be able to submit your own
patches to Rails if necessary.

The great think about Rails is that all the sources are at our hands and
the amount of framework code is quite small and understandable.

Karel