Rails + Legacy Database [Ms Sql Server 2005] ?

Hi there,

I'm a relative newbie to ruby and rails. I have an upcoming project
for a company that uses MS SQL Server 2005, and apparently, prefers to
use PHP. We have to build a bookstore, and we will be self-hosting a
single site.

My first question is:

Is it safe to use the same database that they're using internally for
their inventory system or is it better, in terms of performance, to
duplicate the database and update it nightly (I think the delay will
be acceptable) ? That will give me the flexibility of having a
different database server on a linux machine where I could then run
Rails without wrestling with Sql Server over TCP.

Alternatively, if I run rails on a Linux machine, which uses the same
Sql Server 2005 database over TCP, should I expect performance
issues ? Remember, this is a single site, and we don't expect Amazon-
level traffic :wink:

Finally. How well can the rails handle legacy databases. Is it
terribly hard ? Can anyone share their experience in this regard ?

Any help is appreciated,

Thanks - A.Ali

Is it safe to use the same database that they’re using internally for

their inventory system or is it better, in terms of performance, to
duplicate the database and update it nightly (I think the delay will
be acceptable) ? That will give me the flexibility of having a
different database server on a linux machine where I could then run

Rails without wrestling with Sql Server over TCP.

If you don’t have to duplicate databases, that’s the best approach. It might seem fine in the short term, but I find copying a db nightly is just a big pain. I’d make Rails work with the SQL Server.

Alternatively, if I run rails on a Linux machine, which uses the same
Sql Server 2005 database over TCP, should I expect performance

issues ? Remember, this is a single site, and we don’t expect Amazon-
level traffic :wink:

I think you’ll be just fine. I’ve got lots of web servers hitting SQL server databases without issue.

Finally. How well can the rails handle legacy databases. Is it terribly hard ? Can anyone share their experience in this regard ?

I’d hardly consider SQL Server 2005 to be legacy, unless you mean a legacy schema. That’s not too difficult in most cases… you’ll run into problems if you have column names with dashes, and you’ll want to do something to lower-case all of your column names, but other than that it’s pretty trivial.

There are cases where you just can’t make it play with ActiveRecord, but you can just get around that by using DBI (directly connecting to the database and issuing your own sql calls.). You lose a lot of advantages that Rails provides, but you still have the nice view and controller layers. You’ll have to decide which approach is best. I think you’d be able to tell how possible this will be in about a day. Be sure to read the docs and the Agile book’s chapters on ActiveRecord carefully.

Good luck to you!

I also assume you're talking about legacy schemas. I'm having a bit of hard time with this right now. We have a legacy schema that deviates quite a bit from the Rails opinion of what a schema should look like. The biggest problem has been that we have some indexes whose sort order is descending, which isn't supported by Rails' SQL Server adapter and may not be supported by any of the database adapters (I'm looking into this now). Another more minor problem has been that we had some tables w/o indexes, which was causing test failures. I just added indexes, as they should've been there anyway, but I thought I should mention it.

Craig

Thanks for your input guys.

Yes, I meant legacy schemas :wink:

I suppose trying it out is the only way to know how tough it is....

Thanks again.

- A.Ali