Sync SQL Server 2014 and SQL Server 2014 Express database using stored procedure or C#

Question

I have a C# Windows Forms application (writen in Visual Studio 2013) and I have the same database schema in both SQL Server (for master user) and in SQL Server Express (for clients). My database size is around 2 GB for each client.

I want to synchronize this databases once in a day or when needed. In SQL Server 2008, we have used RMO, but that API has been deprecated with SQL Server 2012.

I have tried using Merge Replication using T-SQL and able to create publication, add articles to publication, create subscriber and also able to register subscriber to publication but data was not synchronized.

Is there any good example for this(merge replication) which will work for SQL Server 2014 or later version then please suggest it.

Thanks.!

Answer 1

This is by no means a comprehensive answer, but it'll give you some pointers which you will then be able to research independently.

Here is how I do merge replication with anonymous pull subscribers:

• Configure publisher and distributor (in my case same SQL Server instance).
• Create an empty local Windows directory or UNC share for the snapshot (we will generate it later).
• Create a local/domain Windows user account for the snapshot agent (usually called Snapshot), give it db_owner access to the database you're about to publish and make sure it has write permissions on the Windows directory (or share) that you have set up for the snapshot
• (optional) Create a local/domain Windows user account that will access the database during synchronization. The Merge Agent on the subscriber will either directly impersonate this account to connect to the publisher, or the subscriber will connect to your Web Sync server, which will in then impersonate this account to connect to the publisher). Give this account db_owner access to your database on the publisher. Make sure this account has read permissions on the snapshot directory/UNC share.
• Create the merge publication using TSQL or the "New Publication" wizard in SQL Server Management Studio.
• Add articles to the publication.
• Add users to the Publication Access List (PAL): this should include the Snapshot user and the optional Windows user created in step 4.
• In publication properties configure snapshot location (point it to the local/UNC path created in step 2) and snapshot agent security (point it to the Windows user created in step 3).
• Generate snapshot by right-clicking the publication in SQL Server Management Studio -> View Snapshot Agent Status -> Start. Wait for it to finish successfully. Use Replication Monitor (sqlmonitor.exe) -> Publication -> Agents tab to debug any issues.
• Connect to the subscriber server
• Create a new empty database (or restore a backup previously taken on the publisher - but keep in mind that subscriber databases created from a backup may fail if you have constraints between different articles in the publication, i.e. foreign keys).
• Run sp_addmergepullsubscription to create an anonymous pull subscription on the newly created subscriber database
• Ensure that the subscriber (or, more specifically, the Windows user that Replication Merge Agent will be running as) has access to the Snapshot directory/UNC share on the publisher/distributor; alternatively you can copy the snapshot (unc) files to a local directory on the subscriber - in that case be sure to specify AltSnapshotFolder for the Replication Merge Agent when you perform the initial sync
• Run the Replication Merge Agent on the subscriber to deliver the initial snapshot and perform the first synchronisation; I understand there is a way to do it through SQL Server Management Studio, but personally I usually call replmerg.exe directly. For SQL Server 2014 you will find it in C:\Program Files\Microsoft SQL Server\120\COM\replmerg.exe (on the subscriber).
• Keep calling replmerg.exe at regular intervals to keep the data and schema changes flowing between your publisher and subscriber.
• Don't forget to log replmerg.exe output as replication history seen in Replication Monitor on the distributor is nowhere near as accurate as what you get from replmerg.exe on the subscriber.

The above roughly describes the process where the subscriber can connect directly to the SQL Server distributor instance. If you are planning on using Web Synchronization to connect to the distributor, that is an even wilder beast to tame (especially with IIS 7 or above), and I will leave that for another time.

Answer 2

If you are looking for an alternative to transaction/merge replication you can use Service Broker as transport and write your own data transfer based on stored procedures and triggers. It is not very hard in your case (one-way). We use that kind of data transfer on production - it is easy to filter table data for "subsribers" without having to reinitialize subscriptions and it is possible to have sequentional data transfer with Service Broker. Also it is guaranteed data transfer - even if no connection service broker will deliver messages after connection is up. Service broker works ok when you have one paid instance of SQL Server and others can be Express.