How to Enable and Configure SQL Server Agent for SQL Server 2019 on RHEL 8.1


By:   |   Updated: 2021-12-02   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | More > SQL Server on Linux


Problem

SQL Server Agent is an important feature of SQL Server to schedule and automate activities. To get SQL Agent to work on Linux, there are a few steps you need to take to get it to work. In this tutorial, I will explain the steps to enable and configure SQL Server Agent on a newly installed SQL Server 2019 instance running on RHEL8.1 operating system.

Solution

The SQL Server Agent service is responsible for the job scheduler that is used to schedule and automate database activities.

Note: If you are using SQL Server 2017 CU4 and above including SQL Server 2019 then you just need to enable this feature and you don't need to install it separately whereas if you are running with SQL Server 2017 CU3 and below then you need to install it as a separate package. If you are upgrading from SQL Server 2017 CU3 or below with the Agent installed, SQL Server Agent will be enabled automatically and previous Agent packages will be uninstalled.

Note, this is 9th article in this series, read the previous articles to understand how to install and configure SQL Server 2019 on RHEL 8.1. See the next steps at the end of this article for links to the other articles.

Checking Status of SQL Server Agent

I installed SQL Server 2019 on RHEL 8.1 and now I have connected to that instance remotely using SQL Server Management Studio as shown in the below image.

The SQL Server Agent folder is disabled in the below image.

ssms object explorer

When we right click on SQL Server Agent, we can see this option to start it is grayed out.

sql server agent

The Properties option is enabled and when we try to open the properties page, we get the below error.

sql server agent xps error

It says the "Agent XPs" for this service are disabled and to use sp_configure to enable.

Enabling SQL Server Agent XPs

Let's check the status of the Agent XPs using the following. We first need to enable the "show advanced options" to be able to see this configuration option.

--Enable advanced options
--Check configured value of Agent XPs
sp_configure 'Show advanced options',1
GO
RECONFIGURE
GO
sp_configure 'Agent XPs'

The output shows that Agent XPs are disabled because the run value is set to 0.

sp_configure

We can try to enable Agent XPs by setting the value to 1 using the below command.

--Enable Agent XPs
sp_configure 'Agent XPs', 1
GO
RECONFIGURE

We can see the change below.

enable agent xps

Again, let's check the configured value and we can see the run value is now set to 1.

query results

Now, we will check SQL Server Management Studio to validate above changes. Refresh the SQL Server instance node by right clicking on it and selecting Refresh.

refresh ssms

We can see that SQL Server Agent now shows that it is running.

ssms object explorer

Next, I will create a simple job and run it to ensure SQL Server Agent is fully enabled. I created a Job named "Test" to fetch the SQL Server version, but when I execute this job it throws this error: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022).

sql server agent error

Something is not working correctly. If we right click on SQL Server Agent, we can see some additional options are enabled, but some are still grayed out.

sql agent menu options

I did some research on MSDN and found the below steps to enable SQL Server Agent for a Linux based SQL Server instance.

First enable SQL Server Agent using mssql-conf utility by running the below commands on the RHEL system. This command will enable SQL Server Agent after restarting the SQL Server service.

--Enable SQL Server Agent
sudo /opt/mssql/bin/mssql-conf set sqlagent.enable true
 
--Restart SQL Server
systemctl restart mssql-server

Have a look at above command executions below.

linux command line sql agent

After enabling SQL Server Agent using the above command this still did not help and the job kept failing.

To fix this issue, we need to add the hostname with and without the domain in the /etc/hosts files on the RHEL system. Run the below command to open the host file in a text editor.

--Open hosts file
sudo nano /etc/hosts

Below shows where I added my server name. Enter the password if prompted.

linux command line sql agent

Once you enter the hostname with and without the domain, save the changes as shown below under the red arrow.

linux command line sql agent
linux command line sql agent

Once saved, you can close the hosts file.

Test SQL Agent Job Again

We will test the job again and we can see below that it executed successfully.

sql agent job run

You can also right click on SQL Server Agent and select Properties to show that this is now working too.

sql agent properties
Next Steps

Go ahead and schedule your jobs to run automatically based on your desired time and schedule.






get scripts

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips


Article Last Updated: 2021-12-02

Comments For This Article





download














vote


get free sql tips
agree to terms