Database Replication – Part III

In this post I will discuss using filters to limit the data that is replicated. Filters can be applied to limit the number of records, limit the fields or a combination of these. With Advantage these filters are defined on the publication therefore all subscribers to the publication will have the same filters applied. If you need specific filters for each subscriber you will need to create a publication and a subscription for each subscriber at the publisher.

• Part I – Overview and Server to Server strategies
• Part II – Multiple server strategies
• Part III – Replication filtering
• Part IV – Replication conflicts

Let me start with a bit of background. I refer to the source database as the publisher , the target database as a subscriber and the item to be replicated as an article. Horizontal filtering limits the records through the use of a filter expression. Vertical filtering specifies the fields to be replicated. Although these strategies can apply to database replication in general some of the information I discuss is specific to Advantage Database Server. You can get an overview of Advantage replication in our online help files.

Example Scenario

To demonstrate filtering we will use an example scenario. We have a corporate office which needs to run reports on consolidated data from all the stores. The corporate office provides pricing information to each of the stores. To meet these requirements we will use a spoke and hub model as shown below.

 

In our example these are convenience type stores which can sell gasoline along with various snacks and groceries.

Horizontal Filtering

In the example described above our stores sell a variety of convenience items, however not all stores sell gasoline and other automotive products. Since product information is updated at the corporate server we will filter the products based on the type of store. This is an example of a horizontal filter since the data is being limited by a condition.

In this simple example the products table has a type column which specifies which type of store stocks the item. This allows us to limit the data that is replicated to the stores based on the value in this column. An example publication for a type 1 store is shown below.

Horizontal filters are applied using any valid expression, making them very powerful. Any record that meets the conditions in the expression will be replicated to all subscribers of the publication.

Vertical Filtering

Another requirement of our example is to provide employee information to all the stores. However, not all of the information in the employee table is required at the store level. Things like social security number and home address for instance. To allow replication of the essential data without the personal information can be done using a vertical filter.

A vertical filter specifies which columns are replicated from a particular table. The subscriber’s table only needs to have the replicated columns defined. Therefore, the table structures can be different although, the fields which are replicated must have the same properties as the publisher’s table.

An example of the vertical filter for the employee table is below:

It is important to remember that all filters are defined in the publication and will apply to all subscribers of the publication. Filters are article (table) specific and both types of filters can be applied to an article.

In the next part of this series I will discuss replication conflicts.