Merging data from two replicated databases

[Dave Banthorpe]

Hi,

I have the following setup:

ServerA Database1 ->replicated to -> ServerB Database1
ServerC Database2 ->replicated to -> ServerD Database2

All databases on all servers use the same schema.

I would like to merge the data from a table in ServerB Database1 and ServerD Database2 and put it in a new table/databas, ideally on ServerB

How would I go about doing this? I know we could get the apps to use queries across both databases but I'd rather have a single DB with all the data in it.

I had thought about using triggers and federation. e.g. creating the following:

ServerB Database1 Table1 -> on insert trigger -> ServerB Database1 Table2
ServerD Database2 Table1 -> on insert federated trigger -> ServerB Database1 Table2

but this would not work. Although the basic trigger worked for data from ServerB Database1 Table1, I could not use federation from ServerD as if the network was down between ServerB and ServerD then the replication failed from ServerC to ServerD as it could not perform the federated trigger to ServerB.

Any advice welcome!

-Dave