Database Replication – Part IV

This post is in continuation to my earlier post. Please check if you missed.

Database Replication – Part I

Database Replication – Part II

Database Replication – Part III

As committed in this post we will continue to cover practical approach, Next step from last post.

Publishing Data Using Transactional Replication

We will create a transactional publication using SQL Server Management Studio to publish a filtered subset of the Item table in the Nav 2018 sample database. We will also add the SQL Server login used by the Distribution Agent to the publication access list (PAL).

To create a publication and define articles

Connect to the Publisher in SQL Server Management Studio, and then expand the server node.

Expand the Replication folder, right-click the Local Publications folder, and click New Publication.

The Publication Configuration Wizard launches.

On the Publication Database page, select Nav 2018 database, and then click Next.

On the Publication Type page, select Transactional publication, and then click Next.

On the Articles page, expand the Tables node, select the check box for table CRONOUS International Ltd_$Item (dbo). Click Next.

On the Filter Table Rows page, click Add.

In the Add Filter dialog box, click the Replenishment System column, click the right arrow to add the column to the Filter statement WHERE clause of the filter query, and modify the WHERE clause as follows:

WHERE [Replenishment System] = 1

Click OK,

Click Next.

Select the Create a snapshot immediately and keep the snapshot available to initialize subscriptions check box, and click Next.

On the Agent Security page, clear Use the security settings from the Snapshot Agent check box.

Click Security Settings for the Snapshot Agent, enter <Machine_Name>\repl_snapshot in the Process account box, supply the password for this account, and then click OK.

Repeat the previous step to set repl_logreader as the process account for the Log Reader Agent

Click Finish.

On the Complete the Wizard page, type Nav2018ItemTrans in the Publication name box, and click Finish.

After the publication is created, click Close to complete the wizard.

To view the status of snapshot generation

• Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.

In the Local Publications folder, right-click Nav2018ItemTrans, and then click View Snapshot Agent Status.

The current status of the Snapshot Agent job for the publication is displayed. Verify that the snapshot job has succeeded.

To add the Distribution Agent login to the PAL

• Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
• In the Local Publications folder, right-click Nav2018ItemTrans, and then click Properties.
• The Publication Properties dialog box is displayed.
• Select the Publication Access List page, and click Add.

In the Add Publication Access dialog box, select <Machine_Name>\repl_distribution and click OK. Click OK.

 

We will discuss Next step in our upcoming post.