AWSEC2-SQLServerDBRestore - AWS Systems Manager Automation runbook reference
PrerequisitesLimitationsParametersRequired IAM permissionsDocument Steps

AWSEC2-SQLServerDBRestore

Description

The AWSEC2-SQLServerDBRestore runbook restores Microsoft SQL Server database backups stored in Amazon S3 to SQL Server 2017 running on an Amazon Elastic Compute Cloud (EC2) Linux instance. You may provide your own EC2 instance running SQL Server 2017 Linux. If an EC2 instance is not provided, the automation launches and configures a new Ubuntu 16.04 EC2 instance with SQL Server 2017. The automation supports restoring full, differential, and transactional log backups. This automation accepts multiple database backup files and automatically restores the most recent valid backup of each database in the files provided.

To automate both backup and restore of an on-premises SQL Server database to an EC2 instance running SQL Server 2017 Linux, you can use the AWS-signed PowerShell script MigrateSQLServerToEC2Linux.

Important

This runbook resets the SQL Server server administrator (SA) user password every time the automation runs. After the automation is complete, you must set your own SA user password again before you connect to the SQL Server instance.

Run this Automation (console)

Document Type

Automation

Owner

Amazon

Platforms

Linux

Prerequisites

To run this automation, you must meet the following prerequisites:

  • The IAM user or role that runs this automation must have an inline policy attached with the permissions outlined in Required IAM permissions.

  • If you provide your own EC2 instance:

    • The EC2 instance that you provide must be a Linux instance running Microsoft SQL Server 2017.

    • The EC2 instance that you provide must be configured with an AWS Identity and Access Management (IAM) instance profile that has the AmazonSSMManagedInstanceCore managed policy attached. For more information, see Create an IAM instance profile for Systems Manager.

    • The SSM Agent must be installed on your EC2 instance. For more information, see Installing and configuring SSM Agent on EC2 instances for Linux.

    • The EC2 instance must have enough free disk space to download and restore the SQL Server backups.

Limitations

This automation does not support restoring to SQL Server running on EC2 instances for Windows Server. This automation only restores database backups that are compatible with SQL Server Linux 2017. For more information, see Editions and Supported Features of SQL Server 2017 on Linux.

Parameters

This automation has the following parameters:

  • DatabaseNames

    Type: String

    Description: (Optional) Comma-separated list of the names of databases to restore.

  • DataDirectorySize

    Type: String

    Description: (Optional) Desired volume size (GiB) of the SQL Server Data directory for the new EC2 instance.

    Default value: 100

  • KeyPair

    Type: String

    Description: (Optional) Key pair to use when creating the new EC2 instance.

  • IamInstanceProfileName

    Type: String

    Description: (Optional) The IAM instance profile to attach to the new EC2 instance. The IAM instance profile must have the AmazonSSMManagedInstanceCore managed policy attached.

  • InstanceId

    Type: String

    Description: (Optional) The instance running SQL Server 2017 on Linux. If no InstanceId is provided, the automation launches a new EC2 instance using the InstanceType and SQLServerEdition provided.

  • InstanceType

    Type: String

    Description: (Optional) The instance type of the EC2 instance to be launched.

  • IsS3PresignedUrl

    Type: String

    Description: (Optional) If S3Input is a pre-signed S3 URL, indicate yes.

    Default value: no

    Valid values: yes | no

  • LogDirectorySize

    Type: String

    Description: (Optional) Desired volume size (GiB) of the SQL Server Log directory for the new EC2 instance.

    Default value: 100

  • S3Input

    Type: String

    Description: (Required) S3 bucket name, comma-separated list of S3 object keys, or comma-separated list of pre-signed S3 URLs containing the SQL backup files to be restored.

  • SQLServerEdition

    Type: String

    Description: (Optional) The edition of SQL Server 2017 to be installed on the newly created EC2 instance.

    Valid values: Standard | Enterprise | Web | Express

  • SubnetId

    Type: String

    Description: (Optional) The subnet in which to launch the new EC2 instance. The subnet must have outbound connectivity to AWS services. If a value for SubnetId is not provided, the automation uses the default subnet.

  • TempDbDirectorySize

    Type: String

    Description: (Optional) Desired volume size (GiB) of the SQL Server TempDB directory for the new EC2 instance.

    Default value: 100

Required IAM permissions

The AutomationAssumeRole parameter requires the following actions to successfully use the runbook.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ec2:CreateTags",
                "ec2:DescribeImages",
                "ec2:DescribeInstanceStatus",
                "ec2:DescribeInstances",
                "ec2:RebootInstances",
                "ec2:RunInstances",
                "ssm:DescribeInstanceInformation",
                "ssm:GetAutomationExecution",
                "ssm:ListCommandInvocations",
                "ssm:ListCommands",
                "ssm:SendCommand",
                "ssm:StartAutomationExecution"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": "iam:PassRole",
            "Resource": "arn:aws:iam::ACCOUNTID:role/ROLENAME"
        }        
    ]
}

Document Steps

To use this automation, follow the steps that apply to your instance type:

For new EC2 instances:

  1. aws:executeAwsApi - Retrieve the AMI ID for SQL Server 2017 on Ubuntu 16.04.

  2. aws:runInstances - Launch a new EC2 instance for Linux.

  3. aws:waitForAwsResourceProperty - Wait for the newly created EC2 instance to be ready.

  4. aws:executeAwsApi - Reboot the instance if the instance is not ready.

  5. aws:assertAwsResourceProperty - Verify that SSM Agent is installed.

  6. aws:runCommand - Run the SQL Server restore script in PowerShell.

For existing EC2 instances:

  1. aws:waitForAwsResourceProperty - Verify that the EC2 instance is ready.

  2. aws:executeAwsApi - Reboot the instance if the instance is not ready.

  3. aws:assertAwsResourceProperty - Verify that SSM Agent is installed.

  4. aws:runCommand - Run the SQL Server restore script in PowerShell.

Outputs

getInstance.InstanceId

restoreToNewInstance.Output

restoreToExistingInstance.Output