How to Create Snapshot Replication in SQL Server - SQL Server DBA Tutorial

In this video you will learn how to create snapshot replication in SQL Server using SQL Server management studio as well as using T-SQL script. You will also learn how to configure the publication for snapshot, best practices of configuring publications for snapshot replication, how to add database and it's articles in snapshot replication, configuring Publication agent security and best practices of security configuration of Publisher as well as Subscriber.
It also briefly talk about what exactly is publisher, distributor and subscriber in replication in SQL Server, also what is replication in sql server and replication components in SQL Server.

Script used in the video to Create Snapshot Replication in SQL Server

USE [SalesOrders]

EXEC Sp_replicationdboption
  @dbname = N'SalesOrders',
  @optname = N'publish',
  @value = N'true'

GO

-- Adding the snapshot publication
USE [SalesOrders]

EXEC Sp_addpublication
  @publication = N'SalesOrder_pub',
  @description = N'Snapshot publication of database ''SalesOrders'' from Publisher ''TBSSQL\SQLPROD''.',
  @sync_method = N'native',
  @retention = 0,
  @allow_push = N'true',
  @allow_pull = N'true',
  @allow_anonymous = N'true',
  @enabled_for_internet = N'false',
  @snapshot_in_defaultfolder = N'true',
  @compress_snapshot = N'false',
  @ftp_port = 21,
  @ftp_login = N'anonymous',
  @allow_subscription_copy = N'false',
  @add_to_active_directory = N'false',
  @repl_freq = N'snapshot',
  @status = N'active',
  @independent_agent = N'true',
  @immediate_sync = N'true',
  @allow_sync_tran = N'false',
  @autogen_sync_procs = N'false',
  @allow_queued_tran = N'false',
  @allow_dts = N'false',
  @replicate_ddl = 1

GO

EXEC Sp_addpublication_snapshot
  @publication = N'SalesOrder_pub',
  @frequency_type = 1,
  @frequency_interval = 0,
  @frequency_relative_interval = 0,
  @frequency_recurrence_factor = 0,
  @frequency_subday = 0,
  @frequency_subday_interval = 0,
  @active_start_time_of_day = 0,
  @active_end_time_of_day = 235959,
  @active_start_date = 0,
  @active_end_date = 0,
  @job_login = N'Techbrothers\clusteradmin',
  @job_password = Mraza1904,
  @publisher_security_mode = 1

USE [SalesOrders]

EXEC Sp_addarticle
  @publication = N'SalesOrder_pub',
  @article = N'Customers',
  @source_owner = N'dbo',
  @source_object = N'Customers',
  @type = N'logbased',
  @description = NULL,
  @creation_script = NULL,
  @pre_creation_cmd = N'drop',
  @schema_option = 0x000000000803509D,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'Customers',
  @destination_owner = N'dbo',
  @vertical_partition = N'false'

GO

USE [SalesOrders]

EXEC Sp_addarticle
  @publication = N'SalesOrder_pub',
  @article = N'Customers_2',
  @source_owner = N'dbo',
  @source_object = N'Customers_2',
  @type = N'logbased',
  @description = NULL,
  @creation_script = NULL,
  @pre_creation_cmd = N'drop',
  @schema_option = 0x000000000803509D,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'Customers_2',
  @destination_owner = N'dbo',
  @vertical_partition = N'false'

GO

USE [SalesOrders]

EXEC Sp_addarticle
  @publication = N'SalesOrder_pub',
  @article = N'Customers_3',
  @source_owner = N'dbo',
  @source_object = N'Customers_3',
  @type = N'logbased',
  @description = NULL,
  @creation_script = NULL,
  @pre_creation_cmd = N'drop',
  @schema_option = 0x000000000803509D,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'Customers_3',
  @destination_owner = N'dbo',
  @vertical_partition = N'false'

GO

USE [SalesOrders]

EXEC Sp_addarticle
  @publication = N'SalesOrder_pub',
  @article = N'Customers_4',
  @source_owner = N'dbo',
  @source_object = N'Customers_4',
  @type = N'logbased',
  @description = NULL,
  @creation_script = NULL,
  @pre_creation_cmd = N'drop',
  @schema_option = 0x000000000803509D,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'Customers_4',
  @destination_owner = N'dbo',
  @vertical_partition = N'false'

GO

USE [SalesOrders]

EXEC Sp_addarticle
  @publication = N'SalesOrder_pub',
  @article = N'Customers_5',
  @source_owner = N'dbo',
  @source_object = N'Customers_5',
  @type = N'logbased',
  @description = NULL,
  @creation_script = NULL,
  @pre_creation_cmd = N'drop',
  @schema_option = 0x000000000803509D,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'Customers_5',
  @destination_owner = N'dbo',
  @vertical_partition = N'false'

GO

-----------------BEGIN: Script to be run at Publisher 'TBSSQL\SQLPROD'-----------------
USE [SalesOrders]

EXEC Sp_addsubscription
  @publication = N'SalesOrder_pub',
  @subscriber = N'TBSClient\SQLMirror',
  @destination_db = N'SalesOrders',
  @subscription_type = N'Push',
  @sync_type = N'automatic',
  @article = N'all',
  @update_mode = N'read only',
  @subscriber_type = 0

EXEC Sp_addpushsubscription_agent
  @publication = N'SalesOrder_pub',
  @subscriber = N'TBSClient\SQLMirror',
  @subscriber_db = N'SalesOrders',
  @job_login = N'Techbrothers\clusteradmin',
  @job_password = Mraza1904,
  @subscriber_security_mode = 1,
  @frequency_type = 64,
  @frequency_interval = 0,
  @frequency_relative_interval = 0,
  @frequency_recurrence_factor = 0,
  @frequency_subday = 0,
  @frequency_subday_interval = 0,
  @active_start_time_of_day = 0,
  @active_end_time_of_day = 235959,
  @active_start_date = 20150310,
  @active_end_date = 99991231,
  @enabled_for_syncmgr = N'False',
  @dts_package_location = N'Distributor'

GO
-----------------END: Script to be run at Publisher 'TBSSQL\SQLPROD'-----------------

How to Create Snapshot Replication in SQL Server