Periodically transmit data from a partner

Question

I was instructed to implement a system to continuously receive a large amount of data from a partner. The data scheme on the partner side is different from ours, so some conversion should occur when the data is received and imported into our database.

What is the best approach to solve this problem?

I have some control over both sides of the equation, i.e. if we decide to use WCF, our partner will execute this service. But it is important to limit the amount of programming that should take place on the side of partners as much as possible. Ideally, they would use some SQL Server function to export their data, and then let's talk to the rest.

Other relevant factors:

• SQL Server is used on both sides (2008 on our side, unknown to the partner).
• .NET 4 and / or everything that comes out of the box from Microsoft, without third-party products.
• Transmission is one way, i.e. from a partner to us.
• The amount of data is about tens of thousands of updated records / objects transmitted daily.
• The data model is quite complex, with several tables and relationships.
• The decision should be tolerant to changes on the part of partners (we do not know when and how they change their model).
• Maintaining health and reliability is more important than performance, that is, we do not need the latest data, but the service must be easily modified and it cannot fail / stop.

I ask this question because I overflowed, but the number of ways you can do this. As a .NET developer, Im is leaning toward implementing WCF, or perhaps even some trivial xml-based exchange. But I know that Biztalk is likely to put the score here, and SQL Server may have some great features that I don't know.

Any pointers, ideas and suggestions are welcome!

Answer 1

In addition to Will's suggestions, you can take a look at SSIS (SQL Server Integration Services). With this, you can export to an FTP site and then import it from the other side.

Answer 2

If you want to get a full copy of your database, sending logs is a good option, otherwise check replication , which gives you finer control over what is copied.

You can copy data as is and work with the conversion logic on your side of the wire.

Answer 3

Perhaps this is how I do it:

• Maintain a local copy of your partner’s database. Let's call this setting because you are transforming the data here. Use Data Compare (a feature of VS 2010 that you can automatically automate) or Replication to achieve this. I'm not sure that Replication launches CDC, but Data Compare surely does.
• Enable data modification in the staging database. This only gives you modified records, so you can selectively convert data and update your database.
• Create and deploy an SSIS job that scans CDC tables, converts data, and uploads it to your database.

As far as I know, Data Compare works well when changing a schema (it ignores schema changes). CDC also works well, all you have to do is create a second capture instance if there is a schema change and delete the first one.