Rails - Jan 2006 - Is RoR on SQLServer suitable for a commercial application?

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060112/eea8d75d/attachment.html

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 Sumskas <petersumskas@gmail.com>
Date: Jan 12, 2006 8:34 PM
Subject: Is RoR on SQLServer suitable for a commercial application?
To: rails@lists.rubyonrails.org

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060112/7bf8a28a/attachment-0001.html

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


On 12/01/2006, at 10:01 PM, Peter Sumskas wrote:
> 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 Sumskas <petersumskas@gmail.com>
> Date: Jan 12, 2006 8:34 PM
> Subject: Is RoR on SQLServer suitable for a commercial application?
> To: rails@lists.rubyonrails.org
>
> 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.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060112/a413d6a4/attachment.html

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 :-)

Hope this helps,
Karel

-- 
Posted via http://www.ruby-forum.com/.

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 ;)

* 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.

-- 
Posted via http://www.ruby-forum.com/.

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

- james

On 1/12/06, David Lee <david@davelee.com.au>
wrote:
> 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
>
>
> On 12/01/2006, at 10:01 PM, Peter Sumskas wrote:
>
> 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 Sumskas <petersumskas@gmail.com>
> Date: Jan 12, 2006 8:34 PM
> Subject: Is RoR on SQLServer suitable for a commercial application?
> To: rails@lists.rubyonrails.org
>
> 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.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

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  
=> <just the table I wanted>.*  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

On Jan 12, 2006, at 4:34 AM, Peter Sumskas wrote:
> 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.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

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

bakki

On 1/12/06, Peter Sumskas <petersumskas@gmail.com>
wrote:
>
> 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.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060112/f0f71aa9/attachment.html

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060113/490cf6a2/attachment.html

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

-- 
Posted via http://www.ruby-forum.com/.