How would you do realtime data transfer between databases when data is updated?
Here's my problem at a high level:
We have two business applications. App1 inputs and stores a large set of data. We need something that will transfer data from App1 to App2 whenever any relevent data in App1 has changed. Essentially we want the data in App2 to be synchronized from App1, except that App2 contains a subset of the data.
App1 uses a SQL Server 2000 database.
App2 uses a SQL Server 2005 database.
So, for example, if a user is using App1 and they update some data, that data needs to get saved to the App1 database and then sent to the App2 database, as realtime as possible.
Looking for some good ideas that won't bring either system to its knees.
Have you considered replication?