Microsoft Sync Framework Error

Question

I'm using the Microsoft Sync Framework v2.1 in order to synchronize two databases i.e. from remote (ms sql server 2012) to local (ms sql server express 2008 R2). The tables are able to be created successfully on the local database, however the data is not being synchronized due to the following error:

The current operation could not be completed because the database is not provisioned for sync or you not have permissions to the sync configuration tables.

However, when synchronizing 2 local databases (using ms sql server express 2008 R2), the synchronization is successful.

Anyone have any suggestions as to what the problem might be?

Thanks.

CODE

The following is the main form:

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // create a connection to the SyncExpressDB database
            string clientConn = ConfigurationManager.ConnectionStrings["ianConnection"].ConnectionString;

            // create a connection to the SyncDB server database
            string serverConn = ConfigurationManager.ConnectionStrings["arvixeConnection"].ConnectionString;

            Utilities.Synchronisation.Db.DBSynchroniser dbSync = new DBSynchroniser(clientConn, serverConn);
            dbSync.ProvisionSyncScope("TestScope", "Products", DBSyncSide.Both);
            dbSync.Sync(Microsoft.Synchronization.SyncDirectionOrder.Download, "TestScope");
            label1.Text = "Sync Done !!!";
        }
    }

The following is the synchronization class

public class DBSynchroniser
    {
        SqlConnection clientConnection;
        SqlConnection serverConnection;

        SyncOrchestrator syncOrchestrator;

        private EventHandler<DbApplyChangeFailedEventArgs> changeFailedHandler;
        public EventHandler<DbApplyChangeFailedEventArgs> ChangeFailedHandler
        {
            get { return changeFailedHandler; }
            set { changeFailedHandler = value; }
        }

        public DBSynchroniser(string clientConn, string serverConn)
        {
            clientConnection = new SqlConnection(clientConn);
            serverConnection = new SqlConnection(serverConn);

            syncOrchestrator = new SyncOrchestrator();
            changeFailedHandler = new EventHandler<DbApplyChangeFailedEventArgs>(ApplyChangeFailed);
        }

        public void ProvisionSyncScope(string syncScopeName, string tableName, DBSyncSide syncSide)
        {
            ProvisionSyncScope(syncScopeName, new List<string>(new string[] { tableName }), syncSide);
        }


        public void ProvisionSyncScope(string syncScopeName, List<string> tableNames, DBSyncSide syncSide)
        {

            DbSyncScopeDescription scopeDesc;
            // define a new scope
            scopeDesc = new DbSyncScopeDescription(syncScopeName);
            scopeDesc.UserComment = "This is to test the sync class";

            foreach (string name in tableNames)
            {
                // get the description of the table name
                // and add the table description to the sync scope definition
                scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(name, serverConnection));
            }

            // create a server scope provisioning object based on the ProductScope
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConnection, scopeDesc);
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConnection, scopeDesc);

            // skipping the creation of table since table already exists on server
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
            clientProvision.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);

            // start the provisioning process
            switch (syncSide)
            {
                case DBSyncSide.Client:
                    clientProvision.Apply();
                    break;
                case DBSyncSide.Server:
                    serverProvision.Apply();
                    break;
                case DBSyncSide.Both:
                    serverProvision.Apply();
                    clientProvision.Apply();
                    break;
                default:
                    break;
            }

        }

        public void DeprovisionScope(string scopeName, DBSyncSide syncSide)
        {
            SqlSyncScopeDeprovisioning clientSqlDepro = new SqlSyncScopeDeprovisioning(clientConnection);
            SqlSyncScopeDeprovisioning serverSqlDepro = new SqlSyncScopeDeprovisioning(serverConnection);

            // First save the deprovisioning script so it can be run on other SQL Server client databases.
            // This step is optional.
            //File.WriteAllText("SampleDeprovisionScript.txt", clientSqlDepro.ScriptDeprovisionScope(scopeName));

            // Remove the scope.
            switch (syncSide)
            {
                case DBSyncSide.Client:
                    clientSqlDepro.DeprovisionScope(scopeName);
                    break;
                case DBSyncSide.Server:
                    serverSqlDepro.DeprovisionScope(scopeName);
                    break;
                case DBSyncSide.Both:
                    clientSqlDepro.DeprovisionScope(scopeName);
                    serverSqlDepro.DeprovisionScope(scopeName);
                    break;
                default:
                    break;
            }

        }

        public DBSyncOperationStatistics Sync(SyncDirectionOrder direction, string syncScopeName)
        {
            // set local provider of orchestrator to a sync provider associated with the 
            // MySyncScope in the client database
            syncOrchestrator.LocalProvider = new SqlSyncProvider(syncScopeName, clientConnection); //check objectPrefix and schema

            // set the remote provider of orchestrator to a server sync provider associated with
            // the MySyncScope in the server database
            syncOrchestrator.RemoteProvider = new SqlSyncProvider(syncScopeName, serverConnection); //check objectPrefix and schema

            // set the direction of sync session to Upload and Download
            syncOrchestrator.Direction = direction;

            // subscribe for errors that occur when applying changes to the client
            ((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += changeFailedHandler;

            // execute the synchronization process
            DBSyncOperationStatistics syncStats = new DBSyncOperationStatistics(syncOrchestrator.Synchronize());

            //return statistics of synchronisation
            return syncStats;
        }



        private void ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
        {
            throw e.Error;
        }


        ~DBSynchroniser()
        {
            if (clientConnection.State == System.Data.ConnectionState.Open)
            {
                clientConnection.Close();
                clientConnection.Dispose();
            }

            if (serverConnection.State == System.Data.ConnectionState.Open)
            {
                serverConnection.Close();
                serverConnection.Dispose();
            }
        }
    }

Answer 1

I had a same Problem As Dear user3206587 mentioned

Client and the server schemas must be simmilar for example both of them should be dbo.

Because if table prefixes become different we will be faced with provisioning or permission Error for instance

dbo.tblTest is different with sampleUser.tblTest

you should change default schemas for sample user on server exactly same as your local database.

Here I demonstrated how you can do the stuff:

Answer 2

I have solved this problem. As I thought, it was a schema related problem. I specified the correct schemas since the schema on the remote database is different from the local one, and the problem is solved. Thanks for all your help.