Basics of SQL Server Task Automation

This is an introductory article about automation in SQL server primarily focused on the basic concepts. We will discuss some standard practices and a few examples to help beginners get started with SQL server automation.

This article also highlights the importance of automating SQL server tasks to save time and effort required to do these tasks manually.

Additionally, we will look at cases in which it is not a good idea to automate SQL server tasks despite the fact that automation saves time and effort.

About Automating SQL Server

Let’s first get familiar with the term ‘automation’ and what exactly is SQL server task automation.

What is Automation?

Automation is a broad term which covers a wide range of things, but it generally means programming tools and devices which allow performing a set of tasks automatically with little or no human intervention.

What is SQL Server Automation?

SQL Server automation can also have several meanings, but it’s most commonly concerned with automating SQL Server administrative tasks in order to save time and effort.

Database administrators or infrastructure teams responsible for the management of SQL Server and hosted database(s) often spend a lot of time performing database maintenance tasks which could be automated through different methods and scheduled to run at specified intervals.

In this article, we are mainly focused on automation of SQL Server administration tasks.

When You Should Automate SQL Tasks

Automating a SQL Server administrative task requires careful planning before it is implemented. Any SQL administrative task such as backup, database maintenance, or database monitoring should be ideally automated if it serves the purpose without causing any other issue.

When You Should Not Automate SQL Tasks

Despite automation being very useful in most cases, there are still some tasks which should better be performed manually.

For example, if a database administration task takes 5 minutes to complete and automating that task requires 15 hours of writing and testing automation scripts, then it’s more effective to do this task manually.bacsql

There are certain tasks which must not be automated. For example, it is often a part of a DBA’s job to check the database changes before deploying it to a live server. This is done to ensure that the changes are compatible with the target database. Automating this task has more risks than benefits, so it is better left as it is.

Generally speaking, if any database task requires manual intervention (i.e. it must be seen or managed by a person/team in order to move to the next step/stage) according to the rules and regulations of your company, then fully automating that task is not feasible unless it is approved by your company’s top management.

Examples of SQL Automation

Let’s now look at a couple of examples of SQL automation.

Database Backup Scenario (DBA perspective)

Database administrators (DBAs) take care of corporate databases and their day-to-day tasks include serving database-related requests and managing the database server.

A DBA spends most of their time managing and deploying small changes to the databases and taking care of the database server.

A DBA has to do the following consistent tasks:

1. Create a daily backup of the database at the end of each working day
2. Create a weekly backup at the end of each week
3. Create a monthly backup at the end of each month.

A daily backup takes half an hour and it is the last thing a DBA does when leaving for the day. Although they can multitask once the daily backup process is started, they still have to keep an eye on how it’s going to ensure that the backup is finished. This is because the backup should be stored on the backup disk when it’s ready.

Even if we ignore the time spent on weekly and monthly backups, the time required to complete the daily backup will increase the more you perform this process because the database is going to have more and more data. Even if it takes half an hour to perform a daily backup at the beginning, this amount of time can increase to approximately one hour in a couple of weeks or months depending on data growth in the database system.

This is an unchanging task which has to be constantly repeated, so it is possible for a DBA to get bored or lose concentration and make mistakes or miss a day or two.

If a DBA automates this SQL task, then they will not only save time and effort. The chances of making a mistake are going to be minimized.

The daily backup process can be scheduled to start during night time when there is low database interaction. The next day, the DBA can check the backup and utilize the last hour of the working day (which was previously used for the backup process) for more important and urgent tasks.

Database Development and Deployment Scenario

Let’s consider a very common example of database development and deployment (I’m deliberately excluding testing in order to stay within the scope of this article). A database developer uses a database development tool such as SQL Server Data Tools (SSDT) to add new tables to a database. Once this is done, they save changes in the SQL database project. These changes are then sent to the DBA or the infrastructure team who have to review these changes and deploy them to the live server.

The important points are as follows:

1. Database developers should not publish their database changes directly to the live server as per standard practices
2. Database administrators or the infrastructure team that manages the live server cannot publish any changes to the live database before reviewing these changes to ensure that they don’t challenge the consistency of the database.

As you can understand from what we discussed, completely automating the database development and deployment tasks is not practical and poses many security risks.

So the best decision would be to automate the steps which depend on a DBA or an infrastructure team.

Database Index Maintenance Tasks

Database indexes play a vital role in retrieving the result set (rows) quicker from the database(s). These indexes, however, do require maintenance with time. Database index maintenance can be done manually during the off-peak hours or automated. It can be a very time-consuming task, so it’s better to automate this task especially when there are so many other database server-related things to manage.

However, some of the experts argue with this point of view and recommend limiting index maintenance to a certain extent in order to keep an eye on the process or even doing this completely manually.

When it comes to index maintenance, the following things can improve database index performance:

1. Rebuild Index
2. Reorganize Index
3. Defrag index
4. Update Statistics

You also need to be on the ball when it comes to query optimization. There are tools out there that let you upgrade SQL server query performance, thus guaranteeing that your efforts to implement index maintenance tasks don’t get scuppered by sluggish responsiveness elsewhere. It’s an example of how automation can be applied across all aspects of database management if you’re willing to put in the initial work required.

How to Automate SQL Tasks

Let’s now look at the basic requirements for automating a SQL Task followed by a very simple example.

Setup Sample Database

This walkthrough requires the following single-table sample database called UniversityV6 to be set up as follows:

-- (1) Create the UniversityV6 sample database
CREATE DATABASE UniversityV6;
GO

USE UniversityV6

CREATE TABLE [dbo].[Student] (
    [StudentId] INT           IDENTITY (1, 1) NOT NULL,
    [Name]      VARCHAR (30)  NULL,
    [Course]    VARCHAR (30)  NULL,
    [Marks]     INT           NULL,
    [ExamDate]  DATETIME2 (7) NULL,
    CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([StudentId] ASC)
);
GO

Run the script to create the sample database:

Starting the SQL Server Agent

Automating a SQL Task usually depends on a SQL Server agent unless you decide to choose an alternative route.

The first thing you need is to make sure that a SQL Server Agent is running. If it is not running, you have to manually start it and keep it running.

Connect to a SQL Server instance and look for the SQL Server Agent node in the Object Explorer. If there is a red ‘stop’ icon displayed at this node, it’s not started yet:

Right-click the SQL Server Agent node and click Start:

Next, you have to confirm that you want to start a SQL Server agent service on your server. Click Yes to do this:

Once started, SQL Server Agent will have a small green icon displayed next to it. You can also expand the node now:

Create a New Job

Right-click on the Jobs folder and click New Job…:

Type “Backup UniversityV6 automated task” and click Steps on the left-side navigation bar:

Add a New Job Step

Next, click New, type “Backup UniversityV6” as the step name, add the following SQL script, and click OK:

-- Backup the UniversityV6 sample database using the date time stamp
DECLARE @BackupName VARCHAR(100)
SET @BackupName=CONCAT('C:\Backup\UniversityV6-',FORMAT(GETDATE(),'yyyy-MM-dd-hh-mm-tt'),'.bak')
BACKUP DATABASE UniversityV6 TO DISK=@BackupName WITH COMPRESSION, INIT;
GO

Click OK to finish setting up the job.

View the Created Job

Scroll down to the SQL Server Agent and locate the newly-created Job:

Test-run the Job (Automated Task)

Right-click Backup UniversityV6 automated task and Click Start Job at Step…:

The job will start and finish:

Check the Backup Location

Now, go to the location at which this automated task created a fresh backup:

Congratulations! You have successfully automated the task of creating a database backup. You can now store it any backup drive simply by changing the drive letter name.

Please remember that you should always save a backup to a backup drive other than the system drive C:. You can schedule this task to run at frequent intervals by right-clicking the job, clicking Properties, and then clicking Schedules:

Things to do

Now that you can automate basic database backup tasks, please try the following to improve your skills:

1. Schedule this task to run every day in the afternoon for one week as a test
2. Create a new table called Stats in the sample database with the following columns:
   1. StatID (INT)
   2. StatDate (DATETIME2)
   3. TotalRows (INT)

Now test yourself by creating an automated task (new job) of logging the row count for the Student table along with the date/time in the Stats table. Try to populate the Student table frequently.

Schedule the task to run every hour and check if the Stats table reflects your data insertions by showing you the row count of the table every hour.