Overview of SqlCeReplication methods - LoadProperties and SaveProperties

Whenever sync is started, developers provide all the necessary properties to an object of SqlCeReplication class. These properties are provided through the constructors or by directly setting the public properties of the object. In this approach developers need to provide this data for every sync, which forces them to store this data somewhere in their code. Even if the properties seldom change, developers provide this data all the time. This data includes passwords such as DistributorPassword, InternetPassword, InternetProxyPassword and PublisherPassword which developers are better off, not saving or handling in their code.

This blog article throws light on SaveProperties/LoadProperties methods which provide a good solution to issues described above. SaveProperties saves all the properties a developer can provide to SqlCeReplication object (except for two which are described below) in a system table called __sysMergeSubscriptionProperties within the replicated database. The benefits of this approach are:

1)      The replicated database can be encrypted securing this data inside the sdf file.

2)      Passwords are encrypted with the unique device hardware key enabling an extra layer of security.

3)      Data that seldom changes need not be explicitly provided by developer for every sync

Code example for calling SaveProperties:

// Example code for SaveProperties starts

repl = new SqlCeReplication();

repl.InternetUrl = "https://www.adventure-works.com/sqlmobile/sqlcesa35.dll";

repl.InternetLogin = "MyInternetLogin";

repl.InternetPassword = "<enterStrongPassword>";

repl.Publisher = "MyPublisher";

repl.PublisherDatabase = "MyPublisherDatabase";

repl.PublisherLogin = "MyPublisherLogin";

repl.PublisherPassword = "<enterStrongPassword>";

repl.Publication = "MyPublication";

repl.Subscriber = "MySubscriber";

repl.SubscriberConnectionString = "Data Source=MyDatabase.sdf";

repl.SaveProperties();

 

// Example code for SaveProperties ends

Note: SaveProperties will succeed only if the Subscription is already created on the machine.

This simplifies subsequent syncs in that developers need to provide only SubscriberConnectionString (to identify the local database) to SqlCeReplication object and call LoadProperties. If __sysMergeSubscriptionProperties table has only one record this data is automatically loaded into SqlCeReplication object. If there are more records, then in additional to SubscriberConnectionString, developers need to provide Publication, Publisher and PublisherDatabase to uniquely identify a saved profile for a subscription.

Code example for calling LoadProperties:

// Example code for LoadProperties starts

repl = new SqlCeReplication();

repl.SubscriberConnectionString = "Data Source='Test.sdf'; Pwd='<enterStrongPassword>'";

repl.LoadProperties(); // assuming only one subscription’s properties are saved

 

// Example code for LoadProperties ends

LoadProperties/SaveProperties thus provide an efficient way to store and retrieve data relieving developers from maintaining sensitive and unchanging data.

Properties not saved by SaveProperties:

SendTimeout and ReceiveTimeout properties are not saved via SaveProperties. These values must be explicitly provided to SqlCeReplication object for every sync.

Note:

There are two things to note while using this functionality.

1)      Before calling SaveProperties, please make sure all necessary properties are set on the SqlCeReplication object. If we call SaveProperties with required properties missing, this data will be set to default even if there is a saved profile already present for this subscription in the Properties table.

2)      Passwords stored are encrypted with the unique device hardware key. Therefore if the database file is moved to a different machine LoadProperties will fail to retrieve the correct password. Developers need to provide the password and other required properties, and call SaveProperties again to refresh the saved profile on the new machine.

For more information please refer MSDN documentation:

a)      SqlCeReplication.SaveProperties - https://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcereplication.saveproperties.aspx

b)      b) SqlCeReplication.LoadProperties - https://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcereplication.loadproperties.aspx

Thanks,
Balasubramaniam Bodedhala