none
Why are all samples/tutorials SQL-2-CE and none SQL-2-SQL ??? RRS feed

  • Question

  • I'm having a hard time finding material on how to sync two SQL servers. 
    all samples i find are usually SQL2008 to SQLCE - and a lot of tutorials on how to setup a "Local Database Cache" in Visual Studio...

    but, i haven't found any samples/tutorials on how to use MS Sync Framework to sync a SQL2005 with a SQL2008 database !!!!

    why ???

    am i supposed to use SQL built in database replication instead ??

    thanks
    Tuesday, January 5, 2010 3:11 PM

Answers

  • Montago,

    Take a look at the following walkthrough:

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

    This shows you how to sync both SQL Express and SQL CE with a central server.  Unfortunately, we have not added Visual Studio tooling support for these providers yet so this is about as good as it gets in terms of complexity.  If you are looking for a no code solution, you might want to check out Merge Replication as well.  Let us know if you run into any issues.

    Regards,
    Sean Kelley
    Senior Program Manager
    Microsoft
    Friday, January 8, 2010 1:07 AM
    Moderator
  • Montago,

    We do not support column level change tracking out of the box.  That said, you could look at rolling your own change racking strategy but i suspect this may end up being fairly complex.  Unfortunately, we have not written a sample that demonstrates how you would go about doing this.  That being said, one option you may want to consider would be to track changes at the row level but write some custom conflict resolution logic that iterates through the columns and merges the results.  I have not had a chance to write this myself but it should be fairly manageable.

    Hope this helps,


    Sean Kelley
    Senior Program Manager
    Microsoft
    • Marked as answer by Montago Wednesday, January 13, 2010 7:29 AM
    Wednesday, January 13, 2010 6:21 AM
    Moderator

All replies

  • Hey,

    A SQL to SQL Sync sample was just posted at

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

    Please take a look and let us know if you have further questions.

    Thanks,
    Ann Tang
    Tuesday, January 5, 2010 11:20 PM
  • Hello 

    Thanks - i found that sample, and spent 3 hours yesterday trying to get it up and running...

    some errors i encountered:

    - an assembly is build in 64bit which force me to run the whole project in 64bit (no problem, but took some time solving)
    - the sample assumes that there are a Standard SQL Server running on the host (instead of SQL Express) - i got it connected with Express using a connection string hack

    the thing I'm now trying to uncover is how to connect to a database which is NOT constructed like the sample database... the sample is asking for the specified tables in the sample database... 



    ... the sample also includes code for generating destination database+tables ... maybe stretching the word "sample" a little ;-)

    ... I thought that the MS Sync Fx would enable me to write 5 lines of code and then 'be sailing' - but it seems that some work are required... 

    Wednesday, January 6, 2010 10:58 AM
  • Hello 

    Thanks - i found that sample, and spent 3 hours yesterday trying to get it up and running...

    some errors i encountered:

    - an assembly is build in 64bit which force me to run the whole project in 64bit (no problem, but took some time solving)
    - the sample assumes that there are a Standard SQL Server running on the host (instead of SQL Express) - i got it connected with Express using a connection string hack

    the thing I'm now trying to uncover is how to connect to a database which is NOT constructed like the sample database... the sample is asking for the specified tables in the sample database... 



    ... the sample also includes code for generating destination database+tables ... maybe stretching the word "sample" a little ;-)

    ... I thought that the MS Sync Fx would enable me to write 5 lines of code and then 'be sailing' - but it seems that some work are required... 

    Hi,

    - I opened the sample too, and did not have an issue with the 64bit part, perhaps you are running your OS in 64 bit and installed the 64bit MSF runtime? Would that explain the issue?
    - The differenct with SQL standard and SQL Express is usually just the instance name, SQL standard has by default no instance name so the 'hostName' variable is set to 'localhost' or '.', whereas SQL Express tends to install with a default instance name, so indeed you need to change the code so that the connectionstring 'hostname' points to <machinename>\<InstanceName> (e.g. 'localhost\SqlExpress')

    - You can specify which tables are to be synchronized in the 'CommonUtils project => SqlCreationUtilities.cs => SyncUtils class => SyncAdapterTables array. You will need to update the SyncAdapterTablesPrimaryKeys array accordingly

    - The sample is a sample, just to help you learn MSF, and indeed your assumption to be up and running with '5 lines of code' is well off.
    Wednesday, January 6, 2010 12:15 PM
  • Hi,

    - I opened the sample too, and did not have an issue with the 64bit part, perhaps you are running your OS in 64 bit and installed the 64bit MSF runtime? Would that explain the issue?
    - The differenct with SQL standard and SQL Express is usually just the instance name, SQL standard has by default no instance name so the 'hostName' variable is set to 'localhost' or '.', whereas SQL Express tends to install with a default instance name, so indeed you need to change the code so that the connectionstring 'hostname' points to <machinename>\<InstanceName> (e.g. 'localhost\SqlExpress')

    - You can specify which tables are to be synchronized in the 'CommonUtils project => SqlCreationUtilities.cs => SyncUtils class => SyncAdapterTables array. You will need to update the SyncAdapterTablesPrimaryKeys array accordingly

    - The sample is a sample, just to help you learn MSF, and indeed your assumption to be up and running with '5 lines of code' is well off.

    Okay so far so good... it is now that i got stuck... 

    When reading the material and watching the videos from the MSF site, i get the impression that MSF is "VERY EASY" to use if you want to sync MS products like MS SQL 2005 / 2008 - using ADO.NET or the provided SyncProviders -- and even using WCF...

    The video shows how to setup a Sync sample using a SQL Server with 3 tables - then connecting to a local SQL CE using the "Local Database Cache" -- The video also show a quick example of how to manage common sync errors...

    Thats why i got the impression that MSF would be easy to setup and use... 


    Let's say i have 10 tables that are all indexed with a primary GUID key... 

    how much work would you say i need to do, to setup the following scheme:

    - Local clients SQL Express (5 clients total)
    - Remote SQL 2008 Server (having IIS and use WCF to sync)



    since its called a "Framework" and every product used is Microsoft SQL servers... i would think that all i need to do is:
    - Setup Remote and Local SqlSyncProvider'ers
    - Use the SyncAgent or the SyncOrchestrator using the two SqlSyncProviders
    - List the tables needed to be synced
    - Configure the actions when Sync error occur
    - Do Synchronize()



    am i wrong ?


    Wednesday, January 6, 2010 4:37 PM
  • Hi,

    The Microsoft Sync Framework is a generic framework to help developers write end-to-end sync applications. With MSFv2 release, new Collaboration providers -- SqlSyncProvider and SqlCeSyncProvider are added. They are targetted to specific databases and are much easier to setup than DbSyncProvider. Since MSF doesn't control the protocol and network used by sync applications, developers need to implement their own data transportation logic for n-tier scenarios.

    If you will write a simple 2-tier console app, the code will be much simplier, and it willl pretty much as the pattern that you just described in your email. But this sample is a UI based app and uses WCF for remote sync. Additional logics are added on top of MSF API flows to complete this end-to-end app.

    In future MSF releases, we are considering adding new toolings to further simplify the MSF database provider user experience. thanks a lot for your feedback.

    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, January 6, 2010 7:06 PM
    Moderator
  • Hi Dong

    thank you for your answers.


    What i need to get started with MSF is a step-by-step tutorial on how to setup and use MSF to sync SQL databases...

    what I've found so far on the /Sync site is product sales speeches saying "this product is so fantastic..." - and like i said, the only real guide to get started is with SQL <-> SQLCE inside Visual Studio...

    all the readable material I've found has been very generic and hard to read (MSDN API pages)..


    so here is an easy question :-)

    is there a guide/tutorial/video somewhere that show me how to get startet with MSF and how to sync 2 SQL databases (with or without WCF)


    - any help is appreciated -

    Thanks !!

    Edit:

    I found some stuff using the right keywords on google:

    Syncing SQL Server 2008 Databases over HTTP using WCF & Sync Framework

    SQL Express - Client Synchronization Sample
    Thursday, January 7, 2010 10:53 AM
  • Montago,

    Take a look at the following walkthrough:

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

    This shows you how to sync both SQL Express and SQL CE with a central server.  Unfortunately, we have not added Visual Studio tooling support for these providers yet so this is about as good as it gets in terms of complexity.  If you are looking for a no code solution, you might want to check out Merge Replication as well.  Let us know if you run into any issues.

    Regards,
    Sean Kelley
    Senior Program Manager
    Microsoft
    Friday, January 8, 2010 1:07 AM
    Moderator
  • Hello Sean

    I followed the text very closely and made a working program... 

    I'm now able to create a very simple database in SQL2008 or SQL2005 and configure some tables for synchronization... 

    I'm also able to get the specification from the Server and apply them on the Client

    It works with my simple setup !!


    thank you for the link !


    i have some issues when syncing between more complex tables, but i guess thats for a new thread to talk about.

    Friday, January 8, 2010 5:23 PM
  • Montago,

    any chance of sharing your sample code so I can get somewhere as well? :-)

    Thanks.
    Friday, January 8, 2010 5:48 PM
  • Following the guide I'm missing something...


    is it possible to setup a Sync Scope on the Column level instead of Row level ??


    the example shows how to setup row-by-row sync only (i think)


    thanks.
    Tuesday, January 12, 2010 1:22 PM
  • Hi

    Could you describe your question with more detail?

    The sample does show that can setup a scope that only contains certain columns of a table.
    // Definition for CustomerContact, including the list of columns to include.
    Collection<string> columnsToInclude = new Collection<string>();
    columnsToInclude.Add("CustomerId");
    columnsToInclude.Add("PhoneType");
    DbSyncTableDescription customerContactDescription =
        SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn);
    


    BTW: I share your pain; it took me quite a while to realize that the 'Offline scenario' providers, with the VS support and SQL Server 2008 Change Tracking support is something very different than the 'Collaboration providers' which do support SQL server on the client but have no VS Tooling support and do not support SQL 2008 Change Tracking.

    If you need SQL-SQL the only supported solution is the Collaboration providers, which do involve some more complexity, imho.
    Tuesday, January 12, 2010 3:38 PM
  • Hi

    Could you describe your question with more detail?

    The sample does show that can setup a scope that only contains certain columns of a table.
    // Definition for CustomerContact, including the list of columns to include.
    Collection<string> columnsToInclude = new Collection<string>();
    columnsToInclude.Add("CustomerId");
    columnsToInclude.Add("PhoneType");
    DbSyncTableDescription customerContactDescription =
        SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn);
    
    


    BTW: I share your pain; it took me quite a while to realize that the 'Offline scenario' providers, with the VS support and SQL Server 2008 Change Tracking support is something very different than the 'Collaboration providers' which do support SQL server on the client but have no VS Tooling support and do not support SQL 2008 Change Tracking.

    If you need SQL-SQL the only supported solution is the Collaboration providers, which do involve some more complexity, imho.
    Wow... that explains some of the pain, not understanding why the SyncAgent doesn't take an SqlSyncProvider - Thanks for the heads up !


    What i need/want - is to support the following scenario:

    Table:
    indx + colA + colB

    User A - changes ColA Locally
    User B - changes ColB Remotely

    Merged Row = indx + ChangeA + ChangeB


    With the sync program i've made now, the change is done on Row-level => 
    indx + ChangeA + OldB 
    or if UploadThenDownlad:
    indx + OldA + ChangeB)


    i hope you get the problem...
    Tuesday, January 12, 2010 9:25 PM
  • Montago,

    We do not support column level change tracking out of the box.  That said, you could look at rolling your own change racking strategy but i suspect this may end up being fairly complex.  Unfortunately, we have not written a sample that demonstrates how you would go about doing this.  That being said, one option you may want to consider would be to track changes at the row level but write some custom conflict resolution logic that iterates through the columns and merges the results.  I have not had a chance to write this myself but it should be fairly manageable.

    Hope this helps,


    Sean Kelley
    Senior Program Manager
    Microsoft
    • Marked as answer by Montago Wednesday, January 13, 2010 7:29 AM
    Wednesday, January 13, 2010 6:21 AM
    Moderator
  • OK, Thanks !

    I guess it should be somewhat 'easy' to merge in the ApplyChangeFailed event ?

    One thing that wonders me a bit, is that most Samples (Those which use the VS plugin to sync CE with SQL) - only have 1 ApplyChangeFailed event and seems to do the Sync in 3 directions : Up, Down, Both

    using the SyncOrchestrator i have 4 directions: Up, Down, UpDown, DownUp... 
    if a change is done on the same row, in both databases, does that mean i get 2 events ?  (i think ill just try and see)



    Thanks for the help. !
    Wednesday, January 13, 2010 7:28 AM