Scheduling SQL Server Tasks on Linux Part 2: Advanced Cron Topics

By: Daniel Farina   |   Updated: 2018-10-18   |   Comments   |   Related: > SQL Server on Linux

Problem

In a previous tip we looked at how to use Cron to schedule tasks for SQL Server on Linux. In this tip we take a deeper look to help answer these questions: Is there is any security configuration that I can use? Is there a way to view a log of cron work? How I can check if a job was successful or failed? In this tip I will answer those questions.

Solution

In my previous tip in this series I introduced you to the cron daemon and its configuration file, crontab. I omitted many items in order to make the introduction simple. My intentions with that tip were mostly to introduce you to the format of crontab file. Now I will cover more advanced topics.

Advanced Cron Topics

It is not enough to just know the crontab file format to use cron. Think about it. It is pointless to schedule a job if you won’t be able to know whether it was successful or failed.

Here is a list of topics I will cover in this tip:

• Crontab Variables
• Crontab Logging
• Crontab Security
• Crontab Shortcuts

Let’s start.

Crontab Variables

You can define and use variables in a crontab file. There are predefined variables where the name has a specific use. Take a look at the next table. Some of those variables have a default value which can be overridden by redefining it. Also, you can assign an empty string if you want to blank out the contents of a variable (i.e. VARIABLE="").

Something important to note is that cron doesn’t perform variable substitution. In layman’s terms, you can’t assign a variable the sum or composition of other variables. Let’s consider the following example:

HOME=/home/daniel
PATH=HOME:/bin/:/usr/bin

On the previous example the user who wrote the crontab lines wants the PATH variable to contain the directory specified in the HOME variable and additionally /bin and /usr/bin directories. But since there is no variable substitution in cron, the PATH will take "HOME:/bin/:/usr/bin" as a literal. The proper way to write the previous lines is as follows:

HOME=/home/daniel
PATH=/home/daniel:/bin/:/usr/bin

In the next image you will see a crontab file with SHELL and PATH variables declared.

The next code fragment shows how to use a variable in a job definition. It is declaring a variable SQLDATA with the path to SQL Server backups and each day at midnight moves the backup files to an external device.

SQLDATA=/var/opt/mssql/data
0 0 * * *  mv $SQLDATA/*.bak /mnt/

Cron Logging

Cron jobs are logged by default to the file /var/log/syslog, which is the file where all services log statuses and messages. There are different ways you can view its content.

The next script uses the grep command to search the pattern "cron" in the file /var/log/syslog (the -i is to make the search case insensitive).

grep -i cron /var/log/syslog

Also, we can use a pipe to send the output to the tail command if we are interested only in the last 10 lines.

grep -i cron /var/log/syslog | tail

The systemctl command shows the status of cron service as well as its last entries in syslog.

systemctl status cron

Also, we can use journalctl (a command that queries the system journal) and pass the system unit cron as a parameter. It has the peculiarity that shows system reboots.

journalctl -u cron

Creating a cron.log File

We can create a single file that holds the cron messages by editing the /etc/rsyslog.d/50-default.conf file and uncommenting the line that starts with #cron.*. On the next image you can see that line.

After you uncomment that line and save the changes you should run the following command to restart the rsyslog service.

systemctl restart rsyslog

Crontab Security

Cron has two files, /etc/cron.allow and /etc/cron.deny that we can use to configure and manage the users allowed to have a personal crontab. The system-wide crontab will work regardless of the contents of those files. Those files have the same format which is one user per line. Here is how they work.

• cron.allow - defines the users that are able to have their crontab file and therefore use cron.
• cron.deny - contains all the users that are forbidden to have their crontab file and therefore use cron.
• Depending on your distribution you may not have one of those files but you can create them.

In order to protect the system-wide crontab you have to set the proper permissions to the /etc/crontab file. The following command will give read and write permissions only for the root user and no other user will be able to read the file.

chmod 600 /etc/crontab

Crontab Shortcuts

Cron also has special folders that serve as shortcuts to schedule jobs at specific times. In the next table I enumerate those folders.

The system-wide crontab is what executes the content of each of those folders, so it may be useful to set those folders with root only permission to avoid the chance that a user puts malicious scripts in those folders.

If you want to know at which specific time the scripts in those folders will execute, you have to look at the /etc/crontab file. The next code is the content of my Ubuntu’s /etc/crontab file.

# m h dom mon dow user  command
17 *    * * *   root    cd / && run-parts --report /etc/cron.hourly
25 6    * * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )
47 6    * * 7   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.weekly )
52 6    1 * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.monthly )

Based on the previous example, contents of /etc/cron.hourly folder will start to execute at minute 17 every hour. Content of /etc/cron.weekly directory will run daily at 06:25 AM. The scripts in /etc/cron.weekly folder will run at 06:47 AM on Sunday each week. Finally, the scripts in /etc/monthly will execute at 06:52 AM the first day of the month.

Next Steps

• Read my previous tip in this series.
• New to Linux? This is where you must begin: Getting Started with SQL Server on Linux.
• After reading the previous tip I suggest you read 7 Things Every SQL Server DBA Should Know About Linux.
• In the following tip you will find the most used commands for SQL Server DBA’s: Top 10 Linux Commands for SQL Server DBAs.
• Also, if you want to learn other Linux administration basics you can read my previous tips where I cover how to check for CPU Usage, Disk I/O and Disk Space.
• For more information regarding SQL Server and Linux check the SQL Server on Linux Tips Category.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips