In this article, I am going to introduce some advanced usages of the SQL Server Agent service in Microsoft SQL Server. In my previous article, Introduction to SQL Server Agent, I have discussed in detail how to use the service and the various components related to the service. To recap briefly, the SQL Server Agent is a job scheduler service within SQL Server and allows us to schedule T-SQL scripts, SSIS jobs, automate database backups and other tasks etc. In the last article, I have shown how to schedule a simple T-SQL script using the SQL Server Agent. This article will focus more on advanced concepts like scheduling a package in SSIS and processing an OLAP cube.
In this tutorial, we are going to perform the following tasks:
- Run a database backup job
- Schedule a T-SQL script
- Execute an SSIS package from the SSIS catalog
- Process an SSAS OLAP cube
Let’s get started with the above.
Run a database backup job using the SQL Server Agent Service
Backing up a database is a very important job as it relates to the data that is being used by the service. Usually, it depends on the company to company about how often you should backup your databases and what should your backup type be. Usually, the most common practice is backing up every day during off-business hours so that it doesn’t impact the business. Let us now create a job in the SQL Server Agent and back up an existing database and schedule the backup to run daily at midnight.
Right-click on the database for which you want to schedule the backup. Select Tasks and then click on Backup. On the Backup window, select the appropriate options, for example, if you would want to have a full database backup or a differential backup. You can also select the backup destination. Once you have selected the options and you are good to go, click on Script and copy the script to be used while scheduling the job.
Figure 1 – Generating a backup script for the database
Once the script is copied, the next step is to create a job using the Agent Service. Right-click on Jobs and select New Job. Provide a suitable name for the job and navigate to Steps.
Figure 2 – New Agent Job for Database Backup
In the new Step page, provide a valid name for the step and paste the backup T-SQL script in the Command window. Click OK once done and navigate to the Schedules tab.
Figure 3 – Adding the Backup Step
Click on New, provide a valid name for the schedule and choose a schedule to execute the job daily at midnight. Click OK once done and submit the agent job. That’s it, your job is now scheduled to execute daily at midnight, and it will generate a backup of the database in the location mentioned in the path. You can change the backup schedule by adjusting the frequency under the Schedules tab.
Figure 4 – Scheduling daily execution
Schedule a T-SQL script
As already mentioned in my previous article, the easiest part of SQL Server Agent is to execute a T-SQL statement on a schedule. Just create a new Job following the steps mentioned above and go to the Steps.
Figure 5 – Creating a T-SQL job step in the Agent Service
Once the step is created, the next step is to schedule the job. Navigate to the Schedules tab and choose an appropriate schedule. For this, I am going to choose every minute such that the script will be executed, and the table will be populated with the latest data every minute.
Figure 6 – Schedule job to run every minute
Click OK once done and you are done. The job is now scheduled to run every minute.
Execute an SSIS package from the SSIS catalog
As a DBA, you might also need to deal with deploying and scheduling SSIS packages on the SQL Server. Usually, most SSIS jobs are developed to move data in a batch from one location to another. Since these operations are resource-intensive, we should try to schedule these jobs during off-business hours. Now, since we already have created a database backup job at midnight, it is advisable that we schedule the SSIS package before or after the database backup is generated. Let us schedule the package to be executed at 2:00 am.
For this article, I already have an SSIS package deployed to the SSIS catalog. However, if you need help in building an SSIS package, you can follow the official tutorials to get started.
Figure 7 – SSIS Package deployed in SSIS Catalog
As you can see in the above figure, the SSIS package is deployed in the catalog database. We are going to schedule this package using the SQL Server Agent. Head over to SSMS and create a new job. In the Steps, provide a suitable name for the step and select Type as SQL Server Integration Package Services. Select the Run as an option as SQL Server Agent Service Account. This will execute the job under the role of the service account. This translates to the service account having the right permissions to execute the package; however, this is beyond the scope of the article and shall be covered in some other article later.
Figure 8 – Job Step for SSIS Package
Also, select the server on which the SSIS catalog exists and then select the package that you want to schedule for execution. Schedule the job in a similar fashion we did earlier in the article and it is done.
Figure 9 – SSIS Package Scheduled
Process an SSAS OLAP cube
The method of scheduling an SSAS cube is almost like that of scheduling an SSIS package. As you can see in the figure below, I have an OLAP cube deployed to the Analysis Services Server. You can learn to create a new SSAS cube from scratch by following the official tutorial here.
Figure 10 – OLAP Cube Deployed on SSAS Server
Right-click on the cube that you want to process and click Process. Verify that all the parameters for processing the cube are OK. Click on Scripts to generate the script required to process the OLAP cube. It will generate an XMLA script that you can use to process the OLAP cube.
Figure 11 – XMLA Script for processing the OLAP cube
Now, follow the same steps as mentioned above for creating a new job in the SQL Server Agent. Once in the Steps, select the name of the server and paste the XMLA script that has been generated in the previous step.
Figure 12 – Creating a job step for OLAP Cube
Click OK once done. Usually, an OLAP cube is processed once the data warehouse has been refreshed by the SSIS job. Since the SSIS job was scheduled to execute at 2am in the morning every day, we will schedule the OLAP processing after an hour, i.e. at 3am.
Figure 13 – OLAP Process Scheduled
That’s all. You can now see all the scheduled jobs in the SQL Server Agent browser in the navigation pane.
Figure 14 – Scheduled Jobs
Conclusion
This article explains how to create and maintain multiple job schedules within SQL Server using the SQL Server Agent utility. This is a very powerful utility and can be achieved to automate a lot of normal tasks that need to be done in the life of a DBA or a developer. It is also important that you schedule the jobs so that you don’t overload the server by running all the jobs at the same time. You need to take into consideration the average execution time of each of the jobs and then schedule time accordingly such that there are the least chances of executing more jobs at the same time.
He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.
In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn
View all posts by Aveek Das
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021