In the previous blog, we discussed how to Configure Distribution Database. Once you setup your Distribution database successfully, you can create a publication. Here, we will learn how to create a publication for Transaction Replication and how many jobs get created after the publication setup.

Create Publication

I’ll walk you through step by step process of creating Publication using GUI feature.

• Open the SQL Server Management Studio and connect the Publisher instance  (In my case, the instance is going to act as publisher and distributor both)

• Expand the Replication folder, right-click on the “Local Publication” folder, and click on the option “New Publication”

• The “New Publication Wizard” (Welcome page) appears and provide information about creating a new Publication. You can ignore the page and just click Next.

• The “Publication Database” page appears, select the Publication database (In this case, I will be using Forest database). Click Next.

• On the “Publication Type” page, you select the publication type, which corresponds to the type of replication you’re implementing. I am going to use Transnational Publication. Click Next

• The “Articles” page appears; you can choose which all the articles should be part of this Publication. Click Next

Note: You can also define the article properties. I will be discuss about the article properties in next article.

• The next page is “Filter Table” Rows. This page gives you the option to define filters to selected articles. Click Next

• On the “Snapshot Agent” wizard page, you specify when you want to run the snapshot agent. Click Next

Note: The snapshot agent creates the initial snapshot that is used to start the synchronization replication.

• The next page in the wizard is Filter “Agent Security”. You can specify the account to use to run the snapshot agent. You can select “SQL Server Agent Service Account” to run snapshot agent. But, I would recommend you to follow your company best practices. Click “OK”

• Once you click on “OK” at the previous step, it will bring you on the below page. Click Next.

• After that you will return to “Wizard Actions” page. Click Next

• On the page “Complete the Wizard”, you mention your publication name in the “Publication Name” text box, and then click “Finish” to create the publication. In my case, I am creating publication “ForestPub”.

• On the page “Creating Publication”, you will find information about the wizard’s progress as it works through each step of the process. Click Close

• Once the “Creating Publication” page should show that the process has been completed successful, Verify the created publication by refreshing the folder “Local Publications”

How many jobs get created during setting a new Publication?

• Before creating a new Publication, let’s see how many SQL Jobs were on the instance.
  

• After creating the Publication, let’s see how many jobs were on the instance.

Two more new jobs get introduced when you create Publication on the SQL Server.

• Log Reader Agent Job – The “Log Reader Agent” job is responsible for reading the log file of the Publisher database and copy all the data changes in the subscription database. It stores that information in the Distribution database. The job is named pattern following pattern;

<Publisher>-<Publication Database>-<number>

   Note:  SQL Server allows only one “Log Reader Agent” per transactionally replicated Database.

• Snapshot Agent Job – The Snapshot Agent creates files containing the publication schema and data that are used to initialize new subscriptions. Generating a snapshot involves two steps;

1. The Snapshot Agent generates the scripts necessary to drop & create the replicated objects on the Subscribe.
2. The agent uses the BCP utility to generate files that contain copies of the data from all the published tables.

<Server>-<Publication Database>-<Publication>-<number>

Hope, you enjoyed learning!

The post Setting Up a Publication for Transaction Replication appeared first on .