none
How to change sync framework isolation level from "allow snapshot isolation level " to read uncommited RRS feed

  • Question

  • I am using sync framework 2.1, sqlsyncprovider, sync between sql server 2005 and sql server 2008.

    allow_snapshot_isolation_level =on causing lot of performance issues for other applications using the database.

    Is there anything at sqlsyncprovider level, can we change the isolation settings.

    I tried this: but no use.

     provider.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);

    And also when I generate the script using (SqlSyncScopeProvisioning sqlServerProv = new SqlSyncScopeProvisioning(replicaBConn, myScope);) , what settings helps me to add a "set transaction level read uncommitted" line in all table stored procedures in the scope.



    ess


    • Edited by saitrips Wednesday, March 20, 2013 9:22 PM
    Wednesday, March 20, 2013 9:21 PM

All replies

  • create a new provider that inherits from SqlSyncProvider and override CreateEnumerationTransaction...

    see previous thread: http://social.msdn.microsoft.com/Forums/en-US/syncdevdiscussions/thread/d0ad8f07-5d65-4e14-8fca-3c1c1ce9890a

    Wednesday, March 20, 2013 11:46 PM
    Moderator
  • I implemented it. But, Right now I cant test it (some maintenance work is going on the database)

    Tomorrow morning I will test and let you know the results.

    Now , can I disable allow_snapshot_isolation on database when using isolationlevel.readuncommitted. or allow_snapshot_isolation should be on for "read uncommitted" to work?

    Many applications (sync app, desktop client,web client) share the database. wht would you suggest for isolation level?



    ess

    Thursday, March 21, 2013 3:56 AM
  • my question is why are you changing it anyway? why would you want sync to pick up changes that are not yet committed?

    if sync picks it up during selectchanges and sends it to the destination, but then the original transaction rolls back, you've just applied a change that's non-existent.

    Thursday, March 21, 2013 4:29 AM
    Moderator