Hello, and welcome to this introduction of ApexSQL Job. ApexSQL Job is a powerful application used to manage and automate SQL Server Agent Jobs. Its wide range of features allow users to manage SQL Server schedules, define and manage SQL Server Job alerts, view detailed job history, monitor SQL Server Agent logs, and export job details to various file formats. In this video, we'll demonstrate some of its features and capabilities.
To begin, let's connect to a SQL Server instance by clicking on the Add button in the Home tab and selecting the instance from the Server dropdown list. Choose the authentication type. Enter the credentials and click OK. Then, select a custom folder for the newly added SQL Server, or add it to an existing one. To add more SQL Servers, click on the Add button again and repeat the same steps.
Now, check to see if its agent service is up and running. A yellow triangle on the icon indicates the service is not running. To start it, click on the Start button on the Home tab. Let's create a new job by clicking on the New button under the Jobs tab. This will open the New Job Wizard.
In the New Job Wizard, under the General tab, choose the preferred SQL server where you want to create the job. Provide a name for the job, select its owner, and optionally, provide a meaningful description of its functionality. Additionally, choose a category for the job. Move down to the Steps tab of the New Job Wizard. Here, add a new step to the job by clicking on the New button.
In the New Job Step window, provide a name for the step. We recommend using a name that describes the step's functionality. Also, select the step type, among the 12 different types of tasks offered in the dropdown list. Choose the database on which to perform this job step, and write the command directly in the command box. Additionally, there is the option to load a prepared script directly from its folder.
Still within the New Job Wizard, go to the Advanced tab to further tune the Job Step settings. Here, control the action that will be taken when the step is either completed successfully or fails. In case of failure, specify the number of retries and elapsed time between retries. You may also choose where to write the output of the step execution. Click OK to complete step creation. To create more job steps, click on the New button again, and repeat the same steps. The Create a Job step will appear in the Steps list, where it can be further edited, deleted, and moved up or down on the list.
The next step now would be to set a scheduled time for our job, which will specify when and how frequently the job will be executed. To create a new schedule for a specific job, click on the Schedules tab on the left side of the New Job Wizard. Here, we could create a new custom schedule, or select an already created one by clicking on the Pick dropdown button. Let's now click on the New button to open the New Job Schedule window and go through its creation steps.
The New Job Schedule window allows you to specify when and how frequently the job will be executed. After entering a descriptive name for the schedule, select a type of schedule-- either Event or Time. Event is selected by default.
The Event schedule type indicates that the job will be triggered as a response to a specific event. One out of the two types of Events can be selected. In that case, the job could start when the SQL Server Agent starts, or when the CPU is in an idle state. Selection of either of those two directly affects the Summary.
If the Time is selected as a schedule type, set the desired job frequency in the frequency area. Choose the job recurrences and job start and end dates. Chosen schedule parameters can be seen in the Summary section. The Active checkout must be marked in order for the selected schedule to become active upon its creation. Click OK to finish setting the schedule.
Once the new schedule for the job is created, it is shown under the Schedules list, where it can be edited or deleted at any time by clicking on the Edit or Delete buttons, respectively. Once the job schedule is set, the next focus of job creation is setting job alerts. These can be accessed by clicking on the Alerts tab in the New Job Wizard. Click on the Add button to create a new alert.
There are three types of alerts that can be created in the SQL Server Agent. SQL Server Event Alert. This alert is fired when an event with a specific error number or error severity level occurs. SQL Server Performance Condition Alert. This alert is fired when a specific performance counter exceeds, equals, or falls below a predefined threshold value. WMI Event Alert. This alert is fired when a specific Windows Management Instrumentation, WMI, event occurs.
Apex SQL Job allows the user to create all three mentioned SQL Server Agent Alert types. Each alert type has its own configuration setting for this demo, we will focus only on the SQL Server Event Alert type, which is chosen by default. When SQL Server Event Alert is selected, you must specify the following settings-- the alert name, the database to which the alert will apply, and whether it should fire in response to a specific SQL server error number, or error severity level.
The next step in creating an alert is to specify the action that should be performed as a response to the event. In the Create Alert window, click on the Response tab on the left to configure the response type. There are two types of responses for an event. Execute Job. Select with SQL Server Agent