How can I copy specific tables into one SQL Server Table into a separate table automatically?

Question

I'm not sure if this is possible. Looking for some help with a specific issue. I am building an iPhone application with a data layer written in Ruby that will connect to SQL Server 2008. For various reasons, I need to separate the iPhone application from the servers that power my websites. The databases will be hosted on separate servers, and the tables will need to be laid out differently, but I need to push some information from the one database into the other database automatically and instantaneously.

Example: Someone makes a change on the website, updating the website DB. This website DB has a table which will need to be pushed onto a differently named table on a different server.

How can I go about doing this?

Answer 1

The instant answer that springs to mind is transactional replication. If it needs to be bi-directional, then peer-to-peer transactional replication.

If the latency here proves too high, or if the schemabinding is too different, then you could have a synchronous solution, with a linked server between the two DB servers and a trigger on the table which fires on insert/update/delete, that updates the table on the other server using the linked server. Warning here is that triggers can degrade performance a little.