Configure Distribution Database

, 2017-05-18 (first published: )

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!!!

The post Configure Distribution Database appeared first on .





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads