Automating SQL Server Administration Tasks

In this post I would like to demonstrate with hands-on examples how to use automation (jobs,alerts,operators,notifications) in SQL Server to automate SQL Server administration tasks.

The importance of automating tasks is profound.The following tasks could be automated

• Backing up the database
• Checking data integrity
• Dropping and re-creating indexes
• Transferring data

We can automate all these tasks to occur on a regular schedule.A common example is to automate the Backup of a database, every Saturday night e.g at 12.00 am.

For task automation we need SQL Server service (MSSQLSERVER) to be up and running. The Windows application log is the location of all operating system messages for the Windows operating system.MSSQLSERVER service is needed to write events to the Windows application log.  We can have a look into the Windows application log using the Windows Event Viewer.SQL Server Agent service must also be up and running.SQL Server Agent’s role is to take an action after a notification comes through the Windows application log.

Before I move on, let me talk a bit about SQL Server Agent. SQL Server Agent executes jobs and fire alerts. Some people are confused with the terms jobs and alerts.Jobs and alerts are defined separately and can be executed independently.A job can invoke an alert but the opposite is also possible.When we have alerts we can also have notifications of one or more operators by e-mail using Database Mail. Have a look in this post of mine to see how to configure Database Mail in SQL Server.

There are some steps involved when creating a job

• Create the job and its steps
• Create a schedule of the job execution if the job is not to be executed on demand
• Notify operators about the status of the job

We can define a job and its steps in various ways.We can use T-SQL statements in a job step. We can also use commands that start the execution of a utility like bcp command.In a job step we can also execute a program that is developed with Visual Basic or C#. We must always define what happens when a job step fails.A job step starts the next job step when it is successful.If a job step fails any job steps that follow will not be executed.We must define how many times a job step should be retried.

We can create a job using various ways

• We can launch SSMS and define a job through SSMS
• By using system stored procedures (sp_add,sp_add_jobstep)

Let’s begin with our hands-on examples

We need an instance of SQL Server running in our machine. I have SQL Server 2008 R2 Developer edition installed in my machine.

In case you do not have an instance of Enterprise edition or Developer edition  of SQL Server 2008 / R2, you can download it here . This is a trial version.

1) Launch SQL Server Management studio and connect to the desired instance of  SQL Server.

2) I will create a simple job that backs up the AdventureWorks database.You can follow my example by backing up any database you wish.

3) Expand SQL Server Agent,right-click Jobs and then click New Job.
The New Job window appears.I will fill in a name for the job and a description in the General tab

Have a look at the picture below

Please note that all jobs are enabled by default. SQL Server Agent disables jobs if the job schedule is defined  at a specific time that has passed.It also disables jobs that are defined on a recurring basis with an end date that has also passed.

4) Now click on the Steps tab and click New.The New Job Step dialog appears.We must enter a name for the job step.In the Type drop down list, I choose Transact-SQL script ( T-SQL ) because the backup of the database will be executed using T-SQL.In the Database drop down list I choose the master database.I do that because master system database must be current if we want to backup a database.

Have a look at the picture below

The T-SQL statement for backing up the database is

BACKUP DATABASE [AdventureWorks] TO
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks.bak'
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

5) Now click on the Advanced tab to specify retry attempts and retry interval .Then click OK

Ηave a look at the picture below

6) Now we are ready to create a Job schedule.Each created job can be executed on demand or by using a schedule.A scheduled job can occur at a specific time or on a recurring schedule.Select the Schedules tab and click New.
I fill in a name for the schedule and the schedule type is recurring.It occurs every Monday each week at 9 am starting on 25/05/2012

Have a look at the picture below

Click OK on this window.Click OK once more to finish creating the job.

7) If you want to create a notification about a job status you need to create an operator first.I will use SSMS to create an operator.Expand SQL Server Agent node and ten right-click on Operators and then click New Operator.Enter the name of the operator and then specify one or more methods of notifying the operator.

Have a look at the picture below

8) To notify one or more operators after the job finishes we need to select the job we just created and select Properties and then we need to select the Notifications tab.Then we just need to check the corresponding objects.

Have a look at the picture below

Then click OK

9) If you want to find out information about a job you can have a look at the sysjobhistory table of the msdb database.You can also select a job and right-click on it and then click View History.The Log File Viewer dialog box shows the history log of the job.We can find information such as

• Whether the job completed successfully or not
• Duration of the job
• Operators who were notified
• Date and time when the job step occurred

10) Alerts as mentioned earlier can be used to respond to potential problems,system errors and user-defined errors.Information about execution of  jobs ans system error messages is stored in Windows application log.The SQL Server Agent reads the log and then compares the stored messages with the alerts defined for the system.If there is a match, SQL Server Agent fires the alert.Let’s create an alert that responds to a system error.Expand SQL Server Agent node, right-click Alerts and click New Alert.In the New Alert dialog box I enter the name (deadlock_alert) of the alert.I will create an alert that will respond to deadlocks.In the Error number I enter 1205
and select the alert type to be SQL Server event alert.

Have a look at the picture below

Now we need to define the response for the alert.Click on the Response page.I check the Notify operators and then in the operator list I select the operator I created earlier.

Have a look at the picture below

To recap SQL Server allows us to automate administrator tasks such as index maintenance and database backups.We can create jobs,operators,notifications and alerts. SQL Server Agent must be up and running to have automation.

Hope it helps!!!