title: Best way to sync remote SQL Server database with local SQL Server Compact database?
body: <p>I realise this is a much discussed topic but all the suggestions I see seem to involve direct access to the SQL Server which in our instance is not ideal.</p>
  
  <p>Our scenario is a remote SQL Server database with (say) 100 tables. We are developing a lightweight desktop application which will use an SQL Server Compact database and sync a subset of (say) 20 tables with the remote server periodically.</p>
  
  <p>I would like to have control over how the replication occurs because speed is a major issue for us since the remote server is 1000's of miles away.</p>
  
  <p>Also I don't need to sync all the records in each table - only those relevant to each user.</p>
  
  <p>I quite like the <a href="http://msdn.microsoft.com/en-us/library/bb522522%28v=sql.105%29.aspx" rel="noreferrer">SQL Merge</a> facility however it requires that the client be connected to the remote SQL Server. This is currently not possible and we were thinking of interfacing to the remote server through a web service accessed through our application or some other method.</p>
  
  <p>Any suggestions welcome.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Just to clarify, internet connection will be intermittent, that's the main reason why we need to sync the two databases.</p>