SQL Server Automation Tutorial from Coding compiler | SQL Server DBA Tutorial – Part 6, this blog is the sixth tutorial in the series, here you are going to learn about how SQL server automation works. Before going to learn this tutorial, we recommend you to go through the previous SQL Server Restoration DBA Tutorial – Part 5.
Topics Covered in this SQL Server DBA Tutorial
- Automation Introduction
- Ways of Performing Automation
- Autonation Jobs
- How to Create a Job
- Post Job Steps
- Troubleshooting the Job Failures
- Automation Maintenance Plans
SQL Server AUTOMATION Tutorial
Automation refers to scheduling the regular maintenance tasks. Whenever schedule time comes automatically that task will be executed. DBA regular activities can be automated we can get the status f the task in the form of alert and helpful while generating reports.
Automation can be done in 2 ways,
- Jobs
- Maintenance plans
JOBS
Sql server agent jobs useful to perform daily scheduled maintenance tasks.
- All these jobs falls under database maintenance category
- We can start, stop, enable, disable job as per our requirement
- Easy to create and maintain jobs. Whenever any issue happen we can troubleshoot easily.
- Scheduling also easy with jobs.
How to create a job
Right click on the job folder under sql server agent → Select New job from options menu
General → it helps DBA’s to provide identification of jobs
Name → we need to provide name for the job
Owner → always job owner is to be sysadmin(SA)
Category → under which category this job falls, mostly all DBA tasks falls under database maintenance
Decryption → provide some description about the job
Enabled → job will be created now, we can enable later using this option.
Steps: Steps are nothing but a task under job
Select New button
Step name → PROVIDES DETAILS OF STEP OF JOB
Type → sql server uses T-sql language by default
Runs → Only that privileged persons can able to run this job. If we specify
Database → By default it points to master, we can change as per requirement
Command → T-sql command to perform the task
Parse → we can verify syntax errors of the T-sql command using this option.
On success → provide next steps once the first step complets means it has go to next step (or) it has to quit the job.
Retry attempts → no.of retry attempts need to specify if a job fails it will attempt try for specific times and report to user if fails again.
Retry Internal → wait for specified time and attempt next try
On failure → on failure where this job have to go whether it has to start from beginning (or) quit the job reporting failure to user.
Output file → we configure output file to get the information about the job. If it fails with what error it fails we will get from this.
Append output → overwrites the existing job information
Log to table → if we want job running status data in the form of table.
Output in history → to view history of job execution
We can add, edit, delete steps more the order of steps using insert, edit and delete buttons.
Name → name of schedule we need to provide here
Schedule type → whether it is one time job (or) daily job we need to provide type.
Enabled → schedules it to be enable now (or) later
Occurs → whether it is daily (or) weekly (or) Monthly
Recurs every → it will re-run the stops on specified day.
Occurs every → we need to specify the frequency of executing this job means every 1 hour (or) 4 hours as per our requirement
Summary of job will be displayed at the end.
Once all the details provide click OK, it will create a job with specified name.
Post Job Steps
- Once the job is created we can start, stop, enable disable and delete job by right clicking on the job
- We can edit the ob if we want
- We can script out the job and copy the script for creating same job on other server during migration
Right click on job → select script job as → open job in new query window → copy the script of job place on different server and execute.
Troubleshooting the Job Failures
- Output file → open output file which we configured while creating jobs under advanced steps of job. It records all the details of job for troubleshoot.
- View history → right click on job go to view history provides of job execution. If fails with what error it failed we’ll get all details here.
- Verify in Msdb tables like sysjobs, sysjobhistory
- Go through sql server error logs for failure notification messages.
Related SQL Server DBA Tutorials And Interview Questions
SQL Server DBA Tutorial – Part 1
SQL Server DBA Tutorial – Part 2.
SQL Server DBA Tutorial – Part 3
SQL Server DBA Tutorial- Part 4
SQL Server Interview Questions