This is a very common problem, faced by virtually every DBA who has to responsibilities to both application and BI teams. Consider the following:
- You have two T-SQL servers, Production and Reporting.
- Every day, at 6am, you want to copy new data from Production to Reporting. There may be filters or some other ETL done in this process.
- You don't want to copy any more data than you need to, so ideally you want a
MERGE
of some sort.
What are the idiomatic solutions to this problem?
In my own research, I have found:
- Linked Servers - They are very slow when doing upserts and are prone to sending your whole table over the network when it only needed a few rows.
- SSIS - Painfully unportable, very old, and forces you to jump through extra hoops like putting staging tables on both servers.
I hope that there is something better.