locked
SQL Server Sync RRS feed

  • Question

  • Hello,

    1) I'd like to synchronize a table A with a table B in a two-way mode (A and B are stored in two different Microsoft SQL Servers) but their schema is different and i don't want to synchronize all the fields. Does it exist a sample which deals with that case ?

    2) I don't want to alter the schemas of the databases, just modify the existing rows of A and B. How to store all the metadata in an other table (other server) ?

    Monday, March 16, 2009 3:37 PM

Answers

  • You can use the DbSyncAdapter.ColumnMappings to setup the local and remote database mapping. But you need to ensure that the mapping column types are compatible.

    Thanks,
    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, April 1, 2009 12:33 AM
    Moderator

All replies

  • 1. The generic method is - A send a set of SELECT statement to B to query for changes and apply them on itself.  B send a set of SELECT statement to A for changes and apply changes on itself.  If they are both SQL server, then you can consider using Sync Services for ADO.NET V1.  If you are comfortable iwth it, then you can treat one server using DBSyncProvider and anotehr one using SQL Express provider, (see http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200)

    2. If you are using SQL Server 2008, then there is a way to have SQL server internally keep track of changes for your table (without changing the table schema).  If not SQL 2008, are you comformatble of track changes using DML triggers?

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 18, 2009 7:59 PM
    Answerer
  • Thank you Leo!

    I'll use DBSyncProvider and SQL Express provider  (the servers are both SQL Server 2008 :) ) but how to specify that a field 'foo' of A is mapped to the field 'bar' of B ?

    Monday, March 23, 2009 6:09 PM
  • You can use the DbSyncAdapter.ColumnMappings to setup the local and remote database mapping. But you need to ensure that the mapping column types are compatible.

    Thanks,
    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, April 1, 2009 12:33 AM
    Moderator