Embedded Files
Check
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
Replication Monitor
Note the dropdowns on these screens which provide additional information about the various replication components....
Setup
Setup
Add Service Accounts to correct Roles
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
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
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
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
Bibliography
Bibliography
https://www.brentozar.com/archive/2014/07/performance-tuning-sql-server-transactional-replication-checklist/https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/https://blogs.msdn.microsoft.com/sqlserverfaq/2010/09/17/sometimes-replication-monitor-shows-number-of-undistributed-transactions-as-a-very-high-number-but-replication-itself-works-properly/http://www.sqlsoldier.com/wp/sqlserver/measuringtransactionalreplicationlatencywithouttracertokenshttps://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms151740(v=sql.90) Frequently Asked Questions for Replication Administratorshttps://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms152483(v=sql.90) Scripting replicationhttps://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms151256(v=sql.90) How to: Delete a Publication (SQL Server Management Studio)https://repltalk.com/https://repltalk.com/2010/09/20/how-to-monitor-the-health-of-sql-server-replication/https://repltalk.com/2010/03/03/links-for-getting-started-with-transactional-replication/https://dba.stackexchange.com/questions/52662/is-replication-possible-on-two-different-domainshttps://www.sqlservercentral.com/stairways/stairway-to-sql-server-replication
Setuphttps://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-adddistributiondb-transact-sql?view=sql-server-ver15
Page updated
Google Sites
Report abuse