Moving data between Sql2000 servers

[yvasiliev]

There are two SQL2000 servers- first (Production) one is collecting data (up to 50,000 records per poll interval, let say, 15 minutes), second (Analyser) one is working with this data. I am looking a best approach of periodically (once in above mentioned poll interval) moving data from first server to another one. Thanks

[MAK]

Choices

1. DTS and schaeduled job
2. Transactional replication
3. BCP and Sheduled job
4. Linked server and transfer data through scheduled job and insert statement (distributed query)
5. Snapshot replciation - nightly
6. Backup and restore


All depends on how and when you want to do the data transfer.

[yvasiliev]

Thanks for answer but I was wondering if you could point me some details. First, I don't want to use any kind of replication cause my intention is to transfer data in 1-2 second's batch and not to keep connection between servers for the whole poll interval. Second, I am not sure that "Backup and Restore" for 50,000 record's SELECT is a good idea. Anyway, I decided to create scheduled job (and you pointed the same option) with only one step- stored procedure (it's OK- I've tested already from Query Analyzer) to transfer data from one linked server-Production (Target server- TSX) to Analyzer (Master server- MSX). Job was created on MSX- I pointed "Target multiple servers" and "Subscriber" DB on MSX as "The database to use when using a Transact-SQL". Then I started the job and received an error:"There is no Subscriber DB on TSX" ! What I've done wrong, what DB I need to point-Subscriber from MSX where I would like to put data and where stored procedure is located or Publisher DB on TSX where data will be moved from? Thanks in advance.

[MAK]

Put target as local server, since you are running as a stored procedure through linked server.