MsSql server 2008 r2 Data Syncronization

Question

I'm working on a project in C#.NET (WPF) with 2 SQL Server 2008 R2 databases. I need to update new/changed data from local db to online db. Client system has low bandwidth connection. So I need a solution to upload a file to sync.

Can anyone tell me how I can do this? Programming example will be more beneficial for me.

Answer 1

Learn about the following:

• SQL Replication
  • Supports unidirectional or bidirectional synchronization
• SSIS
  • Lets you define the mappings of the data, as well as transformations, and attach other code to the process easily

• Linked-servers

  • Allows you to query databases and tables on remote servers as though they are part of the local database. Very easy to setup (just call exec sp_addlinkedserver) and once defined uses nothing but plain old SQL

  
  Here is a simple tutorial about how to create a linked server. After creating linked server, we can query it as follows:

  select * from [LinkedServerName].[DatabaseName].[schema].[TableName]
  

If you need this to occur on a button-click or so, then I'd suggest you use linked servers within a stored procedure--they're the simplest option. SSIS would also be suitable, you'd need to execute the package on the button-click.