Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Simple SQL Server 2008 Table changes RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am using the sample from http://code.msdn.microsoft.com/sync/Release/ProjectReleases.aspx?ReleaseId=3422

    I modified the code to Sync 1 table.

    here's my setup

    localProvider = SQL 2008 Express

    remoteprovider = SQL 2008 Express

    actually i am testing it on the same server.

    i have 1 table called Badge and i add 1 record to local and i do a synchronize and i see that on the remote. I add one more record works.

    now, i try modifying the one of the field values and i do not see that change on the remote.

    here's the code i use.

    Synchronize code

            public SyncOperationStatistics SynchronizeProviders(KnowledgeSyncProvider masterProvider, KnowledgeSyncProvider slaveProvider)

            {

                try

                {

                    SyncOrchestrator orchestrator = new SyncOrchestrator();

                    orchestrator.LocalProvider = masterProvider;

                    orchestrator.RemoteProvider = slaveProvider;

                    orchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

                    SyncOperationStatistics status = orchestrator.Synchronize();

     

                    return status;

     

                }

                catch (Exception)

                {

                    throw;

                }

            }

    here's the code for ConfigureSyncProvider

            public SqlSyncProvider ConfigureSqlSyncProvider(string server,string db,string uid,string pwd)

            {

                SqlSyncProvider provider = new SqlSyncProvider();

     

                provider.ScopeName = scopename;

                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                builder.DataSource = server;

                builder.InitialCatalog = db;

                builder.ConnectTimeout = 0;

                if (uid.Length == 0)

                {

                    builder.IntegratedSecurity = true;

                }

                else

                {

                    builder.IntegratedSecurity = false;

                    builder.UserID = uid;

                    builder.Password = pwd;

                }

                provider.Connection = new SqlConnection(builder.ToString());

     

                DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopename);

                SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((SqlConnection)provider.Connection);

                DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Badge", (SqlConnection)provider.Connection);

                scopeDesc.Tables.Add(tableDesc);

                if (!serverConfig.ScopeExists(scopename))

                {

                serverConfig.PopulateFromScopeDescription(scopeDesc);

                serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);

                serverConfig.Apply();

                }

                return provider;

            }

    I dont see any updates going down to remoteprovider please Help.

     

     

     

    Friday, September 24, 2010 8:19 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Finally i found the problem and solved it.

                serverConfig.PopulateFromScopeDescription(scopeDesc);

                serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);

        serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);

            the above line was missing.     

    serverConfig.Apply();

    Monday, September 27, 2010 6:58 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    have you tried running SQL Profiler to capture the selectchanges sp and see if it's detecting the updates?

    you may want to check in the ApplyChange failed event as well if there are any errors/conflicts being raised when applying the change.

    Monday, September 27, 2010 3:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Finally i found the problem and solved it.

                serverConfig.PopulateFromScopeDescription(scopeDesc);

                serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);

        serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);

            the above line was missing.     

    serverConfig.Apply();

    Monday, September 27, 2010 6:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    are you using Sync Fx v2.1? because CreateOrUseExisting is the default setting and you dont need to specify it.
    Tuesday, September 28, 2010 1:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes. But i am not going to use this method

     

    http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/12/sync-services-for-ado-net-with-sql-server-2008-change-tracking.aspx

    its much better and uses Sync Framework

    Tuesday, September 28, 2010 3:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    the example in the link you stated above uses the offline provider. if you're using SQL Express on the client, there is no out-of-the-box provider for SQL Express for the offline scenario.

    also, the Local Database Cache by default doesnt do bi-directional sync, so you will have to do some additional work to get it up and running.

    Tuesday, September 28, 2010 10:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    true. My requirement is we have a Remoting Server where all clients connect and add data using client App and request for updated data.when they request, instead of pulling all the records for updates i can pull updated data from tables that i am watching for changes using Change tracking and it will return only records that are modified and i can pass that to client as dataset which in turn can update their local SQLExpress database and it uses Sync framework and i can clear updated data once it been sent. i am still evaluating this option instead of creating several tracking tables, we have 150 tables and to watch all 150 its big.

    please let me know if Change tracking from SQL Server is better or the Sync framework tracking is better for my situation.

    Appreciate your help.

    Thanks

    Wednesday, September 29, 2010 2:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    SQL Change Tracking can only tell you what has changed since a particular point in time. So you still have to worry about retrieving the changes, transferring it to the client, applying the changes, handling errors/conflicts if they occur and keeping track of what was sent/received.

    In terms for performance, there is a thread on the Sync Fx v3.0 Preview on Sync Fx Tracking vs SQL Change Tracking, if i remember it right, there is not much difference.

    One thing to note about SQL Change Tracking is that change tracking is happening outside of Sync Fx (including retention period), so the change tracking metadata might get cleaned up by SQL Server and Sync Fx cant do much about it. Whereas in Sync Fx, the change tracking mechanism is very much visible to developers.

    Moving forward, most of the improvements on the database providers are on the collaboration providers rather than the offline provider. even the upcoming v3 is using the collaboration provider scope/knowledge approach whereas the offline providers has not seen much update for some time already.

    Wednesday, September 29, 2010 3:44 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes. I do understand about the retention period.

    here's my take on SQL Change Tracking vs Sync 2.x

    SQL Change Tracking - Enable tracking on tables you want and thats it. I can get changes only after the initial get all.

    Sync 2.x - Enable tracking on 4 tables, it creates tracking tables/stored procedures thats too much and only good thing is i can deprovision those when i don't need them it gets deleted same way i can for SQL Change Tracking.

    Yes. Sync Fx only receives the updates from SQL Change tracking which is what we need. right now, we use a flag field set to true if its an updated record or not and then reset the value once we take those records.

     I thought SQL Change Tracking is better than Sync Fx in terms of performance. check this Microsoft website what it says:

    Change Tracking and Sync Services for ADO.NET

    SQL Server 2008 R2

     

    Sync Services for ADO.NET enables synchronization between databases, providing an intuitive and flexible API that enables you to build applications that target offline and collaboration scenarios. Sync Services for ADO.NET provides an API to synchronize changes, but it does not actually track changes in the server or peer database. You can create a custom change tracking system, but this typically introduces significant complexity and performance overhead. To track changes in a server or peer database, we recommend that you use change tracking in SQL Server 2008 because it is easy to configure and provides high performance tracking.

    What do you think?

    Wednesday, September 29, 2010 2:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    my point is that CT is just a subset of Sync functionality, you can use SQL CT and write most of the other plumbing to enable a full sync solution.

    now in terms of performance, as i have mentioned, i dont think there's a significant difference like one being twice as fast as the other.

    there's a thread on the Sync Fx v3 specifically asking why SQL CT is not supported in the collaboration providers, it has some interesting insights on SQL CT vs Sync Fx CT : http://social.microsoft.com/Forums/en-US/synclab/thread/2ea93e2e-58f8-4847-85e6-c43a13a09a20

    Wednesday, September 29, 2010 11:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    my point is that CT is just a subset of Sync functionality, you can use SQL CT and write most of the other plumbing to enable a full sync solution.

    now in terms of performance, as i have mentioned, i dont think there's a significant difference like one being twice as fast as the other.

    there's a thread on the Sync Fx v3 specifically asking why SQL CT is not supported in the collaboration providers, it has some interesting insights on SQL CT vs Sync Fx CT : http://social.microsoft.com/Forums/en-US/synclab/thread/2ea93e2e-58f8-4847-85e6-c43a13a09a20

    But the problem with Sync Fx is that it creates tables/storedprocedures for each table that i like to track thats a lot of tables.

     

    Thursday, September 30, 2010 3:10 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    internally, SQL CT creates tables as well. you can query sys.internal_tables to find the tables created for every user table where CT is enabled.

    personally, i dont mind the extra tables and stored procedures created by Sync Fx, they even represent extension points where i can inject any custom logic i want to put in. it's not nice to see them in SQL Management Studio, but the synchronization applications dont enumerate these tables and SPs anyway.

    Thursday, September 30, 2010 3:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    But when we do updates i use snapshot(Red Gate) updates to the database schema which might have added or removed fields how will it work if i have version 1 of db is running and we come out with version 2 and customer has to update the database they run our update utility to update the database which will add or remove certain fields. How will it work in this scenario?
    Thursday, September 30, 2010 6:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sync Fx doesnt handle schema changes automatically so you'll have to come up with an approach to update the databases as Sync Fx doenst allow you to update scopes as well.

    Friday, October 1, 2010 1:27 AM