Microsoft sync framework with MS SQL Server 2008 EE and FILESTREAM option do not work

Question

I need help with MS Sync Framework as I am new in it. I have tried the simple synchronization between two same tables in 2 different databases. The code I used from MS Sync tutorial http://msdn.microsoft.com/en-us/library/ff928494.aspx.

When I try to Apply the provisioning on the server side I get the exception

ALTER DATABASE failed because the READ_COMMITTED_SNAPSHOT and the ALLOW_SNAPSHOT_ISOLATION options cannot be set to ON when a database has FILESTREAM filegroups. To set READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION to ON, you must remove the FILESTREAM filegroups from the database. ALTER DATABASE statement failed.

public void CreateProvisioningOnServer()
{

  // create a connection to the SyncExpressDB database
  SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS2008; Initial Catalog=SyncDB; Trusted_Connection=Yes");

  // get the description of ArticlesScope from the SyncDB server database
  DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ArticlesScope", serverConn);

  // create server provisioning object based on the ProductsScope
  SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);


  if (clientProvision.ScopeExists(scopeDesc.ScopeName))
    return;

  // starts the provisioning process
  clientProvision.Apply();
}

I am using FILESTREAM option for the SQL Server 2008 Express edition for storing images, so I cannot use the MS Sync Framework as it is and I MUST override somehow the SqlSyncScopeProvisioning?

Next question I have

I am using the MS Sync Framework for synchronization between 2 Databases, both in Express Edition.

The client side will download data from 16 server tables - tables structure same. The client side will upload data to 4 server tables - maybe little different tables structure on client and server.

Is it good way which I want to go to use the MS Sync Framework? I have always made such synchronization by my own Stored Procedures - but there was the performance issue (and also others), when the synchronization with clients DBs via internet and linked servers is very inefficient.

So which way to go?

First question: is it possible to use 2 databases, one that is synced, and one that isn't with the images? Or do you need to sync the images as well? In that case, can you use sync framework file sync instead of db sync for the images? — stombeur
Second question: yes, sync fw is an ok candidate for this scenario, as long as you stick to that kind of number of tables (20-ish). I have used the sync fw 2.1 approach (metadata tables), but decided later on that the 2.0 approach with change tracking was better. That is not supported with express on the client side though. But there is a good (not supported) workaround for that. — stombeur
@StephaneT thank you for your time. Yes I need to sync the images too. But the main problem is that even if I did not sync the images the sync framework throws that exception. — MartinM

stombeur stombeur · Accepted Answer · 2014-04-14T13:27:19

Sync Framework uses snapshot isolation to filter out changes that happen during the sync process. If you use a lower isolation level, changes that happen during the sync process, but are rollbacked could still be included in your sync. This would cause non-existent changes on the server (they were rollbacked) to be sent to your clients. That's why you get the error and to resolve it you could rewrite the SqlSyncProvider like you point out. But I wouldn't recommend that.

From what I understand, if you intend to continue using sync framework, you would be better off removing the tables with images from your server database and removing the filestream option filegroup from your server database.

After that, the data part of the sync should work. Then you can decide how you want to sync your images to the clients. Either store them on disk on the server and use the standard sync framework file synchronization towards the clients, or store them in another database (on the same server) and extend the sync framework file synchronization to read them from a database instead of from disk and send them to disk on the clients.

Microsoft sync framework with MS SQL Server 2008 EE and FILESTREAM option do not work

1 Answers