In the previous blog, we discussed how to configure the Distribution Database. Once you have successfully set up your Distribution database, you can proceed to create a publication. In this article, we will walk you through the step-by-step process of creating a publication for Transaction Replication and discuss the number of jobs that are created after the publication setup.

Create Publication

To create a publication, you can use the GUI feature in SQL Server Management Studio. Follow these steps:

1. Open SQL Server Management Studio and connect to the Publisher instance (which will act as both the publisher and distributor in this case).
2. Expand the Replication folder, right-click on the “Local Publication” folder, and click on the option “New Publication”.
3. The “New Publication Wizard” will appear. You can ignore the welcome page and click Next.
4. On the “Publication Database” page, select the publication database (in this case, we will be using the Forest database). Click Next.
5. On the “Publication Type” page, select the publication type that corresponds to the type of replication you are implementing (in this case, Transnational Publication). Click Next.
6. On the “Articles” page, choose which articles should be part of this publication. Click Next.
7. On the “Filter Table Rows” page, you have the option to define filters for selected articles. Click Next.
8. On the “Snapshot Agent” wizard page, specify when you want to run the snapshot agent. Click Next.
9. On the “Filter Agent Security” page, specify the account to use to run the snapshot agent. Click “OK”.
10. Click Next on the following page and then on the “Wizard Actions” page.
11. On the “Complete the Wizard” page, enter your publication name in the “Publication Name” text box and click “Finish” to create the publication (in this case, we are creating the publication “ForestPub”).
12. On the “Creating Publication” page, 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 shows that the process has been completed successfully, you can verify the created publication by refreshing the “Local Publications” folder.

How Many Jobs Get Created During Setting Up a New Publication?

Before creating a new publication, let’s take a look at how many SQL jobs are on the instance. After creating the publication, we can compare the number of jobs on the instance.

When you create a publication on SQL Server, two additional jobs are introduced:

• Log Reader Agent Job: The “Log Reader Agent” job is responsible for reading the log file of the Publisher database and copying all the data changes to the subscription database. It stores this information in the Distribution database. The job is named following the pattern: <Publisher>-<Publication Database>-<number>. It’s important to note that 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: the Snapshot Agent generates the scripts necessary to drop and create the replicated objects on the subscriber, and the agent uses the BCP utility to generate files that contain copies of the data from all the published tables. The job is named following the pattern: <Server>-<Publication Database>-<Publication>-<number>.

We hope you found this article helpful in setting up a publication for transaction replication. Stay tuned for more SQL Server tips and tricks!