<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=204641&amp;fmt=gif">

Customizing your dbWatch Job for SQL Servers

Posted by Rey Lawrence Torrecampo on Feb 8, 2022 4:54:32 PM

What’s inside a template job? 

After learning about the different directories, we will now proceed on how you can add a job template to your active dbWatch Control Center. Download the job_templates.json to your local server. Preferably, this should be in the same machine where your active dbWatch Control Center is placed.  

 

1-Jan-13-2022-04-54-29-74-AM

 

Previously, we discussed the file structure of dbWatch Control Center. We learned how each directory in dbWatch Control Center’s architecture works to achieve a holistic monitoring and management solution. dbWatch Control Center’s file structure is separated into four different directories. The database monitor’s install directory is found in C:\Program Files\ControlCenter. The database monitor’s install directory holds dbWatch’s User Interface and configuration files.

Next, the database monitor’s Library directory contains dbWatch Control Center’s software.  More information about the database monitor’s Library directory can be read in the link provided. Finally, you have the Server work directory. When you open this directory, you will be able to see the following files: 

 

Graphical user interface, text

Description automatically generated with medium confidence

 

The Server Work Directory contains all the comigration, log and data files for the software and domain. You can read more about the different directories inside by going to the link.  

 

What’s inside a template job? 

After learning about the different directories, we will now proceed on how you can add a job template to your active dbWatch Control Center. Download the job_templates.json to your local server. Preferably, this should be in the same machine where your active dbWatch Control Center is placed.  

 

The Json file for the Jobs template should contain the following: 

Text

Description automatically generated

Figure 2 – Job Template for dbWatch Control Center 

 

Note that the above image is a compressed version of the actual file. In reality, there are multiple jobs linked to a template name. For example, the template name “Maintenance (MS SQL Server)” has ms_cycle_error_log, ms_frameWork_job, ms_dbcc_checkdb, etc attached to it.   

 

Text

Description automatically generated

Figure 3 – Maintenance Template for Microsoft SQL Server 

 

 

These maintenance jobs are linked to the actual jobs that will be installed to dbWatch Control Center. 

 

Graphical user interface, application

Description automatically generated

Figure 4 – Maintenance Jobs for Microsoft SQL Server instance 

 

As you can see, the ones listed above corresponds to their job names, such as ms_dbcc_checkdb relates to dbcc_checkdb job. These listed job items in the jJson file are called artifact id. To get the artifact id, you can run this FDL query in the FDL console: 

Replace $value with the instance name, then run the FDL query. You will get all the artifact IDs installed in that instance.  

 

Graphical user interface, text, application, email

Description automatically generated

Figure 5 – FDL query to extract artifact jobs in Microsoft SQL Server instance 

 

Note that the artifact ID is followed by its version number. For instance, we have “com.dbwatch.job:ms_agent_jobs_check_job:2.8” highlighted above. The artifact is com.dbwatch.job:ms_agent_jobs_check_job and its version number is 2.8. To verify this, let’s look at the job details of Agent Jobs Check. 

 

Graphical user interface, text, application, email

Description automatically generated

Figure 6 – Job Editor for Agent Jobs Check 

 

The task name is “Agent Jobs Check” while its version is 2.8. 

 

 

Adding Job Template to dbWatch Control Center 

After downloading the json file, put it in the Server Work Directory, specifically in C:\ProgramData\dbWatchControlCenter\config\domain

 

Graphical user interface

Description automatically generated

Figure 7 – Appending Job Template to Server Work Directory 

 

 

Next, rerun your dbWatch Control Center monitor.  You can verify if the job template has been installed by right- clicking on one instance and selecting configure jobs. You should be able to see a “Template” tab after the “Custom” tab.  

Graphical user interface, text, application

Description automatically generated

Figure 8 – dbWatch Control Center monitor with active Job Template 

 

 

Let’s try two scenarios on the application of a template job. The first one is editing a current dbWatch instance and the second one is adding a new instance. For the first scenario, I have an microsoft sql server instance installed with the name sql_local. When I configure the jobs, I have the option to choose among the templates I can apply in Figure 2. 

 

Graphical user interface, application

Description automatically generated

Figure 9 – Selecting Job Template for Microsoft SQL Server 

 

 

You can mix and match any of the selections in Figure 9. Just be mindful ofon what job template you wish to apply. Don’t worry if the template is inapplicable to your instance such as ticking “Test (Oracle)” to your Microsoft SQL Server instance. You can easily verify it with the jobs listed in the “Template” tab as seen in Figure 10.  

 

Graphical user interface, text, application, email

Description automatically generated

Figure 10 – Maintenance and Test jobs for Oracle with Disable Mode 

 

 

Then, select one of the modes available: Keep, Disable, or Uninstall. These Modes are for the dbWatch jobs currently installed to your Microsoft SQL Server instance. As the name implies, “Keep” instills all the current jobs and jobs specified in the template will be installed. “Disable”, on the other hand, disables all non-specified and non-relevant dbWatch jobs. “Uninstall” will uninstall jobs not part of the list. 

 

Graphical user interface, text, application, email

Description automatically generated

Figure 11 – Options for Templates 

 

Once you’re satisfied with the options, click “Sync” and it will apply the Mode and install the template jobs. In Figure 12, you can see the Job Templates applied to the local instance.  

 

 

Graphical user interface, text, application, email

Description automatically generated

Figure 12 – sql_local with Maintenance Jobs (Mode: Disable, Template: Test(Oracle) and Maintenance (MS SQL Server) 

 

Graphical user interface, application

Description automatically generated

Figure 13 – Default Job Configuration of sql_2017_Enterprise_edition 

 

Graphical user interface, text, application, email

Description automatically generated

Figure 14 – Templated Job for sql_2017_Enterprise_edition 

 

You can see a Before (Figure 13) and After (Figure 14) when the Job Template is applied. Take note that, you can append the JSON file with the jobs you wish. This makes it easier to add or remove dbWatch jobs without selecting them one by one. 

 

-----------------------

 

Customization is an integral part of personalizing your dbWatch Control Center. By learning the file structure, you know which files to manipulate, and the added benefit of a job template lessens the hassle of adding and deleting jobs for your dbWatch Control Center.  

Get your free license for five instances valid for six months now! dbWatch Control Center free license.  

If you have any questions about dbWatch Control Center, feel free to contact me: rey@dbwatch.com  

For more information, visit www.dbWatch.com or the dbWatch wiki pages   

#DevOps #DBA #DatabaseManagement #databasemonitoring #sqlserver #sqlmonitor #sqlmanagement #dbmonitor #database monitor #databasefarms #clouddb #Azure  

 

-----------------------

 

About the Author: 

Rey Lawrence Torrecampo is a Pre-sales Engineer for dbWatch and  

a full-time Database Administrator. He has extensive knowledge in Postgres and MSSQL database management systems, with SQL as his most proficient language. 

 

-----------------------

 

Other Blogs: 

Recovering and migrating your dbWatch Control Center 

dbWatch 101: The dbWatch File Structure