1

This is a very common problem, faced by virtually every DBA who has to responsibilities to both application and BI teams. Consider the following:

  1. You have two T-SQL servers, Production and Reporting.
  2. 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.
  3. 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.

2
  • You missed transactional replication
    – SergeyA
    Nov 20, 2023 at 15:01
  • @SergeyA And I hope I've missed many more.
    – J. Mini
    Nov 20, 2023 at 16:03

1 Answer 1

1

From the information you provided, I would encourage you to look into Azure Data Factory if you are cloud friendly. It would allow you to only send the rows you need from one server to another (assuming you have logic in place that allows you to track changes in the source) Similar to SSIS, but a little more modern, and allows you to build ETL pipelines.

As another user mentioned, transactional replication may work as well. You can choose which columns / fields you are replicating.

Other options you can look into but may be overkill would be: log shipping and always on availability groups both of these are replication rather than ETL, but would allow you to do all of your transformation on the destination server. (bonus, you have a backup DB if production suddenly goes down)

6
  • Good start. I'm very AWS friendly.
    – J. Mini
    Nov 20, 2023 at 19:24
  • If you are AWS friendly, I think the corresponding AWS software is AWS Glue. I haven't used it so I can't speak to anything about it, but I wanted to mention just in case.
    – Dagny
    Nov 20, 2023 at 19:33
  • Interestingly, Brent Ozar just covered Availability Groups. If he's right about them, then I'm happy to rule them out.
    – J. Mini
    Nov 20, 2023 at 21:35
  • 1
    Brent was complaining about the number of changes made to AGs from the POV of a trainer. But one of the latest changes is exactly what you might use in this scenario: Read Scale AGs. You don't want to have to put your DW in the same cluster as your production server. And that's the feature that enables that. So you can have a read-only replica of your production database on your DW database as the source of your ETL. learn.microsoft.com/en-us/sql/database-engine/… Nov 22, 2023 at 0:52
  • Can you expand on the Azure Data Factory example? How would you do cross-server upserts there?
    – J. Mini
    Dec 29, 2023 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.