Automate database tasks in SQL Server Express edition running on Amazon EC2 by using AWS Lambda and Task Scheduler - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsBest practicesEpicsTroubleshootingRelated resources

Automate database tasks in SQL Server Express edition running on Amazon EC2 by using AWS Lambda and Task Scheduler

Subhani Shaik, Amazon Web Services

Summary

This pattern demonstrates how to schedule and manage database tasks in SQL Server Express edition, which is the free version of SQL Server. However, SQL Server Express edition lacks the SQL Server Agent service that typically handles automated database operations. This pattern explains how you can use Task Scheduler and Lambda as an alternative for automating database tasks in SQL Server Express edition running on an Amazon Elastic Compute Cloud (Amazon EC2) instance.

Task Scheduler is a built-in Windows system utility that facilitates the automatic execution of routine tasks. It provides a mechanism to schedule and manage automated operations, eliminating the need for manual intervention in recurring processes. AWS Lambda is a serverless computing service that automatically runs code in response to events, without requiring you to manage the underlying infrastructure.

Prerequisites and limitations

Prerequisites

  • An active AWS account

  • A virtual private cloud (VPC) created with Amazon Virtual Private Cloud (Amazon VPC)

  • An Amazon EC2 instance with Windows Server

  • Amazon Elastic Block Store (Amazon EBS) volumes that are attached to an Amazon EC2 instance with Windows Server

  • SQL Server Express Edition binaries

Limitations

Product versions

  • SQL Server 2016 or later with SQL Server Express edition

Architecture

The following diagram shows an Amazon EC2 instance running with SQL Server Express edition installed. The instance is accessible through Remote Desktop Protocol (RDP) client or from AWS Systems Manager Session Manager. AWS Key Management Service (AWS KMS) handles the data encryption for the Amazon EBS volumes to ensure data-at-rest security. The infrastructure also includes AWS Identity and Access Management (IAM), which provides access control and manages permissions for the execution of Lambda functions. Amazon Simple Storage Service (Amazon S3) stores Lambda functions.

An Amazon EC2 instance running with SQL Server Express edition installed on a private subnet.

Tools

AWS services

Other tools

  • Microsoft SQL Server Management Studio (SSMS) is a tool for managing SQL Server, including accessing, configuring, and administering SQL Server components.

  • Python is a general-purpose computer programming language. You can use it to build applications, automate tasks, and develop services on the AWS Cloud.

  • Task Scheduler is a Microsoft tool that you can use to schedule routine tasks on your computer automatically.

Best practices

Epics

TaskDescriptionSkills required

Deploy an Amazon EC2 instance.

To create an Amazon EC2 instance, open the Amazon EC2 console at https://console.aws.amazon.com/ec2/ and select an Amazon Machine Image (AMI) from the list of instances available for Windows Server.

For more information, see Launch an Amazon EC2 instance in the AWS documentation.

DBA, AWS DevOps

Install SQL Server Express edition.

To install SQL Server Express edition, complete the following steps:

  1. To connect to your Amazon EC2 instance, choose an option:

  2. To download the required SQL Server Express edition, go to SQL Server Downloads on the Microsoft website.

  3. To install SQL Server Express edition, follow the instructions in Plan a SQL Server installation on the Microsoft website.

DBA, AWS DevOps
TaskDescriptionSkills required

Identify routine tasks.

Identify the routine tasks that you want to automate. For example, the following tasks are eligible for automation:

  • Database backups (Full, differential, and transaction log)

  • Index maintenance and reorganization

  • Statistics updates

  • Application-specific operations

  • Data cleanup or archiving

DBA

Prepare SQL scripts.

To prepare SQL scripts, do the following:

  1. Create SQL queries for each maintenance task. Following is an example T-SQL query to perform a specific database backup: Backup Database <Database_Name> To Disk='C:\Backups\Database_Name.bak'

  2. Save the script file as <File Name>.sql. Then, save the scripts to an accessible location on the server’s local drive on the Amazon EC2 instance or a network file share.

DBA

Configure access permissions.

To configure access permissions, do the following:

  1. Set appropriate file system permissions. For instructions, see Configure file system permissions for Database Engine access on the Microsoft website.

  2. Check that the SQL Server service account has the necessary access. For instructions, see Configure Windows service accounts and permissions on the Microsoft website.

  3. Verify network connectivity for remote shares. For more information, see Accessing data using file shares in the AWS documentation.

DBA
TaskDescriptionSkills required

Create batch files.

  • To create a batch file, use a text editor to type the following command. Replace the parameters username and password with your own values. Then save the file as <Name>.bat.

sqlcmd -S servername -U username -P password -i <T-SQL query path.sql>

  • To create a batch file for SQL tasks, use a text editor and type the following commands. Replace the parameters ServerName, DatabaseName, username, and password with your own values. Then save the file as <Name>.bat.


@echo off
sqlcmd -S [ServerName] -d [DatabaseName] -U username -P password -i "PathToSQLScript\Script.sql" -o "PathToOutput\Output.txt"
AWS DevOps, DBA

Create tasks in Task Scheduler.

To create a task in Task Scheduler, use the following steps:

  1. To open Task Scheduler, type taskschd.msc in Windows search.

  2. Choose the Action menu, and then select Create Basic Task.

  3. For Name, provide a name for the task, and then choose Next.

  4. For Trigger, select the option for when you want the task to start and then choose Next.

  5. Provide the Start and Recur information for the task, and then choose Next.

  6. For the Action section, select Start a program and then choose Next.

  7. For Program/script, specify the path for the batch file that you created in the previous task and then choose Next.

  8. Choose Finish.

To run the task manually, right-click the newly created task and then select Run.

DBA

View task status.

To view the status of a task in Task Scheduler, use the following steps:

  1. In Task Scheduler, go to Task Scheduler Library, which displays all tasks.

  2. To see the status of the task that you created earlier, select the task and then go to the History tab.

DBA, AWS DevOps
TaskDescriptionSkills required

Implement the solution.

To implement this pattern’s solution, use the following steps:

  1. Create a Lambda function. For instructions, see Create your first Lambda function in the AWS documentation.

  2. Schedule the Lambda function. For instructions, see Invoke a Lambda function on a schedule in the AWS documentation.

  3. Run T-SQL queries. For more information, see Tutorial: Using a Lambda function to access an Amazon RDS database in the AWS documentation. The tutorial explains how to connect Amazon RDS databases from Lambda functions to run SQL queries

AWS DevOps, DevOps engineer

Troubleshooting

IssueSolution

Lambda issues

For help with errors and issues that you might encounter when using AWS Lambda, see Troubleshooting issues in Lambda in the AWS documentation.

Related resources