Question

snabhi on Wed, 31 Jul 2019 10:44:09


Hello All,

We are using merge replication to replicate the data between our application and Windows Server 2008/2012. As part of platform upgrade, we need to achieve the same in Server 2016 also. 

But I'm not able to install SQL Server Compact Server Tools in Windows Server 2016. I learnt that it is not supported in Server 2016. 

So, now how can I support the merge replication, which is currently supported by Server 2012, in Server 2016 (so that I need not to change my application code)? Any ways?

Replies

Alberto P. Silva on Thu, 01 Aug 2019 13:24:23


Hi,
At a first glance I understood you were planning to move the database to a SQL Server 2016 instance, so I'll leave below my first response below in italic in case it may help anyone moving to SQL Server 2016.

Regarding installing the SQL Server Compact tools on Windows Server 2016, you'll need to trick the setup installer, because it won't recognize IIS as being installed, due to a bug in the IIS version checking mechanism. At your own risk, open regedit and change the "MajorVersion" in the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\InetStp] key from dword:0000000a to dword:00000008 then install the server tools. Set the value mentioned above back to its original value, dword:0000000a.

You will need to keep an instance with SQL Server 2012 to be able to user Merge Replication with SQL Server CE.
I you really need to push the database to a SQL Server 2016 instance, I would suggest you to try the following:
1. publish the database on the SQL Server 2016 instance (not necessarily Merge Replication, check the other options) and subscribe it from the SQL Server 2012 instance. You won't need to install the SQL Server Compact tools on the SQL Server 2016 machine, and the database can be kept in sync with the SQL Server Agent
2. on the SQL Server 2012 instance, publish the database subscribed in the previous step, so it can be synched with SQL Server Compact. It may give you some headaches to implement, but it should work, I've done it in the past

snabhi on Fri, 02 Aug 2019 04:22:46


Hello Alberto,

Thank you for your reply.

Your solution of registry key modification really worked out and removed the error. But, I have one more error while installing SQL Server Compact 3.5 SP2 in Server 2016 i.e., 

"You must first install the Replication components for SQL Server 2005 or SQL Server 2008." when I click More.. button beside "SQL Server requirement".

Is there anyway to get around this?

Also, Is there anyway to achieve the kind of merger replication in Windows Server 2016 (I want to avoid dependency on Server 2012 since it may also go out of its life cycle, but I will try your option though)?

Alberto P. Silva on Wed, 07 Aug 2019 16:06:01


What we have working in customers is the combination of SQL Server 2012 (SP1, latest CU) and Windows Server 2016 (tricking the setup) in the same server, to be honest it was installed by a coworker, not myself. 

I'm really not sure about the specifics of your two questions, but do not expect any version of SQL Server post 2012 to support replication with SQL Server Compact. Also, there's no warranty that Windows Server v. next to allow to install the SQL Server Compact Server Tools.

It's really sad that Microsoft has pulled the plug on SQL Server Compact - would have loved to use an Android version with Merge Replication - so you must start considering other options. We ended up creating our own sync mechanism for our apps, which is not as powerful or as optimized as Merge Replication is, but with proper Android devices runs fine with SQLite on the device and SQL Server on the server.