none
How To Integrate OR Replicate OR Synchronize The data From Two Database with Different Schema.

    Question

  • Hello Every One,

    I am not Sure if this is the right place for me to ask this question or not.

    I am working on a website which is approximately a replica for OLD one our client using. Like suppose we are saving the product information into the database, in first Database we have two different tables for Storing Information one is product and other Product_info while in the second database we are storing this information in three Different tables as product, product_info, product _attrib.

    Now what i want is to Synchronize the data between two database for specific tables (May be or may not be for all tables).

    I am not that much good with "MSSQL" and only know some of the concepts, so could anyone provide me the details and methods to achieve the above mentioned task (so that both the databases remains up to date whether using a schedule for the synchronization for databases).

    is that possible to use a web service or console application to do this for us or Tools provided by the "MSSQL" server can be sufficient to accomplish the task. Also provide the Examples if Possible.

    Any type of Help will be appreciated. Thanks in advance.

    Best Regards.

    Friday, December 16, 2011 9:18 AM

Answers

  • As I said before best way would be T-sql scripting  - you can use replications / logshipping / db mirroring to synchronize db for same schema. 

    if you have different schema you should be writing t-sql (joins) to found the missing new /deleted / updated entries in source tables and synchnize target.


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Incredible.Ram Saturday, December 17, 2011 4:44 AM
    Friday, December 16, 2011 12:01 PM

All replies

  • If tables having different schema then as per my experience best way would be write T-SQL scritping to synchronize data. here is sample to write the code http://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/

    http://uk.linkedin.com/in/ramjaddu
    Friday, December 16, 2011 10:02 AM
  • Hi Ram,

    Thanks for the Reply i have studied the Link you gave me in your post but it only include the Normal DDL, DML Commands for the tables and database, what i want is the Integration/Replication or Synchronization type of thing for two databases so that we can synchronize the tables for the databases (Way to update data for two databases through scheduling or some other mean- To Update one database on the basis of the other at the end of Day or Real time updation of one database whenever there is a change in other database ).

    i need to know which is the Best way to do it when the Schema and tables structure for two databases is different.

    Also do let me know  some other example for "T-SQL scripting". i tried to Goggle this but didn't find any satisfactory answer.

     

    Thanks and Best Regards.


    Friday, December 16, 2011 11:46 AM
  • As I said before best way would be T-sql scripting  - you can use replications / logshipping / db mirroring to synchronize db for same schema. 

    if you have different schema you should be writing t-sql (joins) to found the missing new /deleted / updated entries in source tables and synchnize target.


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Incredible.Ram Saturday, December 17, 2011 4:44 AM
    Friday, December 16, 2011 12:01 PM