I would like to share a script to enable database for replication. So it is the step before adding publication and articles in replication.

USE master
GO

exec sp_replicationdboption @dbname = N'PublisherDB', @optname = N'publish', @value = N'true'
GO

exec [PublisherDB].sys.sp_addlogreader_agent @job_login = N'Domain\UserName', @job_password = null, @publisher_security_mode = 1
-- Or (for SQL User 'sa' or any system admin user)
exec [PublisherDB].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N''
GO

exec [PublisherDB].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO

Published under: SQL Server Tips ·  ·  ·  ·