Priyanka Devre
View snapshot agent status

Add new articles, drop the article, change the snapshot folder path and Data filter rows in SQL Server Replication

April 2, 2019 by

In the last articles, we have learned Configuring Snapshot and Transactional SQL Server replication and Configuring Peer to Peer and Merge SQL replication. Now, once we configured SQL Server replication, there will be some instances where we need to modify the SQL Server replication configuration as per the project updates. In this article, we will learn a few modifications in the existing SQL Replication such as Add new article, drop an article and change the Snapshot folder path and data filter in the current SQL Server replication.

Add new Articles into the existing published database

In this section, we will learn, Add a new article into the existing published database. Once we created the publication, the publication contains few sets of database objects like tables, views and stored procedures. As per the project requirement sometimes we need to create new tables or views or stored procedures into the existing published database for some calculations or storing specific limited data. If we also wish to replicate those tables or views or stored procedures into the current SQL replication, then we need to add new database objects into the published database. This section will help to insert new articles into the existing published database of the SQL Server replication.

I have already configured the transactional SQL replication on the node SQL1 for the database AdventuresWorksLT2012 as shown in the following fig.

Published database of the replication

Before adding a new article, first, we need to check, which are the database objects are already part of the SQL Server replication. Right-click the configured publication of the database and Go to Publication properties where we will get a list of articles which are part of the SQL replication.

In the following fig. articles which ticked are already part of SQL Server replication.

Articles which are part of replication

The new articles which need to add into publication database, First, un-check Show only checked items in the list option as shown in the following fig. we will get a list of all the objects from the database. We will select new tables ProductCategory and ProductDescription from the following list and click OK.

Select articles which need to add

Once we added the new list of articles in the current publication, right-click Configured publication and select Reinitialize all Subscription as the following.

Reinitialize all subscription

Click on the Use a new snapshot; this option will able to create a new snapshot till time and click on Mark for Reinitialization.

Use a new snapshot

Right-click configured publication and Select Launch Replication Monitor.

Launch replication monitor

Go to Agent tab as shown in the following.

Agent tab

Right-click on snapshot section and click start agent, this process will create a new snapshot after adding the new articles in the current publication and will be used for the further SQL replication process.

Start agent

After running agent, we can check Last start time and last action and duration of snapshot run as shown in the following window.

Last run and last action of the agent

After creating a new snapshot, we will verify the status of the snapshot agent. Right-click the publication and select View Snapshot Agent Status as the following.

View snapshot agent status

Snapshot agent status is in a healthy state. A snapshot of 9 articles generated as shown on the following fig.

Generated snapshot

After adding new articles in the current publication, we can verify that new articles have added to the subscriber server SQL2 as shown in the following fig.

New articles created on the subscriber

Drop Table from the publication

In this section, we will learn about to drop a table from the publication.

The table Customer of the database AdventureWorksLT_2012 is part of the SQL Server replication. We will try to drop it as the following; however, it will not allow us to drop it because a table configured as an article in the SQL replication and It will throw an error as shown in the following.

Drop article from the publisher

Before dropping a table, first, we need to remove a table from the publication. Right-click publication and Go to Publication Properties. So, we want to drop a table Customer, so Let’s uncheck it from the list of objects to publish section. Once we uncheck it, we will get a pop-up warning click Yes.

Uncheck article

Once clicked on Yes, we will get a pop-up to create a new snapshot. Select Use a new snapshot and click Mark for Reinitialization.

Use a new snapshot

Right-click publication and Go to Replication monitor. Right-click view details.

View Details of the snapshot

The following section gives us the history of the snapshot agent. The next part shows nine articles have generated after removing one article from the publication.

History of snapshot agent

Right-click publication and click Reinitialize

Reinitialize subscription

Click Use the current snapshot to reflect the results on the subscriber.

use the current snapshot

Drop the table on the publisher SQL1 as the following

drop table

On the subscriber SQL2, we can verify the table removed from the subscriber.

Articles on subscriber

Change Snapshot folder Path

While configuring SQL Server Distribution database, we had given the following snapshot folder path. In this section, we will learn to change the snapshot folder path in the from the current distribution.

snapshot folder path

Right-click publication and Go to properties.

Publication properties

Go to Snapshot page on the left side and check current snapshot files path.

location of snapshot files

To change the path, Right-click Replication folder and click Distribution Properties.

Distributor properties

In the Publisher page section, click on the icon in the following fig.

Publisher icon

Provide the new correct path and click OK

Change the Snapshot folder path

Data Filter

SQL Server replication provides an excellent feature to filter the data using the Column data filter. In the data filter, we will able to show the column which we want to display for the reposting purpose. We can remove the column which contains sensitive information like customer name, customer email, credit card, etc.

In the project environment, we build a dashboard in the tableau framework. Client vendors used to access those tableau dashboards for data analysis purposes. In that, we want to show data information of the one particular vendor to that specific vendor only. We do not want to show one vendor information to another vendor. So to limit data visibility, we will set-up SQL Server replication with data filter option.

Once the distribution database configured, we will proceed to set the publication.

Right-click Replication folder and select New Publication. Select the database AdventureWorksLT2012 as the following and click Next

Select database

Select the required database objects and click Next

Select articles

In the Filter Table Rows window, if we do not want to filter data, we can skip this step. Now we need to filter data so that we will configure this section window. Click on Add as shown in the following.

Add filter table rows

Select the table we need to use for the data filter. We selected the table Customer from the SalesLT schema. Next, we need to add the column which contains sensitive information on the right-side filter statement. We will apply the condition for the specified column, and it will show the results only for the conditional statement.

In this case, we added the column CompanyName for the condition; company name starts with A

Apply filter condition

Once we added the table Customer in the filter section as shown in the following fig.

Filtered tables

In the Snapshot section, select a checkbox for Create a snapshot and click Next.

Create a snapshot

In the Agent Security, configure the service account for the snapshot agent and the log reader agent as the following and click Next.

configure service account

In the complete wizard window, provide publication name as the following and click Finish.

Provide Publication name

Publication is configured successfully as the following.

Creating Publication

Data Verification on the Subscriber

In this step, we will verify the data; On the Publisher server SQL1, we will retrieve records from the table Customer. We can see, the column CompanyName shows the values for all the company name.

CompanyName records on publisher

In this step, we will verify the data on the Subscriber server SQL2. When we retrieved records for the same table on the subscriber server SQL2, we will get filter data for the condition which we have specified in the filter section. We got the company name which starts with only character A as shown in the following fig.

Filtered records on Subscriber

Table of contents

SQL Server replication configuration: Peer to Peer and Merge Replication
SQL Server replication: Configuring Snapshot and Transactional Replication
Add new articles, drop the article, change the snapshot folder path and Data filter rows in SQL Server Replication

Priyanka Devre

Priyanka Devre

I am From Bangalore, India. I work with SQL Server for Infosys in India. I work in High Availability AlwaysON, Disaster recovery, performance tuning, Back-up and restore, and  Cloud infrastructure.
Priyanka Devre
Replication

About Priyanka Devre

I am From Bangalore, India. I work with SQL Server for Infosys in India. I work in High Availability AlwaysON, Disaster recovery, performance tuning, Back-up and restore, and  Cloud infrastructure.

110 Views