Guide to MS SQL 2008 Transactional Replication (Configure Distributor) Part-II

In previous article (Step by Step Guide to MS SQL 2008 Transactional Replication over Internet Part – I) I have explained replication and how transactional replication work. Now I am going to explain some pre-configuration settings which need to be done before setting up MSSQL replication over Internet and also I will tell how to configure a Distribution Agent.

Servers:
As we have seen in the Scenario that there are two servers i.e. one in US and second in India and following are the details

US Server:
Hostname: Master-DB
IP Address: 1.2.3.4

India Server:
Hostname: Slave-DB
IP Address: 4.3.2.1

Pre-configuration Settings:

MSSQL Version: Before configuring MSSQL Replication make sure same edition and version of MSSQL is installed on both the servers and also both have same versions of updates installed.

Server Hostname: Hostname plays a very important role in MSSQL Replication as it doesn’t support IP based Replication. So, its important that hostname of both the servers should resolve each other. This can be easily done by adding both the names in hosts file. Open and add following lines in hosts file located at “C:/WINDOWS/system32/drivers/etc/hosts” on both the servers.

Hostname in Database: Make sure the hostname you have set for the server is set for network name and the SQL Server instance name as well other wise you will get error “SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.”

Setup Distributor Agent

In this setup we can going to configure the Distributor and Publisher on single server and following are the steps to configure Distributor.

1) Open MSSQL Management Studio on “Master-DB” Server and login into it using the Hostname.

2) Under Localhost right click on Replication folder and select “Configure Distribution…“

3) Configure Distribution Wizard window will open; Press Next.

4) SQL Server Agent Start: select “Yes, configure SQL Server Agent service start automatically” and press Next.

5) Snapshot Folder: Insert the Snapshot folder path where snapshots will be stored and press Next.

6) Distribution Database: Mention Distribution Database and log file path. Press Next to continue.

7) Publishers: Enable the servers which will become publisher and use this server as distributor. I have selected the Local System i.e. “Master-DB”. Press Next.

8) Wizard Actions: Select the actions that will be performed when you press “Finish”. Select both the check boxes and Press Next.

9) Script File Properties: You have to mention the actions that distributor will take if the scripts already exit and file type. Click on “Next”.

10) Complete Wizard: It will give a summary of options you have selected. Press “Finish” if everything looks Ok or you can go Back and change the settings.

11) You will get following results once Distributor is configured Properly.

12) You can check the Distributor Properties from MSSQL Management Studio >> LocalHost >> Replication (right click) >>Distributor Properties…

In Next Article (Guide to MS SQL 2008 Transactional Replication (Create Publisher) Part-III) I have explained how you can setup and configure Publisher.