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

*****************************************************************************************
* How to Configure Distribution in SQL Server Replication *
*****************************************************************************************

We will be using two ways in SQL Server
1. Using SQL Server management studio graphic user interface GUI Version
2. Using T-SQL script

1- Distributor
The Distributor is a server that contains the distribution database and stores metadata and history data for all types of replication. The Distributor also stores transactions for transactional replication.

2- Publisher
A publisher is the main database copy on which publication is configured, making data available to other MS SQL servers that are configured to be used in the replication process. The Publisher can have more than one publication.

3- Subscriber
A subscriber is a database that receives the replicated data from a publication.

Script used in the video to Configure Distribution in SQL Server

/****** Scripting replication configuration. ******/

USE master

EXEC Sp_adddistributor
@distributor = N'CHIRAG-LAPPY',
@password = N''

GO

EXEC Sp_adddistributiondb
@database = N'distribution',
@data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA',
@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\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.MSSQLSERVER\MSSQL\DATA\ReplData',
'user',
dbo,
'table',
'UIProperties'
ELSE
EXEC Sp_addextendedproperty
N'SnapshotFolder',
N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ReplData',
'user',
dbo,
'table',
'UIProperties'

GO

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

GO

Note : Flow the Process shown in video.

😉Subscribe and like for more videos:
💛Don't forget to, 💘Follow, 💝Like, 💖Share 💙&, Comment

Tutorial Link :

Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
_________________________________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.