3

We have an application that requires our customers to have a SQL server instance on site. At their request, the application needs to synchronize the data in their database with a copy in our datacenter.

We're using .Net 3.5 SP1. We need to synchronize the data exactly, including IDENTITY columns.

We'd prefer to use something like LINQ to SQL that would let us make some simple select and insert/update calls against mapped entities. However, the IDENTITY columns seem to be a problem with LINQ and similar approaches.

We can do this all with built-up SQL statements and turn IDENTITY INSERT on / off as needed, but I'd prefer a more elegant solution.

Thanks!

** Edit - We DO need to write our own solution, and we do need to use .Net 3.5 SP1 to do it. I won't spend your time explaining all the reasons why, but please limit suggestions to options within the .Net playground.

bopapa_1979
  • 8,949
  • 10
  • 51
  • 76
  • Is this a VPN connection or a public connection? – Maess Nov 16 '11 at 21:26
  • 1
    There are several things you can do. Have a look at Replication (http://msdn.microsoft.com/en-us/library/ms151198.aspx) and Linked Servers (http://msdn.microsoft.com/en-us/library/ms188279.aspx). You might be able to get SQL Server to do all the work for you and not require your application to do anything other then tell SQL Server to start working on the process. With a Linked Server you might be able to run your own SQL Statements to do the inserts and updates. – David Parvin Nov 16 '11 at 21:46
  • @Maess - For the sake of problem solving you can considerate a private connection. I can deal with all the transport issues. – bopapa_1979 Nov 16 '11 at 22:59
  • @DavidParvin - We cannot reliably use SQL server facilitites to do this. The customer servers are not under our jurisdiction, and we cannot dictate their configurationi to the necessary degree. The question is also vastly simplified from the real world scenario. We need a .Net solution. I'd rather not waste your time explaining all the reasons why. Thanks for the suggestions though! – bopapa_1979 Nov 16 '11 at 23:01
  • Have you had a look at the Microsoft Sync Framework (http://msdn.microsoft.com/en-us/sync/bb736753)? I looked a little at it for some stuff I am planning in the future and it might be something that will fit your uses as well. – David Parvin Nov 17 '11 at 18:15
  • @DavidParvin - I had not looked at the MS Sync Framework until you mentioned it. So far that looks like the best option, though not, strictly speaking, simple to set up. I'll spend some more time with it and post back here if we end up using that approach. Thanks! – bopapa_1979 Nov 18 '11 at 00:48

2 Answers2

1

Microsoft Sync Framework can be your solution. This is framework description from Microsoft:

Microsoft Sync Framework is a data synchronization platform from Microsoft that can be used to synchronize data across multiple data stores. Sync Framework includes a transport-agnostic architecture, into which data store-specific synchronization providers, modelled on the ADO.NET data provider API, can be plugged in.

Sync Framework is a comprehensive data synchronization solution that enables developers to build solutions that support synchronization of any database, on any data protocol over any network topology. msdn.microsoft.com

For your convinience providing link to good tutorial on the subject

Yuri
  • 2,820
  • 4
  • 28
  • 40
0

If it is just a couple of tables that need to be synchronized and there is not a lot of data in the tables (now and future) you could develop some sort of bulk copy from your servers and bulk insert routine on the customer's server.

Since you said you can't use SQL Server replication services or SSIS, then perhaps a backup/restore procedure could be written. You could take a scheduled backup of your database and make it available to calling applications which could then copy the backup, restore it to another instance on the customers server, then pull all data you need via any number of methods and it would exist locally on the customers servers.

Beyond that, I think you may be asking for a maintenance and synchronization nightmare if you can't base your solution on tools that are made to do this sort of thing.

Chris Townsend
  • 3,042
  • 27
  • 31