SQL Server Transactional Replication Static Row and Column Filters
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?
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:
- 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).
- 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.
- Check out these other Database Replication Tips
About the author
View all my tips