^
--tags Key="AWSRDSCustom",Value="custom-sqlserver"
```
------
## Step 5: Create or modify a RDS Custom for SQL Server DB instance
Create or modify a RDS Custom for SQL Server DB instance for use with your directory. You can use the console, CLI, or RDS API to associate a DB instance with a directory. You can do this in one of the following ways:
+ Create a new SQL Server DB instance using the console, the [create-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html) CLI command, or the [CreateDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html) RDS API operation.
For instructions, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ Modify an existing SQL Server DB instance using the console, the [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) CLI command, or the [ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) RDS API operation.
For instructions, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
+ Restore a SQL Server DB instance from a DB snapshot using the console, the [restore-db-instance-from-db-snapshot](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html) CLI command, or the [RestoreDBInstanceFromDBSnapshot](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html) RDS API operation.
For instructions, see [Restoring to a DB instance](USER_RestoreFromSnapshot.md).
+ Restore a SQL Server DB instance to a point-in-time using the console, the [restore-db-instance-to-point-in-time](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html) CLI command, or the [RestoreDBInstanceToPointInTime](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html) RDS API operation.
For instructions, see [Restoring a DB instance to a specified time for Amazon RDS](USER_PIT.md).
**Note**
If your RDS Custom for SQL Server instance is already joined to an AD manually, check the settings for [Network configuration port rules](custom-sqlserver-WinAuth.NWConfigPorts.md), [Network Validation](custom-sqlserver-WinAuth.NWValidation.md), and complete steps 1 though Step 4. Update the `--domain-fqdn`, `--domain-ou`, and `--domain-auth-secret-arn` to your AD, so that domain join credentials and configurations are registered with RDS Custom to monitor, register CNAME, and take recovery actions.
When you use the AWS CLI, the following parameters are required for the DB instance to be able to use the directory that you created:
+ For the `--domain-fqdn` parameter, use the fully qualified domain name of your self-managed AD.
+ For the `--domain-ou` parameter, use the OU that you created in your self-managed AD.
+ For the `--domain-auth-secret-arn` parameter, use the value of the **Secret ARN** that you created.
**Important**
If you modify a DB instance to join or remove from a self-managed AD domain or AWS Managed Microsoft AD, a reboot of the DB instance is required for the modification to take effect. You can choose to apply the changes immediately or wait until the next maintenance window. Choosing the **Apply Immediately** option causes downtime for a single-AZ DB instance. A Multi-AZ DB cluster performs a failover before completing a reboot. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
The following CLI command creates a new RDS Custom for SQL Server DB instance and joins it to self-managed or AWS Managed Microsoft AD domain.
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--engine custom-sqlserver-se \
--engine-version 15.00.4312.2.v1 \
--db-instance-identifier my-custom-instance \
--db-instance-class db.m5.large \
--allocated-storage 100 --storage-type io1 --iops 1000 \
--master-username my-master-username \
--master-user-password my-master-password \
--kms-key-id my-RDSCustom-key-id \
--custom-iam-instance-profile AWSRDSCustomInstanceProfileForRdsCustomInstance \
--domain-fqdn "corp.example.com" \
--domain-ou "OU=RDSCustomOU,DC=corp,DC=example,DC=com" \
--domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:do-not-delete-rds-custom-my-AD-test-secret-123456" \
--db-subnet-group-name my-DB-subnet-grp \
--vpc-security-group-ids my-securitygroup-id \
--no-publicly-accessible \
--backup-retention-period 3 \
--port 8200 \
--region us-west-2 \
--no-multi-az
```
For Windows:
```
aws rds create-db-instance ^
--engine custom-sqlserver-se ^
--engine-version 15.00.4312.2.v1 ^
--db-instance-identifier my-custom-instance ^
--db-instance-class db.m5.large ^
--allocated-storage 100 --storage-type io1 --iops 1000 ^
--master-usernamemy-master-username ^
--master-user-password my-master-password ^
--kms-key-id my-RDSCustom-key-id ^
--custom-iam-instance-profile AWSRDSCustomInstanceProfileForRdsCustomInstance ^
--domain-fqdn "corp.example.com" ^
--domain-ou "OU=RDSCustomOU,DC=corp,DC=example,DC=com" ^
--domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:do-not-delete-rds-custom-my-AD-test-secret-123456" ^
--db-subnet-group-name my-DB-subnet-grp ^
--vpc-security-group-ids my-securitygroup-id ^
--no-publicly-accessible ^
--backup-retention-period 3 ^
--port 8200 ^
--region us-west-2 ^
--no-multi-az
```
**Important**
If your NetBIOS for AWS Managed Microsoft AD is **corpexample**, then it appears as an OU itself. Any new OU created earlier will appear as a nested OU. For AWS Managed Microsoft AD, set `--domain-ou` to `"OU=RDSCustomOU,OU=corpexample,DC=corp,DC=example,DC=com"`.
The following command modifies an existing RDS Custom for SQL Server DB instance to use an Active Directory domain.
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier my-custom-instance \
--domain-fqdn "corp.example.com" \
--domain-ou "OU=RDSCustomOU,DC=corp,DC=example,DC=com" \
--domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:do-not-delete-rds-custom-my-AD-test-secret-123456" \
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier my-custom-instance ^
--domain-fqdn "corp.example.com" ^
--domain-ou "OU=RDSCustomOU,DC=corp,DC=example,DC=com" ^
--domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:do-not-delete-rds-custom-my-AD-test-secret-123456" ^
```
The following CLI command removes and RDS Custom for SQL Server DB instance from a Active Directory domain.
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier my-custom-instance \
--disable-domain
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier my-custom-instance ^
--disable-domain
```
When using the console to create or modify your instance, click on **Enable Microsoft SQL Server Windows Authentication** to see the following options.
![\[Microsoft SQL Server Windows Authentication directory\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/custom-sqs-WinAuth.png)
You are responsible to make sure your domain FQDN is resolving to the domain controller IP addresses. If domain controller IPs are not resolving, domain join operations fail but RDS Custom for SQL Server instance creation succeeds. For troubleshooting information, see [Troubleshooting Active Directory](custom-sqlserver-WinAuth.Troubleshoot.md).
## Step 6: Create Windows Authentication SQL Server Login
Use the Amazon RDS master user credentials to connect to the SQL Server DB instance as you do for any other DB instance. Because the DB instance is joined to the AD domain, you can provision SQL Server logins and users. You do this from the AD users and groups utility in your AD domain. Database permissions are managed through standard SQL Server permissions granted and revoked to these Windows logins.
For an AD user to authenticate with SQL Server, a SQL Server Windows login must exist for the AD user or an Active Directory group that the user is a member of. Fine-grained access control is handled through granting and revoking permissions on these SQL Server logins. An AD user that doesn't have a SQL Server login or belong to an AD group with such a login can't access the SQL Server DB instance.
The `ALTER ANY LOGIN` permission is required to create an AD SQL Server login. If you haven't created any logins with this permission, connect as the DB instance's master user using SQL Server Authentication and create your AD SQL Server logins under the context of the master user.
You can run a data definition language (DDL) command such as the following to create a SQL Server login for an AD user or group.
```
USE [master]
GO
CREATE LOGIN [mydomain\myuser] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english];
GO
```
Users (both humans and applications) from your domain can now connect to the RDS Custom for SQL Server instance from a domain-joined client machine using Windows authentication.
## Step 7: Using Kerberos or NTLM Authentication
### NTLM authentication using RDS endpoint
Each Amazon RDS DB instance has an endpoint and each endpoint has a DNS name and port number for the DB instance. To connect to your DB instance using a SQL client application, you need the DNS name and port number for your DB instance. To authenticate using NTLM authentication, you must connect to the RDS endpoint.
During planned database maintenance or unplanned service disruption, Amazon RDS automatically fails over to the up-to-date secondary database so operations can resume quickly without manual intervention. The primary and secondary instances use the same endpoint, whose physical network address transitions to the secondary as part of the failover process. You don't have to reconfigure your application when a failover occurs.
### Kerberos authentication
Kerberos-based authentication for RDS Custom for SQL Server requires connections be made to a specific Service Principal Name (SPN). However, after a failover event, the application might not be aware of the new SPN. To address this, RDS Custom for SQL Server offers a Kerberos-based endpoint.
The Kerberos-based endpoint follows a specific format. If your RDS endpoint is `rds-instance-name.account-region-hash.aws-region.rds.amazonaws.com`, the corresponding Kerberos-based endpoint would be `rds-instance-name.account-region-hash.aws-region.awsrds.fully qualified domain name (FQDN)`.
For example, if the RDS endpoint is `ad-test.cocv6zwtircu.us-east-1.rds.amazonaws.com` and the domain name is `corp-ad.company.com`, the Kerberos-based endpoint would be `ad-test.cocv6zwtircu.us-east-1.awsrds.corp-ad.company.com`.
This Kerberos-based endpoint can be used to authenticate with the SQL Server instance using Kerberos, even after a failover event, as the endpoint is automatically updated to point to the new SPN of the primary SQL Server instance.
### Finding your CNAME
To find your CNAME, connect to your domain controller and open **DNS Manager**. Navigate to **Forward Lookup Zones** and your FQDN.
Navigate through **awsrds**, **aws-region**, and **account and region specific hash**.
If you are connecting the RDS Custom EC2 instance and trying to connect to the database locally using CNAME, your connection will use NTLM authentication instead of Kerberos.
If after connecting CNAME from remote client, an NTLM connection is returned, check if required ports are allowlisted.
To check if your connection is using Kerberos, run the following query:
```
SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SSPID;
```
# Managing a DB instance in a Domain
You can use the console, AWS CLI, or the Amazon RDS API to manage your DB instance and its relationship with your domain. For example, you can move the DB instance into, out of, or between domains.
For example, using the Amazon RDS API, you can do the following:
+ To reattempt a domain join for a failed membership, use the [ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) API operation and specify the current membership's directory ID.
+ To update the IAM role name for membership, use the `ModifyDBInstance` API operation and specify the current membership's directory ID and the new IAM role.
+ To remove a DB instance from a domain, use the `ModifyDBInstance` API operation and specify `none` as the domain parameter.
+ To move a DB instance from one domain to another, use the `ModifyDBInstance` API operation and specify the domain identifier of the new domain as the domain parameter.
+ To list membership for each DB instance, use the [DescribeDBInstances](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/DescribeDBInstances.html) API operation.
## Restoring a RDS Custom for SQL Server DB instance and adding it to an Active Directory domain
You can restore a DB snapshot or do point-in-time recovery (PITR) for a SQL Server DB instance and then add it to an Active Directory domain. Once the DB instance is restored, modify the instance using the process explained in [Step 5: Create or modify a RDS Custom for SQL Server DB instance](custom-sqlserver-WinAuth.settingUp.md#custom-sqlserver-WinAuth.settingUp.CreateDBInstance) to add the DB instance to an AD domain.
# Understanding Domain membership
After you create or modify your DB instance, the instance becomes a member of the domain. The AWS console indicates the status of the domain membership for the DB instance. The status of the DB instance can be one of the following:
+ **joined** – The instance is a member of the domain.
+ **joining** – The instance is in the process of becoming a member of the domain.
+ **pending-join** – The instance membership is pending.
+ **pending-maintenance-join** – AWS will attempt to make the instance a member of the domain during the next scheduled maintenance window.
+ **pending-removal** – The removal of the instance from the domain is pending.
+ **pending-maintenance-removal** – AWS will attempt to remove the instance from the domain during the next scheduled maintenance window.
+ **failed** – A configuration problem has prevented the instance from joining the domain. Check and fix your configuration before reissuing the instance modify command.
+ **removing** – The instance is being removed from the domain.
A request to become a member of a domain can fail because of a network connectivity issue or an incorrect IAM role. For example, you might create a DB instance or modify an existing instance and have the attempt fail for the DB instance to become a member of a domain. In this case, either reissue the command to create or modify the DB instance or modify the newly created instance to join the domain.
# Troubleshooting Active Directory
The following are issues you might encounter when you set up or modify an AD.
| Error Code | Description | Common causes | Troubleshooting suggestions |
| --- | --- | --- | --- |
| Error 2 / 0x2 | The system cannot find the file specified. | The format or location for the Organizational Unit (OU) specified with the `—domain-ou` parameter is invalid. The domain service account specified via AWS Secrets Manager lack the permissions required to join the OU. | Review the `—domain-ou` parameter. Ensure the domain service account has the correct permissions to the OU. |
| Error 5 / 0x5 | Access is denied. | Misconfigured permissions for the domain service account, or the computer account already exists in the domain. | Review the domain service account permissions in the domain, and verify that the RDS computer account is not duplicated in the domain. You can verify the name of the RDS computer account by running `SELECT @@SERVERNAME` on your RDS Custom for SQL Server DB instance. If you are using Multi-AZ, try rebooting with failover and then verify that the RDS computer account again. For more information, see [Rebooting a DB instance](USER_RebootInstance.md). |
| Error 87 / 0x57 | The parameter is incorrect. | The domain service account specified via AWS Secrets Manager doesn't have the correct permissions. The user profile may also be corrupted. | Review the requirements for the domain service account. |
| Error 234 / 0xEA | Specified Organizational Unit (OU) does not exist. | The OU specified with the `—domain-ou` parameter doesn't exist in your AD. | Review the `—domain-ou` parameter and ensure the specified OU exists in your AD. |
| Error 1326 / 0x52E | The user name or password is incorrect. | The domain service account credentials provided in AWS Secrets Manager contains an unknown username or bad password. The domain account may also be disabled in your AD. | Ensure the credentials provided in AWS Secrets Manager are correct and the domain account is enabled in your Active Directory. |
| Error 1355 / 0x54B | The specified domain either does not exist or could not be contacted. | The domain is down, the specified set of DNS IPs are unreachable, or the specified FQDN is unreachable. | Review the `—domain-dns-ips` and `—domain-fqdn` parameters to ensure they're correct. Review the networking configuration of your RDS Custom for SQL Server DB instance and ensure your AD is reachable. |
| Error 1722 / 0x6BA | The RPC server is unavailable. | There was an issue reaching the RPC service of your AD domain. This might be a service or network issue. | Validate that the RPC service is running on your domain controllers and that the TCP ports `135` and `49152-65535` are reachable on your domain from your RDS Custom for SQL Server DB instance. |
| Error 2224 / 0x8B0 | The user account already exists. | The computer account that's attempting to be added to your AD already exists. | Identify the computer account by running `SELECT @@SERVERNAME` on your RDS Custom for SQL Server DB instance and then carefully remove it from your AD. |
| Error 2242 / 0x8c2 | The password of this user has expired. | The password for the domain service account specified via AWS Secrets Manager has expired. | Update the password for the domain service account used to join your RDS Custom for SQL Server DB instance to your AD. |
# Managing a Multi-AZ deployment for RDS Custom for SQL Server
In a Multi-AZ DB instance deployment for RDS Custom for SQL Server, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone (AZ). The primary DB instance is synchronously replicated across Availability Zones to a standby replica to provide data redundancy.
**Important**
A Multi-AZ deployment for RDS Custom for SQL Server is different than Multi-AZ for RDS for SQL Server. Unlike Multi-AZ for RDS for SQL Server, you must set up prerequisites for RDS Custom for SQL Server before creating your Multi-AZ DB instance because RDS Custom runs inside your own account, which requires permissions.
If you don't complete the prerequisites, your Multi-AZ DB instance might fail to run, or automatically revert to a Single-AZ DB instance. For more information about prerequisites, see [Prerequisites for a Multi-AZ deployment with RDS Custom for SQL Server](custom-sqlserver-multiaz.prerequisites.md).
Running a DB instance with high availability can enhance availability during planned system maintenance. In the event of planned database maintenance or unplanned service disruption, Amazon RDS automatically fails over to the up-to-date secondary DB instance. This functionality lets database operations resume quickly without manual intervention. The primary and standby instances use the same endpoint, whose physical network address transitions to the secondary replica as part of the failover process. You don't have to reconfigure your application when a failover occurs.
![\[RDS Custom for SQL Server supports Multi-AZ.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/custom-sqlserver-multiaz-architecture.png)
You can create an RDS Custom for SQL Server Multi-AZ deployment by specifying Multi-AZ when creating an RDS Custom DB instance. You can use the console to convert existing RDS Custom for SQL Server DB instances to Multi-AZ deployments by modifying the DB instance and specifying the Multi-AZ option. You can also specify a Multi-AZ DB instance deployment with the AWS CLI or Amazon RDS API.
The RDS console shows the Availability Zone of the standby replica (the secondary AZ). You can also use the `describe-db-instances` CLI command or the `DescribeDBInstances` API operation to find the secondary AZ.
RDS Custom for SQL Server DB instances with Multi-AZ deployment can have increased write and commit latency compared to a Single-AZ deployment. This increase can happen because of the synchronous data replication between DB instances. You might have a change in latency if your deployment fails over to the standby replica, although AWS is engineered with low-latency network connectivity between Availability Zones.
**Note**
For production workloads, we recommend that you use a DB instance class with Provisioned IOPS (input/output operations per second) for fast, consistent performance. For more information about DB instance classes, see [Requirements and limitations for Amazon RDS Custom for SQL Server](custom-reqs-limits-MS.md).
**Topics**
+ [
## Region and version availability
](#custom-sqlserver-multiaz.regionversion)
+ [
## Limitations for a Multi-AZ deployment with RDS Custom for SQL Server
](#custom-sqlserver-multiaz.limitations)
+ [
# Prerequisites for a Multi-AZ deployment with RDS Custom for SQL Server
](custom-sqlserver-multiaz.prerequisites.md)
+ [
## Creating an RDS Custom for SQL Server Multi-AZ deployment
](#custom-sqlserver-multiaz.creating)
+ [
# Modifying an RDS Custom for SQL Server Single-AZ deployment to a Multi-AZ deployment
](custom-sqlserver-multiaz.modify-saztomaz.md)
+ [
# Modifying an RDS Custom for SQL Server Multi-AZ deployment to a Single-AZ deployment
](custom-sqlserver-multiaz.modify-maztosaz.md)
+ [
# Failover process for an RDS Custom for SQL Server Multi-AZ deployment
](custom-sqlserver-multiaz.failover.md)
## Region and version availability
Multi-AZ deployments for RDS Custom for SQL Server are supported for the following SQL Server editions:
+ SQL Server 2022 and 2019: Enterprise, Standard, Web, and Developer Edition
**Note**
Multi-AZ deployments for RDS Custom for SQL Server aren't supported on SQL Server 2019 CU8 (15.00.4073.23) or lower versions.
Multi-AZ deployments for RDS Custom for SQL Server are available in all Regions where RDS Custom for SQL Server is available. For more information on Region availability of Multi-AZ deployments for RDS Custom for SQL Server, see [Supported Regions and DB engines for RDS Custom for SQL Server](Concepts.RDS_Fea_Regions_DB-eng.Feature.RDSCustom.md#Concepts.RDS_Fea_Regions_DB-eng.Feature.RDSCustom.sq).
## Limitations for a Multi-AZ deployment with RDS Custom for SQL Server
Multi-AZ deployments with RDS Custom for SQL Server have the following limitations:
+ Cross-Region Multi-AZ deployments aren't supported.
+ You can’t configure the secondary DB instance to accept database read activity.
+ When you use a Custom Engine Version (CEV) with a Multi-AZ deployment, your secondary DB instance will also use the same CEV. The secondary DB instance can't use a different CEV.
# Prerequisites for a Multi-AZ deployment with RDS Custom for SQL Server
If you have an existing RDS Custom for SQL Server Single-AZ deployment, the following additional prerequisites are required before modifying it to a Multi-AZ deployment. You can choose to complete the prerequisites manually or with the provided CloudFormation template. The latest CloudFormation template contains the prerequisites for both Single-AZ and Multi-AZ deployments.
**Important**
To simplify setup, we recommend that you use the latest CloudFormation template file provided in the network setup instructions to create the prerequisites. For more information, see [Configuring with CloudFormation](custom-setup-sqlserver.md#custom-setup-sqlserver.cf).
**Note**
When you modify an existing RDS Custom for SQL Server Single-AZ deployment to a Multi-AZ deployment, you must complete these prerequisites. If you don't complete the prerequisites, the Multi-AZ setup will fail. To complete the prerequisites, follow the steps in [Modifying an RDS Custom for SQL Server Single-AZ deployment to a Multi-AZ deployment](custom-sqlserver-multiaz.modify-saztomaz.md).
+ Update the RDS security group inbound and outbound rules to allow port 1120.
+ Add a rule in your private network Access Control List (ACL) that allows TCP ports `0-65535` for the DB instance VPC.
+ Create new Amazon SQS VPC endpoints that allow the RDS Custom for SQL Server DB instance to communicate with SQS.
+ Update the SQS permissions in the instance profile role.
## Creating an RDS Custom for SQL Server Multi-AZ deployment
To create an RDS Custom for SQL Server Multi-AZ deployment, follow the steps in [Creating and connecting to a DB instance for Amazon RDS Custom for SQL Server](custom-creating-sqlserver.md).
**Important**
To simplify setup, we recommend that you use the latest CloudFormation template file provided in the network setup instructions. For more information, see [Configuring with CloudFormation](custom-setup-sqlserver.md#custom-setup-sqlserver.cf).
Creating a Multi-AZ deployment takes a few minutes to complete.
# Modifying an RDS Custom for SQL Server Single-AZ deployment to a Multi-AZ deployment
You can modify an existing RDS Custom for SQL Server DB instance from a Single-AZ deployment to a Multi-AZ deployment. When you modify the DB instance,Amazon RDS performs several actions:
+ Takes a snapshot of the primary DB instance.
+ Creates new volumes for the standby replica from the snapshot. These volumes initialize in the background, and maximum volume performance is achieved after the data is fully initialized.
+ Turns on synchronous block-level replication between the primary and secondary DB instances.
**Important**
We recommend that you avoid modifying your RDS Custom for SQL Server DB instance from a Single-AZ to a Multi-AZ deployment on a production DB instance during periods of peak activity.
AWS uses a snapshot to create the standby instance to avoid downtime when you convert from Single-AZ to Multi-AZ, but performance might be impacted during and after converting to Multi-AZ. This impact can be significant for workloads that are sensitive to write latency. While this capability allows large volumes to quickly be restored from snapshots, it can cause increase in the latency of I/O operations because of the synchronous replication. This latency can impact your database performance.
**Note**
If you created your RDS Custom for SQL Server DB instance before 29 August, 2024, patch to the latest minor version before modifying.
For SQL Server 2019 instances, upgrade the DB engine version to `15.00.4410.1.v1` or higher.
For SQL Server 2022 instances, upgrade the DB engine version to `16.00.4150.1.v1` or higher.
**Topics**
+ [
## Configuring prerequisites to modify a Single-AZ to a Multi-AZ deployment using CloudFormation
](#custom-sqlserver-multiaz.modify-saztomaz-prereqs.cf)
+ [
## Configuring prerequisites to modify a Single-AZ to a Multi-AZ deployment manually
](#custom-sqlserver-multiaz.modify-saztomaz-prereqs.manual)
+ [
## Modify using the RDS console, AWS CLI, or RDS API.
](#custom-sqlserver-multiaz.modify-saztomaz-afterprereqs)
## Configuring prerequisites to modify a Single-AZ to a Multi-AZ deployment using CloudFormation
To use a Multi-AZ deployment, you must ensure you've applied the latest CloudFormation template with prerequisites, or manually configure the latest prerequisites. If you've already applied the latest CloudFormation prerequisite template, you can skip these steps.
To configure the RDS Custom for SQL Server Multi-AZ deployment prerequisites using CloudFormation
1. Open the CloudFormation console at [https://console.aws.amazon.com/cloudformation](https://console.aws.amazon.com/cloudformation/).
1. To start the Create Stack wizard, select the existing stack you used to create a Single-AZ deployment and choose** Update**.
The **Update stack** page appears.
1. For **Prerequisite - Prepare template**, choose **Replace current template**.
1. For **Specify template**, do the following:
1. Download the latest CloudFormation template file. Open the context (right-click) menu for the link [custom-sqlserver-onboard.zip](samples/custom-sqlserver-onboard.zip) and choose **Save Link As**.
1. Save and extract the `custom-sqlserver-onboard.json` file to your computer.
1. For **Template source**, choose **Upload a template file**.
1. For **Choose file**, navigate to and then choose `custom-sqlserver-onboard.json`.
1. Choose **Next**.
The **Specify stack details** page appears.
1. To keep the default options, choose **Next**.
The **Advanced Options** page appears.
1. To keep the default options, choose **Next**.
1. To keep the default options, choose **Next**.
1. On the **Review Changes** page, do the following:
1. For **Capabilities**, select the ****I acknowledge that CloudFormation might create IAM resources with custom names**** check box.
1. Choose **Submit**.
1. Verify the update is successful. The status of a successful operation shows `UPDATE_COMPLETE`.
If the update fails, any new configuration specified in the update process will be rolled back. The existing resource will still be usable. For example, if you add network ACL rules numbered 18 and 19, but there were existing rules with same numbers, the update would return the following error: `Resource handler returned message: "The network acl entry identified by 18 already exists.` In this scenario you can modify the existing ACL rules to use a number lower than 18, then retry the update.
## Configuring prerequisites to modify a Single-AZ to a Multi-AZ deployment manually
**Important**
To simplify setup, we recommend that you use the latest CloudFormation template file provided in the network setup instructions. For more information, see [Configuring prerequisites to modify a Single-AZ to a Multi-AZ deployment using CloudFormation](#custom-sqlserver-multiaz.modify-saztomaz-prereqs.cf).
If you choose to configure the prerequisites manually, perform the following tasks.
1. Open the Amazon VPC console at [https://console.aws.amazon.com/vpc/](https://console.aws.amazon.com/vpc/).
1. Choose **Endpoint**. The **Create Endpoint** page appears.
1. For **Service Category**, choose **AWS services**.
1. In **Services**, search for *SQS*
1. In **VPC**, choose the VPC where your RDS Custom for SQL Server DB instance is deployed.
1. In **Subnets**, choose the subnets where your RDS Custom for SQL Server DB instance is deployed.
1. In **Security Groups**, choose the *-vpc-endpoint-sg* group.
1. For **Policy**, choose **Custom**
1. In your custom policy, replace the *AWS partition*, *Region*, *accountId*,and *IAM-Instance-role* with your own values.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Condition": {
"StringLike": {
"aws:ResourceTag/AWSRDSCustom": "custom-sqlserver"
}
},
"Action": [
"SQS:SendMessage",
"SQS:ReceiveMessage",
"SQS:DeleteMessage",
"SQS:GetQueueUrl"
],
"Resource": "arn:aws:sqs:us-east-1:111122223333:do-not-delete-rds-custom-*",
"Effect": "Allow",
"Principal": {
"AWS": "arn:aws:iam::111122223333:role/{IAM-Instance-role}"
}
}
]
}
```
------
1. Update the **Instance profile** with permission to access Amazon SQS. Replace the *AWS partition*, *Region*, and *accountId* with your own values.
```
{
"Sid": "SendMessageToSQSQueue",
"Effect": "Allow",
"Action": [
"SQS:SendMessage",
"SQS:ReceiveMessage",
"SQS:DeleteMessage",
"SQS:GetQueueUrl"
],
"Resource": [
{
"Fn::Sub": "arn:${AWS::Partition}:sqs:${AWS::Region}:${AWS::AccountId}:do-not-delete-rds-custom-*"
}
],
"Condition": {
"StringLike": {
"aws:ResourceTag/AWSRDSCustom": "custom-sqlserver"
}
}
}
>
```
1. Update the Amazon RDS security group inbound and outbound rules to allow port 1120.
1. In **Security Groups**, choose the *-rds-custom-instance-sg* group.
1. For **Inbound Rules**, create a **Custom TCP** rule to allow port *1120* from the source *-rds-custom-instance-sg* group.
1. For **Outbound Rules**, create a **Custom TCP** rule to allow port *1120* to the destination *-rds-custom-instance-sg* group.
1. Add a rule in your private network Access Control List (ACL) that allows TCP ports `0-65535` for the source subnet of the DB instance.
**Note**
When creating an **Inbound Rule** and **Outbound Rule**, take note of the highest existing **Rule number**. The new rules you create must have a **Rule number** lower than 100 and not match any existing **Rule number**.
1. In **Network ACLs**, choose the *-private-network-acl* group.
1. For **Inbound Rules**, create an **All TCP** rule to allow TCP ports `0-65535` with a source from *privatesubnet1* and *privatesubnet2*.
1. For **Outbound Rules**, create an **All TCP** rule to allow TCP ports `0-65535` to destination *privatesubnet1* and *privatesubnet2*.
## Modify using the RDS console, AWS CLI, or RDS API.
After you've completed the prerequisites, you can modify an RDS Custom for SQL Server DB instance from a Single-AZ to Multi-AZ deployment using the RDS console, AWS CLI, or RDS API.
### Console
**To modify an existing RDS Custom for SQL Server Single-AZ to Multi-AZ deployment**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the Amazon RDS console, choose **Databases**.
The **Databases** pane appears.
1. Choose the RDS Custom for SQL Server DB instance that you want to modify.
1. For **Actions**, choose **Convert to Multi-AZ deployment**.
1. On the **Confirmation** page, choose **Apply immediately** to apply the changes immediately. Choosing this option doesn't cause downtime, but there is a possible performance impact. Alternatively, you can choose to apply the update during the next maintenance window. For more information, see [Using the schedule modifications setting](USER_ModifyInstance.ApplyImmediately.md).
1. On the **Confirmation** page, choose **Convert to Multi-AZ**.
### AWS CLI
To convert to a Multi-AZ DB instance deployment by using the AWS CLI, call the [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) command and set the `--multi-az` option. Specify the DB instance identifier and the values for other options that you want to modify. For information about each option, see [Settings for DB instances](USER_ModifyInstance.Settings.md).
**Example**
The following code modifies `mycustomdbinstance` by including the `--multi-az` option. The changes are applied during the next maintenance window by using `--no-apply-immediately`. Use `--apply-immediately` to apply the changes immediately. For more information, see [Using the schedule modifications setting](USER_ModifyInstance.ApplyImmediately.md).
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mycustomdbinstance \
--multi-az \
--no-apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mycustomdbinstance ^
--multi-az \ ^
--no-apply-immediately
```
### RDS API
To convert to a Multi-AZ DB instance deployment with the RDS API, call the [ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) operation and set the `MultiAZ` parameter to true.
# Modifying an RDS Custom for SQL Server Multi-AZ deployment to a Single-AZ deployment
You can modify an existing RDS Custom for SQL Server DB instance from a Multi-AZ to a Single-AZ deployment.
## Console
**To modify an RDS Custom for SQL Server DB instance from a Multi-AZ to Single-AZ deployment.**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the Amazon RDS console, choose **Databases**.
The **Databases** pane appears.
1. Choose the RDS Custom for SQL Server DB instance that you want to modify.
1. For **Multi-AZ deployment**, choose **No**.
1. On the **Confirmation** page, choose **Apply immediately** to apply the changes immediately. Choosing this option doesn't cause downtime, but there is a possible performance impact. Alternatively, you can choose to apply the update during the next maintenance window. For more information, see [Using the schedule modifications setting](USER_ModifyInstance.ApplyImmediately.md).
1. On the **Confirmation** page, choose **Modify DB Instance**.
## AWS CLI
To modify a Multi-AZ deployment to a Single-AZ deployment by using the AWS CLI, call the [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) command and include the `--no-multi-az` option. Specify the DB instance identifier and the values for other options that you want to modify. For information about each option, see [Settings for DB instances](USER_ModifyInstance.Settings.md).
**Example**
The following code modifies `mycustomdbinstance` by including the `--no-multi-az` option. The changes are applied during the next maintenance window by using `--no-apply-immediately`. Use `--apply-immediately` to apply the changes immediately. For more information, see [Using the schedule modifications setting](USER_ModifyInstance.ApplyImmediately.md).
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mycustomdbinstance \
--no-multi-az \
--no-apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mycustomdbinstance ^
--no-multi-az \ ^
--no-apply-immediately
```
## RDS API
To modify a Multi-AZ deployment to a Single-AZ deployment by using the RDS API, call the [ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) operation and set the `MultiAZ` parameter to `false`.
# Failover process for an RDS Custom for SQL Server Multi-AZ deployment
If a planned or unplanned outage of your DB instance results from an infrastructure defect, Amazon RDS automatically switches to a standby replica in another Availability Zone if you have turned on Multi-AZ. The time that it takes for the failover to complete depends on the database activity and other conditions at the time that the primary DB instance became unavailable. Failover times are typically 60 – 120 seconds. However, large transactions or a lengthy recovery process can increase failover time. When the failover is complete, it can take additional time for the RDS console to show the new Availability Zone.
**Note**
You can force a failover manually when you reboot a DB instance with failover. For more information on rebooting a DB instance, see [Rebooting a DB instance](USER_RebootInstance.md)
Amazon RDS handles failovers automatically so you can resume database operations as quickly as possible without administrative intervention. The primary DB instance switches over automatically to the standby replica if any of the conditions described in the following table occurs. You can view these failover reasons in the RDS event log.
****
| Failover reason | Description |
| --- | --- |
| `The operating system for the RDS Custom for SQL Server Multi-AZ DB instance is being patched in an offline operation` | A failover was triggered during the maintenance window for an OS patch or a security update. For more information, see [Maintaining a DB instance](USER_UpgradeDBInstance.Maintenance.md). |
| `The primary host of the RDS Custom for SQL Server Multi-AZ DB instance is unhealthy.` | The Multi-AZ DB instance deployment detected an impaired primary DB instance and failed over. |
| `The primary host of the RDS Custom for SQL Server Multi-AZ DB instance is unreachable due to loss of network connectivity.` | RDS monitoring detected a network reachability failure to the primary DB instance and triggered a failover. |
| `The RDS Custom for SQL Server Multi-AZ DB instance was modified by the customer.` | A DB instance modification triggered a failover. For more information, see [Modifying an RDS Custom for SQL Server DB instance](custom-managing.modify-sqlserver.md). |
| `The storage volume of the primary host of the RDS Custom for SQL Server Multi-AZ DB instance experienced a failure.` | The Multi-AZ DB instance deployment detected a storage issue on the primary DB instance and failed over. |
| `The user requested a failover of the RDS Custom for SQL Server Multi-AZ DB instance.` | The RDS Custom for SQL Server Multi-AZ DB instance was rebooted with failover. For more information, see [Rebooting a DB instance](USER_RebootInstance.md). |
| `The RDS Custom for SQL Server Multi-AZ primary DB instance is busy or unresponsive.` | The primary DB instance is unresponsive. We recommend that you try the following steps: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-sqlserver-multiaz.failover.html) |
To determine if your Multi-AZ DB instance has failed over, you can do the following:
+ Set up DB event subscriptions to notify you by email or SMS that a failover has been initiated. For more information about events, see [Working with Amazon RDS event notification](USER_Events.md).
+ View your DB events by using the RDS console or API operations.
+ View the current state of your RDS Custom for SQL Server Multi-AZ DB instance deployment by using the RDS console, CLI, or API operations.
## Time to live (TTL) settings with applications using an RDS Custom for SQL Server Multi-AZ deployment
The failover mechanism automatically changes the Domain Name System (DNS) record of the DB instance to point to the standby DB instance. As a result, you need to re-establish any existing connections to your DB instance. Ensure that any DNS cache time-to-live (TTL) configuration value is low, and validate that your application will not cache DNS for an extended time. A high TTL value might prevent your application from quickly reconnecting to the DB instance after failover.
# Backing up and restoring an Amazon RDS Custom for SQL Server DB instance
Like Amazon RDS, RDS Custom creates and saves automated backups of your RDS Custom for SQL Server DB instance when backup retention is enabled. You can also back up your DB instance manually. The automated backups are comprised of snapshot backups and transaction log backups. Snapshot backups are taken for the entire storage volume of DB instance during your specified backup window. Transaction log backups are taken for the PITR-eligible databases on a regular interval period. RDS Custom saves the automated backups of your DB instance according to your specified backup retention period. You can use automated backups to recover your DB instance to a point in time within the backup retention period.
You can also take snapshot backups manually. You can create a new DB instance from these snapshot backups at any time. For more information about manually creating a DB snapshot, see [Creating an RDS Custom for SQL Server snapshot](custom-backup-sqlserver.creating.md).
Although snapshot backups serve operationally as full backups, you are billed only for incremental storage use. The first snapshot of an RDS Custom DB instance contains the data for the full DB instance. Subsequent snapshots of the same database are incremental, which means that only the data that has changed after your most recent snapshot is saved.
**Topics**
+ [
# Creating an RDS Custom for SQL Server snapshot
](custom-backup-sqlserver.creating.md)
+ [
# Restoring from an RDS Custom for SQL Server DB snapshot
](custom-backup-sqlserver.restoring.md)
+ [
# Restoring an RDS Custom for SQL Server instance to a point in time
](custom-backup.pitr-sqs.md)
+ [
# Deleting an RDS Custom for SQL Server snapshot
](custom-backup-sqlserver.deleting.md)
+ [
# Deleting RDS Custom for SQL Server automated backups
](custom-backup-sqlserver.deleting-backups.md)
# Creating an RDS Custom for SQL Server snapshot
RDS Custom for SQL Server creates a storage volume snapshot of your DB instance, backing up the entire DB instance and not just individual databases. When you create a snapshot, specify which RDS Custom for SQL Server DB instance to back up. Give your snapshot a name so you can restore from it later.
When you create a snapshot, RDS Custom for SQL Server creates an Amazon EBS snapshot for volume `(D:)`, which is the database volume attached to the DB instance. To make snapshots easy to associate with a specific DB instance, they're tagged with `DBSnapshotIdentifier`, `DbiResourceId`, and `VolumeType`.
Creating a DB snapshot results in a brief I/O suspension. This suspension can last from a few seconds to a few minutes, depending on the size and class of your DB instance. The snapshot creation time varies with the total count and size of your databases. To learn more about the number of databases eligible for a point in time restore (PITR) operation, see [Number of databases eligible for PITR per instance class type](custom-backup.pitr-sqs.md#custom-backup.pitr.sqlserver.eligiblecountperinstance).
Because the snapshot includes the entire storage volume, the size of files, such as temporary files, also affects snapshot creation time. To learn more about creating snapshots, see [Creating a DB snapshot for a Single-AZ DB instance for Amazon RDS](USER_CreateSnapshot.md).
Create an RDS Custom for SQL Server snapshot using the console or the AWS CLI.
## Console
**To create an RDS Custom snapshot**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Databases**.
1. In the list of RDS Custom DB instances, choose the instance for which you want to take a snapshot.
1. For **Actions**, choose **Take snapshot**.
The **Take DB snapshot** window appears.
1. For **Snapshot name**, enter the name of the snapshot.
1. Choose **Take snapshot**.
## AWS CLI
You create a snapshot of an RDS Custom DB instance by using the [create-db-snapshot](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-snapshot.html) AWS CLI command.
Specify the following options:
+ `--db-instance-identifier` – Identifies which RDS Custom DB instance you are going to back up
+ `--db-snapshot-identifier` – Names your RDS Custom snapshot so you can restore from it later
In this example, you create a DB snapshot called *`my-custom-snapshot`* for an RDS Custom DB instance called `my-custom-instance`.
**Example**
For Linux, macOS, or Unix:
```
1. aws rds create-db-snapshot \
2. --db-instance-identifier my-custom-instance \
3. --db-snapshot-identifier my-custom-snapshot
```
For Windows:
```
1. aws rds create-db-snapshot ^
2. --db-instance-identifier my-custom-instance ^
3. --db-snapshot-identifier my-custom-snapshot
```
# Restoring from an RDS Custom for SQL Server DB snapshot
When you restore an RDS Custom for SQL Server DB instance, you provide the name of the DB snapshot and a name for the new instance. You can't restore from a snapshot to an existing RDS Custom DB instance. A new RDS Custom for SQL Server DB instance is created when you restore.
Restoring from a snapshot will restore the storage volume to the point in time at which the snapshot was taken. This will include all the databases and any other files that were present on the `(D:)` volume.
## Console
**To restore an RDS Custom DB instance from a DB snapshot**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Snapshots**.
1. Choose the DB snapshot that you want to restore from.
1. For **Actions**, choose **Restore snapshot**.
1. On the **Restore DB instance** page, for **DB instance identifier**, enter the name for your restored RDS Custom DB instance.
1. Choose **Restore DB instance**.
## AWS CLI
You restore an RDS Custom DB snapshot by using the [ restore-db-instance-from-db-snapshot](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html) AWS CLI command.
If the snapshot you are restoring from is for a private DB instance, make sure to specify both the correct `db-subnet-group-name` and `no-publicly-accessible`. Otherwise, the DB instance defaults to publicly accessible. The following options are required:
+ `db-snapshot-identifier` – Identifies the snapshot from which to restore
+ `db-instance-identifier` – Specifies the name of the RDS Custom DB instance to create from the DB snapshot
+ `custom-iam-instance-profile` – Specifies the instance profile associated with the underlying Amazon EC2 instance of an RDS Custom DB instance.
The following code restores the snapshot named `my-custom-snapshot` for `my-custom-instance`.
**Example**
For Linux, macOS, or Unix:
```
aws rds restore-db-instance-from-db-snapshot \
--db-snapshot-identifier my-custom-snapshot \
--db-instance-identifier my-custom-instance \
--custom-iam-instance-profile AWSRDSCustomInstanceProfileForRdsCustomInstance \
--no-publicly-accessible
```
For Windows:
```
aws rds restore-db-instance-from-db-snapshot ^
--db-snapshot-identifier my-custom-snapshot ^
--db-instance-identifier my-custom-instance ^
--custom-iam-instance-profile AWSRDSCustomInstanceProfileForRdsCustomInstance ^
--no-publicly-accessible
```
# Restoring an RDS Custom for SQL Server instance to a point in time
You can restore a DB instance to a specific point in time (PITR), creating a new DB instance. To support PITR, your DB instances must have backup retention enabled.
The latest restorable time for an RDS Custom for SQL Server DB instance depends on several factors, but is typically within 5 minutes of the current time. To see the latest restorable time for a DB instance, use the AWS CLI [describe-db-instances](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-instances.html) command and look at the value returned in the `LatestRestorableTime` field for the DB instance. To see the latest restorable time for each DB instance in the Amazon RDS console, choose **Automated backups**.
You can restore to any point in time within your backup retention period. To see the earliest restorable time for each DB instance, choose **Automated backups** in the Amazon RDS console.
For general information about PITR, see [Restoring a DB instance to a specified time for Amazon RDS](USER_PIT.md).
**Topics**
+ [
## PITR considerations for RDS Custom for SQL Server
](#custom-backup.pitr.sqlserver)
+ [
## Number of databases eligible for PITR per instance class type
](#custom-backup.pitr.sqlserver.eligiblecountperinstance)
+ [
## Making databases ineligible for PITR
](#custom-backup.pitr.sqlserver.ineligible)
+ [
## Transaction logs in Amazon S3
](#custom-backup.pitr.sqlserver.tlogs)
+ [
## PITR Restore using the AWS Management Console, the AWS CLI, or the RDS API.
](#custom-backup.pitr-sqs-concli)
## PITR considerations for RDS Custom for SQL Server
In RDS Custom for SQL Server, PITR differs in the following important ways from PITR in Amazon RDS:
+ PITR only restores the databases in the DB instance. It doesn't restore the operating system or files on the C: drive.
+ For an RDS Custom for SQL Server DB instance, a database is backed up automatically and is eligible for PITR only under the following conditions:
+ The database is online.
+ Its recovery model is set to `FULL`.
+ It's writable.
+ It has its physical files on the D: drive.
+ It's not listed in the `rds_pitr_blocked_databases` table. For more information, see [Making databases ineligible for PITR](#custom-backup.pitr.sqlserver.ineligible).
+ The databases eligible for PITR are determined by the order of their database ID. RDS Custom for SQL Server allows up to 5,000 databases per DB instance. However, the maximum number of databases restored by a PITR operation for an RDS Custom for SQL Server DB instance is dependent on the instance class type. For more information, see [Number of databases eligible for PITR per instance class type](#custom-backup.pitr.sqlserver.eligiblecountperinstance).
Other databases that aren't part of PITR can be restored from DB snapshots, including the automated snapshot backups used for PITR.
+ Adding a new database, renaming a database, or restoring a database that is eligible for PITR initiates a snapshot of the DB instance.
+ The maximum number of databases eligible for PITR changes when the database instance goes through a scale compute operation, depending on the target instance class type. If the instance is scaled up, allowing more databases on the instance to be eligible for PITR, a new snapshot is taken.
+ Restored databases have the same name as in the source DB instance. You can't specify a different name.
+ `AWSRDSCustomSQLServerIamRolePolicy` requires access to other AWS services. For more information, see [Add an access policy to AWSRDSCustomSQLServerInstanceRole](custom-setup-sqlserver.md#custom-setup-sqlserver.iam.add-policy).
+ Time zone changes aren't supported for RDS Custom for SQL Server. If you change the operating system or DB instance time zone, PITR (and other automation) doesn't work.
## Number of databases eligible for PITR per instance class type
The following table shows the maximum number of databases eligible for PITR based on instance class type.
| Instance class type | Maximum number of PITR eligible databases |
| --- | --- |
| db.\$1.large | 100 |
| db.\$1.xlarge to db.\$1.2xlarge | 150 |
| db.\$1.4xlarge to db.\$1.8xlarge | 300 |
| db.\$1.12xlarge to db.\$1.16xlarge | 600 |
| db.\$1.24xlarge, db.\$132xlarge | 1000 |
`*` *Represents different instance class types.*
The maximum number of databases eligible for PITR on a DB instance depends on the instance class type. The number ranges from 100 on the smallest to 1000 on the largest instance class types supported by RDS Custom for SQL Server. SQL server system databases `(master, model, msdb, tempdb)`, aren't included in this limit. When a DB instance is scaled up or down, depending on the target instance class type, RDS Custom will automatically update the number of database eligible for PITR. RDS Custom for SQL Server will send `RDS-EVENT-0352` when the maximum number of databases eligible for PITR changes on a DB instance. For more information, see [Custom engine version events](USER_Events.Messages.md#USER_Events.Messages.CEV).
**Note**
PITR support for greater than 100 databases is only available on DB instances created after August 26, 2023. For instances created before August 26, 2023, the maximum number of databases eligible for PITR is 100, regardless of the instance class. To enable PITR support for more than 100 databases on DB instances created before August 26, 2023, you can perform the following action:
Upgrade the DB engine version to 15.00.4322.2.v1 or higher
During a PITR operation, RDS Custom will restore all of the databases that were part of PITR on source DB instance at restore time. Once the target DB instance has completed restore operations, if backup retention is enabled, the DB instance will start backing up based on the maximum number of databases eligible for PITR on target DB instance.
For example, if your DB instance runs on a `db.*.xlarge` that has 200 databases:
1. RDS Custom for SQL Server will choose the first 150 databases, ordered by their database ID, for PITR backup.
1. You modify the instance to scale up to db.\$1.4xlarge.
1. Once the scale compute operation is completed, RDS Custom for SQL Server will choose the first 300 databases, ordered by their database ID, for PITR backup. Each one of the 200 databases that satisfy the PITR requirement conditions will now be eligible for PITR.
1. You now modify the instance to scale down back to db.\$1.xlarge.
1. Once the scale compute operation is completed, RDS Custom for SQL Server will again select the first 150 databases, ordered by their database ID, for PITR backup.
## Making databases ineligible for PITR
You can choose to exclude individual databases from PITR. To do this, put their `database_id` values into a `rds_pitr_blocked_databases` table. Use the following SQL script to create the table.
**To create the rds\$1pitr\$1blocked\$1databases table**
+ Run the following SQL script.
```
create table msdb..rds_pitr_blocked_databases
(
database_id INT NOT NULL,
database_name SYSNAME NOT NULL,
db_entry_updated_date datetime NOT NULL DEFAULT GETDATE(),
db_entry_updated_by SYSNAME NOT NULL DEFAULT CURRENT_USER,
PRIMARY KEY (database_id)
);
```
For the list of eligible and ineligible databases, see the `RI.End` file in the `RDSCustomForSQLServer/Instances/DB_instance_resource_ID/TransactionLogMetadata` directory in the Amazon S3 bucket `do-not-delete-rds-custom-$ACCOUNT_ID-$REGION-unique_identifier`. For more information about the `RI.End` file, see [Transaction logs in Amazon S3](#custom-backup.pitr.sqlserver.tlogs).
You can also determine the list of eligible databases for PITR using the following SQL script. Set the `@limit` variable to the maximum number of databases on eligible for PITR for the instance class. For more information, see [Number of databases eligible for PITR per instance class type](#custom-backup.pitr.sqlserver.eligiblecountperinstance).
**To determine the list of eligible databases for PITR on a DB instance class**
+ Run the following SQL script.
```
DECLARE @Limit INT;
SET @Limit = (insert-database-instance-limit-here);
USE msdb;
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'rds_pitr_blocked_databases'))
WITH TABLE0 AS (
SELECT hdrs.database_id as DatabaseId, sdb.name as DatabaseName, 'ALWAYS_ON_NOT_WRITABLE_REPLICA' as Reason, NULL as DatabaseNameOnPitrTable
FROM sys.dm_hadr_database_replica_states hdrs
INNER JOIN sys.databases sdb ON sdb.database_id = hdrs.database_id
WHERE (hdrs.is_local = 1 AND hdrs.is_primary_replica = 0)
OR (sys.fn_hadr_is_primary_replica (sdb.name) = 1 AND DATABASEPROPERTYEX (sdb.name, 'Updateability') = 'READ_ONLY')
),
TABLE1 as (
SELECT dbs.database_id as DatabaseId, sysdbs.name as DatabaseName, 'OPTOUT' as Reason,
CASE WHEN dbs.database_name = sysdbs.name THEN NULL ELSE dbs.database_name END AS DatabaseNameOnPitrTable
FROM msdb.dbo.rds_pitr_blocked_databases dbs
INNER JOIN sys.databases sysdbs ON dbs.database_id = sysdbs.database_id
WHERE sysdbs.database_id > 4
),
TABLE2 as (
SELECT
db.name AS DatabaseName,
db.create_date AS CreateDate,
db.state_desc AS DatabaseState,
db.database_id AS DatabaseId,
rs.database_guid AS DatabaseGuid,
rs.last_log_backup_lsn AS LastLogBackupLSN,
rs.recovery_fork_guid RecoveryForkGuid,
rs.first_recovery_fork_guid AS FirstRecoveryForkGuid,
db.recovery_model_desc AS RecoveryModel,
db.is_auto_close_on AS IsAutoClose,
db.is_read_only as IsReadOnly,
NEWID() as FileName,
CASE WHEN(db.state_desc = 'ONLINE'
AND db.recovery_model_desc != 'SIMPLE'
AND((db.is_auto_close_on = 0 and db.collation_name IS NOT NULL) OR db.is_auto_close_on = 1))
AND db.is_read_only != 1
AND db.user_access = 0
AND db.source_database_id IS NULL
AND db.is_in_standby != 1
THEN 1 ELSE 0 END AS IsPartOfSnapshot,
CASE WHEN db.source_database_id IS NULL THEN 0 ELSE 1 END AS IsDatabaseSnapshot
FROM sys.databases db
INNER JOIN sys.database_recovery_status rs
ON db.database_id = rs.database_id
WHERE DB_NAME(db.database_id) NOT IN('tempdb') AND
db.database_id NOT IN (SELECT DISTINCT DatabaseId FROM TABLE1) AND
db.database_id NOT IN (SELECT DISTINCT DatabaseId FROM TABLE0)
),
TABLE3 as(
Select @Limit+count(DatabaseName) as TotalNumberOfDatabases from TABLE2 where TABLE2.IsPartOfSnapshot=1 and DatabaseName in ('master','model','msdb')
)
SELECT TOP(SELECT TotalNumberOfDatabases from TABLE3) DatabaseName,CreateDate,DatabaseState,DatabaseId from TABLE2 where TABLE2.IsPartOfSnapshot=1
ORDER BY TABLE2.DatabaseID ASC
ELSE
WITH TABLE0 AS (
SELECT hdrs.database_id as DatabaseId, sdb.name as DatabaseName, 'ALWAYS_ON_NOT_WRITABLE_REPLICA' as Reason, NULL as DatabaseNameOnPitrTable
FROM sys.dm_hadr_database_replica_states hdrs
INNER JOIN sys.databases sdb ON sdb.database_id = hdrs.database_id
WHERE (hdrs.is_local = 1 AND hdrs.is_primary_replica = 0)
OR (sys.fn_hadr_is_primary_replica (sdb.name) = 1 AND DATABASEPROPERTYEX (sdb.name, 'Updateability') = 'READ_ONLY')
),
TABLE1 as (
SELECT
db.name AS DatabaseName,
db.create_date AS CreateDate,
db.state_desc AS DatabaseState,
db.database_id AS DatabaseId,
rs.database_guid AS DatabaseGuid,
rs.last_log_backup_lsn AS LastLogBackupLSN,
rs.recovery_fork_guid RecoveryForkGuid,
rs.first_recovery_fork_guid AS FirstRecoveryForkGuid,
db.recovery_model_desc AS RecoveryModel,
db.is_auto_close_on AS IsAutoClose,
db.is_read_only as IsReadOnly,
NEWID() as FileName,
CASE WHEN(db.state_desc = 'ONLINE'
AND db.recovery_model_desc != 'SIMPLE'
AND((db.is_auto_close_on = 0 and db.collation_name IS NOT NULL) OR db.is_auto_close_on = 1))
AND db.is_read_only != 1
AND db.user_access = 0
AND db.source_database_id IS NULL
AND db.is_in_standby != 1
THEN 1 ELSE 0 END AS IsPartOfSnapshot,
CASE WHEN db.source_database_id IS NULL THEN 0 ELSE 1 END AS IsDatabaseSnapshot
FROM sys.databases db
INNER JOIN sys.database_recovery_status rs
ON db.database_id = rs.database_id
WHERE DB_NAME(db.database_id) NOT IN('tempdb') AND
db.database_id NOT IN (SELECT DISTINCT DatabaseId FROM TABLE0)
),
TABLE2 as(
SELECT @Limit+count(DatabaseName) as TotalNumberOfDatabases from TABLE1 where TABLE1.IsPartOfSnapshot=1 and DatabaseName in ('master','model','msdb')
)
select top(select TotalNumberOfDatabases from TABLE2) DatabaseName,CreateDate,DatabaseState,DatabaseId from TABLE1 where TABLE1.IsPartOfSnapshot=1
ORDER BY TABLE1.DatabaseID ASC
```
**Note**
The databases that are only symbolic links are also excluded from databases eligible for PITR operations. The above query doesn’t filter based on this criteria.
## Transaction logs in Amazon S3
The backup retention period determines whether transaction logs for RDS Custom for SQL Server DB instances are automatically extracted and uploaded to Amazon S3. A nonzero value means that automatic backups are created, and that the RDS Custom agent uploads the transaction logs to S3 every 5 minutes.
Transaction log files on S3 are encrypted at rest using the AWS KMS key that you provided when you created your DB instance. For more information, see [Protecting data using server-side encryption](https://docs.aws.amazon.com/AmazonS3/latest/userguide/serv-side-encryption.html) in the *Amazon Simple Storage Service User Guide*.
The transaction logs for each database are uploaded to an S3 bucket named `do-not-delete-rds-custom-$ACCOUNT_ID-$REGION-unique_identifier`. The `RDSCustomForSQLServer/Instances/DB_instance_resource_ID` directory in the S3 bucket contains two subdirectories:
+ `TransactionLogs` – Contains the transaction logs for each database and their respective metadata.
The transaction log file name follows the pattern `yyyyMMddHHmm.database_id.timestamp`, for example:
```
202110202230.11.1634769287
```
The same file name with the suffix `_metadata` contains information about the transaction log such as log sequence numbers, database name, and `RdsChunkCount`. `RdsChunkCount` determines how many physical files represent a single transaction log file. You might see files with suffixes `_0001`, `_0002`, and so on, which mean the physical chunks of a transaction log file. If you want to use a chunked transaction log file, make sure to merge the chunks after downloading them.
Consider a scenario where you have the following files:
+ `202110202230.11.1634769287`
+ ` 202110202230.11.1634769287_0001`
+ ` 202110202230.11.1634769287_0002 `
+ ` 202110202230.11.1634769287_metadata`
The `RdsChunkCount` is `3`. The order for merging the files is the following: `202110202230.11.1634769287`, ` 202110202230.11.1634769287_0001`, `202110202230.11.1634769287_0002`.
+ `TransactionLogMetadata` – Contains metadata information about each iteration of transaction log extraction.
The `RI.End` file contains information for all databases that had their transaction logs extracted, and all databases that exist but didn't have their transaction logs extracted. The `RI.End` file name follows the pattern `yyyyMMddHHmm.RI.End.timestamp`, for example:
```
202110202230.RI.End.1634769281
```
## PITR Restore using the AWS Management Console, the AWS CLI, or the RDS API.
You can restore an RDS Custom for SQL Server DB instance to a point in time using the AWS Management Console, the AWS CLI, or the RDS API.
### Console
**To restore an RDS Custom DB instance to a specified time**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Automated backups**.
1. Choose the RDS Custom DB instance that you want to restore.
1. For **Actions**, choose **Restore to point in time**.
The **Restore to point in time** window appears.
1. Choose **Latest restorable time** to restore to the latest possible time, or choose **Custom** to choose a time.
If you chose **Custom**, enter the date and time to which you want to restore the instance.
Times are shown in your local time zone, which is indicated by an offset from Coordinated Universal Time (UTC). For example, UTC-5 is Eastern Standard Time/Central Daylight Time.
1. For **DB instance identifier**, enter the name of the target restored RDS Custom DB instance. The name must be unique.
1. Choose other options as needed, such as DB instance class.
1. Choose **Restore to point in time**.
### AWS CLI
You restore a DB instance to a specified time by using the [ restore-db-instance-to-point-in-time](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html) AWS CLI command to create a new RDS Custom DB instance.
Use one of the following options to specify the backup to restore from:
+ `--source-db-instance-identifier mysourcedbinstance`
+ `--source-dbi-resource-id dbinstanceresourceID`
+ `--source-db-instance-automated-backups-arn backupARN`
The `custom-iam-instance-profile` option is required.
The following example restores `my-custom-db-instance` to a new DB instance named `my-restored-custom-db-instance`, as of the specified time.
**Example**
For Linux, macOS, or Unix:
```
1. aws rds restore-db-instance-to-point-in-time \
2. --source-db-instance-identifier my-custom-db-instance\
3. --target-db-instance-identifier my-restored-custom-db-instance \
4. --custom-iam-instance-profile AWSRDSCustomInstanceProfileForRdsCustomInstance \
5. --restore-time 2022-10-14T23:45:00.000Z
```
For Windows:
```
1. aws rds restore-db-instance-to-point-in-time ^
2. --source-db-instance-identifier my-custom-db-instance ^
3. --target-db-instance-identifier my-restored-custom-db-instance ^
4. --custom-iam-instance-profile AWSRDSCustomInstanceProfileForRdsCustomInstance ^
5. --restore-time 2022-10-14T23:45:00.000Z
```
# Deleting an RDS Custom for SQL Server snapshot
You can delete DB snapshots managed by RDS Custom for SQL Server when you no longer need them. The deletion procedure is the same for both Amazon RDS and RDS Custom DB instances.
The Amazon EBS snapshots for the binary and root volumes remain in your account for a longer time because they might be linked to some instances running in your account or to other RDS Custom for SQL Server snapshots. These EBS snapshots are automatically deleted after they're no longer related to any existing RDS Custom for SQL Server resources (DB instances or backups).
## Console
**To delete a snapshot of an RDS Custom DB instance**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Snapshots**.
1. Choose the DB snapshot that you want to delete.
1. For **Actions**, choose **Delete snapshot**.
1. Choose **Delete** on the confirmation page.
## AWS CLI
To delete an RDS Custom snapshot, use the AWS CLI command [delete-db-snapshot](https://docs.aws.amazon.com/cli/latest/reference/rds/delete-db-snapshot.html).
The following option is required:
+ `--db-snapshot-identifier` – The snapshot to be deleted
The following example deletes the `my-custom-snapshot` DB snapshot.
**Example**
For Linux, macOS, or Unix:
```
1. aws rds delete-db-snapshot \
2. --db-snapshot-identifier my-custom-snapshot
```
For Windows:
```
1. aws rds delete-db-snapshot ^
2. --db-snapshot-identifier my-custom-snapshot
```
# Deleting RDS Custom for SQL Server automated backups
You can delete retained automated backups for RDS Custom for SQL Server when they are no longer needed. The procedure is the same as the procedure for deleting Amazon RDS backups.
## Console
**To delete a retained automated backup**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Automated backups**.
1. Choose **Retained**.
1. Choose the retained automated backup that you want to delete.
1. For **Actions**, choose **Delete**.
1. On the confirmation page, enter **delete me** and choose **Delete**.
## AWS CLI
You can delete a retained automated backup by using the AWS CLI command [delete-db-instance-automated-backup](https://docs.aws.amazon.com/cli/latest/reference/rds/delete-db-instance-automated-backup.html).
The following option is used to delete a retained automated backup:
+ `--dbi-resource-id` – The resource identifier for the source RDS Custom DB instance.
You can find the resource identifier for the source DB instance of a retained automated backup by using the AWS CLI command [describe-db-instance-automated-backups](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-instance-automated-backups.html).
The following example deletes the retained automated backup with source DB instance resource identifier `custom-db-123ABCEXAMPLE`.
**Example**
For Linux, macOS, or Unix:
```
1. aws rds delete-db-instance-automated-backup \
2. --dbi-resource-id custom-db-123ABCEXAMPLE
```
For Windows:
```
1. aws rds delete-db-instance-automated-backup ^
2. --dbi-resource-id custom-db-123ABCEXAMPLE
```
# Copying an Amazon RDS Custom for SQL Server DB snapshot
With RDS Custom for SQL Server, you can copy automated backups and manual DB snapshots. After copying a snapshot, the copy you create is a manual snapshot. You can make multiple copies of an automated backup or manual snapshot but each copy must have a unique identifier.
You can only copy a snapshot within the same AWS account across different AWS Regions where RDS Custom for SQL Server is available. The following operations are currently not supported:
+ Copying DB snapshots within the same AWS Region.
+ Copying DB snapshots across AWS accounts.
RDS Custom for SQL Server supports incremental snapshot copying. For more information, see [Considerations for incremental snapshot copying](USER_CopySnapshot.md#USER_CopySnapshot.Incremental).
**Topics**
+ [
## Limitations
](#custom-copying-snapshot-sqlserver.Limitations)
+ [
## Handling encryption
](#custom-copying-snapshot-sqlserver.Encryption)
+ [
## Cross-Region copying
](#custom-copying-snapshot-sqlserver.XRCopy)
+ [
## Snapshots of DB instances created with Custom Engine Versions (CEV)
](#custom-copying-snapshot-sqlserver.CEVSnap)
+ [
## Grant required permissions to your IAM principal
](#custom-copying-snapshot-sqlserver.GrantPermIAM)
+ [
## Copying a DB snapshot
](#custom-copying-snapshot-sqlserver.CopyingDBSnapshot)
## Limitations
The following limitations apply to copying a DB snapshot for RDS Custom for SQL Server:
+ If you delete a source snapshot before the target snapshot becomes available, the snapshot copy might fail. Verify that the target snapshot has a status of `AVAILABLE` before you delete the source snapshot.
+ You cannot specify an option group name or copy an options group in your DB snapshot copy request.
+ If you delete any dependent AWS resources of the source DB snapshot before or during the copy process, your copy snapshot request could fail asynchronously.
+ If you delete the Service Master Key (SMK) backup file for your source DB instance stored in the RDS Custom managed S3 bucket in your account, the DB snapshot copy succeeds asynchronously. However, SQL Server features dependent on SMK such as TDE enabled databases run into issues. For more information, see [Troubleshooting PENDING\$1RECOVERY state for TDE enabled databases in RDS Custom for SQL Server](custom-troubleshooting-sqlserver.md#custom-troubleshooting-sqlserver.pending_recovery).
+ Copying DB snapshots within the same AWS Region is currently not supported.
+ Copying DB snapshots across AWS accounts is currently not supported.
The limitations of copying a DB snapshot for Amazon RDS also apply to RDS Custom for SQL Server. For more information, see [Limitations](USER_CopySnapshot.md#USER_CopySnapshot.Limitations).
## Handling encryption
All RDS Custom for SQL Server DB instances and DB snapshots are encrypted with KMS keys. You can only copy an encrypted snapshot to an encrypted snapshot, therefore you must specify a KMS key valid in the destination AWS Region for your DB snapshot copy request.
The source snapshot remains encrypted throughout the copy process. Amazon RDS uses envelope encryption to protect data during the copy operation with the specified destination AWS Region KMS key. For more information, see [Envelope encryption](https://docs.aws.amazon.com/kms/latest/developerguide/concepts.html#enveloping) in the *AWS Key Management Service Developer Guide*.
## Cross-Region copying
You can copy DB snapshots across AWS Regions. However, there are certain constraints and considerations for cross-Region snapshot copying.
### Authorizing RDS to communicate across AWS Regions for snapshot copying
After a cross-Region DB snapshot copy request is processed successfully, RDS starts the copy. An authorization request for RDS to access the source snapshot is created. This authorization request links the source DB snapshot to the target DB snapshot. This allows RDS to copy only to the specified target snapshot.
RDS verifies the authorization by using the `rds:CrossRegionCommunication` permission in the service-linked IAM role. If the copy is authorized, RDS can communicate with the source Region and complete the copy operation.
RDS doesn’t have access to DB snapshots that weren't authorized previously by a CopyDBSnapshot request. The authorization is revoked after the copy completes.
RDS uses the service-linked role to verify the authorization in the source Region. The copy fails if you delete the service-linked role during the copy process.
For more information, see [Using service-linked roles](https://docs.aws.amazon.com/IAM/latest/UserGuide/using-service-linked-roles.html) in the *AWS Identity and Access Management User Guide*.
### Using AWS Security Token Service credentials
Session tokens from the global AWS Security Token Service (AWS STS) endpoint are valid only in AWS Regions that are enabled by default (commercial Regions). If you use credentials from the `assumeRole` API operation in AWS STS, use the regional endpoint if the source Region is an opt-in Region. Otherwise, the request fails. Your credentials must be valid in both Regions, which is true for opt-in Regions only when you use the regional AWS STS endpoint.
To use the global endpoint, make sure that it's enabled for both Regions in the operations. Set the global endpoint to `Valid` in all AWS Regions in the AWS STS account settings.
For more information, see [Managing AWS STS in an AWS Region](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_temp_enable-regions.html) in the *AWS Identity and Access Management User Guide*.
## Snapshots of DB instances created with Custom Engine Versions (CEV)
For a DB snapshot of a DB instance using a [Custom Engine Version (CEV)](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-cev-sqlserver.html), RDS associates the CEV with the DB snapshot. To copy a source DB snapshot associated with a CEV across AWS Regions, RDS copies the CEV along with the source DB snapshot to the destination region.
If you are copying multiple DB snapshots associated with the same CEV to the same destination region, the first copy request copies the associated CEV. The copy process of the following requests finds the initially copied CEV and associates it with the following DB snapshot copies. The existing CEV copy must be in `AVAILABLE` state to be associated with the DB snapshot copies.
To copy a DB snapshot associated with a CEV, the requester's IAM policy must have the permissions to authorize both the DB snapshot copying and the associated CEV copying. The following permissions are needed in your requester's IAM policy to allow the associated CEV copying:
+ `rds:CopyCustomDBEngineVersion` ‐ Your requester IAM principal needs to have the permission to copy the source CEV to the target region along with the source DB snapshot. The snapshot copy request fails due to authorization errors if your requester IAM principal is not authorized to copy the source CEV.
+ `ec2:CreateTags` ‐ The underlying EC2 AMI of the source CEV is copied to the target region as a part of the CEV copy. RDS Custom attempts to tag the AMI with the `AWSRDSCustom` tag before copying the AMI. Make sure your requester IAM principal has the permission to create the tag against the AMI underlying the source CEV in the source region.
For more information about CEV copying permissions, see [Grant required permissions to your IAM principal](#custom-copying-snapshot-sqlserver.GrantPermIAM).
## Grant required permissions to your IAM principal
Make sure that you have sufficient access to copy a RDS Custom for SQL Server DB snapshot. The IAM role or user (referred to as the IAM principal) for copying a DB snapshot using the console or CLI must have either of the following policies for successful DB instance creation:
+ The `AdministratorAccess` policy or,
+ The `AmazonRDSFullAccess` policy with the following additional permissions:
```
s3:CreateBucket
s3:GetBucketPolicy
s3:PutBucketPolicy
kms:CreateGrant
kms:DescribeKey
ec2:CreateTags
```
RDS Custom uses these permissions during snapshot copying across AWS Regions. These permissions configure resources in your account that are required for RDS Custom operations. For more information about the `kms:CreateGrant` permission, see [AWS KMS key management](Overview.Encryption.Keys.md).
The following sample JSON policy grants the required permissions in addition to `AmazonRDSFullAccess` policy.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Sid": "CreateS3BucketAndReadWriteBucketPolicy",
"Effect": "Allow",
"Action": [
"s3:CreateBucket",
"s3:PutBucketPolicy",
"s3:GetBucketPolicy"
],
"Resource": "arn:aws:s3:::do-not-delete-rds-custom-*"
},
{
"Sid": "CreateKmsGrant",
"Effect": "Allow",
"Action": [
"kms:CreateGrant",
"kms:DescribeKey"
],
"Resource": "*"
},
{
"Sid": "CreateEc2Tags",
"Effect": "Allow",
"Action": [
"ec2:CreateTags"
],
"Resource": "*"
}
]
}
```
------
**Note**
Make sure that the listed permissions aren't restricted by service control policies (SCPs), permission boundaries, or session policies associated with the IAM principal.
If you use conditions with context keys in the requester's IAM policy, certain conditions can cause the request to fail. For more information about common pitfalls due to IAM policy conditions, see [Requesting a cross-Region DB snapshot copy](USER_CopySnapshot.md#USER_CopySnapshot.AcrossRegions.Policy).
## Copying a DB snapshot
Use the following procedures to copy a DB snapshot. For each AWS account, you can copy up to 20 DB snapshots at a time from one AWS Region to another. If you copy a DB snapshot to another AWS Region, you create a manual DB snapshot that is retained in that AWS Region. Copying a DB snapshot out of the source AWS Region incurs Amazon RDS data transfer charges. For more information about data transfer pricing, see [Amazon RDS pricing](https://aws.amazon.com/rds/pricing/).
After the DB snapshot copy has been created in the new AWS Region, the DB snapshot copy behaves the same as all other DB snapshots in that AWS Region.
You can copy a DB snapshot using the AWS Management Console, AWS CLI, or the Amazon RDS API.
------
#### [ Console ]
The following procedure copies a RDS Custom for SQL Server DB snapshot by using the AWS Management Console.
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Snapshots**.
1. Select the RDS Custom for SQL Server DB snapshot that you want to copy.
1. In the **Actions** dropwdown , choose **Copy snapshot**.
![\[The Copy snapshot page in the Amazon RDS console. The settings are loaded in the page.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/XRSC-Snapshot-Copy.png)
1. To copy the DB snapshot to a different AWS Region, set **Destination Region** to the required value.
**Note**
The destination AWS Region must have the same database engine version available as the source AWS Region.
1. For **New DB snapshot identifier**, enter a unique name for the DB snapshot. You can make multiple copies of an automated backup or manual snapshot but each copy must have a unique identifier.
1. (Optional) Select **Copy Tags** to copy tags and values from the snapshot to the copy of the snapshot.
1. For **Encryption**, specify the KMS key identifier to use to encrypt the DB snapshot copy.
**Note**
RDS Custom for SQL Server encrypts all DB snapshots. You can't create an unencrypted DB snapshot.
1. Choose **Copy snapshot**.
RDS Custom for SQL Server creates a DB snapshot copy of your DB instance in the AWS Region of your selection.
------
#### [ AWS CLI ]
You can copy a RDS Custom for SQL Server DB snapshot by using the AWS CLI command [https://docs.aws.amazon.com/cli/latest/reference/rds/copy-db-snapshot.html](https://docs.aws.amazon.com/cli/latest/reference/rds/copy-db-snapshot.html). If you are copying the snapshot to a new AWS Region, run the command in the new AWS Region. The following options are used to copy a DB snapshot. Not all options are required for all scenarios.
+ `--source-db-snapshot-identifier` ‐ The identifier for the source DB snapshot.
+ If the source snapshot is in a different AWS Region than the copy, specify a valid DB snapshot ARN. For example, `arn:aws:rds:us-west-2:123456789012:snapshot:instance1-snapshot-12345678`.
+ `--target-db-snapshot-identifier` ‐ The identifier for the new copy of the DB snapshot.
+ `--kms-key-id` ‐The KMS key identifier for an encrypted DB snapshot. The KMS key identifier is the Amazon Resource Name (ARN), key identifier, or key alias for the KMS key.
+ If you copy an encrypted snapshot to a different AWS Region, then you must specify a KMS key for the destination AWS Region. KMS keys are specific to the AWS Region that they are created in and you cannot use encryption keys from one AWS Region in another AWS Region unless a multi-Region key is used. For more information on multi-Region KMS keys, see [Using multi-Region keys in AWS KMS](https://docs.aws.amazon.com/kms/latest/developerguide/multi-region-keys-overview.html).
+ `--copy-tags` ‐ Include the tags and values from the source snapshot to the copy of the snapshot.
The following options are not supported for copying an RDS Custom for SQL Server DB snapshot:
+ `--copy-option-group `
+ `--option-group-name`
+ `--pre-signed-url`
+ `--target-custom-availability-zone`
The following code example copies an encrypted DB snapshot from the US West (Oregon) Region to the US East (N. Virginia) Region. Run the command in the destination (us-east-1) Region.
For Linux, macOS, or Unix:
```
aws rds copy-db-snapshot \
--region us-east-1 \
--source-db-snapshot-identifier arn:aws:rds:us-west-2:123456789012:snapshot:instance1-snapshot-12345678 \
--target-db-snapshot-identifier mydbsnapshotcopy \
--kms-key-id a1b2c3d4-1234-5678-wxyz-a1b2c3d4d5e6
```
For Windows:
```
aws rds copy-db-snapshot ^
--region us-east-1 ^
--source-db-snapshot-identifier arn:aws:rds:us-west-2:123456789012:snapshot:instance1-snapshot-12345678 ^
--target-db-snapshot-identifier mydbsnapshotcopy ^
--kms-key-id a1b2c3d4-1234-5678-wxyz-a1b2c3d4d5e6
```
------
#### [ RDS API ]
You can copy a RDS Custom for SQL Server DB snapshot by using the Amazon RDS API operation [CopyDBSnapshot](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CopyDBSnapshot.html). If you are copying the snapshot to a new AWS Region, perform the action in the new AWS Region. The following parameters are used to copy a DB snapshot. Not all parameters are required:
+ `SourceDBSnapshotIdentifier` ‐ The identifier for the source DB snapshot.
+ If the source snapshot is in a different AWS Region than the copy, specify a valid DB snapshot ARN. For example, `arn:aws:rds:us-west-2:123456789012:snapshot:instance1-snapshot-12345678`.
+ `TargetDBSnapshotIdentifier` ‐ The identifier for the new copy of the DB snapshot.
+ `KmsKeyId` ‐ The KMS key identifier for an encrypted DB snapshot. The KMS key identifier is the Amazon Resource Name (ARN), key identifier, or key alias for the KMS key.
+ If you copy an encrypted snapshot to a different AWS Region, then you must specify a KMS key for the destination AWS Region. KMS keys are specific to the AWS Region that they are created in and you cannot use encryption keys from one AWS Region in another AWS Region unless a multi-Region key is used. For more information on multi-Region KMS keys, see [Using multi-Region keys in AWS KMS](https://docs.aws.amazon.com/kms/latest/developerguide/multi-region-keys-overview.html).
+ `CopyTags` ‐ Set this parameter to `true` to copy tags and values from the source snapshot to the copy of the snapshot. The default is `false`.
The following options are not supported copying a RDS Custom for SQL Server DB snapshot:
+ `CopyOptionGroup`
+ `OptionGroupName`
+ `PreSignedUrl`
+ `TargetCustomAvailabilityZone`
The following code creates a copy of a snapshot, with the new name `mydbsnapshotcopy`, in the US East (N. Virginia) Region.
```
https://rds.us-east-1.amazonaws.com/
?Action=CopyDBSnapshot
&KmsKeyId=a1b2c3d4-1234-5678-wxyz-a1b2c3d4d5e6
&SourceDBSnapshotIdentifier=arn%3Aaws%3Ards%3Aus-west-2%3A123456789012%3Asnapshot%3Ainstance1-snapshot-12345678
&TargetDBSnapshotIdentifier=mydbsnapshotcopy
&Version=2014-10-31
&X-Amz-Algorithm=AWS4-HMAC-SHA256
&X-Amz-Credential=AKIADQKE4SARGYLE/20161117/us-east-1/rds/aws4_request
&X-Amz-Date=20161117T221704Z
&X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
&X-Amz-Signature=da4f2da66739d2e722c85fcfd225dc27bba7e2b8dbea8d8612434378e52adccf
```
------
# Migrating an on-premises database to Amazon RDS Custom for SQL Server
You can use the following process to migrate an on-premises Microsoft SQL Server database to Amazon RDS Custom for SQL Server using native backup and restore:
1. Take a full backup of the database on the on-premises DB instance.
1. Upload the backup file to Amazon S3.
1. Download the backup file from S3 to your RDS Custom for SQL Server DB instance.
1. Restore a database using the downloaded backup file on the RDS Custom for SQL Server DB instance.
This process explains the migration of a database from on-premises to RDS Custom for SQL Server, using native full backup and restore. To reduce the cutover time during the migration process, you might also consider using differential or log backups.
For general information about native backup and restore for RDS for SQL Server, see [Importing and exporting SQL Server databases using native backup and restore](SQLServer.Procedural.Importing.md).
**Topics**
+ [
## Prerequisites
](#custom-migrating.prereqs)
+ [
## Backing up the on-premises database
](#custom-migrating.backup)
+ [
## Uploading the backup file to Amazon S3
](#custom-migrating.upload)
+ [
## Downloading the backup file from Amazon S3
](#custom-migrating.upload)
+ [
## Restoring the backup file to the RDS Custom for SQL Server DB instance
](#custom-migrating.restore)
## Prerequisites
Perform the following tasks before migrating the database:
1. Configure Remote Desktop Connection (RDP) for your RDS Custom for SQL Server DB instance. For more information, see [Connecting to your RDS Custom DB instance using RDP](custom-creating-sqlserver.rdp.md).
1. Configure access to Amazon S3 so you can upload and download the database backup file.
## Backing up the on-premises database
You use SQL Server native backup to take a full backup of the database on the on-premises DB instance.
The following example shows a backup of a database called `mydatabase`, with the `COMPRESSION` option specified to reduce the backup file size.
**To back up the on-premises database**
1. Using SQL Server Management Studio (SSMS), connect to the on-premises SQL Server instance.
1. Run the following T-SQL command.
```
backup database mydatabase to
disk ='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\mydb-full-compressed.bak'
with compression;
```
## Uploading the backup file to Amazon S3
You use the AWS Management Console to upload the backup file `mydb-full-compressed.bak` to Amazon S3.
**To upload the backup file to S3**
1. Sign in to the AWS Management Console and open the Amazon S3 console at [https://console.aws.amazon.com/s3/](https://console.aws.amazon.com/s3/).
1. For **Buckets**, choose the name of the bucket to which you want to upload your backup file.
1. Choose **Upload**.
1. In the **Upload** window, do one of the following:
+ Drag and drop `mydb-full-compressed.bak` to the **Upload** window.
+ Choose **Add file**, choose `mydb-full-compressed.bak`, and then choose **Open**.
Amazon S3 uploads your backup file as an S3 object. When the upload completes, you can see a success message on the **Upload: status** page.
## Downloading the backup file from Amazon S3
You use the console to download the backup file from S3 to the RDS Custom for SQL Server DB instance.
**To download the backup file from S3**
1. Using RDP, connect to your RDS Custom for SQL Server DB instance.
1. Sign in to the AWS Management Console and open the Amazon S3 console at [https://console.aws.amazon.com/s3/](https://console.aws.amazon.com/s3/).
1. In the **Buckets** list, choose the name of the bucket that contains your backup file.
1. Choose the backup file `mydb-full-compressed.bak`.
1. For **Actions**, choose **Download as**.
1. Open the context (right-click) menu for the link provided, then choose **Save As**.
1. Save `mydb-full-compressed.bak` to the `D:\rdsdbdata\BACKUP` directory.
## Restoring the backup file to the RDS Custom for SQL Server DB instance
You use SQL Server native restore to restore the backup file to your RDS Custom for SQL Server DB instance.
In this example, the `MOVE` option is specified because the data and log file directories are different from the on-premises DB instance.
**To restore the backup file**
1. Using SSMS, connect to your RDS Custom for SQL Server DB instance.
1. Run the following T-SQL command.
```
restore database mydatabase from disk='D:\rdsdbdata\BACKUP\mydb-full-compressed.bak'
with move 'mydatabase' to 'D:\rdsdbdata\DATA\mydatabase.mdf',
move 'mydatabase_log' to 'D:\rdsdbdata\DATA\mydatabase_log.ldf';
```
# RDS Custom for SQL Server Operating system updates
RDS Custom for SQL Server provides the following methods to apply operating system updates to your RDS Provided Engine Version (RPEV) instances:
+ *system-update maintenance actions*
+ *database minor version upgrades*
+ DB minor engine version upgrades using RPEV include up to date Operating System updates. This approach is particularly useful if you want to combine OS updates with SQL Server minor version upgrades. For more information, see [Upgrading an Amazon RDS Custom for SQL Server DB instance](custom-upgrading-sqlserver.md).
## Scenarios for Operating system update
There are two ways to ways to manage Operating system updates for your RDS Custom for SQL Server instances:
+ For Single-AZ instances, the instance is unavailable during the Operating system update.
+ For Multi-AZ deployments, RDS applies operating system updates in the following manner:
+ First, RDS performs an Operating system update on the standby instance.
+ RDS fails over to the upgraded standby DB instance, making it the new primary DB instance.
+ Lastly, RDS performs an Operating system update on the new standby DB instance.
The downtime for Multi-AZ deployments is the time it takes for the failover.
## Applying Operating system updates using system-update maintenance actions
To apply Operating system updates to your Amazon RDS RPEV instances, you can use the AWS Management Console, AWS CLI, or RDS API. For more information, see [Operating system updates for RDS DB instances](USER_UpgradeDBInstance.Maintenance.md#OS_Updates).
**Example**
For Linux, macOS, or Unix:
**Step 1: Check for available updates**
Use the `describe-pending-maintenance-actions` command to see if OS updates are available for your instances:
```
aws rds describe-pending-maintenance-actions
```
Example response:
```
{
"PendingMaintenanceActions": [
{
"ResourceIdentifier": "arn:aws:rds:us-east-1:111122223333:db:my-sqlserver-instance",
"PendingMaintenanceActionDetails": [
{
"Action": "system-update",
"Description": "New Operating System update is available"
}
]
}
]
}
```
An action type of `system-update` indicates that an OS update is available for your instance.
**Step 2: Apply the OS update**
Use the `apply-pending-maintenance-action` command to schedule the update:
```
aws rds apply-pending-maintenance-action \
--resource-identifier arn:aws:rds:us-east-1:111122223333:db:my-sqlserver-instance \
--apply-action system-update \
--opt-in-type immediate
```
The `opt-in-type` input has the following options:
+ `immediate`: Apply the update right away
+ `next-maintenance`: Apply the update during the next scheduled maintenance window
+ `undo-opt-in`: Cancel a previously scheduled update
Example response:
```
{
"ResourcePendingMaintenanceActions": {
"ResourceIdentifier": "arn:aws:rds:us-east-1:111122223333:db:my-sqlserver-instance",
"PendingMaintenanceActionDetails": [
{
"Action": "system-update",
"AutoAppliedAfterDate": "2024-04-10T20:41:01.695000+00:00",
"ForcedApplyDate": "2024-04-10T20:41:01.694000+00:00",
"CurrentApplyDate": "2024-04-10T20:41:01.695000+00:00",
"Description": "New Operating System update is available"
}
]
}
}
```
## OS update notifications
To be notified when a new, optional operating system patch becomes available, you can subscribe to [RDS-EVENT-0230](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Events.Messages.html#RDS-EVENT-0230) in the security patching event category. For information about subscribing to RDS events, see [Subscribing to Amazon RDS event notification](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Events.Subscribing.html).
## Considerations
The following consideations and limitations apply to OS updates:
+ Any operating system customizations made to the C:\$1 drive are not preserved during Operating system updates.
+ We recommend taking a manual snapshot before applying updates.
# Upgrading an Amazon RDS Custom for SQL Server DB instance
You can upgrade an Amazon RDS Custom for SQL Server DB instance by modifying it to use a new DB engine version. For general information about upgrading DB instances, see [Upgrading a DB instance engine version](USER_UpgradeDBInstance.Upgrading.md).
**Topics**
+ [
## Overview of upgrades in RDS Custom for SQL Server
](#custom-upgrading-sqlserver.Overview)
+ [
## Upgrading major and minor engine version
](#custom-upgrading-sqlserver.Upgrade)
+ [
## Database compatibility level
](#custom-upgrading-sqlserver.Major.Compatibility)
## Overview of upgrades in RDS Custom for SQL Server
Amazon RDS Custom for SQL Server supports major and minor version upgrades. Minor version upgrades can include security patches, bug fixes, and engine improvements. Microsoft releases these updates as cumulative updates (CUs). Major version upgrades introduce new features and engine changes between versions, like upgrading from SQL Server 2019 to 2022. You can apply both upgrades immediately or during scheduled maintenance windows. To prevent potential backward compatibility issues, we recommend testing your applications in a non-production environment before upgrading.
RDS Custom for SQL Server allows you to upgrade an RDS Provided Engine Version (RPEV) or a Custom Engine Version (CEV).
+ RDS-provided engine versions (RPEV) contain up-to-date operating system (OS) patches and SQL Server cumulative updates (CU).
+ For a custom engine version (CEV), you must follow a two-step process. First, create a new CEV with your target SQL Server version, see [Preparing to create a CEV for RDS Custom for SQL Server](custom-cev-sqlserver.preparing.md). This target version must be equal to or newer than your current version. Once the new CEV is created, modify your database instance to use this new version. For more information, see [ Performing a minor version upgrade for Amazon RDS Custom for SQL Server CEV with Multi-AZ](https://aws.amazon.com/blogs/database/performing-a-minor-version-upgrade-for-amazon-rds-custom-for-sql-server-cev-with-multi-az/).
Do not apply SQL Server cumulative updates in-place to your running RDS Custom instance. Once you create a CEV with a specific SQL Server version (for example, SQL Server 2022 CU16), applying a newer cumulative update directly to the instance takes it out of the support perimeter and reports error `SP-S3006`. To patch an existing SQL Server instance using a CEV, create a new CEV that includes the desired cumulative update, then modify your existing instance to switch to the new CEV.
If you upgrade an RDS Custom for SQL Server DB instance in a Multi-AZ deployment, RDS Custom for SQL Server performs rolling upgrades for your instance. This approach minimizes downtime by upgrading one instance at a time. RDS performs the following actions to perform rolling upgrades:
1. Upgrade the standby DB instance.
1. Failover to the upgraded standby DB instance, making it the new primary DB instance.
1. Upgrade the new standby DB instance.
The DB instance downtime for Multi-AZ deployments is the time it takes for the failover.
The following limitations apply when upgrading an RDS Custom for SQL Server DB instance:
+ Custom DB option and parameter groups aren't supported.
+ Any additional storage volumes that you attach to your RDS Custom for SQL Server DB instance are not attached after the upgrade.
+ For CEVs, in-place application of SQL Server cumulative updates is not supported and results in the instance being taken out of the support perimeter.
## Upgrading major and minor engine version
Both major and minor engine version upgrades are irreversible and must always be done to a newer version. To identify available target versions, use the AWS Management Console and choose from the available versions when modifying your DB instance. Alternatively, use the [https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-engine-versions.html](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-engine-versions.html) CLI command or [DescribeDBEngineVersions](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeDBEngineVersions.html) RDS API command.
For Linux, macOS, or Unix:
```
aws rds describe-db-engine-versions \
--engine custom-sqlserver-se \
--engine-version 15.00.4322.2.v1 \
--query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" \
--output table
```
For Windows:
```
aws rds describe-db-engine-versions ^
--engine custom-sqlserver-se ^
--engine-version 15.00.4322.2.v1 ^
--query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" ^
--output table
```
The output shows the available target engine versions:
```
--------------------------
|DescribeDBEngineVersions|
+------------------------+
| EngineVersion |
+------------------------+
| 15.00.4410.1.v1 |
| 15.00.4415.2.v1 |
| 15.00.4430.1.v1 |
| 16.00.4165.4.v1 |
| 16.00.4175.1.v1 |
| 16.00.4185.3.v1 |
+------------------------+
```
After identifying your target version, use the AWS Management Console and follow the instructions in [Modifying an RDS Custom for SQL Server DB instance](custom-managing.modify-sqlserver.md). Alternatively, use [https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) CLI command or [ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) RDS API command.
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier DB_INSTANCE_IDENTIFIER \
--engine-version ENGINE_VERSION \
--allow-major-version-upgrade \
--region Region \
--no-apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier DB_INSTANCE_IDENTIFIER ^
--engine-version ENGINE_VERSION ^
--allow-major-version-upgrade ^
--region Region ^
--no-apply-immediately
```
**Note**
You must include the `--allow-major-version-upgrade` parameter to perform major version upgrades.
## Database compatibility level
You can use Microsoft SQL Server database compatibility levels to adjust some database behaviors to mimic previous versions of SQL Server. For more information, see [Compatibility level](https://msdn.microsoft.com/en-us/library/bb510680.aspx) in the Microsoft documentation.
When you upgrade your DB instance, all existing databases remain at their original compatibility level. For example, if you upgrade from SQL Server 2019 to SQL Server 2022, all existing databases have a compatibility level of 150. Any new database created after the upgrade have compatibility level 160.
You can change the compatibility level of a database by using the ALTER DATABASE command. For example, to change a database named `customeracct` to be compatible with SQL Server 2022, issue the following command:
```
1. ALTER DATABASE customeracct SET COMPATIBILITY_LEVEL = 160
```
# 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](custom-troubleshooting.md).
**Topics**
+ [
## Viewing RDS Custom events
](#custom-troubleshooting-sqlserver.support-perimeter.viewing-events)
+ [
## Subscribing to RDS Custom events
](#custom-troubleshooting-sqlserver.support-perimeter.subscribing)
+ [
## Troubleshooting CEV errors for RDS Custom for SQL Server
](#custom-troubleshooting-sqlserver.cev)
+ [
## Fixing unsupported configurations in RDS Custom for SQL Server
](#custom-troubleshooting-sqlserver.fix-unsupported)
+ [
## Troubleshooting `Storage-Full` in RDS Custom for SQL Server
](#custom-troubleshooting-storage-full)
+ [
## Troubleshooting PENDING\$1RECOVERY state for TDE enabled databases in RDS Custom for SQL Server
](#custom-troubleshooting-sqlserver.pending_recovery)
## 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](USER_ListEvents.md).
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](USER_Events.Messages.md).
The following example retrieves details for the events that have occurred for the specified RDS Custom DB instance.
```
1. aws rds describe-events \
2. --source-identifier my-custom-instance \
3. --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](USER_Events.Subscribing.md).
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`.
```
1. aws rds create-event-subscription \
2. --subscription-name my-instance-events \
3. --source-type db-instance \
4. --event-categories '["backup","recovery"]' \
5. --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](USER_Events.Messages.md).
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](https://docs.aws.amazon.com/AWSEC2/latest/WindowsGuide/Creating_EBSbacked_WinAMI.html#sysprep-using-ec2launchv2). |
| `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: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-troubleshooting-sqlserver.html) |
| `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](custom-cev-sqlserver.preparing.md#custom-cev-sqlserver.preparing.VersionSupport). |
| `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](custom-cev-sqlserver.preparing.md#custom-cev-sqlserver.preparing.Requirements). |
| `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\$164** 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: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-troubleshooting-sqlserver.html) |
| `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](custom-troubleshooting.md#custom-troubleshooting.fix-unsupported).
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](custom-concept.md#custom-troubleshooting.support-perimeter). For event descriptions, see [Amazon RDS event categories and event messages](USER_Events.Messages.md).
| 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. | |
| `SP-S1011` | Event bus policy | The RDS Custom DB instance status is set to [Unsupported configuration] because of: The resource-based policy for your event bus arn:aws:events:region-1:123456789012:event-bus/default denies Amazon CloudWatch events:PutEvents actions. Resolve this by modifying your resource-based policy to allow events:PutEvents actions for EventBus %s. | |
| `SP-S1012` | CloudWatch VPC permissions | The RDS Custom DB instance status is set to [Unsupported configuration] because of: A VPC endpoint policy is missing permissions to access Amazon CloudWatch events. Resolve this by modifying your VPC endpoint policy to allow events:PutEvents on EventBus arn:aws:events:region-1:123456789012:event-bus/default. | |
| `SP-S1013` | Service control policy | The RDS Custom DB instance status is set to [Unsupported configuration] because of: A service control policy in your AWS Organizations is missing permissions to access Amazon CloudWatch events. Resolve this by modifying your service control policy to allow events:PutEvents on EventBus arn:aws:events:region-1:123456789012:event-bus/default. | |
| `SP-S1014` | IAM instance profile | The RDS Custom DB instance status is set to [Unsupported configuration] because of: Your IAM instance profile %s permissions deny Amazon CloudWatch events. Resolve this by setting ["events:PutEvents"] to 'Allow' and allowing events:PutEvents on EventBus arn:aws:events:region-1:123456789012:event-bus/default in your IAM profile associated with the instance. | |
| `SP-S1015` | IAM instance profile | The RDS Custom DB instance status is set to [Unsupported configuration] because of: Your IAM instance profile %s is missing Amazon CloudWatch event permissions. Resolve this by including the ["events:PutEvents"] permissions and allowing events:PutEvents on EventBus arn:aws:events:region-1:123456789012:event-bus/default in your IAM profile associated with the instance. | |
| `SP-S1016` | IAM permissions boundary | The RDS Custom DB instance status is set to [Unsupported configuration] because of: Your IAM instance profile %s has permissions boundary that deny Amazon CloudWatch events. Resolve this by setting ["events:PutEvents"] to 'Allow' for the EventBus arn:aws:events:region-1:123456789012:event-bus/default in your IAM instance profile permissions boundary. | |
**Operating system**
| Event Code | Configuration area | RDS event message | Validation process |
| --- | --- | --- | --- |
| `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](https://docs.aws.amazon.com/systems-manager/latest/userguide/troubleshooting-ssm-agent.html). To troubleshoot SSM endpoints, see [Unable to connect to SSM endpoints](https://docs.aws.amazon.com/systems-manager/latest/userguide/troubleshooting-ssm-agent.html#systems-manager-ssm-agent-troubleshooting-endpoint-access) and [Use ssm-cli to troubleshoot managed node availability](https://docs.aws.amazon.com/systems-manager/latest/userguide/ssm-cli.html#agent-ts-ssm-cli). |
| `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](custom-reqs-limits-MS.TimeZone.md). |
| `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
|
| SP-S2009 | SSL Certificate | The RDS Custom DB instance is set to [Unsupported configuration] because of: Non self-signed SSL certificate(s) causing disruption in RDS. To resolve this issue, remove the non self-signed certificate(s) from the trusted root certificate store. | Run the following PowerShell command to review non self-signed certificate(s). Get-ChildItem cert:\LocalMachine\root -Recurse | Where-Object {$_.Issuer -ne $_.Subject -and $_.Issuer -notlike "*RDSCustomAgentCA*"} For more information, see [ HTTP Error 403.16 when you try to access a website that's hosted on IIS](https://learn.microsoft.com/en-us/troubleshoot/developer/webapps/iis/site-behavior-performance/http-403-forbidden-access-website). |
| SP-S2010 | Root Volume Storage Status | The RDS Custom DB instance is set to [Unsupported configuration] because of: Root volume storage is full. To resolve this issue, free up at least 500 MiB of storage space in the root EBS volume "volume-id" or increase the volume size and resize the C drive on the EC2 instance "instance-id". The root volume size changes do not persist when you replace the EC2 instance. | Use the following command to view available storage on the root (C:) volume. (Get-PSDrive -Name C).Free / 1MB
For more information on modifying the EBS root volume, see [How](https://forums.aws.amazon.com/knowledge-center/expand-ebs-root-volume-windows) |
**Database**
| Event Code | Configuration area | RDS event message | Validation process |
| --- | --- | --- | --- |
| `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](custom-managing.modify-sqlserver.md) and [Upgrading a DB instance engine version](USER_UpgradeDBInstance.Upgrading.md). |
| `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:
**Example**
```
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:
**Example**
```
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](custom-managing.modify-sqlserver.md) and [Upgrading a DB instance engine version](USER_UpgradeDBInstance.Upgrading.md). |
| `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:\$1 drive. You can resolve this by making sure that all database files, including ROW, LOG, FILESTREAM, etc... are stored on the D:\$1 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:\';
|
| `SP-S3008` | Database Count Limit Exceeded | The RDS Custom DB instance status is set to [Unsupported configuration] because of: The total number of databases on the DB instance exceeds the maximum limit of 5000. To resolve this, reduce the number of databases below the maximum supported limit. | Use the following command to view total database count: SELECT COUNT(name) as databaseCount
FROM sys.databases
WHERE name not in ('tempdb','master','model','msdb','DWDiagnostics','DWConfiguration','DWQueue');
|
## Troubleshooting `Storage-Full` in RDS Custom for SQL Server
RDS Custom also monitors the root (C:) volume. The RDS Custom for SQL Server DB instance moves to the `unsupported-configuration` state when the root volume has less than 500 MiB disk space available. See `Event SP-S2010` in [Fixing unsupported configurations in RDS Custom for SQL Server](#custom-troubleshooting-sqlserver.fix-unsupported).
## Troubleshooting PENDING\$1RECOVERY 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 = '';
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 SET ONLINE;
GO
-- Verify TDE databases are now in ONLINE state
SELECT name, is_encrypted, state_desc FROM sys.databases;
GO
```