My question is: What is the best way to port the data back and forth from our client’s local database to/from our webserver database?
An explanation of the problem:
Our clients run our software against their local copy of our SQL Server 2008 R2 database. We routinely (once a day, middle of the night) need to combine fields stored in multiple tables for each of these clients (i.e. a view) and send that information over the internet to a SQL Server 2008 R2 database which we will host on our webserver. Each of our clients may have tens-of-thousands of records which we will need to port to our webserver database. This information will allow our client’s customers to make payments and place orders. We will store these transactions in one or more tables in our webserver database. At regular intervals we need to push these transaction records back to our client’s local database. The client will then process these transactions and update the records which we push up to our webserver database at night.
I am a C# programmer in a very small shop. My first thought was to write a windows service to control the porting of data back and forth. This would require installing the service on each of our client’s server. I am concerned with our ability to easily maintain and extend that service. In particular, when we decide to port more data back and forth this would require updating the service at each client site. Given the size of our shop, that would become a serious challenge.
I would prefer to manage this process through SQL Server, preferably at the SQL server instance on our webserver. However, we have no one with extensive knowledge of SQL Server. (I am the SQL Server guru here, and I know just enough to be dangerous.) Some of our clients are very small companies and only have SQL Server express installed on their server. We did some experiments with replication, but never found a way to make it wok reliably over the internet, especially with SQL Server express.
I have read some about SSIS, Linked Servers, Service Broker, and 3rd party tools such as RedGate’s SQL Compare. I am uncertain which, if any, of these options would best suit our needs and have not found clear examples showing how to make use of each.
Any guidance on this issue would be very much appreciated. It would be particularly helpful if you can point me to relevant examples showing how to do what I have described above.
Just fast,
one option is to use the MS Sync Framework - it does as I can see exactly what you need, though not sure of the specifics in your case.
hope this helps
