Troubleshooting DB issues for Amazon RDS Custom for SQL Server - Amazon Relational Database Service
Viewing RDS Custom eventsSubscribing to RDS Custom eventsTroubleshooting CEV errors for RDS Custom for SQL ServerFixing unsupported configurations in RDS Custom for SQL ServerTroubleshooting Storage-FullTroubleshooting PENDING_RECOVERY state for TDE enabled databases in RDS Custom for SQL Server

Troubleshooting DB issues for Amazon RDS Custom for SQL Server

The shared responsibility model of RDS Custom provides OS shell–level access and database administrator access. RDS Custom runs resources in your account, unlike Amazon RDS, which runs resources in a system account. With greater access comes greater responsibility. In the following sections, you can learn how to troubleshoot issues with Amazon RDS Custom for SQL Server DB instances.

Note

This section explains how to troubleshoot RDS Custom for SQL Server. For troubleshooting RDS Custom for Oracle, see Troubleshooting DB issues for Amazon RDS Custom for Oracle.

Viewing RDS Custom events

The procedure for viewing events is the same for RDS Custom and Amazon RDS DB instances. For more information, see Viewing Amazon RDS events.

To view RDS Custom event notification using the AWS CLI, use the describe-events command. RDS Custom introduces several new events. The event categories are the same as for Amazon RDS. For the list of events, see Amazon RDS event categories and event messages.

The following example retrieves details for the events that have occurred for the specified RDS Custom DB instance.

aws rds describe-events \
    --source-identifier my-custom-instance \
    --source-type db-instance

Subscribing to RDS Custom events

The procedure for subscribing to events is the same for RDS Custom and Amazon RDS DB instances. For more information, see Subscribing to Amazon RDS event notification.

To subscribe to RDS Custom event notification using the CLI, use the create-event-subscription command. Include the following required parameters:

  • --subscription-name

  • --sns-topic-arn

The following example creates a subscription for backup and recovery events for an RDS Custom DB instance in the current AWS account. Notifications are sent to an Amazon Simple Notification Service (Amazon SNS) topic, specified by --sns-topic-arn.

aws rds create-event-subscription \
    --subscription-name my-instance-events \
    --source-type db-instance \
    --event-categories '["backup","recovery"]' \
    --sns-topic-arn arn:aws:sns:us-east-1:123456789012:interesting-events

Troubleshooting CEV errors for RDS Custom for SQL Server

When you try to create a CEV, it might fail. In this case, RDS Custom issues the RDS-EVENT-0198 event message. For more information on viewing RDS events, see Amazon RDS event categories and event messages.

Use the following information to help you address possible causes.

Message Troubleshooting suggestions

Custom Engine Version creation expected a Sysprep’d AMI. Retry creation using a Sysprep’d AMI.

Run Sysprep on the EC2 instance that you created from the AMI. For more information about prepping an AMI using Sysprep, see Create a standardized Amazon Machine Image (AMI) using Sysprep.

EC2 Image permissions for image (AMI_ID) weren't found for customer (Customer_ID). Verify customer (Customer_ID) has valid permissions on the EC2 Image.

Verify that your account and profile used for creation has the required permissions on create EC2 Instance and Describe Images for the selected AMI.

Failed to rebuild databases with server collation (collation name) due to missing setup.exe file for SQL Server.

Verify that the setup file is available at C:\Program Files\Microsoft SQL Server\nnn\Setup Bootstrap\SQLnnnn\setup.exe.

Image (AMI_ID) doesn't exist in your account (ACCOUNT_ID). Verify (ACCOUNT_ID) is the owner of the EC2 image.

Ensure the AMI exists in the same customer account.

Image id (AMI_ID) isn't valid. Specify a valid image id, and try again.

The name of the AMI is incorrect. Ensure the correct AMI ID is provided.

Image (AMI_ID) operating system platform isn't supported. Specify a valid image, and try again.

Choose a supported AMI that has Windows Server with SQL Server Enterprise, Standard, or Web edition. Choose an AMI with one of the following usage operation codes from the EC2 Marketplace:

  • RunInstances:0102 - Windows with SQL Server Enterprise

  • RunInstances:0006 - Windows with SQL Server Standard

  • RunInstances:0202 - Windows with SQL Server Web

SQL Server Web Edition isn't supported for creating a Custom Engine Version using Bring Your Own Media. Specify a valid image, and try again.

Use an AMI that contains a supported edition of SQL Server. For more information, see Version support for RDS Custom for SQL Server CEVs.

The custom engine version can't be the same as the OEV engine version. Specify a valid CEV, and try again.

Classic RDS Custom for SQL Server engine versions aren't supported. For example, version 15.00.4073.23.v1. Use a supported version number.

The custom engine version isn't in an active state. Specify a valid CEV, and try again.

The CEV must be in an AVAILABLE state to complete the operation. Modify the CEV from INACTIVE to AVAILABLE.

The custom engine version isn't valid for an upgrade. Specify a valid CEV with an engine version greater or equal to (X), and try again.

The target CEV is not valid. Check the requirements for a valid upgrade path.

The custom engine version isn't valid. Names can include only lowercase letters (a-z), dashes (-), underscores (_), and periods (.). Specify a valid CEV, and try again.

Follow the required CEV naming convention. For more information, see Requirements for RDS Custom for SQL Server CEVs.

The custom engine version isn't valid. Specify valid database engine version, and try again. Example: 15.00.4073.23-cev123.

An unsupported DB engine version was provided. Use a supported DB engine version.

The expected architecture is (X) for image (AMI_ID), but architecture (Y) was found.

Use an AMI built on the x86_64 architecture.

The expected owner of image (AMI_ID) is customer account ID (ACCOUNT_ID), but owner (ACCOUNT_ID) was found.

Create the EC2 instance from the AMI that you have permission for. Run Sysprep on the EC2 instance to create and save a base image.

The expected platform is (X) for image (AMI_ID), but platform (Y) was found.

Use an AMI built with the Windows platform.

The expected root device type is (X) for image %s, but root device type (Y) was found.

Create the AMI with the EBS device type.

The expected SQL Server edition is (X), but (Y) was found.

Choose a supported AMI that has Windows Server with SQL Server Enterprise, Standard, or Web edition. Choose an AMI with one of the following usage operation codes from the EC2 Marketplace:

  • RunInstances:0102 - Windows with SQL Server Enterprise

  • RunInstances:0006 - Windows with SQL Server Standard

  • RunInstances:0202 - Windows with SQL Server Web

The expected state is (X) for image (AMI_ID), but the following state was found: (Y).

Ensure the AMI is in a state of AVAILABLE.

The provided Windows OS name (X) isn’t valid. Make sure the OS is one of the following: (Y).

Use a supported Windows OS.

Unable to find bootstrap log file in path.

Verify that the log file is available at C:\Program Files\Microsoft SQL Server\nnn\Setup Bootstrap\Log\Summary.txt.

RDS expected a Windows build version greater than or equal to (X), but found version (Y)..

Use an AMI with a minimum OS build version of 14393.

RDS expected a Windows major version greater than or equal to (X), but found version (Y)..

Use an AMI with a minimum OS major version of 10.0 or higher.

Fixing unsupported configurations in RDS Custom for SQL Server

Because of the shared responsibility model, it's your responsibility to fix configuration issues that put your RDS Custom for SQL Server DB instance into the unsupported-configuration state. If the issue is with the AWS infrastructure, you can use the console or the AWS CLI to fix it. If the issue is with the operating system or the database configuration, you can log in to the host to fix it.

Note

This section explains how to fix unsupported configurations in RDS Custom for SQL Server. For information about RDS Custom for Oracle, see Fixing unsupported configurations in RDS Custom for Oracle.

In the following tables, you can find descriptions of the notifications and events that the support perimeter sends and how to fix them. These notifications and the support perimeter are subject to change. For background on the support perimeter, see RDS Custom support perimeter. For event descriptions, see Amazon RDS event categories and event messages.

Event Code Configuration area RDS event message Validation process

SP-S0000

Manual Unsupported Configuration

The RDS Custom DB instance status is set to [Unsupported configuration] because of: X.

To resolve this issue, create a support case.

AWS resource (infrastructure)

Event Code Configuration area RDS event message Validation process

SP-S1001

EC2 Instance State

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The underlying EC2 instance %s has been stopped without stopping the RDS instance. You can resolve this by starting the underlying EC2 instance and ensuring that the binary and data volumes are attached. If your intention is to stop the RDS instance, make sure that underlying EC2 instance is in the AVAILABLE state first and then use the RDS console or CLI to stop the RDS instance.

To check the status of a DB instance, use the console or run the following AWS CLI command:

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep DBInstanceStatus

SP-S1002

EC2 Instance State

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The RDS DB instance status is set to STOPPED but the underlying EC2 instance %s has been started. You can resolve this by stopping the underlying EC2 instance. If your intention is to start the RDS instance, use the console or CLI.

Use the following AWS CLI command to check the status of a DB instance:

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep DBInstanceStatus

You can also check the status of the EC2 instance using the EC2 console.

To start a DB instance, use the console or run the following AWS CLI command:

aws rds start-db-instance \
    --db-instance-identifier db-instance-name

SP-S1003

EC2 Instance Class

The RDS Custom DB instance status is set to [Unsupported configuration] because of: There is a mismatch between the expected and configured DB instance class of the EC2 host. You can resolve this by modifying the DB instance class to its original class type.

Use the following CLI command to check the expected DB instance class: 

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep DBInstanceClass

SP-S1004

EBS Storage Volume Not Accessible

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The original EBS storage volume %s that was associated with the EC2 instance is currently not accessible.

SP-S1005

EBS Storage Volume Detached

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The original EBS storage volume "volume-id" isn’t attached. You can resolve this by attaching the EBS volume associated to the EC2 instance.

After re-attaching the EBS volume, use the following CLI commands to check if the EBS volume 'volume-id' is properly attached to the RDS instance: 

aws ec2 describe-volumes \
    --volume-ids volume-id |grep InstanceId

SP-S1006

EBS Storage Volume Size

The RDS Custom DB instance status is set to [Unsupported configuration] because of: There is a mismatch between the expected and configured settings of EBS storage volume "volume-id". The volume size has been changed manually at EC2 level from its original value(s) of [%s]. To resolve this issue, create a support case.

Use the following CLI command to compare the volume size of the EBS volume 'volume-id' details and the RDS instance details: 

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep AllocatedStorage

Use the following CLI command to view the actual allocated volume size: 

aws ec2 describe-volumes \
    --volume-ids |grep Size

SP-S1007

EBS Storage Volume Configuration

The RDS Custom DB instance status is set to [Unsupported configuration] because of: There is a mismatch between the expected and configured settings of EBS storage volume "volume-id". You can resolve this by modifying the EBS storage volume configuration [IOPS, Throughput, Volume type] to its original value(s) of [IOPS: %s, Throughput: %s, Volume type: %s] at the EC2 level. For future storage modifications, use the RDS console or CLI. The volume size has also been changed manually at EC2 level from its original value(s) of [%s]. To resolve this issue, create a support case.

Use the following CLI command to compare the volume type of the EBS volume 'volume-id' details and the RDS instance details. Make sure that the values at the EBS level matches the values at the RDS level: 

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep StorageType

To get the expected value for Storage Throughput at the RDS level:

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep StorageThroughput

To get the expected value for Volume IOPS at the RDS level:

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep Iops

To get the current Storage Type at the EC2 Level:

aws ec2 describe-volumes \
    --volume-ids |grep VolumeType

To get the current value for Storage Throughput at the EC2 Level:

aws ec2 describe-volumes \
    --volume-ids |grep Throughput

To get the current value for Volume IOPS at the EC2 Level:

aws ec2 describe-volumes \
    --volume-ids |grep Iops

SP-S1008

EBS Storage Volume Size and Configuration

The RDS Custom DB instance status is set to [Unsupported configuration] because of: There is a mismatch between the expected and configured settings of EBS storage volume "volume-id". You can resolve this by modifying the EBS storage volume configuration [IOPS, Throughput, Volume type] to its original value(s) of [IOPS: %s, Throughput: %s, Volume type: %s] at the EC2 level. For future storage modifications, use the RDS console or CLI. The volume size has also been changed manually at EC2 level from its original value(s) of [%s]. To resolve this issue, create a support case.

Use the following CLI command to compare the volume type of the EBS volume 'volume-id' details and the RDS instance details. Make sure that the values at the EBS level matches the values at the RDS level: 

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep StorageType

To get the expected value for Storage Throughput at the RDS level:

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep StorageThroughput

To get the expected value for Volume IOPS at the RDS level:

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep Iops

To get the current Storage Type at the EC2 Level:

aws ec2 describe-volumes \
    --volume-ids |grep VolumeType

To get the current value for Storage Throughput at the EC2 Level:

aws ec2 describe-volumes \
    --volume-ids |grep Throughput

To get the current value for Volume IOPS at the EC2 Level:

aws ec2 describe-volumes \
    --volume-ids |grep Iops

To get the expected Allocated Volume Size:

aws rds describe-db-instances \
    --db-instance-identifier db-instance-name |grep AllocatedStorage

To get the actual Allocated Volume Size:

aws ec2 describe-volumes \
    --volume-ids |grep Size

SP-S1009

SQS Permissions

The RDS Custom DB instance status is set to [Unsupported configuration] because of: Amazon Simple Queue Service (SQS) permissions are missing for the IAM instance profile. You can resolve this by making sure the IAM profile associated with the host has the following permissions: ["SQS:SendMessage","SQS:ReceiveMessage","SQS:DeleteMessage","SQS:GetQueueUrl"].

SP-S1010

SQS VPC Endpoint

The RDS Custom DB instance status is set to [Unsupported configuration] because of: A VPC endpoint policy is blocking the Amazon Simple Queue Service (SQS) operations. You can resolve this by modifying your VPC endpoint policy to allow the required SQS actions.

Operating system

Event Code Configuration area RDS event message Validation proces

SP-S2001

SQL Service Status

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The SQL Server service isn’t started. You can resolve this by restarting the SQL Server service on the host. If this DB instance is a Multi-AZ DB instance and restart fails, then stop and start the host to initiate a failover.

SP-S2002

RDS Custom Agent Status

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The RDS Custom Agent service isn’t installed or couldn’t be started. You can resolve this by reviewing the Windows Event Log to determine why the service won’t start, and take appropriate steps to fix the issue. For additional assistance, create a support case.

Log in to the host and make sure that the RDS Custom agent is running.

You can use the following commands to view the agent status.

$name = "RDSCustomAgent"
$service = Get-Service $name
Write-Host $service.Status

If the status isn't Running, you can start the service with the following command:

Start-Service $name

If the agent can't start, check the Windows Events to see why it can't start. The agent requires a Windows user to start the service. Ensure a Windows user exists and has privileges to run the service.

SP-S2003

SSM Agent Status

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The Amazon SSM Agent service is unreachable. You can troubleshoot this by checking the service status with the Get-Service AmazonSSMAgent PowerShell command, or starting the service with Start-Service AmazonSSMAgent. Ensure that HTTPS (port 443) outbound traffic to the ssm, ssmmessages, and ec2messages regional endpoints is allowed.

For more information, see Troubleshooting SSM Agent.

To troubleshoot SSM endpoints, see Unable to connect to SSM endpoints and Use ssm-cli to troubleshoot managed node availability.

SP-S2004

RDS Custom Agent Login

The RDS Custom DB instance status is set to [Unsupported configuration] because of: An unexpected issue occurred with the SQL login "$HOSTNAME/RDSAgent”. To resolve this issue, create a support case.

SP-S2005

Timezone

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The timezone on the Amazon EC2 Instance [%s] was changed. You can resolve this by modifying the time zone back to the setting specified during instance creation. If you would like to create an instance with a specific timezone, see the RDS Custom documentation.

Run the Get-Timezone PowerShell command to confirm the timezone.

For more information, see Local time zone for RDS Custom for SQL Server DB instances.

SP-S2006

High Availability Software Solution Version

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The high availability software solution of the current instance is different from the expected version. To resolve this issue, create a support case.

SP-S2007

High Availability Software Solution Configuration

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The configuration settings of the high availability software solution have been modified to unexpected values on the instance %s. To fix this issue, reboot the EC2 instance. When you reboot the EC2 instance, it automatically updates the settings to the required configuration for the high availability software solution.

SP-S2008

SQL Server Service

The RDS Custom DB instance is set to [Unsupported configuration]: SQLServer (MSSQLServer) service doesn't exist on the host. To resolve this, create a support case.

You can use the following commands to view the agent status.

$name = "MSSQLServer"
$service = Get-Service $name
Write-Host $service.Status

Database

Event Code Configuration area RDS event message Validation proces

SP-S3001

SQL Server Shared Memory Protocol

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The SQL Server shared memory protocol is disabled. You can resolve this by enabling the shared memory protocol in SQL Server Configuration Manager.

You can validate this by checking: SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER> Shared Memory as Enabled. After you enable the protocol, restart the SQL Server process.

SP-S3002

Service Master Key

The RDS Custom DB instance status is set to [Unsupported configuration] because of: RDS Automation is unable to take the backup of Service Master Key (SMK) as part of the new SMK generation. To resolve this issue, create a support case.

SP-S3003

Service Master Key

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The metadata related to the Service Master Key (SMK) is missing or incomplete. To resolve this issue, create a support case.

SP-S3004

DB Engine Version and Edition

The RDS Custom DB instance status is set to [Unsupported configuration] because of: There is a mismatch between the expected and installed SQL Server version and edition: Modifying the SQL Server edition is not supported on RDS Custom for SQL Server. Also, manually changing the SQL Server version on the RDS Custom EC2 instance is not supported. To resolve this issue, create a support case.

Run the following query to get the SQL version:

select @@version

Run the following AWS CLI command to get the RDS SQL engine version and edition:

aws rds describe-db-instances \
--db-instance-identifier db-instance-name |grep EngineVersion
aws rds describe-db-instances \
--db-instance-identifier db-instance-name |grep Engine

For more information, see Modifying an RDS Custom for SQL Server DB instance and Upgrading a DB instance engine version.

SP-S3005

DB Engine Edition

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The current SQL Server edition doesn't match the expected SQL Server edition [%s]: Modifying the SQL Server edition is not supported on RDS Custom for SQL Server. To resolve this issue, create a support case.

Run the following query to get the SQL edition:

select @@version

Run the following AWS CLI command to get the RDS SQL engine edition:

aws rds describe-db-instances \
--db-instance-identifier db-instance-name |grep Engine

SP-S3006

DB Engine Version

The RDS Custom DB instance status is set to [Unsupported configuration] because of: The current SQL Server version doesn't match the expected SQL Server version [%s]: You can't manually change the SQL Server version on the RDS Custom EC2 instance. To resolve this issue, create a support case. For any future modifications to SQL Server version, you can modify the instance from the AWS RDS console or through the modify-db-instance CLI command.

Run the following query to get the SQL version:

select @@version

Run the following AWS CLI command to get the RDS SQL engine version:

aws rds describe-db-instances \
--db-instance-identifier db-instance-name |grep EngineVersion

For more information, see Modifying an RDS Custom for SQL Server DB instance and Upgrading a DB instance engine version.

SP-S3007

Database file location

The RDS Custom DB instance status is set to [Unsupported configuration] because of: Database files are configured outside of the D:\ drive. You can resolve this by making sure that all database files, including ROW, LOG, FILESTREAM, etc... are stored on the D:\ drive.

Run the following query to list the location of database files that aren't in the default path:

USE master;
SELECT physical_name as  files_not_in_default_path
FROM sys.master_files
WHERE SUBSTRING(physical_name,1,3)!='D:\';

Troubleshooting Storage-Full in RDS Custom for SQL Server

RDS Custom monitors the available space on both the root (C:) and data (D:) volumes of an RDS Custom for SQL Server DB instance. RDS Custom moves the instance state to the Storage-Full status when either volume has less than 500 MiB disk space available. To scale the instance storage, see Modifying the storage for an RDS Custom for SQL Server DB instance.

Note

Instances in Storage-Full can take up to 30 minutes to resolve after scaling storage.

Troubleshooting PENDING_RECOVERY state for TDE enabled databases in RDS Custom for SQL Server

SQL Server databases with transparent data encryption (TDE) enabled might remain in PENDING_RECOVERY state if the automatic decryption runs into issues. This typically occurs after a DB instance restore if the source DB instance Service Master Key (SMK) backup file stored in the RDS Custom managed S3 bucket in your account has been deleted prior to the restore completion.

To enable the automatic decryption and bring the TDE enabled databases online, you need to open the Database Master Key (DMK) with its password and ecrypt the DMK using the SMK.

Use the following SQL Server commands for reference:

-- Identify PENDING_RECOVERY TDE databases
USE MASTER;
GO
SELECT name, is_encrypted, state_desc FROM sys.databases;
GO

-- Open DMK using password
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>';
GO

-- Encrypt DMK using SMK
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

-- Close SMK
CLOSE MASTER KEY;
GO

-- Bring the TDE databases online
ALTER DATABASE <database_name> SET ONLINE;
GO

-- Verify TDE databases are now in ONLINE state
SELECT name, is_encrypted, state_desc FROM sys.databases;
GO