I'm looking to update several tables between two servers daily to have a replica of the data available.
My question is, what is the least resource heavy and most effective method to implement this?
As a little more background, the operation will be completed out of core hours every day. I understand there's several methods to complete the operation including:
- SSIS Package to delete -> bulk update the data (accessing the data through a linked server instance)
- Replication between the two sources (my understanding is that they will be in sync therefore wouldn't this be more resource heavy?)
There's not a huge amount of data within the tables (largest table currently stands at c70k records and the growth of the tables is relatively slow).
Both servers are running SQL Server 2008 R2 Standard.