Introduction to Replication.
Before enabling Replication on Publisher you must decide where you want to run the distribution database. Distribution database keeps track of logs to be replicated among subscribers.
Keeping distribution database on same server as publisher may cause IO bottlenecks, depending upon hardware configuration and existing load on server.
Follow the steps to below to enable Distribution and Replication. These steps will add distribution server on selected server.
1. Open SQL Server Management Studio, Connect to SQL Server and Expand SQL Server where you wish to enable publishing and distribution.
2. Right click on Replication in Object Explorer, then select Configure Distribution. This will start “Configure Distribution Wizard”.
3. In case distribution is already setup somewhere, then select appropriate option,
4. In the following screenshot, notice that Snapshot Folder location is a network location. It is a good idea to keep it as network location, so that sql server agent can running on another server can access this location in case of pull subscription.
5. Enter the location to create data files,
6. From the following screen, you can add publisher server to distribution list. Click on Add button and select “Add SQL Server Publisher” to add SQL Server publisher. There is one more option available to add Oracle Publisher too. You may add more than one publisher here.
7. From the below screen select password for remote connection to distribution database.
8. From the following window, you can either select to Configure distribution, or generate the t-sql to configure distribution manually or both.
9. Read the summary of actions below before configuring the distribution.
10. Click Finish to complete the Configuration.