I have two MS SQL servers at different locations on the WAN. At the moment I use LINKED servers to copy table data from one to the other, but performance is toilet. It takes about 10 minutes to copy a 40 field table with 800 rows.
What is the best 'simple' method of copying/replacing table data on a different SQL server. This solution must be scriptable as it needs to happen every hour. The master server is SQL 2000, the linked server is SQL 2005.
I appreciated any help given.
Andrew
I don't know what exactly you're trying to achieve, but is replication not an option ?
write on one server (master) for example an export to a file, and use the bulkinsert command line to import files. both solutions could be easily used via command line and also via scripting!
Use the Import/Export Wizard to copy tables from one server to another. This is the quickest approach.
Put the tables you want to copy in a separate filegroup, then detach the database from the originating server, copy the files over and reattach on both servers. You might be able to use the shadow copy functions in windows to take a snapshot from the originating server.
It sounds like your best approach is to get a networking guy to look at your WAN link.
40 fields * 800 rows doesn't equate to a whole lot of data unless you have some binary fields.
Three questions you need to ask yourself is: how big is the data? How big is your pipe? How much of that pipe can you actually use?
For example, if you have an OC3 pipe, but it's at 99% capacity then it doesn't matter what mechanism you use to transfer the data. It's going to be slow.
I'd use SSIS on the 2005 box to pull the data from the 2000 box. Then schedule the SSIS package to run every hour.