In this blog, we are going to learn about the Distribution database, how to configure the Distribution database, and how many jobs get created during the configuration of the Distribution database.

Distribution Database

Distribution Database is a system database. It contains many objects that keep replication metadata and replicated data information. Each Publisher is linked to a single Distributor database. It identifies changes to the articles on each of its Publishers and sends the changed data to Subscribers. The data change information is stored in the distribution database until all Subscribers have been synced or the retention period has expired.

Configure the Distributor

Let’s follow the below steps to configure Distributor

• Open the SQL Server management studio and connect the instance where you want to configure Distributor

• Right-click the Replication Folder and click on the “Configure Distribution” option

• On the “Distributor” page, you can choose to set up the current instance to be a Distributor, or you can select another instance that’s already been configured for a Distributor. Click Next

• The “SQL Server Agent Start” page gives you the option to enable the SQL Server Agent, If the SQL Server Agent is not set up to start automatically. Click Next

• You specify the location for the snapshot folder on the “Snapshot Folder” page. During the initial synchronization or re-initialization of replication, the snapshot folder will be used. Click Next

Note: If It is shared location, you must ensure that file sharing has been enabled on the shared (snapshot) folder.  Right-click on the folder, open the property of the folder;

– Go to the “Security” tab and give write access to “Authenticated Users” Windows group.
– Go to the “Sharing” tab, click on the “Advanced Sharing”, click on the “Permissions”, & give read access to the “Everyone” Windows Group

• On the “Distribution Database” page, you specify the name of the distribution database and the data & log files folder location. The distribution database will be created based on the provided parameters. Click Next
  

• By default, the local instance gets selected on the “Publisher” page. It is because a SQL Server instance that is set up to be a Distributor can use only itself as a Distributor for publishing. Click Next

Note: Any SQL Server Instance that you want to set up as a Publisher and the instance is going to use this Distributor database needs to be added to this list.

• The “Wizard Actions” page gives you the option to generate a script that you can execute at a later time. Click Next
  

• The “Complete the Wizard” page shows the selected configuration to have a final review. Click on the finish to create your Distributor. Click Next

• The Configure page displays that the distribution database is being created.

 You have successfully configured the Distribution database!

How many jobs get create during the configuration of Distribution database?

It is a very common question which is asked in an interview. To get the answer of the question, lets see;

• How many SQL Jobs were on the instance before configuring the distribution.
  

• After configuring the distribution database, how many jobs were on the instance.

Here is the jobs name and description which got created during configure distribution

Agent history clean up: distribution – It deletes all the history records from the Distribution database but before deleting the history records, It has to check Distribution database history retention setting to determine whether the records needs to be delete or not.

Distribution Cleanup up: distribution – It removes replicated transaction from Distribution database by making sure that each transaction has been replicated from Distribution database to Subscriber database successfully.

Expired subscription clean up – It detects and removes expired subscriptions, were failed to connect to the subscriber server within Publication retention period, from Publication database.

Reinitialize subscriptions having data validation failures – It identifies all subscriptions that have data validation error and marks them for re-initialization. Data validation can be used to verify the data consistency between Publisher and Subscriber.

Replication agents checkup – It detects replication agents that are not actively logging history. It writes to the Microsoft Windows event log if a job step fails to reports it status in the Distribution database.

Replication monitoring refresher for distribution – It is used to refresh the cached queries used by Replication Monitor.

Great Learning!!!