Our company has three servers. One server is situated at the head office, and the other two servers are located 150 miles away. These servers communicate only through a dial-up connection. I want to use Data Transformation Services (DTS) to update data in one of the remote servers while the main server is in replication mode. Under these conditions, can I use DTS to enter approximately 10,000 records, one by one, using customized software?
Technically, you can use DTS to update your data through a dial-up connection. However, the amount of network bandwidth available compared to the amount of data you want to transfer might limit your efforts. If you have the required bandwidth (or time to transfer the data), you can push the data in a compressed file to the remote servers by using FTP or a Copy command in a batch file. Next, you can decompress, then load the data by using BULK INSERT or DTS. If you need a more data-aware solution, and if you need to be able to make regular updates at any one of the three nodes, you should look into setting up merge replication. You would designate one of the servers as the publisher and the other two as subscribers. You could then insert rows at any one of the nodes and configure the replication to propagate the inserts to the other two nodes. Because you perform this merge replication over a dial-up connection whose bandwidth might cause a bottleneck, we advise you to test this configuration for performance and connection reliability.