0

I have master SQL server with DB Central and a lot of satellite SQL servers with DB Client. I need to collect data from log tables(LogTable) on Client(each client has own ID in log table) to one big table on Central(LogTableCentral).

  • Data must go only from Client to Central
  • On each Client I want to have only data for this Client
  • I need solution with minimal amount of work on client side because of count of clients
  • Central is MS SQL server Enterprise, Clients are MS SQL server 2005, 2008

Thanks a lot

EDIT: data can be collected periodically(for example: every day at 01:00)

drizzt
  • 2,756
  • 6
  • 27
  • 41
  • Is your requirement to replicate the data immediate (as it is recorded), or is it acceptable to have some sort of scheduled process to do this periodically? – James Wiseman Nov 15 '10 at 10:47

1 Answers1

0

As it is done periodically, have you considered using SSIS for this task?

You can add multiple data connections, and then have a series of data sources (each connected to one of the connections) feeding into a data destination (also connected to one of the data sources)

You could then schedule this as part of a SQL Agent Job. Something like this:

alt text

James Wiseman
  • 29,946
  • 17
  • 95
  • 158
  • Good advice thanks(I never used SSIS), but with replication I do not need to handle new inserts, updates, deletes....all this thinks handles replication. SSIS handles this for me too, if yes how can I setup it? – drizzt Nov 15 '10 at 16:07
  • Not sure, I must admit, I was just envisaging a full-pull of the data, which i realise is probably not feasible for you. I've never used SSIS to handle I/U/D in this way. It may be worth looking at the merge task for this. Regardless, the agent job may be useful for scheduling any replication tasks, whatever form they take, – James Wiseman Nov 16 '10 at 11:45