How to Configure Distribution in SQL Server Replication - SQL Server DBA Tutorial

In this video you will learn how to configure Distribution in SQL Server, how to select various options while install distribution for Replication in sql server. How to select Distributor SQL Server during configuration, best practices of configuring distribution on SQL Server.


Script used in the video to Configure Distribution in SQL Server

/****** Scripting replication configuration. Script Date: 3/10/2015 5:01:59 PM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server as a Distributor. Script Date: 3/10/2015 5:01:59 PM ******/
USE master

EXEC Sp_adddistributor
  @distributor = N'TBSSQL\SQLPROD',
  @password = N''

GO

EXEC Sp_adddistributiondb
  @database = N'distribution',
  @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\Data',
  @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\Data',
  @log_file_size = 2,
  @min_distretention = 0,
  @max_distretention = 72,
  @history_retention = 48,
  @security_mode = 1

GO

USE [distribution]

IF ( NOT EXISTS (SELECT *
                 FROM   sysobjects
                 WHERE  name = 'UIProperties'
                        AND type = 'U ') )
  CREATE TABLE UIProperties
    (
       id INT
    )

IF ( EXISTS (SELECT *
             FROM   ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', NULL, NULL)) )
  EXEC Sp_updateextendedproperty
    N'SnapshotFolder',
    N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\ReplData',
    'user',
    dbo,
    'table',
    'UIProperties'
ELSE
  EXEC Sp_addextendedproperty
    N'SnapshotFolder',
    N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\ReplData',
    'user',
    dbo,
    'table',
    'UIProperties'

GO

EXEC Sp_adddistpublisher
  @publisher = N'TBSSQL\SQLPROD',
  @distribution_db = N'distribution',
  @security_mode = 1,
  @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\ReplData',
  @trusted = N'false',
  @thirdparty_flag = 0,
  @publisher_type = N'MSSQLSERVER'

GO 


How to Configure Distribution in SQL Server Replication