MS SQL Server Replication with Ruby on Rails
May 25th, 2008
For my day job I have the following setup:
- An intranet website with (of course) networking and password restrictions
- A ‘public’ website with no restrictions on access
For reasons that aren’t really worth getting into, the ‘public’ website has no content management system (CMS) tied to it making it very difficult to update. I needed to be able to use the intranet as both an application for manipulating business data (which is not appropriate for public consumption), and as a CMS for the public website. Ruby on Rails works well for this since it allows for rapid creation of web forms for manipulating the data. At first I contemplated the idea of simply using the same database for both websites, with the intranet being the only one of the two having write access to the DB. But security concerns necessitate that the intranet DB never be accessed by the public at large since it may contain sensitive business data.
Hence SQL Server Replication with Ruby on Rails. The end result is two databases: whenever changes are made on the intranet website database they are automatically pushed (per transaction) to the public website database. This happens in real time (depending on the number of transactions occurring) - usually just a matter of seconds. And you can choose, with great granularity which tables/columns get replicated; so tables which contain data not appropriate for the public website simply don’t exist in the public website database. All in all very cool.
The details and a few gotchas:
- There are three kinds of SQL Server Replication: Snapshot, Transactional, and Merge. I’m using Transactional replication - which pushes across each transaction in real near time - appropriate for a one way push.
- This is a one-way push for security reasons. Two-way replication can apparently be set up - especially with Merge replication - but isn’t appropriate for my needs.
- Every table that needs to have columns replicated from it MUST have an ID column. This is how SQL Server maps rows between the databases. Hence even the join tables for Has and Belongs To Many relationships must have an ID. And these columns must be named something other than ‘id’. I forget the reason for this - something to do with Rails pulling back the wrong ID as it does it’s nifty queries.
- Any time you change what tables/columns will be replicated you must re-initialize the replication. This doesn’t result (to the best of my knowledge) in any down time for the public website but does add to the workload.
- Most SQL Server objects can be replicated - including stored procedures. For my setup I’m using stored procedures for all queries on my public website and so what has worked out well is to create the stored procedures in Rails migrations. I know, I know - this is probably the greatest sin a Rails developer can commit. I apologize to you all for this heresy, yet it has proven useful.
I’ll update this post soon with more details and code examples.
