Introducing Replication

Microsoft® SQL Server™ 2000 replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases for consistency.

Using replication, you can distribute data to different locations, to remote or mobile users over a local area network, using a dial-up connection, and over the Internet. Replication also allows you to enhance application performance, physically separate data based on how it is used (for example, to separate online transaction processing (OLTP) and decision support systems), or distribute database processing across multiple servers.

Benefits of Replication

Replication offers various benefits depending on the type of replication and the options you choose, but the common benefit of SQL Server 2000 replication is the availability of data when and where it is needed.

Other benefits include:

• Allowing multiple sites to keep copies of the same data. This is useful when multiple sites need to read the same data or need separate servers for reporting applications.
  
  
• Separating OLTP applications from read-intensive applications such as online analytical processing (OLAP) databases, data marts, or data warehouses.
  
  
• Allowing greater autonomy. Users can work with copies of data while disconnected and then propagate changes they make to other databases when they are connected.
  
  
• Scale out of data to be browsed, such as browsing data using Web-based applications.
  
  
• Increasing aggregate read performance.
  
  
• Bringing data closer to individuals or groups. This helps to reduce conflicts based on multiple user data modifications and queries because data can be distributed throughout the network, and you can partition data based on the needs of different business units or users.
  
  
• Using replication as part of a customized standby server strategy. Replication is one choice for standby server strategy. Other choices in SQL Server 2000 include log shipping and failover clustering, which provide copies of data in case of server failure.

When to Use Replication

With organizations supporting diverse hardware and software applications in distributed environments, it becomes necessary to store data redundantly. Moreover, different applications have different needs for autonomy and data consistency.

Replication is a solution for a distributed data environment when you need to:

• Copy and distribute data to one or more sites.
  
  
• Distribute copies of data on a scheduled basis.
  
  
• Distribute data changes to other servers.
  
  
• Allow multiple users and sites to make changes then merge the data modifications together, potentially identifying and resolving conflicts.
  
  
• Build data applications that need to be used in online and offline environments.
  
  
• Build Web applications where users can browse large volumes of data.
  
  
• Optionally make changes at subscribing sites that are transparently under transactional control of the Publisher.