ETL table from one SQL Server to another SQL Server

Question

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?

• MBuschi: I would say SSIS package but you don't like it...
• J.D.: 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.
• xhr489: @J.D.: with a columnstore index it is around 30 GB in the source.
• J.D.: 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.
• David Browne - Microsoft: 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/…

Answer 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.

• xhr489: Can you add some information on why SSIS would be faster then the options mentioned in my question?
• xhr489: If bottleneck is the network speed then I don't understand why SSIS is so superior...
• xhr489: Also the source is an AG. I guess it is possible right?
• J.D.: @xhr489 Yes it's possible to configure a database in an Availability Group as a Publisher in Replication.
• J.D.: 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.