Hi,

I would like to know, which is the best method for data transfer across two different SQL servers. My requirement is, to get data from one server and populate tables on the other. is it okay if I use trigger on the first Server's table? Data flow is only one way. From first server to second.

thanks,

Arin

Hi

The method I use is to populate a set of staging tables from the triggers. I have a DTS set up to transfer the data from Server1 (staging tables) to Server 2 (staging tables). Once the data is in Server2 staging tables I execute SPs to insert the data into the 'real' tables.

The main advantage of this is that the DTS is run on a schedule out of hours so if large amounts of data is transferred then it does not affect performance. I also have a lot of validation to perform so I can do this in the SPs before I populate the 'real' tables.

This is 1 method out of many ways to transfer the data and I hope it helps give you an idea on how to tranfer your data.

Nickie