Updating two different database at the same time

Question

Let's say I have two Databases like so :

DatabaseA

create table Table1 ( field1 int, field2 int, field3 int )

DatabaseB

create table Table1 ( field1 int, field2 int )

Our old application uses B and must continue to do so. We are developing a new application that is going to use A. What is the best way to make sure all changes in A:Table1 are replicated in B:Table1 ?

---

Here are a few things to consider:

• The old application will only do selects in B:Table1, no need for the syncronization to go both ways.
• The old application must keep using B.
• Most of the fields in A:Table1 are the same as B:Table1, but some type of data conversion will be needed in some cases.
• Performance is not a big problem, because there isn't going to be a lot of modification.
• At first, there will only be 1-2 tables that need to be syncronized, but as we migrate more modules to the new version, more tables will be added.
• Both databases are from different vendor.

---

Here are the options I have considered :

• In the new application, make update to the two databases:
  I don't like this option, because it adds a lot of complexity to the new application datalayer and there is no transactions between the two databases
• Using triggers in A to launch an application that will replicate the changes in B
  Not sure if that is a good practice and how reliable it is
• Using triggers in A to launch a stored procedure to replicate the changes
  Some of the data conversion might be too complicated to do in a stored procedure
• Launch an application or a stored procedure at a set interval to make the changes
  Not real time, so I would rather not use this

---

What are your toughs on this ?
Did I miss another option ?
Have you ever done something similar ?
Is there a software (free or not) that might do the job ?

As always, thank you all for your time and your input.

Answer 1

What do you think about transactional replication? You have the ability to replicate only certain columns and can write custom stored procedures to do the inserts, updates, and deletes. It's asynchronous too, so your new application shouldn't be bogged down by it.

Answer 2

You have lots of options here...

• You can link the servers and have the updates applied together in a transaction. This is if the databases are not on the same server.
• You could use Service Broker to provide guaranteed asynchronous updates to the other server.
• Log shipping will also allow the other server to receive updates in a near real-time fashion.

In the end you'd need to read up more on each and decide which best fits your needs. It's going to be a trade-off between reliability and performance.

From the information in your question alone it sounds like Service Broker is going to be the best fit. You can still use triggers to invoke the broker, which then responds asynchronously.