Copy data between two linked servers

Question

I have two MSSQL Server instances and one is on DMZ so it has not access to the inside network.

So SERVER1 (On the inside of firewall) pushes today data to SERVER2 (on DMZ).

How do i get better performance in shuffling large amount of rows to tables on SERVER2? Today when doing this.

INSERT INTO SERVER2.DB.DBO.TABLE SELECT something from SERVER1Table

Its very slow and time consuming and not to say the least it locks the table for outside users.

The thing is that SERVER2 is a webserver that is a portal for customers to log in and check certain information.

Or am I almost pushed into the choice of using pull-data query? So that I need to open up the MSSQL port through the firewall and let the DMZ SERVER2 pull data from SERVER1?

Answer 1

SQL Server Integration Services (SSIS) should be right tool for the job...

The tool's purpose is to transfer and transform data, so they are really good at this.

You can easily extend your packages and develop simple tasks like the one you mention in minutes.

Answer 2

Ssis will likely take a similar amount of time. You should optimise your architecture. Try adding two more steps to minimise locking:

1. Copy to a new local table very quickly on server, using any filtering.
2. Copy to server2 on a similarly named new table
3. Copy from the new table on server2 to the final destination.

This way the slowest step occurs between two tables completely disconnected from affecting users.