Replicating a database using the Windows Briefcase

Microsoft SQL Server Replication

Replication is an integral component of SQL Server. It is based on a transaction log, which allows logged transactions associated with tables that have been marked for replication to be distributed automatically. All changes to a replicated table are applied asynchronously to destination tables residing on multiple destination servers so that the main database processes can continue.

Figure 6.21 SQL Server replication

The goals of SQL Server replication are:

• Continuous distribution of transactions.
• A minimum amount of time for the replication process (minimal transaction latency).
• Maximum concurrency.
• Transaction consistency.

SQL Server replication features include:

• Replication at the row level, called horizontal synchronization , or at the column level, called vertical synchronization .
• Replication to different (heterogeneous) ODBC-compliant data sources such as Microsoft Access and ORACLE.
• Fault-tolerant design.

For data to be made available for replication, a publication must be created. The following publisher/subscriber metaphor describes how SQL Server replication works.

The Publisher/Subscriber Metaphor

In a publishing environment, a publisher disseminates information and a subscriber receives information. In SQL Server replication, a server can perform any combination of these roles:

• Publisher

  Maintains source databases and holds data available for replication.

• Distributor

  Receives and stores changes and forwards the changes to subscribers.

• Subscriber

  Receives data modifications.

Publication Server

A publication server maintains publication databases, makes published data from those databases available for replication, and sends copies of all changes to the published data to the distribution server.

Distribution Server

The distribution server contains the distribution database, also known as the store-and-forward database. The distribution server receives changes to the published data, stores the changes in the distribution database, and forwards the changes to the appropriate subscription servers. The publisher and the distributor can be on the same server, but in heavy use the distributor should be located on its own server.

Subscription Server

The subscription server holds a copy of the data and receives changes from the modified published data. Published data moves in only one directionfrom the publishing server to the subscribing servers. Data should be modified only on the publishing server. The replicated data copied to a subscribing server should be treated as read-only and should not be updated on the subscriber.

Synchronization

Synchronization allows entire databases or individual tables to be synchronized before you begin to replicate data modifications. New subscribers can be synchronized at time intervals defined by an administrator. A scheduled synchronization event for tables or databases must be the first step for initiating replication. This is accomplished by providing a snapshot of the table schema and the data in the table to ensure data integrity. Then, modifications to the published data can be replicated to the subscribing servers.

Lesson Summary

Database replication allows two or more users to work on their own copy of a database at the same time without changing the contents of the original database. Synchronization must take place periodically to update the original. Microsoft Access and SQL Server databases can be replicated.