Understanding Replication Components: The PublisherSubscriber Metaphor

Understanding Replication Components: The Publisher/Subscriber Metaphor

Before you get involved in a deeper discussion on replication, it's important that you become familiar with the basic concept of the Publisher/Subscriber metaphor. The Publisher/Subscriber metaphor defines the different roles SQL Server can play throughout the replication process. Frankly speaking, SQL server can also play a third role: the Distributor. Each role provides functionality that aids in the replication process. In simple terms, the Publisher is the server that makes the data available so that it can be sent to the Subscriber. The Distributor is the intermediary that collects the data from the Publisher and transports it to the Subscriber. The Subscriber is the server that ends up receiving the data.

This metaphor is analogous to the processing of a monthly magazine subscription or other periodical. In the case of a magazine, the magazine company produces copies of the magazine that they then send to a distributor to be circulated to individual customers. As you will see, this is similar to the roles of the Publisher and Distributor in a replication scenario. The Publisher makes the data available to be dispersed to the subscribing machines through a Distributor. The magazine distributor (courier or mailman), like the Distributor, is used as an intermediary between the magazine company and the customer. The replication Distributor is responsible for the delivery of the data to all subscribers. Finally, the individual customer who receives the magazine copy through the mail can be thought of as the Subscriber in SQL Server, with one important difference: In some SQL Server replication scenarios, the Subscriber can change the content and send updates back to the publisher (which I'm sure would not be desirable in the case of most magazine subscriptions). This process is represented in Figure 11.1. Each role is discussed in more detail in the following sections.

Figure 11.1. The Publisher/Subscriber metaphor.

EXAM TIP

The Publisher/Subscriber Metaphor on the Exam Obviously, the exam will not ask you such direct questions as, "What is the Publisher/Subscriber metaphor?" Nevertheless, understanding the Publisher/Subscriber metaphor is vital in understanding how replication is implemented, which may very well be a question on the exam.

Publisher

In replication terminology, the Publisher is the server that produces the original data for the use of the replication process. The Publisher can produce many publications or sets of data to be distributed to other subscribing machines. One Publisher can produce data to be replicated to many Subscribers. Also, many Publishers may produce data to be distributed to just a single, central Subscriber. The former is implemented as a standard Central Publisher/Distributor/Subscriber replication model, and the latter is referred to as a Central Subscriber replication.

Distributor

In SQL Server terminology, the Distributor is the server that contains the distribution database, data history, and transactions; as its name implies, it sends data to Subscribers. The Distributor may be implemented on the same physical server as the Publisher or Subscriber, though it doesn't need to be. It can reside on a separate server across the world connected via a network. The placement of the Distributor and its characteristics depends on the type of replication used.

Subscriber

A Subscriber in SQL Server terms is the server that receives replicated data through the Distributor. A Subscriber receives publications by subscribing to them ”not, however, by subscribing to individual articles that represent only a portion of a publication. It is also possible to use stored procedures to set up subscriptions, in which case it is possible to subscribe to a single article. Subscribers can choose from the publications available at the Publisher and don't necessarily need to subscribe to all of them. Remember the magazine example at the beginning of this section, where the Subscriber is analogous to a customer receiving magazine issues.

Articles and Publications

Simply put, articles are data structures made up of selected columns and/or rows from a table. An article could also be the entire table, although it is recommended that the content of an article be kept to just the minimum amount of data needed for the specifics of an implementation. One or more articles are bundled into a publication to be used for replication. Selecting subsets of tables (certain rows or certain columns) is known as partitioning , which is discussed in more detail later in this chapter.

Articles must be grouped into a publication before they are ready to be replicated. In other words, a publication is a collection of one or more articles and is capable of being replicated.

Horizontal and Vertical Partitions

Horizontal partitioning is the breaking down of tables into smaller subsets based on the selection of rows. Row selections are made using a standard condition that would be provided as a WHERE clause for a SELECT statement. Vertical partitioning is the segmenting of tables into a subset based on the selection of only the columns that are needed as opposed to all columns in the table.

You can, however, choose not to use either form of partitioning and publish the entire table, if this is what is needed to meet the goals of the implementation. Partitions are used when a business does not need to replicate all the information contained in a table; they also make the replication process more efficient by minimizing the amount of data being sent over a network. Partitions are also used when you do not want confidential information to be sent to users, and when the storage space on the Subscriber is a consideration. Vertical and horizontal partitioning are supported by all three types of replication (merge, snapshot, and transactional, covered later in this chapter).

IN THE FIELD: ARTICLE COMPOSITION

A single article may take advantage of both vertical and horizontal partitioning at the same time. In fact, because doing so produces the smallest amount of data to be transferred, it is considered the desired technique.

Vertical partitioning, also known as vertical filtering, provides a good solution when a business needs to replicate only a subset of data, which is produced by a few columns segmented from a table.

For example, imagine a food manufacturing business called Purls that keeps a table called "Products" residing in their headquarters, which lists detailed information for each product they have. Columns included in the Products table might be Product_Id , Product , Price , and Description . Eight branch offices located in different areas throughout the country need a copy of the Price column from the Products table as soon as possible.

In this case, Purls decides to vertically partition their article by selecting only the columns that are needed (that is, the Price column and Product_Id column), and then replicating them to all branch offices. This increases the efficiency with which the data is sent to the different branch offices, because Purls avoids sending unnecessary information held on the Description and Product columns, and sends only what is required: the Price column.

Horizontal partitioning is used in similar ways. In another example, there might be a major ISP (Internet Service Provider) business called PrimeNet that tracks information on clients in a Customer table. PrimeNet maintains data for both active and inactive clients. It is probably desirable to replicate only the active customer information to sales representatives. A condition such as WHERE Status = ACTIVE can be applied to eliminate inactive clients and create a much smaller amount of information to be transmitted.

Subscriptions

Subscriptions are requests from the Subscriber for data or data objects to be sent. A subscription depends upon three factors: when publications will be received, what publications will be received, and where the publications will be received.

There are two types of subscriptions: a push subscription and a pull subscription. A push subscription is one where the Publisher initiates a subscription and provides the basis for scheduling the replication process. The pull subscription is one where the Subscriber initiates the subscription and provides the basis and timing on which data is to be obtained. In either case, whoever initiates a subscription selects the appropriate articles to be transmitted.