First SQL Server Agent overview

This series of articles from Sqlservercentral, please refer to the original text for details.

SQL Server Agent is the job scheduling and alerting service for SQL Server, which can greatly simplify the workload of the DBA if used properly.
The core of SQL Server Agent is the ability to run bulk jobs. "Batch" can be easily understood as "a series of actions", often a TSQL script. They can be run in the form of a job in a particular schedule, which you can choose at a point in time/segment where only a small number of users access the system. In other words, you can use SQL Server Agent to schedule bulk jobs. A bulk job can be a normal T-SQL task, or it can be a complex, multi-system job that runs between multiple DB instances and multiple computers. SQL Server Agent also allows users to be notified, such as when a job completes, or notifies the user by e-mail when an error occurs (the notification engine is described in the third article).
Job A DBA, it is possible that you can quickly deploy jobs on a new database server with SQL Server Agent. For example, you would first create a backup job of a system database that alerts you whether the backup job succeeded or failed. Subsequent work may include creating and dispatching business database backups, index maintenance (rebuilding or reorganization), and so on. If you are managing a relatively simple database system, you can use the SSMS Database Maintenance Plan Wizard to enable maintenance scheduled job scheduling.
This article is the first in this series, mainly to introduce the characteristics of SQL Server Agent and discuss the advantages of using SQL Server Agent. It focuses on the components and features of SQL Server Agent, shows how to start a service, demonstrates how to create and run a simple job from SSMs. Subsequent chapters drill down to the main components, including how SQL Server Agent uses SMO, PowerShell, and Transact-SQL.
This series focuses primarily on SQL Server 2005, SQL Server 2008, and SQL Server R2, and most of the content also applies to SQL Server 2000.
SQL Server Agent runs
SQL Server Agent is not enabled by default for the first installation of the database, so the first thing to use SQL Server Agent is to enable the SQL Server Agent service.
set SQL Server Agent to auto-start mode
To set up SQL Server Agent to start automatically, open SQL Server Configuration Manager and look for the Microsoft SQL Server, configuration Tools folder. You need to be the local Windows administrator (or its members) to start.
Once started, Configuration Manager will automatically connect to the local, click on the SQL Server service on the left tree, and in the right panel you will see the list of SQL Server services installed. There should be one called SQL Server Agent (instance name), which is the name you set when you installed the database, or the default "Mssqlsever". If the boot mode is already automatic, very good, it has been properly configured during the installation process. Otherwise, double-click the data SQL Server Agent, click the "Services" tab in the Pop-up proxy properties, then launch the mode item to expand the drop-down list, select Automatic, and finally click Apply (1.1 for the interface you should see). SQL Server Agent starts automatically when the service restarts. If for some reason SQL Server Agent is not running, start the service now.
Figure 1.1 SQL Server Agent startup mode configuration
Service Account Selection
You need to make sure that you have a suitable service account to configure based on what you will do with SQL Server Agent. If you follow the actions below, on the SQL Server Agent Properties page, click on the "Login" tab, the job base action, you can use the built-in account, such as Network Service (this service account can be set regardless of whether it is selected during initialization of the installation). Figure 1.2 shows the selection effect. Just select Network Service under the built-in account list. If you plan to use SQL Server Agent to connect to a remote computer (such as copying files, or managing multiple systems), you should switch to a domain user account (the account in Figure 1.2 is obscured by a drop-down list), and domain users may be created specifically for this purpose. You may need to ask for something from the domain administrator, from the domain administrator's point of view, you need a normal user account. In any case, select the correct service account and once you make changes, you must click the Restart button for the changes to take effect. Once you have done this, click the OK button to complete the configuration of the SQL Server Agent service. You can view more service account information from the link.

Figure 1.2 The SQL Server Agent service Account selection
Create a first job
Now that SQL Server Agent is running, you can create the first job. By demonstrating that we are building a job to perform important DBA tasks, backing up the system database, and not needing to manually back up those databases, you can create an automated job to do.
Job
A job is the core of a logical task, such as backing up a database. A task can contain one or more job steps, a job can contain notifications (such as a job failure message to notify the DBA), a schedule (when you want the job to run), or even run on multiple systems. As a first backup job, we try to make it simple.
In order to create this job, open SSMs and then connect to the DB instance. Expand the SQL Server Agent node, and then right-click the job. Choose to create a new job, as shown in 1.3
Figure 1.3 Expanding the Create Job dialog box
A job has a series of components, as shown in 1.4
Figure 1.4 New Job dialog box
We name the job "backing up the master database". Job categories can be useful for managing your jobs, but there is no need to set a category for the first job. You will learn how to set up categories later in the chapter. In the description form, write a little descriptive text to help you remember why you created the job and the purpose of the job. Remember that others will manage the system later, because they do not create these jobs, they do not know the purpose of the job and whether the job is important.
After all this, you've basically finished creating the "shell" of the job, just one of the components seen in the New Job dialog box. The most important part is in the other tab components.
Job Steps
A job in SQL Server Agent contains at least one step. When most people say that homework can do some work, it actually means a job step. A job step can be defined as one of the following types as required:
->activex Script
Operating System (CMDEXEC)
->powershell
Copy series Tasks
->sql Server Analysis Services (SSAS) commands
->sql Server Analysis Services (SSAS) queries
->sql Server Integration Services (SSIS) package
->transact-sql script (T-SQL)
For most DBAs, most jobs choose the last type, T-SQL. The job can be executed by the job owner, or another security context, based on the job owner and the permissions of the configuration agent. The job steps and subsystems are described in detail in the next section.
As you can see in the SSMs GUI, the control job step is called "step". Click on the step, we can create a job step, at the bottom of the dialog box click New, pop up a new Job Step dialog box (Figure 1.5)
Figure 1.5 New Job step
Set a name for the job step, in our example, the job name and step name are the same-back up the master database. The job step type is T-SQL, and we will back up the data with a simple backup command. You can ignore run as, and the default is to run the job as the owner.
For the command, you can open a file, or copy and paste some valid T-SQL query window that you wrote. To keep it simple, let's make a simple backup command. If your database is not installed on the C drive, you need to modify the file storage path.

BACKUP DATABASE Master      to DISK = ' C:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\backup\master.bak'with     INIT

When finished should be shown in 1.6, click OK to create the job step.
Figure 1.6 Job Step completion
Plan
Schedule control when the job starts, there are four types of schedule for the job schedule
->sql Server Agent starts automatically when started
->CPU Start when idle
-Repeated executions
Execute once
Most job schedules use recurring executions (for example, running my backup job every Sunday 7 o'clock in the afternoon). The plan is a stand-alone object in SQL Server Agent, and a plan can correspond to multiple jobs.
For our jobs, we want to back up the database weekly. So, click on the Plan tab, select Create a new job schedule, 1.7. Set a name for the job schedule (can be shared between jobs). I call this Plan "weekly system backup". Fortunately, the default settings for the dialog box are good. We will be doing our homework at midnight of Sunday every week. Click OK when you name the plan, then click OK to complete the creation of the entire job.

Figure 1.7 New Job Schedule
Execute Job
Now that we have created the backup job, it is time to run the job to verify that we are doing it correctly. Although we set the job to run weekly, we can still run the job manually as required. In SSMs, the Jobs folder, you can see your job, right click on the job, select "Job Start Step", 1.8, the job will be executed, then the master database will be backed up.

Figure 1.8 Executing a job in SMSS
Next article
In the next few sections we will delve deeper into the various areas of SQL Server Agent, as well as specific instances of the case, work and alerts can greatly improve your SQL Server system's ability to make your life easier. The next article focuses on creating and configuring job steps, understanding the subsystems of various jobs, and securing job steps.

First SQL Server Agent overview