Secure SQL Database Backups and Logs Synchronization with Amazon Simple Storage Service (S3)

1.    Overview

The SQL/PowerShell automation procedure is designed to perform off-site archiving of local SQL Server full and log backups in the form of compressed and encrypted files (7zip) which are being uploaded to Amazon Simple Storage Service (Amazon S3).

2.    Native Backup SQL Agent Jobs

The SQL native backup process consists of the following jobs:

  • System databases full backup
  • User databases full backup
  • User databases transaction log backup
  • User databases differential backup

2.1 System Databases Full Backup (SQL Agent Job)

SQL Agent Job name: WTADMIN_BACKUP_SYSTEM_DATABASES_FULL
sql_agent_jobCreates full backup of system databases locally.
Specification and backup options:

Databases System Databases (Master, MSDB, TEMPDB, Model)
Directory E:\Backup\DATABASE\WIN-2PKGIMKGIIA
BackupType Full Backup
Verify Verify the backup
CleanupTime 192 hours (after which the backup files are deleted)
Compress Compress the backup
CheckSum Enable backup checksums
LogToTable Log commands to the table CommandLog
Execute Execute commands

2.2 User Databases Full Backup (SQL Agent Job)

SQL Agent Job name: WTADMIN_BACKUP_USER_DATABASES_FULL
sql_agent_job_fullCreates full backup of user databases locally.

Specification and backup options:

Databases User Databases
Directory E:\Backup\DATABASE\WIN-2PKGIMKGIIA
BackupType Full Backup
Verify Verify the backup
CleanupTime 96 hours (after which the backup files are deleted)
Compress Compress the backup
CheckSum Enable backup checksums
LogToTable Log commands to the table CommandLog
Execute Execute commands

2.3 User Databases Transaction Log Backup (SQL Agent Job)

SQL Agent Job name: WTADMIN_BACKUP_USER_DATABASES_LOG
agent_transaction_logCreates transaction log backup of user databases locally.

Specification and backup options:

Databases User Databases
Directory E:\Backup\DATABASE\WIN-2PKGIMKGIIA
BackupType Transaction Log Backup
Verify Verify the backup
CleanupTime 25 hours (after which the backup files are deleted)
Compress Compress the backup
CheckSum Enable backup checksums
LogToTable Log commands to the table CommandLog
Execute Execute commands

2.4 User Databases Differential Log Backup (SQL Agent Job)

SQL Agent Job name: WTADMIN_BACKUP_USER_DATABASES_DIFF
sql_agent_job_differentialCreates differential backup of user databases locally.

Specification and backup options:

Databases User Databases
Directory E:\Backup\DATABASE\WIN-2PKGIMKGIIA
BackupType Differential Backup
Verify Verify the backup
CleanupTime 96 hours (after which the backup files are deleted)
Compress Compress the backup
CheckSum Enable backup checksums
LogToTable Log commands to the table CommandLog
Execute Execute commands

3.    PowerShell Tasks & Related SQL Agent Jobs

The procedure consists of the following tasks:

  • PowerShell Script to encrypt and compress backup and log files locally
  • PowerShell Script to copy files to Amazon S3 storage
  • PowerShell Script to delete after time period on Amazon S3
  • SQL Server Agent Job for scheduled execution

3.1 Encrypt and compress backup and log files locally (PowerShell script)

Filename: Bak-to-7z.ps1
Location: E:\S3BackupSync
Description: This script will leverage 7z command-line utility to compress and password encrypt SQL backup and log files.

Input folder: E:\Backup\DATABASE
The folder that contains MSSQL .bak and .trn files.

Output folder: E:\Backup\DATABASE\Crypted
The folder where the compressed and password protected files are being created.

 

Example of 7z archive file names:

WIN-2PKGIMKGIIA_company_DB_FULL_20160331_021500.bak.7z

WIN-2PKGIMKGIIA_company_DB_LOG_20160404_001001.trn.7z

3.2 Copy compressed files to Amazon S3 (PowerShell script)

Filename: 7z-to-S3.ps1
Location: E:\S3BackupSync
Description: This script will upload 7zip archives to Amazon S3 bucket.

Note: To work properly this script requires AWS SDK for .NET (https://aws.amazon.com/sdk-for-net/) to be installed on the local machine.

Input folder: E:\Backup\DATABASE\Crypted
The folder where the compressed and password protected files are stored.

Output location:

S3 bucket name: company-backups-new
S3 Region: us-east-1

3.3 S3 bucket obsolete files cleanup (PowerShell script)

Filename: Clean-S3-Bucket.ps1
Location: E:\S3BackupSync
Description: This script will purge obsolete files in Amazon S3 bucket.

Target location:
S3 bucket name: company-backups-new

S3 Region: us-east-1

3.4 SQL Server Agent Job for permanently scheduled execution

The process contains of three SQL Server agent jobs:
sql_server_agent_jobs

3.4.1 S3BackupCleanup

This job will run daily at 6:00:00 AM and once triggered it will call the PowerShell script “Clean-S3-Bucket.ps1” with an argument for the file retention period (in days):
An email notification regarding the job status will be send to Michael Roedeske.
Command:
powershell.exe “&E:\S3BackupSync\Clean-S3-Bucket.ps1 15”

The script will delete all files located on the S3 bucket older than 15 days.

3.4.2 S3BackupSyncFull

This job consist of two steps:
sql_steps

First step will call the PowerShell script “Bak-to-7z.ps1” in order to compress and password protect the SQL database full backups files (.bak).

Command:

powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\Bak-to-7z.ps1 bak”

The PowerShell script is being called with an argument “bak” in order to process only SQL full backups.

Second step will call the PowerShell function “7z-to-S3.ps1” in order to upload the compressed .bak files to S3 bucket.

Command:

powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\7z-to-S3.ps1 bak”

The PowerShell script is being called with an argument “bak” in order to process only SQL full backup archives.

An email notification regarding the job status will be send to the specified email address.

3.4.3 S3BackupSyncLog

This job also consist of two steps:
s3_syncLog

First step will call the PowerShell script “Bak-to-7z.ps1” in order to compress and password protect the SQL database transaction logs (.trn).

Command:

powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\Bak-to-7z.ps1 trn”

The PowerShell script is being called with an argument “trn” in order to process only SQL transaction logs.

Second step will call the PowerShell function “7z-to-S3.ps1” in order to upload the compressed .trn files to S3 bucket.

Command:

powershell -ExecutionPolicy Bypass “&E:\S3BackupSync\7z-to-S3.ps1 trn”

The PowerShell script is being called with an argument “trn” in order to process only SQL transaction log archives.

An email notification regarding the job status will be send to the specified email address.

4.    Additional files/tools

more_tools

7z.dll, 7z.exe – 7zip required files
7zip.bin – 7z encrypted password file
PasswordMgr.exe – S3 credentials and 7z password manager

Password Manager GUI tool has been designed to securely store the 7zip encryption password and S3 account keys.
password_tool

7zip password is encrypted and stored into 7zip.bin file.
S3 Access and Secret keys are stored into local secure store and loaded into memory when needed.

The file is located in %LOCALAPPDATA%\AWSToolKit\RegisteredAccounts.json and the keys are obfuscated- either hashed or encrypted.

Note: The stored credentials can be used only by the same user who created the store. %USERPROFILE% environmental variable must be set.

facebooktwittergoogle_plus

Leave a Reply

Your email address will not be published. Required fields are marked *