locked
Microsoft Synchronization Best Practice - Performance RRS feed

  • Question

  • We are using a product which uses Microsoft Synchonization to sync 2 SQL Server 2005 database (3.0GB in size) which are for the time being setting on the same physical server .  Our initial sync took days and now our second partial sync of about 800,000 rows is taking another day or so. Is this technology meant for databases of this size?  We are able to insert/update/delte about 4 million rows in 2 hours in TSQL proc.. How can we optimize the performance of this process?  After this initial load we won't have that much data to be synced because the database contains content for a website and isn't going to be a high volume transactional database..

    Thanks
    • Edited by CindyWoodVA Wednesday, June 3, 2009 4:37 PM
    • Moved by Max Wang_1983 Thursday, April 21, 2011 1:30 AM forum consolidation (From:SyncFx - Technical Discussion [ReadOnly])
    Wednesday, June 3, 2009 1:39 PM

All replies

  • In general, there are number of ways to sync up 2 different SQL server databases, such as replications and sync services.  May I ask what technology did you use to sync 2 SQL server 2005 databases?

    In general, if you want high availability for ETL kind of service, then transactional replication is your best bet.
    If you prefer to have different changes on each database and merge them later, you can consider sync services or merge replication.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, June 3, 2009 5:23 PM
    Answerer
  • I see you have used Microsoft Synchonization.

    Can you specify what kind of Provider do you have in the sync application?

    Microsoft.Synchronization.Data.DbSyncProvider
    Microsoft.Synchronization.Data.Server.DbServerSyncProvider
    Microsoft.Synchronization.Data.SqlServer.SqlSyncProvider


    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, June 3, 2009 5:33 PM
    Answerer
  • This is my experience:
    You can obtain any performance placing custom logic over channel and dataset only for INITIAL sync.  But for the subsequent sync, i never seen sync handles that much load it will offten get into "Memory insufficient issue".  You need to write code to shrink the dataset and remove the unwanted information later re-populate them.  It will be helpful if you can explain more about what namesapce you are using.

    Thanks.

    Friday, June 12, 2009 4:15 PM