Data transfer/synchronization for Distributed Service Broker application

Question

Hi

What we are doing?

We are developing a distributed service broker application that processes data on different databases residing on different servers. These service broker applications are deployed are interdependent. These service broker applications process business routines (TSQL statements) on receiving a message from an external application. All the data generated as a result of processing service broker applications are saved locally on each servers.

What we want to achieve?

Synchronize/transfer data to a single Master database. There may be 10-15 huge tables and may not be all the tables in the database.

Questions

1. Which is a more viable technology SSIS or replication and why?

2. Are there any other data transfer alternatives to SSIS or replication for the above mentioned scenario?

Answer 1

What is the SQL Server version?

Farooq Mahmud | Support Escalation Engineer | Microsoft Health Solutions Group

Answer 2

  Farooq thanks for the reply. We are using SQL Server 2008.

Answer 3

Hi Jagadish_K,

Regarding to your description, seems you want to synchronize part of tables in the database, because the replication does not give you many options to apply complex logic to the data being replicated. You can specify a WHERE clause to filter the publication, and that will affect which rows are replicated from the source table. If this is insufficient for your needs, you should probably go for SSIS. I think SSIS would be the better option.

The SSIS you can do a significant data manipulation and transformation from the source to the destination databases. However there is no change tracking - you have to track the changes yourself and then extract, transform and push the data.

With replication it does change tracking, can be near real time, but you are limited in how you can transform the data from the publisher to the subscriber.

For more information about SSIS, please link to SSIS forums for help.

---

Regards, Amber zhang

Answer 4

  Hi Farooq Mahmud

 Thanks for your reply. We wan to consolidate the output data that is saved in each of these processing nodes to a single database. So once the processing ends on each database we want to transfer the data without any transformation to a single database in real-time. Since we are processing data on many servers independently we feel that consolidating data to a single server using SSIS is inefficient. As you suggested replication might be a better way of doing it. Let us know what you think.

Answer 5

Hi check this could help you.

http://www2.sys-con.com/itsg/virtualcd/java/archives/0408/tan/index.html