Performance of OPENROWSET to copy data from one server to another server

Question

1. Can we write a query like

   INSER TNTO Customers SELECT * FROM OPENROWSET( 'SQLNCLI', Remote Server Settings , 'SELECT * FROM Customers)

   Remote Server is on some other server over internet public IP.

2. Will this be faster compared to SqlBulkCopy?

I need to create a slave database which can regularly copy data from server accessible through public IP

Does anyone have any idea on the timeouts and failure over large value tables?

Answer 1

Yes, OPENROWSET can be used but it is intended as a one-time method of getting remote data. If you plan to do this often, use linked servers instead.

Depending on your needs, you might consider db mirroring or log shipping to replicate data.

Answer 2

Depending on your needs, you can also consider replication to move your data over.

You can read more detail here http://msdn.microsoft.com/en-us/library/ms151319.aspx

Answer 3

If you are on SQL Server 2008, then you can try Change Data Capture which will allow you to pick up a Delta of changes every time.

http://msdn.microsoft.com/en-us/library/cc645937.aspx