728
Part V: Enterprise Data Management
■ (^) 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 fi le system and from there to a remote server. Although it can be scripted, it is slower
than replication processes, requires signifi cant 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 Transaction
Coordinator (MS DTC). With a distributed transaction, the transaction is committed on the
source server and then on the destination server, and then the application can do the next
unit of work. (This is sometimes called a split write.) The application must be confi gured to
use distributed transactions, and the network connection must be stable and have ample
bandwidth; otherwise, the transactions fails. With distributed transactions, only changes
are “replicated.” The DBA/developer needs to place all tables (along with the initial data),
stored procedures, views, and functions on the destination server.
Triggers
Triggers are similar to distributed transactions. With distributed transactions, all application
code (for example, stored procedures, and sometimes ADO.NET code) must be rewritten for the
distributed transactions. With triggers, the replication logic is incorporated on the trigger.
And like distributed transactions, only changes are replicated. The DBA/developer needs to
place all tables (along with the initial data), stored procedures, views, and functions on the
destination server. There is also overhead with using triggers, especially over a network.
c28.indd 728c28.indd 728 7/31/2012 9:51:11 AM7/31/2012 9:51:11 AM
http://www.it-ebooks.info