SQL Server Replication - Configuring Distributor | SansSQL

Monday, July 15, 2013

SQL Server Replication - Configuring Distributor

When setting up replication in SQL Server, the first thing to set up is Distributor.
The Distributor is a SQL server instance that contains the distribution database, which stores metadata and history data for all types of replication and transactions for transactional replication.

Properties of Distributor

  • Each Publisher can be assigned to a single Distributor instance only
  • Multiple publishers can share a single Distributor

To Configure Distributor:

  1. Connect to the SQL Server instance using Management Studio which you want to make as Distributor
  2. Right-Click on "Replication" and click on "Configure Distribution..."
  3. Click "Next" on the welcome screen
  4. Choose the first option to make the SQL Server instance as distributor and create a new distribution database
  5. In the next step, the wizard allows to change the startup mode of SQL Server Agent service to Automatic if it is not set to automatic. Choose "Yes" and Click "Next"
  6. In this screen, we will choose the snapshot location. This is preferable a network share on which the SQL Server service account have full privilege.
  7. In the next screen, Enter the name for the distribution database and the location where the data and log files of this database needs to be stored.
  8. In the next screen, we need to add the database server names or instance names (Publishers) which can connect to this distributor. This is can be achieved by clicking on "Add" button and the publishers can either be SQL Server or oracle.

    For now, I will be adding a SQL Server as Publisher. So click on "Add SQL Server Publisher..." and connect to the publisher server instance

    When you click on the browse button (...) next to the distributor database name, from the resulting screen you can manage the connection properties for publisher database instance.
  9. In the next screen, provide password for making remote connections to distributor form publisher
  10. In the next screen, you can choose to save the configuration as .sql for future references.
  11. In the next screen, the summary will be displayed and on reviewing the summary click "Finish" for the wizard to start configuring distributor
  12. The wizard will now configure the distributor and once the actions are completed, you will find a new system database by name "distributor"

No comments:

Post a Comment