Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Transactional Replication Static Row and Column Filters


By:   |   Updated: 2015-12-02   |   Comments   |   Related: More > 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.

New Publication Wizard in SQL Server

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

Filter Tables in SQL Server Replication Publication

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

Example Statement on how to Filter with SQL Server Replication Publication

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

Add Filter in SQL Server Replication Publication


Filter Table Rows in SQL Server Replication Publication

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.

Validate SQL Server Data from Replication Publication

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

Add new records to the replicated table in SQL Server


Validate the data in the replication publication

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.

SQL Server Replication Column Filters


Reinitialize Subscriptions for SQL Server Replication

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

Validate final data set in SQL Server Management Studio

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



Last Updated: 2015-12-02


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools