Moving Data Between Servers
Replication is not the only way to move data between servers. Following are several alternatives, each with its own pros and cons:
- bcp utility
- Distributed transactions
- Copy Database Wizard
- Backup and restore
- Log shipping and database mirroring
The following sections describe these options in more detail.
Bulk Copy Program
Bulk copy program (bcp) is a command-line tool that you can use to send tabular data to the file system and from there to a remote server. Although it can be scripted, it is slower than replication processes, requires significant work to set up, and the DBA/developer needs to ensure that all objects are in place on the destination server. For example, all tables, views, stored procedures, and functions must be on the destination server. There is no provision for change tracking. In other words, bcp can't tell what has changed in the data and sends only the changes to the destination server. The solution requires change tracking — a way to determine what has been inserted/updated/deleted on the source server. These may involve using Change Data Capture or the Change Tracking features.
Think of SSIS as a programmatic interface to a high-performance bcp utility. It can be faster than bcp. As with bcp, it requires that the DBA/developer place all objects on the destination server, and there is no provision for change tracking.
Distributed transactions normally involve using Microsoft Distributed ...