none
Provision Remote SQL Server database scope using WCF Service RRS feed

  • Question

  • Hi,

       I am using this example 'http://www.systenics.com/blog/sync-sql-server-2012-data-with-sql-ce-35-sp2-over-the-internet-using-wcf-services-using-ms-sync-framework-v21/' as a template for an application that requires synchronization between SQL Server CE 3.5 databases and a remote SQL Server database. The example assumes a direct connection is available for provisioning the remote SQL database with a 'scope'. My application connects to several different SQL Server CE client databases so I require to be able to modify the service to allow creating/removing scopes on the remote SQL database without requiring a direct connection.

    Any help appreciated.

    Thanks

    Paul.

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data.SqlClient;
    using System.Data;
    using System.IO;
    using Microsoft.Synchronization;
    using Microsoft.Synchronization.Data;
    using Microsoft.Synchronization.Data.SqlServer;
    using System.Configuration;
    namespace SyncService
    {
        public class ServerSynchronizationHelper
        {
            string conString =
                ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            /// <summary>
            /// Configure the SqlSyncprovider.  Note that this method assumes you have a direct
            /// conection to the server as this is more of a design time use case vs. runtime
            /// use case.  We think of provisioning the server as something that occurs before
            /// an application is deployed whereas provisioning the client is somethng that
            /// happens during runtime (on intitial sync) after the application is deployed.
            /// </summary>
            /// <param name="hostName"></param>
            /// <returns></returns>
            public SqlSyncProvider ConfigureSqlSyncProvider(string scopeName)
            {
                SqlSyncProvider provider = new SqlSyncProvider();
                provider.ScopeName = scopeName;
                provider.Connection = new SqlConnection(conString);
                //create anew scope description and add the appropriate tables to this scope
                DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName);
                //class to be used to provision the scope defined above
                SqlSyncScopeProvisioning serverConfig =
                    new SqlSyncScopeProvisioning((SqlConnection)provider.Connection);
                //determine if this scope already exists on the server and if not go ahead
                //and provision
                if (!serverConfig.ScopeExists(scopeName))
                {
                    //note that it is important to call this after the tables have been added
                    //to the scope
                    serverConfig.PopulateFromScopeDescription(scopeDesc);
                    //indicate that the base table already exists and does not need to be created
                    serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                    //provision the server
                    serverConfig.Apply();
                }

                return provider;
            }
        }
    }

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.ServiceModel;
    using System.Runtime.Serialization;
    using System.Data;
    using Microsoft.Synchronization.Data;
    using Microsoft.Synchronization;
    namespace ServiceInterface
    {
        [ServiceContract(SessionMode = SessionMode.Required)] // Important attribute
        [ServiceKnownType(typeof(SyncIdFormatGroup))]
        [ServiceKnownType(typeof(DbSyncContext))]
        [ServiceKnownType(typeof(SyncSchema))]
        [ServiceKnownType(typeof(WebSyncFaultException))]
        [ServiceKnownType(typeof(SyncBatchParameters))]
        [ServiceKnownType(typeof(GetChangesParameters))]
        public interface ISyncService
        {
            // Defines the first method to call when using SessionMode
            [OperationContract(IsInitiating = true)]
            void Initialize(string scopeName);
            [OperationContract]
            DbSyncScopeDescription GetScopeDescription();
            [OperationContract]
            void BeginSession(SyncProviderPosition position);
            [OperationContract]
            SyncBatchParameters GetKnowledge();
            [OperationContract]
            GetChangesParameters GetChanges(uint batchSize,
                SyncKnowledge destinationKnowledge);
            [OperationContract]
            SyncSessionStatistics ApplyChanges(ConflictResolutionPolicy
                resolutionPolicy, ChangeBatch sourceChanges, object changeData);
            [OperationContract]
            bool HasUploadedBatchFile(string batchFileid, string remotePeerId);
            [OperationContract]
            void UploadBatchFile(string batchFileid, byte[] batchFile,
                string remotePeerId);
            [OperationContract]
            byte[] DownloadBatchFile(string batchFileId);
            [OperationContract]
            void EndSession();
            //Indicates the last method to call when use SessionMode
            [OperationContract(IsTerminating = true)]
            void Cleanup();
        }
        [DataContract]
        public class SyncBatchParameters
        {
            [DataMember]
            public SyncKnowledge DestinationKnowledge;
            [DataMember]
            public uint BatchSize;
        }
        [DataContract]
        [KnownType(typeof(DataSet))]
        public class GetChangesParameters
        {
            [DataMember]
            public object DataRetriever;
            [DataMember]
            public ChangeBatch ChangeBatch;
        }
        [DataContract]
        public class WebSyncFaultException
        {
            public string message;
            public Exception innerException;
            public WebSyncFaultException(string message, Exception innerException)
            {
                this.message = message;
                this.innerException = innerException;
            }
            [DataMember]
            public string Message
            {
                get
                {
                    return this.message;
                }
                set
                {
                    this.message = value;
                }
            }
            [DataMember]
            public Exception InnerException
            {
                get
                {
                    return this.innerException;
                }
                set
                {
                    this.innerException = value;
                }
            }
        }
    }


    Paul Wainwright

    • Moved by Kristin Xie Monday, February 1, 2016 2:45 AM
    Thursday, January 28, 2016 2:09 PM

Answers

  • Hello Paul,

    In ConfigureSqlSyncProvider, it's assuming you have a direct connection to the server because we have followed the blog to configure the database with SyncSvcUtilUI tool already. According to your latest reply,  I think you want to add/provision a scope dynamically, if that's the case, you can add that logic in ConfigureSqlSyncProvider method in ServerSynchronizationHelper class or define some other helper methods to create scope description, add table description, etc, and this is more related to MS Sync framework development. 

    Below sample is regarding how to define scope and provision server:

    https://msdn.microsoft.com/en-us/library/ff928699(v=sql.110).aspx

    If you have further questions regarding how to develop for MS Sync Framework, you can post your questions to forum https://social.microsoft.com/Forums/en-US/home?forum=syncdevdiscussions

    Thanks,

    Jie

    • Marked as answer by pr_wainwright Friday, February 5, 2016 11:13 AM
    Friday, February 5, 2016 7:42 AM

All replies

  • Maybe the two forums can help you.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=wcf

    https://social.msdn.microsoft.com/Forums/sqlserver/en-us/home?category=sqlserver

    Thursday, January 28, 2016 9:31 PM
  • Will take a look thanks.

    Paul Wainwright

    Friday, January 29, 2016 8:39 AM
  • Hi pr_wainwright

    Like your title mentioned, your case more related to WCF Service, I will move your case to WCF forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 1, 2016 2:45 AM
  • Hello,

    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

    Best Regards,
    Amy Peng


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, February 3, 2016 1:26 PM
    Moderator
  • Hello Paul,

    According to your description, my understanding is you have several client side DBs (SQL Server CE 3.5 databases) and one server side DB (SQL Server DB), and you want to sync the changes in each client side DB to server side DB, if that's the case, I think the current WCF sample provided in that blog ('http://www.systenics.com/blog/sync-sql-server-2012-data-with-sql-ce-35-sp2-over-the-internet-using-wcf-services-using-ms-sync-framework-v21/) should work fine.

    I also created another client application to simulate this scenario which has another local DB (name is ClientDB2.sdf), and there is no issue found during test. 

    Could you share the error you found in your testing scenario?

    Thanks,

    Jie

    Thursday, February 4, 2016 8:31 AM
  • Jie,

         Thanks for the reply. There is no error and the sample works fine. I just need to change the code so I can provision the server without requiring a direct connection to the server. e.g. Be able to apply a scope name to the server via the WCF service from my client application.

    Thanks

    Paul.


    Paul Wainwright

    Thursday, February 4, 2016 10:20 AM
  • Hello Paul,

    In ConfigureSqlSyncProvider, it's assuming you have a direct connection to the server because we have followed the blog to configure the database with SyncSvcUtilUI tool already. According to your latest reply,  I think you want to add/provision a scope dynamically, if that's the case, you can add that logic in ConfigureSqlSyncProvider method in ServerSynchronizationHelper class or define some other helper methods to create scope description, add table description, etc, and this is more related to MS Sync framework development. 

    Below sample is regarding how to define scope and provision server:

    https://msdn.microsoft.com/en-us/library/ff928699(v=sql.110).aspx

    If you have further questions regarding how to develop for MS Sync Framework, you can post your questions to forum https://social.microsoft.com/Forums/en-US/home?forum=syncdevdiscussions

    Thanks,

    Jie

    • Marked as answer by pr_wainwright Friday, February 5, 2016 11:13 AM
    Friday, February 5, 2016 7:42 AM
  • Jie,

        Thanks for the reply. My application previously used a direct connection to SQL server so I used:

    //Get the description from the desktop sdf site database
    DbSyncScopeDescription scopeDesc = SqlCeSyncDescriptionBuilder.GetDescriptionForScope(scopeName, desktopConnection);

    to get the description of the scope for SQL server. As the tables never change only the scope name when different CE clients sync I have added the required tables for provisioning in ConfigureSqlSyncProvider as you suggested. This seems to work ok. Will just need to test performance now.

    Regards

    Paul.


    Paul Wainwright

    Friday, February 5, 2016 11:13 AM
  • Paul,

    We are pleased to see that you've solved the issue. :)

    If you have other issues, welcome to post them to MSDN forums.

    Thanks,

    Jie

    Tuesday, February 9, 2016 1:26 AM