Lesson 4: Physical Replication Models

[Previous] [Next]

This lesson presents the physical replication models used in SQL Server, drawing a connection between the replication types (snapshot, transactional, and merge) and the physically implemented replication models. The lesson concludes with examples of each of these models.

After this lesson, you will be able to

• Describe the physical replication models used in SQL Server

Estimated lesson time: 25 minutes

Overview of the Replication Models

The basic physical replication models shown in Figure 15.10 illustrate how the server replication roles can be implemented in replication. Each of these models is discussed in turn in this section.

Figure 15.10 Physical replication models

The Central Publisher/Distributor Model

In the central Publisher/Distributor model (the SQL Server default), one or two servers are defined as the Publisher/Distributor. The Publisher/Distributor publishes and distributes data to any number of servers that are set up as Subscribers.

The Publisher and the Distributor can reside on one server or on separate servers. In either case, the publication server is the primary owner or source of all replicated data. Typically, the distribution server stores data before the data is replicated to the subscribing servers. Data that is received at subscription sites is intended to be read-only. Administrators must ensure that only the SELECT permission is allowed on Subscriber tables.

If the Publisher and the Distributor are established on separate servers, this model offloads much of the replication work from the Publisher to the Distributor.

The Central Subscriber/Multiple Publishers Model

In the central Subscriber/multiple Publishers model, multiple Publishers replicate data to a single Subscriber. This model addresses the need for consolidating data at a centralized site and providing the local sites with local data only. Because multiple Publishers are writing to the same subscription table, it is important to ensure that all data has a unique local owner so that another Publisher does not overwrite it. You can accomplish this by filtering the data horizontally.

The Multiple Publishers/Multiple Subscribers Model

In the multiple Publishers/multiple Subscribers model, multiple publication servers and multiple subscription servers each potentially play a dual role. This model is the closest implementation to fully distributed data processing. You must be careful when you design both the schema and update types to ensure that an adequate level of data consistency exists at all sites. Horizontal filtering or partitioning will be necessary to achieve this.

Combining Replication Models and Types

For each publication, you can use any of the replication models with any of the replication types. The following are some facts about the interaction between the replication model and the replication type:

• The replication model is the physical implementation of your replication design. You will spend most of your development time in designing your replication model.
• The replication type (snapshot, transactional, or merge) provides the functionality that details how to maintain replicated data. This will be determined by the latency, consistency, and site autonomy requirements of your environment.
• Any of the replication models can use any of the replication types. You typically select the model and the type at the same time; one does not determine the other.

A single database can have many publications with different replication types. For example, in your company database, you might have a publication for inventory information that uses transactional replication with the Immediate Updating Subscribers option. Another publication that contains a customer list could use merge replication so that all sites can update it.

An Example of the Central Publisher/Remote Distributor Model

The example in this section features a sales analysis system used by a corporate headquarters and regional sales offices.

The Current Process

Currently, sales personnel in European regional sales offices are required to forecast sales prior to the end of each month. One of the primary tools that they use is a sales analysis system that exists at the corporate headquarters in New York. The regional sales offices have reliable, high-speed network connections to London. They can connect to the New York headquarters only via dialup Internet connections, however. For this reason, sales data at the regional offices is often unavailable.

The Business Issue

Regional sales offices cannot accurately forecast sales because they do not have consistent access to current sales data. Cost prohibits installing high-speed network connections between all of the regional offices and New York.

The Proposed Replication Model

Implementing a central Publisher model with snapshot replication can make the sales information available to Subscribers in each region. Publications can be created that horizontally filter data so that each region receives sales data for its customers only. This reduces the amount of data that each Subscriber receives.

A remote Distributor can take advantage of a high-speed network connection between New York and London. The Distributor then sends filtered subscriptions to the regional Subscribers in the Rome, Paris, and Dublin offices. Network connections between New York and the Subscriber locations are unnecessary. London would also be a Subscriber for its own data. Figure 15.11 illustrates this proposed solution.

Figure 15.11 Example of a central Publisher/remote Distributor

With this solution, users in each region can query their regional SQL Servers for sales data rather than use the SQL Server at corporate headquarters. The subscribed data cannot be, and doesn't need to be, updated. The snapshot can be scheduled to take place daily, weekly, or at some other appropriate interval.

A variation of this solution would be to configure London as a Publishing Subscriber. It would subscribe to the data from New York and then publish the data to the other European offices. This would be a good approach if all of the regional offices were receiving all of the data and not only their own filtered data.

An Example of the Central Subscriber/Multiple Publishers Model

The example in this section features an automobile parts manufacturer with four regional warehouses across the country.

The Current Process

Currently, inventory levels fall below reorder levels and are not noticed until the warehouse is unable to fill a customer order completely. All warehouses manage their own inventories. When the quantity for a part falls below the inventory reorder point, the inventory control manager at the warehouse places an order with the factory. This procedure requires manual monitoring and order placing by each warehouse.

The Business Issue

No centralized inventory monitoring exists at the factory.

The Proposed Replication Model

Implementing a central Subscriber/multiple Publishers model that uses transactional replication to roll up the inventory information from all of the regional sites to a main inventory database at the central factory site can solve this problem. Inventory can be reordered automatically when established reorder points are reached for individual parts in each warehouse.

Because the ID number for the parts is the same in all regions, adding a separate region code column (reg_code) uniquely identifies data from the regions when the data is rolled up at the central site. The region code column is used as part of a composite primary key (the reg_code and id columns) to uniquely identify the parts from each region. Figure 15.12 illustrates this solution.

Figure 15.12 Example of a central Subscriber with multiple Publishers

With this solution, data does not need to be updated at the factory; each warehouse updates only the rows that it publishes.

An Example of the Multiple Publishers/Multiple Subscribers Model

A pastry company has three shops in different parts of the city. Each shop has a server that publishes its own orders table and subscribes to the orders tables that the other shops publish.

The Current Process

Currently, each shop runs out of certain ingredients on a regular basis. When one shop runs out of a necessary ingredient, it must contact one or both of the other shops to find out whether the ingredient is in stock and then separately arrange to have it ordered.

The Business Issue

Because it is impossible to know the inventory of the other shops until the last minute, situations arise in which all shops are out of the same ingredient at the same time.

The Proposed Replication Model

Implementing a multiple Publishers/multiple Subscribers model that uses transactional replication enables each shop to know immediately if the inventory of the other shops can solve a given problem. Shortages can be anticipated so that each store is able to fill its orders.

As shown in Figure 15.13, shops A, B, and C each have a copy of the orders table. Each shop is both a Publisher and a Subscriber—the server at each shop publishes some rows from the orders table to the other two shops and subscribes to the rows of the orders table published by the other two shops.

Figure 15.13 Example of multiple Publishers with multiple Subscribers

Each shop has responsibility for only a portion of the data. The orders table is horizontally filtered by area. For example, shop A is responsible for updating the data in area 1. For this scenario to work with transactional replication, each shop can update the data in its area only.

Lesson Summary

You can use many different physical models when you implement replication. The primary models are the central Publisher/Distributor model, the central Subscriber/multiple Publishers model, and the multiple Publishers/multiple Subscribers model. These models can be mixed or combined. When implementing replication, you can also choose whether to place the Publisher and the Distributor on the same computer or on different computers.