Take SQL Server Backup using AWS SSM Runbook

Subscribe to my newsletter and never miss my upcoming articles

AWS System Manager helps to automate so many things. Generally, as a SQL Server DBA, we do schedule any maintenance tasks with SQL server agent. But for a change, let's have some fun with AWS SSM.

The RunBook:

The runbooks are a set of action items that will be described in sequences. The output from one step can be used as input in the next step. Right now you can deploy Python and Powershell scripts in the runbook document. We can get the values using parameters for executing a task.

Automation documents are now referred to as runbooks.

SQL Server Backup:

Most of the SQL server DBAs love to use Ola Hallengren's maintenance scripts. The interesting thing is, AWS created some SQL server maintenance runbooks with his maintenance scripts.

image.png

Prerequisites:

If you want to take the backup of your SQL server database, then it should meet the following prerequisites.

  1. The EC2 instance must be configured with an SSM agent. (SSM managed instance)
  2. If the SQL server is hosted on windows, then the authentication must be done with an AD user. So it should be part of the AD.
  3. Or it is hosted on Linux, then you can use SQL server user.
  4. Your EC2 instance must be attached with an IAM role with the following managed IAM policies.
    • SecretsManagerReadWrite
    • AmazonSSMManagedInstanceCore
  5. Windows/Linux credentials must be stored in the secret manager.
  6. (Optional) If you want to upload the SQL server backup to S3, then the IAM role should have permission for S3 as well.

Run the backup:

  • Navigate to SSM and change management.
  • Select the automation
  • From the documentation categories, search for sqlserver then select the AWSSQLServer-Backup
  • It shows the prerequisites.
  • Click on the execute automation.
  • You can select the Simple execution

Input parameters:

  1. InstanceId - If you choose the Show interactive instance picker then it'll list all the instances that are managed by the SSM, so you pick your EC2 instance where the SQL server is hosted.
  2. Database - The database name that you want to backup.
  3. Directory - Path for the backup (both windows and the Linux path will be accepted).
  4. DeleteBackupsOlderThan - If you want to delete any older backup in the backup directory, you can give the value in hours.
  5. S3bucket - Its optional. The bucket to upload the backup file.
  6. ThirdPartySoftwareConsent - Since its not official AWS scripts(its ola hallengren's), so you have to select Yes.
  7. BackupType - FULL/DIFF/LOG
  8. BackupRetentionPolicy - When to delete the old backup files(before the backup process or after or never)
  9. SecretsMangerCredential - Secret Manager credential name that has the SQL server credentials (for windows it should be AD user, Linux it is SQL server user)
  10. S3BucketFolder - A folder in S3 bucket to upload the backup file.

Once you click the execute button, then it'll start backup the database and upload it to the S3 bucket.

image.png

Note: If you are using Linux for SQL server, then better install PowerShell, because it'll try to install the PowerShell if it is not there, and your Linux server doesn't have the repository for PowerShell then the installation will fail.

Conclusion:

I know most of the DBAs will not like to schedule the backup via SSM Runbooks, but it has a lot more features. So you can use this to create your own runbook and play with it.