locked
Can we synch two SQL Server using they Synch platform ?

    Question

  • I am trying to use the Synch framework to synchronize two instances of SQL server , but the problem which I am facing is the local provider is only accepting SQL CE version and not accepting SQL server , so is there any possible way to have this class works for two SQL Servers instances as server and client ?

    Please if any body has answer or code sample I will be gratefull .

    • Moved by Hengzhe Li Friday, April 22, 2011 2:39 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Sunday, April 05, 2009 6:41 AM

Answers

  • After you installed Microsoft Sync Framework 1.0, the samples of Sync Service for ADO.NET 2.0 would be at

    C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\Samples.

    Thanks,

    Ann Tang
    Wednesday, April 08, 2009 5:25 PM
  • This is a bug.  Workaround it to SET IDENTITY INSERT OFF in the procs being used to apply changes.

    Regards,

    Sean Kelley
    Program Manager
    Microsoft
    Thursday, July 16, 2009 7:07 PM
    Moderator

All replies

  • Why dont try to use Sql Server Replication instead of Sync Services ? 
    It's very easy to use and is very powerful with 2 Sql Server Instances.

    I think Sync Services is not the best approch for your problem.

    See this topic for more informations on Sql Server Replication :
    http://msdn.microsoft.com/en-us/library/ms151198.aspx


    Sébastien Pertus. Bewise
    Sunday, April 05, 2009 9:58 PM
  • Hi Sébastien ,

    Thanks for your reply , Actually I have already SQL replication setup between my two SQL Servers , but I am trying to find an alternative for the replication as the replication it self is a comoplex and have many issue in my environment .


    Anas

    Monday, April 06, 2009 6:27 AM
  • I assume you are using Sync service for ADO.NET V1 which only supports SQL server database as server and SQL CE DB as client. However, SQL server to SQL server sync are supported by Sync service for ADO.NET V2 which is part of Microsoft sync framework V1.0. Please install from http://www.microsoft.com/downloads/details.aspx?FamilyId=C88BA2D1-CEF3-4149-B301-9B056E7FB1E6&displaylang=en.

    Also note: please uninstall all previous versions of the Sync Services for ADO.NET prior to installing this release version.


    Thanks,
    Ann Tang
    Monday, April 06, 2009 5:56 PM
  • Thanks for your reply ,

    would you please list down a code sample for two SQL server synchronization .


    Regards,
    Anas

    Monday, April 06, 2009 7:48 PM
  • After you installed Microsoft Sync Framework 1.0, the samples of Sync Service for ADO.NET 2.0 would be at

    C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\Samples.

    Thanks,

    Ann Tang
    Wednesday, April 08, 2009 5:25 PM
  • Is there any way to sync two Sql Server with minimum database schema change? e.g. use change tracking feature in Sql Server 2008 instead of creating triggers and store procedures?

    Thanks,

    jshaqd
    Wednesday, April 15, 2009 3:16 PM
  • Jshaqd,

    When change tracking was originally built, it targeted only one of many scenarios supported by the Sync Framework.  Namely, it was focused with our offline scenario which supports the ability to take a local client-side database offline and then sync local changes through a central server.  The Sync Framework is extremely flexible and supports a much wider range of scenarios: syncing two client databases, syncing two server databases, client to cloud, server to cloud, other data stores, etc.  However, SQL Server change tracking does not provide this same level of flexibility and, as such, we need to look at tweaking it a bit in SQL11.  The new sql provider is built on top of the sync framework and for now you have to leverage custom change tracking.  That being said, we have built the ability to provision your server and clients into the API (see sample noted below) so the complexity is completely eliminated.  Primary drawback is that you are forced to use triggers.

    http://msdn.microsoft.com/en-us/library/dd918848(SQL.105).aspx

    Anas, sample above describes syncing sql express to sql server although you can use SqlSyncProvider with any sku of sql server.

    Regards,

    Sean Kelley
    Program Manager
    Microsoft
    Thursday, July 02, 2009 8:43 PM
    Moderator
  • I tried this code

    ublic class Sync : SyncOrchestrator
        {
            public Sync()
            {
                string sn = "xx1";
                SqlConnection lcon = new SqlConnection(@"Data Source=AZEC-PC\SQLEXPRESS;Initial Catalog=Tst1;Integrated Security=True");
                SqlConnection rcon = new SqlConnection(@"Data Source=AZEC-PC\SQLEXPRESS;Initial Catalog=Tst2;Integrated Security=True");
                
                DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(sn);
                DbSyncTableDescription td = SqlSyncDescriptionBuilder.GetDescriptionForTable("TestTable1", lcon);
                    scopeDesc.Tables.Add(td             );
                    SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
                    DbServerSyncProvider ss = new DbServerSyncProvider();
                    
                
                serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                    serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Create);
                   serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
                   serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Create);
                    serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Create);
                    serverConfig.SetPopulateTrackingTableDefault(DbSyncCreationOption.Create);
    
                    serverConfig.Apply(lcon);
    
            }
    and its workin good if there is no incremented PK-s in table. But if there is, it shows an exception

    -- Imposible to insert in "TestTable1_tracking", when parameter IDENTITY_INSERT is value OFF.

    All working good if there is no incremental columns in table, two bases synchronize perfect. And dont work with one auto incremental column.
    Friday, July 03, 2009 9:00 AM
  • Infrastructure automaticaly made by Sync Framework make id field in TestTable1_tracking as autoincremental, if i manualy OFF autoincrementation, there is no exceptions shown, but also no synchronization made (((.
    So i dont know how to make this system to work.

    all code is

     public class Sync : SyncOrchestrator
        {
            string sn = "xx1";
            SqlConnection lcon = new SqlConnection(@"Data Source=AZEC-PC\SQLEXPRESS;Initial Catalog=Tst1;Integrated Security=True");
            SqlConnection rcon = new SqlConnection(@"Data Source=AZEC-PC\SQLEXPRESS;Initial Catalog=Tst2;Integrated Security=True");
    
            public Sync()
            {
                this.LocalProvider = new SqlSyncProvider(sn,lcon);
                this.RemoteProvider = new SqlSyncProvider(sn, rcon);
                this.Direction = SyncDirectionOrder.Upload;
            }
    
            public void MakeInfrastractureAll() 
            {
                MakeInfrastracture1();
                MakeInfrastracture2();
            }
    
            public void MakeInfrastracture1()
            {
                DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(sn);
                DbSyncTableDescription td = SqlSyncDescriptionBuilder.GetDescriptionForTable("TestTable1", lcon);
                scopeDesc.Tables.Add(td);
    
                SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
                DbServerSyncProvider ss = new DbServerSyncProvider();
    
                serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Create);
                serverConfig.SetPopulateTrackingTableDefault(DbSyncCreationOption.Skip);
    
                serverConfig.Apply(lcon);
            }
    
            public void MakeInfrastracture2()
            {
                DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(sn);
                DbSyncTableDescription td = SqlSyncDescriptionBuilder.GetDescriptionForTable("TestTable1", rcon);
                scopeDesc.Tables.Add(td);
    
                SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
                DbServerSyncProvider ss = new DbServerSyncProvider();
    
                serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Create);
                serverConfig.SetPopulateTrackingTableDefault(DbSyncCreationOption.Skip);
    
                serverConfig.Apply(rcon);
            }
        }
    In default way it shows exception, on disabling autoincrementation in TestTable1_tracking it didnt work, no synchronization processed.
    Monday, July 06, 2009 10:06 AM
  • This is a bug.  Workaround it to SET IDENTITY INSERT OFF in the procs being used to apply changes.

    Regards,

    Sean Kelley
    Program Manager
    Microsoft
    Thursday, July 16, 2009 7:07 PM
    Moderator