1

I would like to ETL a table from one instance of SQL Server to another instance of SQL Server. I do not want to create an SSIS package. I have tried

  1. to extract the data using an External Table on the destination (i.e. I use the External Table to create a local copy e.g. with SELECT INTO #Hello FROM MyExternalTable).
  2. Linked Server with using SELECT * INTO #HELLO FROM MyLinkedServer.MyDB.dbo.MyTable
  3. I have also used OPENROWSET.

They all run single threaded, which I can see from the execution plans. I have not used bcp but it seems that I first need to save the table to a file and then read it from the file and load it. So I do not think it is the correct tool for this task. What is the fastest approch to copy a table from one SQL Server instance to another SQL Server instance?

Improve this question
9
  • 1
    I would say SSIS package but you don't like it...
    – MBuschi
    Commented Oct 12, 2022 at 13:00
  • How big is the table in question?...as I doubt you'll see much difference between the 3 methods you suggested, if you're ETL'ing the entire table, since they all essentially are the same under the hood.
    – J.D.
    Commented Oct 12, 2022 at 13:17
  • @J.D.: with a columnstore index it is around 30 GB in the source.
    – xhr489
    Commented Oct 12, 2022 at 13:29
  • Yea in that case SSIS is probably the best option, otherwise you'd have to test each method you mentioned but I don't think you'll see much difference. What kind of Transformations do you need to apply?...maybe Replication could work.
    – J.D.
    Commented Oct 12, 2022 at 13:45
  • 1
    The fastest way is not ETL. Backup/Restore will be the fastest way to move the data, as it creates a physical copy of the database and doesn't uncompress/recompress the columnstore data. You could also create and seed a readable replica of the database. learn.microsoft.com/en-us/sql/database-engine/…
    – David Browne - Microsoft
    Commented Oct 12, 2022 at 18:57

1 Answer 1

Reset to default
1

As determined in the comments, since you don't need to apply any transformations, this is essentially just a table copy, then you may want to consider using Replication, especially for just a single table.

I'd recommend looking into one-directional Transactional Replication. It is available even on Standard Edition, and will keep the data up to date in near-realtime.

While the initial snapshot synchronization may take a while for a ~30 GB table, this may not even be the fastest methodology for copying the table once. But because it'll keep the data in sync as it changes, it may save you from having to do a full copy of the table again in the future. So it is arguably the most efficient solution in the long term.

Improve this answer
5
  • Can you add some information on why SSIS would be faster then the options mentioned in my question?
    – xhr489
    Commented Oct 12, 2022 at 16:13
  • If bottleneck is the network speed then I don't understand why SSIS is so superior...
    – xhr489
    Commented Oct 12, 2022 at 16:18
  • Also the source is an AG. I guess it is possible right?
    – xhr489
    Commented Oct 12, 2022 at 16:26
  • @xhr489 Yes it's possible to configure a database in an Availability Group as a Publisher in Replication.
    – J.D.
    Commented Oct 12, 2022 at 18:12
  • 1
    When I commented on SSIS, it wasn't considering physical bottlenecks like network, only software bottlenecks. SSIS is designed to be an ETL tool. Linked Servers, External Tables, and OPENROWSET are not. So from a tool perspective, SSIS is the most relevant for ETL. From a physical bottleneck perspective, there shouldn't be any difference among the four options, the network speed will still be a constant for each option, generally speaking. Even if it is your biggest bottleneck.
    – J.D.
    Commented Oct 12, 2022 at 18:24

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.