Overview
SSJAV is a web application built with React, Express, and MSSQL for the company's internal usage for SQL server Agent Jobs to allow employees to easily track server job's status and metrics and statistics. It also provides API endpoints for data integration with other on-premise application. Users can see schedule of agent job through daily calendar as well as in-depth details and graphs of each task of a job and each step of a task. While the application provides insight on which step of a task of a job took more than others and why it took that much time, it also provides non-technical users to track agent jobs that related to their work. Providing RESTful API also enriches the compability of the application with other on-premise application being easily integrate with other applications.
Problem Statement
Employees that their job is on the server agent jobs were getting informed through email or SMS on status of SQL Server Agent Job, other than authorized staff to access SQL server agent jobs were unable to check current status of the task that is related to their work through a UI, this half-blindness effect was causing other negative side effect in their work. There was a need to a UI that can be understandable by both technical and non-technical staff to track related tasks and see how the task is going, when is it going to be finished or is there any error? Alongside with metrics and statistical information.
The Employees have works that depend on SQL Server Agent Jobs, they were getting notified on the status of agent jobs via email or SMS and only the authorized staff does have access to check SQL Server Agent Jobs through MS SQL Server Management Studio. Due to this half-blindness, being unable to check status of agent jobs via a user interface were having negative impact on the company's workflow. There was a need for an user interface that can be understandable by both technical and non-technical staff to track agent jobs and their tasks that are related to their works.
- A requested feature in the user interface involved displaying agent jobs with their start and end times in a monthly calendar or Gantt chart, which proved challenging due to the uncertainty of when certain job tasks would conclude. Although there were potential solutions to still represent jobs in a monthly format, after several scrum meetings, the decision was made to present jobs in a daily calendar. This was a compromise; while it allowed for easier visibility of jobs on a daily basis, it sacrificed the overall perspective of when each job would start and finish in a monthly calendar or Gantt chart.
- Grasping how SQL Server Agent Jobs were organized in the database was essential for the project. Since I was new to MS SQL Server, locating and comprehending the tables related to agent jobs, like sysjobs, required some time to fully grasp, particularly regarding time-related information, such as the duration to finish a step in a job task, which differed from the overall job completion time.
- Determining the duration of a step and translating that into task completion times, along with similar calculations for overall job completion, posed some challenges due to their data storage methods. However, after one or two brief meetings, I gained a clear understanding of how agent job data was organized, allowing me to effortlessly compute completion times for steps, tasks, and jobs. Subsequently, presenting their data in visual charts became significantly easier.
- Gained extensive knowledge of MS SQL and how it differentiates from other SQL database solutions. Alongside understanding how SQL Server Agent Jobs were stored in the database including difference between time related fields for steps, tasks, and jobs.
- Enhanced my backend expertise by designing and building RESTful APIs and utilizing JavaScript for server-side development while sticking to industry best practices.
- Expanded my understanding of Node.js and Express, focusing on keeping backend application secure and reliable through commonly used best practices.