Tranferring Data from multiple local SqlCe Databases to single hosted SQL Server

Question

I've created a WinForms Time Clock application which leverages SQLCe.  It logs the time in / time out of all the employees at a business.  I have a Windows Service that periodically (a few time a day) imports data (employee and schedule info) from another database (PostgreSQL).  Since the front end a WinForms app - therefore employees need to go to one computer to clock in / clock out.

I would like to make this application accessible via the web, but with all the different ways an technologies MS offers - I can't figure out what would be the best approch (MS Sync Services, WCF Data Services, SQL Azure, etc.).  So given the following thing below which I cannot change, what woudl be the best way to make this application more "cloud" friendly (accessible via the web)?

1.  The Employee/Schedule application (PostgreSQL) has to run on premise. 
2.  There will be different businesses using this solutions.  I'd like to have one website and 1 database to maintain (I don't want to have to maintain a separate database for each business)

So I guess my question is - if I have multiple local SqlCe databases at different businesses - each importing data from the local PostgreSql, and logging in/out times thoughout the day - what woudl be the best method to get that data up into the cloud effeciently so that I can serve up the same information via a website?

Answer 1

Hi JJRG2012,

Since SQL Compact edition is not supported by SQL Azure Data Sync Service, you may consider using Windows Azure Sync Service, which acts as a middle tire by using Windows Communication Foundation (WCF) to communicate synchronization commands and data between the client application and the SQL Azure database. For more information, please pay attention to: Windows Azure Sync Service Sample. And here is a thread for this type of usage: Problem Syncing from Sql Ce to Sql Azure.

---

Stephanie Lv

TechNet Community Support

Answer 2

Stephanie,

Thanks for the reply.  I've been reading up on Sync Services.  It seems that the use case for the sync services is to sync data from a centralized database downstream to the clients (1:M) - where all the data from the central db is identical on each client.  

What I would like to do is have all the clients (say 300 of them) which each have DIFFERENT data within their SQLCe db to send this information UPSTREAM to a central DB - to one database preferably (M:1).    I would like this data to be accessible via the web.  (i.e.  Business A logs in to see only business A's clock in/out data)  Is this possible?  Could I have a central Clock_In_Clock_Out table and have a business_ID column.  Could each SqlCe sync their data with the Clock_In_Clock_Out table?  I assume this cannot work, and rather you would have to create a table for each (SqlCe db) business.   

I'm guessing that I can simply have each client upload a .CSV and my web application can ingest that into the database - but that seems really old school and there probably is a better way.  

Answer 3

HI JJRG2012,

Did you ever get any further with this? I am trying to do pretty much the same thing.

Jim