MSSQL Replication

Check

USE distribution

GO

DECLARE @Publisher sysname,

@PublisherDB sysname;


SET @Publisher = @@servername;

SET @PublisherDB = '%';


SELECT [Replication Status] = CASE MD.status

WHEN 1 THEN 'Started'

WHEN 2 THEN 'Succeeded'

WHEN 3 THEN 'In progress'

WHEN 4 THEN 'Idle'

WHEN 5 THEN 'Retrying'

WHEN 6 THEN 'Failed'

END,

MD.agent_name,

[Subscriber] = S.name,

[Subscriber DB] = A.subscriber_db,

[Publisher DB] = MD.publisher_db,

Publisher = MD.publisher,

[Current Latency (sec)] = MD.cur_latency,

[Current Latency (hh:mm:ss)] = RIGHT('00' + CAST(MD.cur_latency/3600 AS VARCHAR), 2) +

':' + RIGHT('00' + CAST((MD.cur_latency%3600)/60 AS VARCHAR), 2) +

':' + RIGHT('00' + CAST(MD.cur_latency%60 AS VARCHAR), 2),

[Latency Threshold (min)] = CAST(T.value As Int),

[Agent Last Stopped (sec)] = DATEDIFF(hour, agentstoptime, getdate()) - 1,

[Agent Last Sync] = MD.last_distsync,

(SELECT TOP 1 LEFT(comments,50) comments

FROM [distribution].dbo.MSdistribution_history

WHERE agent_id = a.id

ORDER BY time desc) AS comment

FROM dbo.MSreplication_monitordata MD WITH(nolock)

INNER JOIN dbo.MSdistribution_agents A WITH(nolock)

ON A.id = MD.agent_id

INNER JOIN master.sys.servers S WITH(nolock)

ON S.server_id = A.subscriber_id

INNER JOIN dbo.MSpublicationthresholds T WITH(nolock)

ON T.publication_id = MD.publication_id

AND T.metric_id = 2 -- Latency

WHERE MD.publisher = @Publisher

AND MD.publisher_db LIKE @PublisherDB

AND MD.publication_type = 0 -- 0 = Transactional publication

AND MD.agent_type = 3; -- 3 = distribution agent

Replication Monitor

Note the dropdowns on these screens which provide additional information about the various replication components....

Setup

Add Service Accounts to correct Roles

USE [master]

GO

EXEC sp_addrolemember N'sysadmin', N'myAD\myLogServiceAccount'


USE [distribution]

GO

EXEC sp_addrolemember N'db_owner', N'myAD\mySnapServiceAccount'

EXEC sp_addrolemember N'db_owner', N'myAD\myLogServiceAccount'

EXEC sp_addrolemember N'db_owner', N'myAD\myDistServiceAccount'


USE [mySourceDB]

GO

EXEC sp_addrolemember N'db_owner', N'myAD\mySnapServiceAccount'

EXEC sp_addrolemember N'db_owner', N'myAD\myLogServiceAccount'

Snapshot Share Permissions

Add myAD\mySnapServiceAccount to have read, write, and modify permissions on the snapshot share (e.g. E:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ReplData)

Add myAD\myDistServiceAccount to have read permissions on the snapshot share (e.g. E:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ReplData) (similar to above)

Configure Distribution

USE [master]


EXEC sp_adddistributor @distributor = N'myDistHostname', @password = N'Password'

GO


EXEC sp_adddistributiondb @database = N'distribution', @data_folder = N'E:\SQLData', @log_folder = N'F:\SQLLog', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1

GO

@database - name of the database to be created. Generally called "distribution", but it is technically possible to have multiple distribution databases with different names.@data_folder - name of the directory to store the distribution database data file (e.g. E:\SQLData)@data_file - if NULL (recommended) the stored procedure constructs a file name using the database name@data_file_size - default is 5MB@

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'E:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'

ELSE

EXEC sp_addextendedproperty N'SnapshotFolder', N'E:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'

GO


EXEC sp_adddistpublisher @publisher = N'myDistHostname', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'E:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'

GO

Create Publication

USE [mySoureceDB]


EXEC sp_replicationdboption @dbname = N'mySourceDB', @optname = N'publish', @value = N'true'

GO


USE [mySoureceDB]


EXEC [mySourceDB].sys.sp_addlogreader_agent @job_login = N'myAD\myLogServiceAccount', @job_password = null, @publisher_security_mode = 1, @job_name = null

GO


-- Adding the transactional publication


USE [mySourceDB]


EXEC sp_addpublication @publication = N'mySourceDB', @description = N'Transactional publication of database ''mySourceDB'' from Publisher ''MYDISTHOSTNAME''.', @sync_method = N'concurrent', @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, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

GO


EXEC sp_addpublication_snapshot @publication = N'mySourceDB', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'myAD\mySnapServiceAccount', @job_password = null, @publisher_security_mode = 1


USE [mySourceDB]

EXEC sp_addarticle @publication = N'mySourceDB', @article = N'Addresses', @source_owner = N'address', @source_object = N'Addresses', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Addresses', @destination_owner = N'address', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_addressAddresses', @del_cmd = N'CALL sp_MSdel_addressAddresses', @upd_cmd = N'SCALL sp_MSupd_addressAddresses'

GO