locked
Client Provider for SQL 2005 RRS feed

  • Question

  • I wish to utilise Microsoft Synch Framework to sync data between two SQL 2005 databases. i.e. the Server & Client are both SQL 2005 databases.

     

    It seems there is not yet an off-the-shelf Client provider for SQL 2005 available from Microsoft and the documention for "Walkthrough: Creating a Synchronization Provider" at http://msdn2.microsoft.com/hi-in/library/bb902809(en-us,SQL.100).aspx no longer seems to exist.

     

    Is it really the case that the only client provider out there is for SQL CE?

     

    I am now moving my efforts to implementing a peer-to-peer implementation which will allow this set up (SQL2005 -> SQL2005). However, as there will only ever be 2 peers, and any synchronisation will only ever occur in one known direction, this seems to be overkill.

     

    Any thoughts appreciated.

     

    Regards,

     

    Jack

    • Moved by Hengzhe Li Friday, April 22, 2011 5:43 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, January 10, 2008 12:41 PM

All replies

  • Hi Jack,

     

    The first thing I would like to point out is that based on what you have told us, I think you will likely want to look into Sync Services for ADO.NET (which is a component of the Microsoft Sync Framework) as this is the component targeted at enabling synchronization between ADO.NET enabled databases for both offline and collaboration (P2P) based solutions. 

     

    Secondly, the client provider will allow you to target SQL 2005 but I have to admit that we are a bit behind in providing a good sample on how to do this.  I can tell you that we are working on completing this sample and will post it to our blog as soon as possible,  Please keep an eye on our Synchronization Blog for updates.

     

    Thirdly, you state that you are planning on implementing P2P, yet it looks as though you are only planning on supporting two database.  Are you thinking of eventually adding more SQL Servers which would require P2P support?  If not, you can realistically use traditional Offline (Hub and Spoke) synchronization to support your environment which can be slightly simpler to implement then P2P.  Keep in mind supporting P2P makes things a little more complicated due to issues such as keeping old change tracking information to support new Peers. 

     

    I hope this helps.

     

    Liam

    • Proposed as answer by Patrick S. Lee Wednesday, February 18, 2009 12:27 AM
    Thursday, January 10, 2008 6:26 PM
  • Hi Liam,

     

    Thanks for your input. You're right - I am looking to use Synch Services for ADO.NET for single directional synching of data between two databases.

     

    I have seen many examples of this but the client always seems to be a SQL CE database.

     

    I am just struggling to create a ClientSyncProvider for SQL2005.

     

    e.g. What can use in step 4 to create a clientSynchProvider for SQL 2005? If i have to implement it myself, how can I do this?

     

    //1. Create the Server Sync Provider

    DbServerSyncProvider serverSyncProvider = new DbServerSyncProvider();

    SyncAgent syncAgent = new SyncAgent();

    syncAgent.RemoteProvider = serverSyncProvider;

    //2. Prepare server db connection and attach it to the server sync provider

    SqlConnection serverConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ServerDB"].ConnectionString);

    serverSyncProvider.Connection = serverConnection;

    //3. Prepare client db connection and attach it to the client sync provider

    SqlConnection clientConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ClientDB"].ConnectionString);

    //4. Create the Client sync Provider

    SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(clientConnection.ToString());

    syncAgent.LocalProvider = clientSyncProvider;

     

     

    Thanks

     

    Jack

    Friday, January 11, 2008 10:45 AM
  • If you would like to sync between SQL 2005 and SQL 2005, why not try to use

    1. Transactional Replication (this is good for one way sync), or

    2. Merge Replication (this is good for 2 way sync).

     

    These features were built inside SQL 2005 (unless you use EXPRESS edition).

    Thanks

     

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, January 14, 2008 8:06 AM
    Answerer
  •  

    Although we are already using the replication built into SQL 2005, it is not ideal for our needs and we have various issues that we believe Sync Services for ADO.NET will overcome elegantly.

     

    The extra control it should allow as well as the abilty to extend to other datastores in the future are all key features for us wanting to go down this route.

     

    The questions i really need answers to are:

     

    1. When will a SQL 2005 clientProvider be available and / or ship with Sync Services ?

    2. Until such time as this is ready, is there any guidance as to how to implement one's own clientProvider (for any relation data store)?

     

     

    Thanks

     

    Jack

    Monday, January 14, 2008 11:00 AM
  •  

    I have implemented part of a ClientSyncProvider for VistaDb (http://www.vistadb.net)  I can tell you it's not exactly easy.  Alot of things undocumented, but once you really get in there, you start to understand exactly how it all works.  In some ways, sync services may be a bit overkill because of all the abstraction (to support multiple databases) which is ironic because they have yet to release any other client providers.

     

    My provider so far can do snapshot and download only, and soon upload only.  I spend a great deal of time in the debugger and SQL profiler just making sure the data going back/forth is correct, etc.  They use alot of nested transactions in the SQL CE core, and VIstaDb does not support those, so it's been a little more complicated to follow the interface correctly.

     

    Essentially, this product was built for SQL CE.  There are ways to get it to work with other databases, but it's time consuming and you may lose some of the excellent benefits that SQL CE provides.  That being said, I have very specific needs and must use VistaDb or SQL Express.

     

    I'd like to see a SQL Express clientsyncprovider as well.

    Tuesday, January 15, 2008 4:26 PM


  • Also, I would like to know when they release the client provider for SQL Express (2005 or 2008)

    Way to go with the VistaDb provider.


    Roger
    Monday, May 19, 2008 9:36 PM
  • Roger,

     

    Did you every locate a SQL 2005 Client Sync Provider  ?

    I need one too .

    Thanks in advance,Peter

    Monday, February 9, 2009 11:16 PM
  • Liam,

    Did anyone create a sample showing show to target SQL 2005 with a Client Provider in hub and spoke toploogy?

    Thanks,Peter 
    Tuesday, February 10, 2009 11:20 PM
  •  

    SqlExpressClientSyncProvider should work with SQL Server 2005. The reason why it is called Express client, and not SQLClientSyncProvider is because SQL server is not intended to serve the simple role of being a client. Nevertheless, it can serve to act as a client within the code sample.

    http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200

    For example, try passing connection to SQL Server 2005 as Client database.

    It should work.

    Make sure to change the following variables before you build.

    Tests.cs within SQLExpressProviderSampleTests

    line 72

    private string serverSqlInstanceName = Environment.MachineName + @"\SQLEXPRESS";
    private
    string clientSqlInstanceName = Environment.MachineName + @"\SQLEXPRESS";

    change to

    private
    string serverSqlInstanceName = Environment.MachineName;
    private
    string clientSqlInstanceName = Environment.MachineName;

    • Proposed as answer by Patrick S. Lee Wednesday, February 18, 2009 12:27 AM
    Wednesday, February 11, 2009 2:08 AM