In this blog we will look at how to configure the distribution database for SQL Server replication. The distribution database is a very important database in replication as it is responsible to ensure that all publishers and subscribers are in sync. It plays a pivotal role in identifying the changes that have occurred in the database when using transactional replication also when performing push subscriptions the agents are run on the same server as the distributor making it a key link between the publisher and the subscriber.
The distributor plays a role similar to that of the witness server in database mirroring in that it is responsible for coordination. Please keep in mind that replication doesn’t support automatic failover.
Right click the replication folder and click configure Distribution
In the below screen we configure the place where the distribution database is hosted. For practical reasons we host the distribution database on a instance that is different from the Publisher. This is because if the publisher goes down we still have the distribution database up and running.
In the below screen you can configure the location of the snapshot folder. This folder is where the snapshot files are located and will be used when the initial snapshot agent runs to create a copy of the database.
You can configure an alternate location for the snapshot folder as well. As seen in the message in order to support pull subscriptions you ened to specify a network path.
In the above screen you are required to specify the name and location for the database files the defaults are populated as shown above, However since there are likely to be many databases called distribution within the same datacentre is usually a good idea to give it a name that helps identify which replication setup it belongs to.
In the above screen you can see the list if services that are allowed to use this server as the distributor. Click Add to add another publisher to this distribution database.
To change to connection string or basically the account used for replication click the Ellipse.
Since replication can happen with machines that are not part of the network they all need to use a common password to validate that belong to the replication topology. This password is entered in the below screen and will be required any time a new machine is added to the topology.
With this the distribution database is configured on the instance.
Please Consider Subscribing
