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

1 comment:

  1. and more importantly, your personal experienceMindfully using our emotions as data about our inner state and knowing when it’s better to de-escalate by taking a time out are great tools. Appreciate you reading and sharing your story, since I can certainly relate and I think others can too
    vidmate.onl

    ReplyDelete