SQL Server™ 2005 Bible

Chapter 39. Replicating Data

In This Chapter

• Using replication effectively

• Understanding the Microsoft replication model

• Creating the four replication types

• Understanding the SQL Server 2005 replication differences

• Defining publishers and subscribers

In any environment where the database administrator (DBA) has to manage more than one SQL Server, the DBA will sooner or later have to distribute data from one SQL Server to another SQL Server, or another RDBMS. SQL Server has a rich set of options to distribute data; SQL Server 2005 has added several options to the mix. Unlike other RDBMS Microsoft solutions to distribute data, are all free; but their richness and robustness make them invaluable components to any distribution data solution.

Why Replicate Data?

There are several reasons why a DBA might want to replicate data:

• Fault tolerance/disaster recovery

• Application requirements

• Performance gains

• Data distribution

Fault Tolerance/Disaster Recovery

Fault tolerance refers to the degree to which a system can withstand errors or problems. Faults generally fall into two categories:

• Local: These are confined to the server or server enclosure, e.g., hardware failure, OS or SQL Server crash, human actions, and so on.

• Regional: These are major disruptions such as earthquake, power failure, terrorist attack, and so on.

In general, most clients will be forgiving of a regional event. A client can typically handle a regional event such as the loss of the power grid that also takes their systems offline. However, ...