I am working with a customer who wants a web application built that
must access a remote MS SQL-Server database, while also accessing a
local MySQL db. Is there any best practice about how to construct
this? The plan is for the MySQL db to be the rails host db, with the
SQL-Server db containing tables of existing information.
Do I have to build custom code to access one of the databases?
This must be a common occurrence, so it must have been addressed
previously. I do not have the need to coordinate both databases
within the same transaction.
Any suggestions are welcome.
The basic idea is to create a new class from which your MS*SQL models
can inherit their connection information. You have to declare that
the class is abstract so that Rails will not assume you’re using STI.
It would look something like this:
class MsSqlConnection < ActiveRecord::Base
self.abstract_class = true
establish_connection “#{RAILS_ENV}_mssql”.to_sym
end
With a class like that you can have database.yml with a development,
test, and production section describing mysql and development_mssql,
test_mssql, and production_mssql describing how to access your remote
MS*SQL db.
Finally, the models that map to the mssql db inherit from
MsSqlConnection rather than ActiveRecord::Base.