Microsoft SQL Server 2012 Bible

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
• SSIS
• Distributed transactions
• Triggers
• 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.

SSIS

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

Distributed transactions normally involve using Microsoft Distributed ...