SQL Server Transactional Replication Static Row and Column Filters

By: Rajendra Gupta   |   Comments   |   Related: > Replication

Problem

SQL Server Replication is widely used to populate reporting databases or to disperse data geographically. Based on the need to send data to various subscribers with different requirements there may be a need to restrict relevant data either at a column level or row level. How can we filter data when replicated?

Solution

Suppose we have a centralized SQL Server OLTP database that stores data from transactions produced across the globe. For reporting purposes we need to segment the data only for a particular location as well as exclude any sensitive data.

So the replication restrictions are based on the following:

1. Each subscriber should only receive data for their location. So, if we have three subscribers (UK, US and Asia) we want each subscriber to only get their data (i.e. UK only gets UK data).
2. Customer sensitive data should not be replicated, such as customer name.

We can achieve these requirements using replication filters, so let's see how to do this by creating a sample database.

Create Database OnlineMarket
go

Use OnlineMarket
Go

Create table dbo.orders(
Orderid int primary key clustered,
ProductName Varchar(10),
OrderQuantity int,
CustomerName Varchar(10),
CustomerAddress varchar(10),
CustomerContactNo	int
)

go

insert into dbo.orders values(101,'AVK IDNC',10,'John','UK',565669)

insert into dbo.orders values(102,'zasdd',5,'Micd','UK',121246)

insert into dbo.orders values(103,'kkkpp',9,'rahm','US',98595)

insert into dbo.orders values(104,'werds',45,'mlok','US',14152)

insert into dbo.orders values(105,'qazdd',1,'redss','Asia',66666)

insert into dbo.orders values(106,'zasdd',1,'jjjsl','Asia',9999)
     

Replication Row Filters

A static row filter uses a WHERE clause to select the appropriate data to be published. When setting up the publication, we can add a filter to only publish specific records. The below screenshot shows "orders" table which will be published.

After you select the Next button, you can set a data filter by clicking the Add button.

We can see the help explains how to build the filter statement.

Now we define the filter as shown below to only include records from the "UK".

Once the publication was created, I created a subscription. The below shows the filtered data that was replicated. As we have created a publication to replicate records only having a CustomerAddress equal to 'UK' only those records are replicated.

If we add additional records as shown below, the filtered condition will only replicated the data where CustomerAddress equals "UK".

Note: Row filters in transactional publications can add significant overhead, because the article filter clause is evaluated for each log row written for a published table to determine whether the row should be replicated. So this should be used with proper performance testing. Also, if you add a row level filter after the publication is created this will require that the subscription be reinitialized.

Replication Column Filters

In order to achieve our second requirement, to filter specific columns we need to use column filters.

We can also create column filters when the publication is being created or afterwards (but doing so afterwards will require the subscription to be reinitialized).

To achieve this, on the articles page for each article we need to select which columns to be replicated. Suppose in our case we don't want to replicate CustomerName, CustomerAddress and CustomerContactNo. So when we uncheck these columns and click "OK" this will prompt us to reinitialize the existing subscription as shown below.

Once replication has been synchronized only the selected columns will be published as shown below.

There are some restrictions where columns cannot be filtered:

• Primary key columns are required for all tables in transactional publications, so they cannot be filtered.
• All columns referenced in row filters must be included in the publication.
• Columns with image, varchar(max), XML, UDT cannot be filtered.

Next Steps

• Check out these other Database Replication Tips

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips