I want to synchronize data from table 1 in server A to tables 2 and 3 in server B, whenever table 1 gets updated.

Tables from server A are not related to tables from server B, so I'd have to specify which columns in table 1 correspond to which columns in tables 2 and 3, and perhaps do some sort of operation on that data.

Server A, Table Y

Name  | Country | Sales  
Oscar | Peru    | 5000

Server B, Table X

Name  | Region | Amount | Currency | Multiplier | TotalAmount  
Oscar | Peru   | 5000   | €        | 5          | 25000 (=5000*5)

The goal here is to insert the record from server A to the table X in server B.

  • The 'Name' field maps to the 'Name' field
  • The 'Country' field maps to the 'Region' field
  • The 'Sales' field maps to the 'Amount' field
  • The 'Currency' field in server B will always be €
  • The 'Multiplier' field would be retrieved from another table in server B
  • The 'TotalAmount' field would be the result of Amount * Multiplier

Would this be possible to automate? Is using triggers the way to go?

I'm using SQL Server (and SMSS). The sync doesn't need to be instantaneous, it could take up to 10 or 20 minutes. The two servers are already linked. Server B is not used just for read only queries.

  • Is server B otherwise just used for read only queries? Mar 5 '16 at 4:46
  • "and perhaps do some sort of operation on that data.".. can you be more specific? are these simple operations that can be done in the SELECT clause? or more complex? Mar 5 '16 at 8:26
  • @AaronBertrand, I don't think I fully understand your question, but both servers A and B come from different programs, so no, server B is not used just for read only queries... Edward Dortland, yes they would be simple operations. I'll edit my question to clarify my goal Mar 5 '16 at 10:24
  • Maybe transactional replication with a view to create the final output you want? I wouldn't try to keep this data in a "finalized" table as there is just too much generalized business logic coming out of thin air. Mar 6 '16 at 5:11

Normally you would use materialized views for sync'ing tables, however your table structures are different which probably rules out this solution. The next solution would be to implement insert/update/delete triggers on your source table. This can cause performance bottlenecks on high volume transactions. The third solution is to implement ETL (extract/transform/load). There are a number of commercial and open source solutions. Informatica, GoldenGate from Oracle are a couple of commercial options, Telend, Jaspersoft ETL, KETL are great open source. http://www.butleranalytics.com/30-etl-tools/ has a list and links to some ETL tools.

  • Thanks Nathan! I don't think performance bottlenecks in this case is a concern, because I don't expect a high volume of transactions being made. I'll research the ETL solution but does it have a steep learning curve? Would you think that ETL could be overkill for a low volume transactions scenario? Mar 7 '16 at 17:47
  • I researched the ETL solution (using Talend Open Studio) and found that it has several benefits to what i'm trying to achieve. However, to create a really automated solution is quite complicated in my opinion, having to install an MDM server to be able to call a job when data is inserted into a table in the database...Is this really the only (free) way to go? I didn't want to go to all this trouble just to find out later that it's too complicated or it doesn't work.. Mar 11 '16 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.