question

HarryNangle-2823 avatar image
0 Votes"
HarryNangle-2823 asked AmeliaGu-msft commented

Replication without SQL Agent

Hi.

The site I am working at uses SQL2016 Std Ed and does not enable the SQL Agent. Scheduled tasks such as backups etc are run centrally from a 3rd party app.

I would like to set up transactional replication.

Most of the SQL Agent jobs that are created are essentially T-SQL tasks, using the TSQL subsystem in SQL Agent to issue EXEC <sproc> commands. However, 3 jobs - the LogReader Agent, Snapshot Agent and Distribution Agent - use the 'LogReader', 'Snapshot' and 'Distribution' subsystems. So these are not simply issuing EXEC <sproc> commands. For example:

the Snapshot Agent Job has 3 steps - steps 1 and 3 use TSQL but step 2 uses the Snapshot subsystem:

Step 1
Name: Snapshot Agent startup message.
Subsystem: TSQL
Command: sp_MSadd_snapshot_history @perfmon_increment = 0, @agent_id = 1, @runstatus = 1, @comments = N'Starting agent.'

Step 2
Name: Run agent.
Subsystem: Snapshot
Command: -Publisher [SQL01] -PublisherDB [pubdb] -Distributor [SQL02] -Publication [mypub] -DistributorSecurityMode 1

Step 3
Name: Detect nonlogged agent shutdown.
Subsystem: TSQL
Command: sp_MSdetect_nonlogged_shutdown @subsystem = 'Snapshot', @agent_id = 1

My question is: without enabling the SQL Server Agent (to be clear: not just disabling the jobs, but disabling the SQL Agent service itself after the initial setup) is it possible to run these replication tasks? Perhaps using PowerShell etc?

Many thanks for any help!

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thought I should give a quick update. This may/may not be more straight forward than I feared. Need to investigate LOGREAD.EXE, SNAPSHOT.EXE and DISTRIB.EXE. Documented:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-agent-administration?view=sql-server-ver15

0 Votes 0 ·

Hi @HarryNangle-2823,
Did the answer help you?
Please feel free to let us know if you have any other question.
If you find the post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·

1 Answer

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

Although you probably can technically do it without SQL Agent, by duplicating the jobs in something else, I would highly recommend against it.

This is built-in functionality which MS expects to be used with SQL Agent. It is likely some processes like updates will fail if it detects replication is installed, but SQL Agent is disabled.

Also, there are several replication related jobs created, not just the subscription jobs. You would need to duplicate all of them.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.