# Options for the Microsoft SQL Server database engine
In this section, you can find descriptions for options that are available for Amazon RDS instances running the Microsoft SQL Server DB engine. To enable these options, you add them to an option group, and then associate the option group with your DB instance. For more information, see [Working with option groups](USER_WorkingWithOptionGroups.md).
If you're looking for optional features that aren't added through RDS option groups (such as SSL, Microsoft Windows Authentication, and Amazon S3 integration), see [Additional features for Microsoft SQL Server on Amazon RDS](User.SQLServer.AdditionalFeatures.md).
Amazon RDS supports the following options for Microsoft SQL Server DB instances.
****
| Option | Option ID | Engine editions |
| --- | --- | --- |
| [Linked Servers with Oracle OLEDB](Appendix.SQLServer.Options.LinkedServers_Oracle_OLEDB.md) | `OLEDB_ORACLE` | SQL Server Enterprise Edition SQL Server Standard Edition |
| [Native backup and restore](Appendix.SQLServer.Options.BackupRestore.md) | `SQLSERVER_BACKUP_RESTORE` | SQL Server Enterprise Edition SQL Server Standard Edition SQL Server Web Edition SQL Server Express Edition |
| [Transparent Data Encryption](Appendix.SQLServer.Options.TDE.md) | `TRANSPARENT_DATA_ENCRYPTION` (RDS console) `TDE` (AWS CLI and RDS API) | SQL Server 2016–2022 Enterprise Edition SQL Server 2022 Standard Edition |
| [SQL Server Audit](Appendix.SQLServer.Options.Audit.md) | `SQLSERVER_AUDIT` | In RDS, starting with SQL Server 2016, all editions of SQL Server support server-level audits, and Enterprise Edition also supports database-level audits. Starting with SQL Server SQL Server 2016 (13.x) SP1, all editions support both server-level and database-level audits. For more information, see [SQL Server Audit (database engine)](https://docs.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017) in the SQL Server documentation. |
| [SQL Server Analysis Services](Appendix.SQLServer.Options.SSAS.md) | `SSAS` | SQL Server Enterprise Edition SQL Server Standard Edition |
| [SQL Server Integration Services](Appendix.SQLServer.Options.SSIS.md) | `SSIS` | SQL Server Enterprise Edition SQL Server Standard Edition |
| [SQL Server Reporting Services](Appendix.SQLServer.Options.SSRS.md) | `SSRS` | SQL Server Enterprise Edition SQL Server Standard Edition |
| [Microsoft Distributed Transaction Coordinator](Appendix.SQLServer.Options.MSDTC.md) | `MSDTC` | In RDS, starting with SQL Server 2016, all editions of SQL Server support distributed transactions. |
| [SQL Server resource governor](Appendix.SQLServer.Options.ResourceGovernor.md) | `RESOURCE_GOVERNOR` | SQL Server Enterprise Edition SQL Server 2022 Developer Edition |
## Listing the available options for SQL Server versions and editions
You can use the `describe-option-group-options` AWS CLI command to list the available options for SQL Server versions and editions, and the settings for those options.
The following example shows the options and option settings for SQL Server 2019 Enterprise Edition. The `--engine-name` option is required.
```
aws rds describe-option-group-options --engine-name sqlserver-ee --major-engine-version 15.00
```
The output resembles the following:
```
{
"OptionGroupOptions": [
{
"Name": "MSDTC",
"Description": "Microsoft Distributed Transaction Coordinator",
"EngineName": "sqlserver-ee",
"MajorEngineVersion": "15.00",
"MinimumRequiredMinorEngineVersion": "4043.16.v1",
"PortRequired": true,
"DefaultPort": 5000,
"OptionsDependedOn": [],
"OptionsConflictsWith": [],
"Persistent": false,
"Permanent": false,
"RequiresAutoMinorEngineVersionUpgrade": false,
"VpcOnly": false,
"OptionGroupOptionSettings": [
{
"SettingName": "ENABLE_SNA_LU",
"SettingDescription": "Enable support for SNA LU protocol",
"DefaultValue": "true",
"ApplyType": "DYNAMIC",
"AllowedValues": "true,false",
"IsModifiable": true,
"IsRequired": false,
"MinimumEngineVersionPerAllowedValue": []
},
...
{
"Name": "TDE",
"Description": "SQL Server - Transparent Data Encryption",
"EngineName": "sqlserver-ee",
"MajorEngineVersion": "15.00",
"MinimumRequiredMinorEngineVersion": "4043.16.v1",
"PortRequired": false,
"OptionsDependedOn": [],
"OptionsConflictsWith": [],
"Persistent": true,
"Permanent": false,
"RequiresAutoMinorEngineVersionUpgrade": false,
"VpcOnly": false,
"OptionGroupOptionSettings": []
}
]
}
```
# Support for Linked Servers with Oracle OLEDB in Amazon RDS for SQL Server
Linked servers with the Oracle Provider for OLEDB on RDS for SQL Server lets you access external data sources on an Oracle database. You can read data from remote Oracle data sources and run commands against remote Oracle database servers outside of your RDS for SQL Server DB instance. Using linked servers with Oracle OLEDB, you can:
+ Directly access data sources other than SQL Server
+ Query against diverse Oracle data sources with the same query without moving the data
+ Issue distributed queries, updates, commands, and transactions on data sources across an enterprise ecosystem
+ Integrate connections to an Oracle database from within the Microsoft Business Intelligence suite (SSIS, SSRS, SSAS)
+ Migrate from an Oracle database to RDS for SQL Server
You can activate one or more linked servers for Oracle on either an existing or new RDS for SQL Server DB instance. Then you can integrate external Oracle data sources with your DB instance.
**Contents**
+ [
## Supported versions and Regions
](#LinkedServers_Oracle_OLEDB.VersionRegionSupport)
+ [
## Limitations and recommendations
](#LinkedServers_Oracle_OLEDB.Limitations)
+ [
## Activating linked servers with Oracle
](#LinkedServers_Oracle_OLEDB.Enabling)
+ [
### Creating the option group for OLEDB\$1ORACLE
](#LinkedServers_Oracle_OLEDB.OptionGroup)
+ [
### Adding the `OLEDB_ORACLE` option to the option group
](#LinkedServers_Oracle_OLEDB.Add)
+ [
### Modifying the `OLEDB_ORACLE` version option to another version
](#LinkedServers_Oracle_OLEDB.Modify)
+ [
### Associating the option group with your DB instance
](#LinkedServers_Oracle_OLEDB.Apply)
+ [
## Modifying OLEDB provider properties
](#LinkedServers_Oracle_OLEDB.ModifyProviderProperties)
+ [
## Modifying OLEDB driver properties
](#LinkedServers_Oracle_OLEDB.ModifyDriverProperties)
+ [
## Deactivating linked servers with Oracle
](#LinkedServers_Oracle_OLEDB.Disable)
## Supported versions and Regions
RDS for SQL Server supports linked servers with Oracle OLEDB in all Regions for SQL Server Standard and Enterprise Editions on the following versions:
+ SQL Server 2022, all versions
+ SQL Server 2019, all versions
+ SQL Server 2017, all versions
Linked servers with Oracle OLEDB is supported for the following Oracle Database versions:
+ Oracle Database 21c, all versions
+ Oracle Database 19c, all versions
+ Oracle Database 18c, all versions
Linked servers with Oracle OLEDB is supported for the following OLEDB Oracle driver versions:
+ 21.7
+ 21.16
## Limitations and recommendations
Keep in mind the following limitations and recommendations that apply to linked servers with Oracle OLEDB:
+ Allow network traffic by adding the applicable TCP port in the security group for each RDS for SQL Server DB instance. For example, if you’re configuring a linked server between an EC2 Oracle DB instance and an RDS for SQL Server DB instance, then you must allow traffic from the IP address of the EC2 Oracle DB instance. You also must allow traffic on the port that SQL Server is using to listen for database communication. For more information on security groups, see [Controlling access with security groups](Overview.RDSSecurityGroups.md).
+ Perform a reboot of the RDS for SQL Server DB instance after turning on, turning off, or modifying the `OLEDB_ORACLE` option in your option group. The option group status displays `pending_reboot` for these events and is required. For RDS for SQL Server Multi-AZ instances with AlwaysOn or Mirroring option enabled, a failover is expected when instance is rebooted after the new instance creation or restore.
+ Only simple authentication is supported with a user name and password for the Oracle data source.
+ Open Database Connectivity (ODBC) drivers are not supported. Only the OLEDB driver versions listed above are supported.
+ Distributed transactions (XA) are supported. To activate distributed transactions, turn on the `MSDTC` option in the Option Group for your DB instance and make sure XA transactions are turned on. For more information, see [Support for Microsoft Distributed Transaction Coordinator in RDS for SQL Server](Appendix.SQLServer.Options.MSDTC.md).
+ Creating data source names (DSNs) to use as a shortcut for a connection string is not supported.
+ OLEDB driver tracing is not supported. You can use SQL Server Extended Events to trace OLEDB events. For more information, see [Set up Extended Events in RDS for SQL Server](https://aws.amazon.com/blogs/database/set-up-extended-events-in-amazon-rds-for-sql-server/).
+ Access to the catalogs folder for an Oracle linked server is not supported using SQL Server Management Studio (SSMS).
## Activating linked servers with Oracle
Activate linked servers with Oracle by adding the `OLEDB_ORACLE` option to your RDS for SQL Server DB instance. Use the following process:
1. Create a new option group, or choose an existing option group.
1. Add the `OLEDB_ORACLE` option to the option group.
1. Choose a version of the OLEDB driver to use.
1. Associate the option group with the DB instance.
1. Reboot the DB instance.
### Creating the option group for OLEDB\$1ORACLE
To work with linked servers with Oracle, create an option group or modify an option group that corresponds to the SQL Server edition and version of the DB instance that you plan to use. To complete this procedure, use the AWS Management Console or the AWS CLI.
#### Console
The following procedure creates an option group for SQL Server Standard Edition 2019.
**To create the option group**
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 **Option groups**.
1. Choose **Create group**.
1. In the **Create option group** window, do the following:
1. For **Name**, enter a name for the option group that is unique within your AWS account, such as **oracle-oledb-se-2019**. The name can contain only letters, digits, and hyphens.
1. For **Description**, enter a brief description of the option group, such as **OLEDB\$1ORACLE option group for SQL Server SE 2019**. The description is used for display purposes.
1. For **Engine**, choose **sqlserver-se**.
1. For **Major engine version**, choose **15.00**.
1. Choose **Create**.
#### CLI
The following procedure creates an option group for SQL Server Standard Edition 2019.
**To create the option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-option-group \
--option-group-name oracle-oledb-se-2019 \
--engine-name sqlserver-se \
--major-engine-version 15.00 \
--option-group-description "OLEDB_ORACLE option group for SQL Server SE 2019"
```
For Windows:
```
aws rds create-option-group ^
--option-group-name oracle-oledb-se-2019 ^
--engine-name sqlserver-se ^
--major-engine-version 15.00 ^
--option-group-description "OLEDB_ORACLE option group for SQL Server SE 2019"
```
### Adding the `OLEDB_ORACLE` option to the option group
Next, use the AWS Management Console or the AWS CLI to add the `OLEDB_ORACLE` option to your option group.
#### Console
**To add the OLEDB\$1ORACLE option**
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 **Option groups**.
1. Choose the option group that you just created, which is **oracle-oledb-se-2019** in this example.
1. Choose **Add option**.
1. Under **Option details**, choose **OLEDB\$1ORACLE** for **Option name**.
1. Under **Version**, choose the version of the OLEDB Oracle driver you want to install.
1. Under **Scheduling**, choose whether to add the option immediately or at the next maintenance window.
1. Choose **Add option**.
#### CLI
**To add the OLEDB\$1ORACLE option**
+ Add the `OLEDB_ORACLE` option to the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--option-group-name oracle-oledb-se-2019 \
--options OptionName=OLEDB_ORACLE, OptionVersion=21.16 \
--apply-immediately
```
For Windows:
```
aws rds add-option-to-option-group ^
--option-group-name oracle-oledb-se-2019 ^
--options OptionName=OLEDB_ORACLE, OptionVersion=21.16 ^
--apply-immediately
```
### Modifying the `OLEDB_ORACLE` version option to another version
To modify the `OLEDB_ORACLE` option version to another version, use the AWS Management Console or the AWS CLI.
#### Console
**To Modify the OLEDB\$1ORACLE option**
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 **Option groups**.
1. Choose the option group with the `OLEDB_ORACLE` option (**oracle-oledb-se-2019** in the previous example).
1. Choose **Modify option**.
1. Under **Option details**, choose **OLEDB\$1ORACLE** for **Option name**.
1. Under **Version**, choose the version of the OLEDB Oracle driver you want to use.
1. Under **Scheduling**, choose whether to modify the option immediately or at the next maintenance window.
1. Choose **Modify option**.
#### CLI
To modify the `OLEDB_ORACLE` option version, use the [https://docs.aws.amazon.com/cli/latest/reference/rds/add-option-to-option-group.html](https://docs.aws.amazon.com/cli/latest/reference/rds/add-option-to-option-group.html)AWS CLI command with the option group and option version that you want to use.
**To modify the OLEDB\$1ORACLE option**
+
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--option-group-name oracle-oledb-se-2019 \
--options OptionName=OLEDB_ORACLE, OptionVersion=21.7 \
--apply-immediately
```
For Windows:
```
aws rds add-option-to-option-group ^
--option-group-name oracle-oledb-se-2019 ^
--options OptionName=OLEDB_ORACLE, OptionVersion=21.7 ^
--apply-immediately
```
### Associating the option group with your DB instance
To associate the `OLEDB_ORACLE` option group and parameter group with your DB instance, use the AWS Management Console or the AWS CLI
#### Console
To finish activating linked servers for Oracle, associate your `OLEDB_ORACLE` option group with a new or existing DB instance:
+ For a new DB instance, associate them when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, associate them by modifying the instance. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
#### CLI
You can associate the `OLEDB_ORACLE` option group and parameter group with a new or existing DB instance.
**To create an instance with the `OLEDB_ORACLE` option group and parameter group**
+ Specify the same DB engine type and major version that you used when creating the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--db-instance-identifier mytestsqlserveroracleoledbinstance \
--db-instance-class db.m5.2xlarge \
--engine sqlserver-se \
--engine-version 15.0.4236.7.v1 \
--allocated-storage 100 \
--manage-master-user-password \
--master-username admin \
--storage-type gp2 \
--license-model li \
--domain-iam-role-name my-directory-iam-role \
--domain my-domain-id \
--option-group-name oracle-oledb-se-2019 \
--db-parameter-group-name my-parameter-group-name
```
For Windows:
```
aws rds create-db-instance ^
--db-instance-identifier mytestsqlserveroracleoledbinstance ^
--db-instance-class db.m5.2xlarge ^
--engine sqlserver-se ^
--engine-version 15.0.4236.7.v1 ^
--allocated-storage 100 ^
--manage-master-user-password ^
--master-username admin ^
--storage-type gp2 ^
--license-model li ^
--domain-iam-role-name my-directory-iam-role ^
--domain my-domain-id ^
--option-group-name oracle-oledb-se-2019 ^
--db-parameter-group-name my-parameter-group-name
```
**To modify an instance and associate the `OLEDB_ORACLE` option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mytestsqlserveroracleoledbinstance \
--option-group-name oracle-oledb-se-2019 \
--db-parameter-group-name my-parameter-group-name \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mytestsqlserveroracleoledbinstance ^
--option-group-name oracle-oledb-se-2019 ^
--db-parameter-group-name my-parameter-group-name ^
--apply-immediately
```
## Modifying OLEDB provider properties
You can view and change the properties of the OLEDB provider. Only the `master` user can perform this task. All linked servers for Oracle that are created on the DB instance use the same properties of that OLEDB provider. Call the `sp_MSset_oledb_prop` stored procedure to change the properties of the OLEDB provider.
To change the OLEDB provider properties
```
USE [master]
GO
EXEC sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
EXEC sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'DynamicParameters', 0
GO
```
The following properties can be modified:
****
| Property name | Recommended Value (1 = On, 0 = Off) | Description |
| --- | --- | --- |
| `Dynamic parameter` | 1 | Allows SQL placeholders (represented by '?') in parameterized queries. |
| `Nested queries` | 1 | Allows nested `SELECT` statements in the `FROM` clause, such as sub-queries. |
| `Level zero only` | 0 | Only base-level OLEDB interfaces are called against the provider. |
| `Allow inprocess` | 1 | If turned on, Microsoft SQL Server allows the provider to be instantiated as an in-process server. Set this property to 1 to use Oracle linked servers. |
| `Non transacted updates` | 0 | If non-zero, SQL Server allows updates. |
| `Index as access path` | False | If non-zero, SQL Server attempts to use indexes of the provider to fetch data. |
| `Disallow adhoc access` | False | If set, SQL Server does not allow running pass-through queries against the OLEDB provider. While this option can be checked, it is sometimes appropriate to run pass-through queries. |
| `Supports LIKE operator` | 1 | Indicates that the provider supports queries using the LIKE keyword. |
## Modifying OLEDB driver properties
You can view and change the properties of the OLEDB driver when creating a linked server for Oracle. Only the `master` user can perform this task. Driver properties define how the OLEDB driver handles data when working with a remote Oracle data source. Driver properties are specific to each Oracle linked server created on the DB instance. Call the `master.dbo.sp_addlinkedserver` stored procedure to change the properties of the OLEDB driver.
Example: To create a linked server and change the OLEDB driver `FetchSize` property
```
EXEC master.dbo.sp_addlinkedserver
@server = N'Oracle_link2',
@srvproduct=N'Oracle',
@provider=N'OraOLEDB.Oracle',
@datasrc=N'my-oracle-test.cnetsipka.us-west-2.rds.amazonaws.com:1521/ORCL',
@provstr='FetchSize=200'
GO
```
```
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'Oracle_link2',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'master',
@rmtpassword='Test#1234'
GO
```
**Note**
Specify a password other than the prompt shown here as a security best practice.
## Deactivating linked servers with Oracle
To deactivate linked servers with Oracle, remove the `OLEDB_ORACLE` option from its option group.
**Important**
Removing the option doesn't delete the existing linked server configurations on the DB instance. You must manually drop them to remove them from the DB instance.
You can reactivate the `OLEDB_ORACLE` option after removal to reuse the linked server configurations that were previously configured on the DB instance.
### Console
The following procedure removes the `OLEDB_ORACLE` option.
**To remove the OLEDB\$1ORACLE option from its option group**
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 **Option groups**.
1. Choose the option group with the `OLEDB_ORACLE` option (`oracle-oledb-se-2019` in the previous examples).
1. Choose **Delete option**.
1. Under **Deletion options**, choose **OLEDB\$1ORACLE** for **Options to delete**.
1. Under **Apply immediately**, choose **Yes** to delete the option immediately, or **No** to delete it during the next maintenance window.
1. Choose **Delete**.
### CLI
The following procedure removes the `OLEDB_ORACLE` option.
**To remove the OLEDB\$1ORACLE option from its option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds remove-option-from-option-group \
--option-group-name oracle-oledb-se-2019 \
--options OLEDB_ORACLE \
--apply-immediately
```
For Windows:
```
aws rds remove-option-from-option-group ^
--option-group-name oracle-oledb-se-2019 ^
--options OLEDB_ORACLE ^
--apply-immediately
```
# Linked Servers with Teradata ODBC in RDS for SQL Server
Support for linked servers with the Teradata ODBC driver on RDS for SQL Server lets you access external data sources on a Teradata database. You can read data and run commands from remote Teradata database servers outside of your RDS for SQL Server instance. Use linked-servers with Teradata ODBC to enable the following capabilities:
+ Directly access data sources other than SQL Server.
+ Query against diverse Teradata data sources with the same query without moving the data.
+ Issue distributed queries, updates, commands, and transactions on data sources across an enterprise ecosystem.
+ Integrate connections to a Teradata database from within the Microsoft Business Intelligence Suite (SSIS, SSRS, SSAS).
+ Migrate from a Teradata database to RDS for SQL Server.
You can choose to activate one or more linked servers for Teradata on either an existing or new RDS for SQL Server DB instance. You can then integrate external Teradata data sources with your DB instance.
**Topics**
+ [
## Supported versions and Regions
](#USER_SQLServerTeradata.VersionRegionSupport)
+ [
## Limitations and recommendations
](#USER_SQLServerTeradata.LimitsandRecommendations)
+ [
## Considerations for Multi-AZ deployment
](#USER_SQLServerTeradata.MultiAZ)
+ [
# Activating linked servers with Teradata
](USER_SQLServerTeradata.Activate.md)
+ [
# Creating linked servers with Teradata
](USER_SQLServerTeradata.CreateLinkedServers.md)
+ [
# Deactivating servers linked to Teradata
](USER_SQLServerTeradata.Deactivate.md)
## Supported versions and Regions
RDS for SQL Server supports linked servers with Teradata ODBC in all AWS Regions for SQL Server Standard and Enterprise Edition for the following versions:
+ SQL Server 2022, all versions
+ SQL Server 2019, all versions
+ SQL Server 2017, all versions
The following Teradata database versions support linking with RDS for SQL Server
+ Teradata 17.20, all versions
## Limitations and recommendations
The following limitations apply to linked servers with Teradata ODBC:
+ RDS for SQL Server support only simple authentication with a username and password for the Teradata source.
+ RDS for SQL Server supports only Teradata ODBC driver version 17.20.0.33.
+ RDS for SQL Server does not support creating data source names (DSNs) to use as shortcuts for a connection string.
+ RDS for SQL Server does not support ODBC driver tracing. Use SQL Server Extended Events to trace ODBC events. For more information, see [Set up Extended Events in RDS for SQL Server](https://aws.amazon.com/blogs/database/set-up-extended-events-in-amazon-rds-for-sql-server/).
+ RDS for SQL Server does not support access to the catalogs folder for a Teradata linked server when using SQL Server Management Studio (SSMS).
Consider the following recommendations when using linked servers with Teradata ODBC:
+ Allow network traffic by adding the applicable TCP port in the security group for each RDS for SQL Server DB instance. If you're configuring a linked server between an EC2 Teradata DB instance and an RDS for SQL Server DB instance, then you must allow traffic from the IP address of the EC2 Teradata DB instance. You also must allow traffic on the port that the RDS for SQL Server DB instance is using to listen for database communication. For more information on security groups, see [Controlling access with security groups](Overview.RDSSecurityGroups.md).
+ Distributed transactions (XA) are supported. To activate distributed transactions, turn on the `MSDTC` option in the option group for your DB instance and make sure XA transactions are turned on. For more information, see [Support for Microsoft Distributed Transaction Coordinator in RDS for SQL Server](Appendix.SQLServer.Options.MSDTC.md).
+ Linked Teradata ODBC support SSL/TLS as long as configured on the Teradata Server. For more information, see [ Enable TLS Connectivity on Teradata Vantage](https://docs.teradata.com/r/Enterprise_IntelliFlex_Lake_VMware/Teradata-Call-Level-Interface-Version-2-Reference-for-Workstation-Attached-Systems-20.00/Mainframe-TLS-Connectivity-Supplement/Enable-TLS-Connectivity-on-Teradata-Vantage).
## Considerations for Multi-AZ deployment
RDS for SQL Server currently doesn't replicate linked servers to the mirrored database server (or Always-On availability group secondary server) in a Multi-AZ deployment. If the linked servers are added before the configuration is changed to add mirroring or Always-On, then the linked servers are copied for the existing linked servers.
Alternatively, you can create the linked servers on the primary instance, fail over to the high availability server instance and then create the linked servers again so that they are on both RDS for SQL Server instances.
# Activating linked servers with Teradata
Activate linked servers with Teradata by adding the `ODBC_TERADATA` option to your RDS for SQL Server DB instance. Use the following process:
**Topics**
+ [
## Creating the option group for `ODBC_TERADATA`
](#USER_SQLServerTeradata.Activate.CreateOG)
+ [
## Adding the `ODBC_TERADATA` option to the option group
](#USER_SQLServerTeradata.Activate.AddOG)
+ [
## Associating the `ODBC_TERADATA` option with your DB instance
](#USER_SQLServerTeradata.Activate.AssociateOG)
## Creating the option group for `ODBC_TERADATA`
To work with linked servers with Teradata, create an option group or modify an option group that corresponds to the SQL Server eddition and version of the DB instance that you plan to use. To complete this procedure, use the AWS Management Console or the AWS CLI.
### Console
Use the following procedure to create an option group for SQL Server Standard Edition 2019.
**To create the option group**
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 **Option groups**.
1. Choose **Create group**.
1. In the **Create option group** window, do the following:
1. For **Name**, enter a name for the option group that is unique within your AWS account, such as `teradata-odbc-se-2019`. The name can contain only letters, digits, and hyphens.
1. For **Description**, enter a brief description of the option group.
1. For **Engine**, choose **sqlserver-se**.
1. For **Major engine version**, choose **15.00**.
1. Choose **Create**.
### AWS CLI
The following procedure creates an option group for SQL Server Standard Edition 2019.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-option-group \
--option-group-name teradata-odbc-se-2019 \
--engine-name sqlserver-se \
--major-engine-version 15.00 \
--option-group-description "ODBC_TERADATA option group for SQL Server SE 2019"
```
**Example**
For Windows:
```
aws rds create-option-group ^
--option-group-name teradata-odbc-se-2019 ^
--engine-name sqlserver-se ^
--major-engine-version 15.00 ^
--option-group-description "ODBC_TERADATA option group for SQL Server SE 2019"
```
## Adding the `ODBC_TERADATA` option to the option group
Next, use the AWS Management Console or the AWS CLI to add the `ODBC_Teradata` option to your option group.
### Console
Use the following procedure creates an option group for SQL Server Standard Edition 2019.
**To add the `ODBC_TERADATA` option**
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 **Option groups**.
1. Choose your new option group.
1. Choose **Add option**.
1. Under **Option details**:
1. Choose **ODBC\$1TERADATA** for **Option name**.
1. For `17.20.33.00` for **Option version**.
1. Under scheduling, choose whether to add the option immediately or at the next maintenance window.
1. Choose **Add option**.
### AWS CLI
The following procedure adds the `ODBC_TERADATA` option to your option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--option-group-name teradata-odbc-se-2019 \
--options "OptionName=ODBC_TERADATA,OptionVersion=17.20.33.00" \
--apply-immediately
```
**Example**
For Windows:
```
aws rds add-option-to-option-group ^
--option-group-name teradata-odbc-se-2019 ^
--options "OptionName=ODBC_TERADATA,OptionVersion=17.20.33.00" ^
--apply-immediately
```
## Associating the `ODBC_TERADATA` option with your DB instance
To associate the `ODBC_TERADATA` option group with your DB instance, use the AWS Management Console or AWS CLI.
### Console
To finish activating linked servers for Teradata, associate your option group with a new or existing DB instance:
+ For a new DB instance, associate it when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, associate it by modifying the instance. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
### AWS CLI
Specify the same DB engine type and major version that you used when creating the option group.
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--db-instance-identifier mytestsqlserverteradataodbcinstance \
--db-instance-class db.m5.2xlarge \
--engine sqlserver-se \
--engine-version 15.00 \
--license-model license-included \
--allocated-storage 100 \
--master-username admin \
--master-user-password password \
--storage-type gp2 \
--option-group-name teradata-odbc-se-2019
```
For Windows:
```
aws rds create-db-instance ^
--db-instance-identifier mytestsqlserverteradataodbcinstance ^
--db-instance-class db.m5.2xlarge ^
--engine sqlserver-se ^
--engine-version 15.00 ^
--license-model license-included ^
--allocated-storage 100 ^
--master-username admin ^
--master-user-password password ^
--storage-type gp2 ^
--option-group-name teradata-odbc-se-2019
```
To modify an instance and associate the new option group:
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mytestsqlserverteradataodbcinstance \
--option-group-name teradata-odbc-se-2019 \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mytestsqlserverteradataodbcinstance ^
--option-group-name teradata-odbc-se-2019 ^
--apply-immediately
```
# Creating linked servers with Teradata
To create a linked server with Teradata, run the following commands:
```
EXECUTE master.dbo.sp_addlinkedserver
@server = N'LinkedServer_NAME',
@srvproduct=N'',
@provider=N'MSDASQL',
@provstr=N'"PROVIDER=MSDASQL;DRIVER={Teradata Database ODBC Driver 17.20};
DBCName=Server;UID=user_name;PWD=user_password;
UseDataEncryption=YES/NO;SSLMODE=PREFER/ALLOW/DISABLE>;"',
@catalog='database'
```
```
EXECUTE master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LinkedServer_NAME',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'user_name',
@rmtpassword = N'user_password'
```
An example of the the commands above are shown here:
```
EXECUTE master.dbo.sp_addlinkedserver
@server = N'LinkedServerToTeradata',
@srvproduct=N'',
@provider=N'MSDASQL',
@provstr=N'"PROVIDER=MSDASQL;DRIVER={Teradata Database ODBC Driver 17.20};
DBCName=my-teradata-test.cnetsipka.us-west-2.rds.amazonaws.com;
UID=master;
PWD=Test#1234;
UseDataEncryption=YES;
SSLMODE=PREFER;"',
@catalog='MyTestTeradataDB'
EXECUTE master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LinkedServerToTeradata',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'master',
@rmtpassword = N'Test#1234'
```
**Note**
Specify a password other than the prompt shown here as a security best practice.
# Deactivating servers linked to Teradata
To deactivate linked servers to Teradata, remove the `ODBC_TERADATA` option from its option group.
**Important**
Deleting the option doesn't delete the linked server configurations on the DB instance. You must manually drop them to remove them from the DB instance.
You can reactivate the `ODBC_TERADATA` after removal to reuse the linked server configurations that were previously configured on the DB instance.
## Console
To remove the `ODBC_TERADATA` option from the option group
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 **Option groups**.
1. Choose the option group with the `ODBC_TERADATA` option.
1. Choose **Delete**.
1. Under **Deletion options**, choose `ODBC_TERADATA` under **Options to delete**.
1. Under **Apply immediately**, choose **Yes** to delete the option immediately, or **No** to delete it during the next maintenance window.
1. Choose **Delete**.
## AWS CLI
The following commands removes the `ODBC_TERADATA` option.
For Linux, macOS, or Unix:
```
aws rds remove-option-from-option-group \
--option-group-name teradata-odbc-se-2019 \
--options ODBC_TERADATA \
--apply-immediately
```
For Windows:
```
aws rds remove-option-from-option-group ^
--option-group-name teradata-odbc-se-2019 ^
--options ODBC_TERADATA ^
--apply-immediately
```
# Support for native backup and restore in SQL Server
By using native backup and restore for SQL Server databases, you can create a differential or full backup of your on-premises database and store the backup files on Amazon S3. You can then restore to an existing Amazon RDS DB instance running SQL Server. You can also back up an RDS for SQL Server database, store it on Amazon S3, and restore it in other locations. In addition, you can restore the backup to an on-premises server, or a different Amazon RDS DB instance running SQL Server. For more information, see [Importing and exporting SQL Server databases using native backup and restore](SQLServer.Procedural.Importing.md).
Amazon RDS supports native backup and restore for Microsoft SQL Server databases by using differential and full backup files (.bak files).
## Adding the native backup and restore option
The general process for adding the native backup and restore option to a DB instance is the following:
1. Create a new option group, or copy or modify an existing option group.
1. Add the `SQLSERVER_BACKUP_RESTORE` option to the option group.
1. Associate an AWS Identity and Access Management (IAM) role with the option. The IAM role must have access to an S3 bucket to store the database backups.
That is, the option must have as its option setting a valid Amazon Resource Name (ARN) in the format `arn:aws:iam::account-id:role/role-name`. For more information, see [Amazon Resource Names (ARNs)](https://docs.aws.amazon.com/general/latest/gr/aws-arns-and-namespaces.html#arn-syntax-iam) in the *AWS General Reference.*
The IAM role must also have a trust relationship and a permissions policy attached. The trust relationship allows RDS to assume the role, and the permissions policy defines the actions that the role can perform. For more information, see [Manually creating an IAM role for native backup and restore](SQLServer.Procedural.Importing.Native.Enabling.md#SQLServer.Procedural.Importing.Native.Enabling.IAM).
1. Associate the option group with the DB instance.
After you add the native backup and restore option, you don't need to restart your DB instance. As soon as the option group is active, you can begin backing up and restoring immediately.
### Console
**To add the native backup and restore option**
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 **Option groups**.
1. Create a new option group or use an existing option group. For information on how to create a custom DB option group, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create).
To use an existing option group, skip to the next step.
1. Add the **SQLSERVER\$1BACKUP\$1RESTORE** option to the option group. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption).
1. Do one of the following:
+ To use an existing IAM role and Amazon S3 settings, choose an existing IAM role for **IAM Role**. If you use an existing IAM role, RDS uses the Amazon S3 settings configured for this role.
+ To create a new role and configure Amazon S3 settings, do the following:
1. For **IAM role**, choose **Create a new role**.
1. For **S3 bucket**, choose an S3 bucket from the list.
1. For **S3 prefix (optional)**, specify a prefix to use for the files stored in your Amazon S3 bucket.
This prefix can include a file path but doesn't have to. If you provide a prefix, RDS attaches that prefix to all backup files. RDS then uses the prefix during a restore to identify related files and ignore irrelevant files. For example, you might use the S3 bucket for purposes besides holding backup files. In this case, you can use the prefix to have RDS perform native backup and restore only on a particular folder and its subfolders.
If you leave the prefix blank, then RDS doesn't use a prefix to identify backup files or files to restore. As a result, during a multiple-file restore, RDS attempts to restore every file in every folder of the S3 bucket.
1. Choose the **Enable encryption** check box to encrypt the backup file. Leave the check box cleared (the default) to have the backup file unencrypted.
If you chose **Enable encryption**, choose an encryption key for **AWS KMS key**. For more information about encryption keys, see [Getting started](https://docs.aws.amazon.com/kms/latest/developerguide/getting-started.html) in the *AWS Key Management Service Developer Guide.*
1. Choose **Add option**.
1. Apply the option group to a new or existing DB instance:
+ For a new DB instance, apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, apply the option group by modifying the instance and attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
### CLI
This procedure makes the following assumptions:
+ You're adding the SQLSERVER\$1BACKUP\$1RESTORE option to an option group that already exists. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption).
+ You're associating the option with an IAM role that already exists and has access to an S3 bucket to store the backups.
+ You're applying the option group to a DB instance that already exists. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
**To add the native backup and restore option**
1. Add the `SQLSERVER_BACKUP_RESTORE` option to the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--apply-immediately \
--option-group-name mybackupgroup \
--options "OptionName=SQLSERVER_BACKUP_RESTORE, \
OptionSettings=[{Name=IAM_ROLE_ARN,Value=arn:aws:iam::account-id:role/role-name}]"
```
For Windows:
```
aws rds add-option-to-option-group ^
--option-group-name mybackupgroup ^
--options "[{\"OptionName\": \"SQLSERVER_BACKUP_RESTORE\", ^
\"OptionSettings\": [{\"Name\": \"IAM_ROLE_ARN\", ^
\"Value\": \"arn:aws:iam::account-id:role/role-name"}]}]" ^
--apply-immediately
```
**Note**
When using the Windows command prompt, you must escape double quotes (") in JSON code by prefixing them with a backslash (\$1).
1. Apply the option group to the DB instance.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mydbinstance \
--option-group-name mybackupgroup \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mydbinstance ^
--option-group-name mybackupgroup ^
--apply-immediately
```
## Modifying native backup and restore option settings
After you enable the native backup and restore option, you can modify the settings for the option. For more information about how to modify option settings, see [Modifying an option setting](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.ModifyOption).
## Removing the native backup and restore option
You can turn off native backup and restore by removing the option from your DB instance. After you remove the native backup and restore option, you don't need to restart your DB instance.
To remove the native backup and restore option from a DB instance, do one of the following:
+ Remove the option from the option group it belongs to. This change affects all DB instances that use the option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption).
+ Modify the DB instance and specify a different option group that doesn't include the native backup and restore option. This change affects a single DB instance. You can specify the default (empty) option group, or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
# Support for Transparent Data Encryption in SQL Server
Amazon RDS supports using Transparent Data Encryption (TDE) to encrypt stored data on your DB instances running Microsoft SQL Server. TDE automatically encrypts data before it is written to storage, and automatically decrypts data when the data is read from storage.
Amazon RDS supports TDE for the following SQL Server versions and editions:
+ SQL Server 2022 Standard and Enterprise Editions
+ SQL Server 2019 Standard and Enterprise Editions
+ SQL Server 2017 Enterprise Edition
+ SQL Server 2016 Enterprise Edition
**Note**
RDS for SQL Server does not support TDE for read-only databases.
Transparent Data Encryption for SQL Server provides encryption key management by using a two-tier key architecture. A certificate, which is generated from the database master key, is used to protect the data encryption keys. The database encryption key performs the actual encryption and decryption of data on the user database. Amazon RDS backs up and manages the database master key and the TDE certificate.
Transparent Data Encryption is used in scenarios where you need to encrypt sensitive data. For example, you might want to provide data files and backups to a third party, or address security-related regulatory compliance issues. You can't encrypt the system databases for SQL Server, such as the `model` or `master` databases.
A detailed discussion of Transparent Data Encryption is beyond the scope of this guide, but make sure that you understand the security strengths and weaknesses of each encryption algorithm and key. For information about Transparent Data Encryption for SQL Server, see [Transparent Data Encryption (TDE)](http://msdn.microsoft.com/en-us/library/bb934049.aspx) in the Microsoft documentation.
**Topics**
+ [
## Turning on TDE for RDS for SQL Server
](#TDE.Enabling)
+ [
# Encrypting data on RDS for SQL Server
](TDE.Encrypting.md)
+ [
# Backing up and restoring TDE certificates on RDS for SQL Server
](TDE.BackupRestoreRDS.md)
+ [
# Backing up and restoring TDE certificates for on-premises databases
](TDE.BackupRestoreOnPrem.md)
+ [
# Turning off TDE for RDS for SQL Server
](TDE.Disabling.md)
## Turning on TDE for RDS for SQL Server
To turn on Transparent Data Encryption for an RDS for SQL Server DB instance, specify the TDE option in an RDS option group that's associated with that DB instance:
1. Determine whether your DB instance is already associated with an option group that has the TDE option. To view the option group that a DB instance is associated with, use the RDS console, the [describe-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-instances.html) AWS CLI command, or the API operation [DescribeDBInstances](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeDBInstances.html).
1. If the DB instance isn't associated with an option group that has TDE turned on, you have two choices. You can create an option group and add the TDE option, or you can modify the associated option group to add it.
**Note**
In the RDS console, the option is named `TRANSPARENT_DATA_ENCRYPTION`. In the AWS CLI and RDS API, it's named `TDE`.
For information about creating or modifying an option group, see [Working with option groups](USER_WorkingWithOptionGroups.md). For information about adding an option to an option group, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption).
1. Associate the DB instance with the option group that has the TDE option. For information about associating a DB instance with an option group, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
### Option group considerations
The TDE option is a persistent option. You can't remove it from an option group unless all DB instances and backups are no longer associated with the option group. After you add the TDE option to an option group, the option group can be associated only with DB instances that use TDE. For more information about persistent options in an option group, see [Option groups overview](USER_WorkingWithOptionGroups.md#Overview.OptionGroups).
Because the TDE option is a persistent option, you can have a conflict between the option group and an associated DB instance. You can have a conflict in the following situations:
+ The current option group has the TDE option, and you replace it with an option group that doesn't have the TDE option.
+ You restore from a DB snapshot to a new DB instance that doesn't have an option group that contains the TDE option. For more information about this scenario, see [Considerations for option groups](USER_CopySnapshot.md#USER_CopySnapshot.Options).
### SQL Server performance considerations
Using Transparent Data Encryption can affect the performance of a SQL Server DB instance.
Performance for unencrypted databases can also be degraded if the databases are on a DB instance that has at least one encrypted database. As a result, we recommend that you keep encrypted and unencrypted databases on separate DB instances.
# Encrypting data on RDS for SQL Server
When the TDE option is added to an option group, Amazon RDS generates a certificate that's used in the encryption process. You can then use the certificate to run SQL statements that encrypt data in a database on the DB instance.
The following example uses the RDS-created certificate called `RDSTDECertificateName` to encrypt a database called `myDatabase`.
```
1. ---------- Turning on TDE -------------
2.
3. -- Find an RDS TDE certificate to use
4. USE [master]
5. GO
6. SELECT name FROM sys.certificates WHERE name LIKE 'RDSTDECertificate%'
7. GO
8.
9. USE [myDatabase]
10. GO
11. -- Create a database encryption key (DEK) using one of the certificates from the previous step
12. CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
13. ENCRYPTION BY SERVER CERTIFICATE [RDSTDECertificateName]
14. GO
15.
16. -- Turn on encryption for the database
17. ALTER DATABASE [myDatabase] SET ENCRYPTION ON
18. GO
19.
20. -- Verify that the database is encrypted
21. USE [master]
22. GO
23. SELECT name FROM sys.databases WHERE is_encrypted = 1
24. GO
25. SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys
26. GO
```
The time that it takes to encrypt a SQL Server database using TDE depends on several factors. These include the size of the DB instance, whether the instance uses Provisioned IOPS storage, the amount of data, and other factors.
# Backing up and restoring TDE certificates on RDS for SQL Server
RDS for SQL Server provides stored procedures for backing up, restoring, and dropping TDE certificates. RDS for SQL Server also provides a function for viewing restored user TDE certificates.
User TDE certificates are used to restore databases to RDS for SQL Server that are on-premises and have TDE turned on. These certificates have the prefix `UserTDECertificate_`. After restoring databases, and before making them available to use, RDS modifies the databases that have TDE turned on to use RDS-generated TDE certificates. These certificates have the prefix `RDSTDECertificate`.
User TDE certificates remain on the RDS for SQL Server DB instance, unless you drop them using the `rds_drop_tde_certificate` stored procedure. For more information, see [Dropping restored TDE certificates](#TDE.BackupRestoreRDS.Drop).
You can use a user TDE certificate to restore other databases from the source DB instance. The databases to restore must use the same TDE certificate and have TDE turned on. You don't have to import (restore) the same certificate again.
**Topics**
+ [
## Prerequisites
](#TDE.BackupRestoreRDS.Prereqs)
+ [
## Limitations
](#TDE.Limitations)
+ [
## Backing up a TDE certificate
](#TDE.BackupRestoreRDS.Backup)
+ [
## Restoring a TDE certificate
](#TDE.BackupRestoreRDS.Restore)
+ [
## Viewing restored TDE certificates
](#TDE.BackupRestoreRDS.Show)
+ [
## Dropping restored TDE certificates
](#TDE.BackupRestoreRDS.Drop)
## Prerequisites
Before you can back up or restore TDE certificates on RDS for SQL Server, make sure to perform the following tasks. The first three are described in [Setting up for native backup and restore](SQLServer.Procedural.Importing.Native.Enabling.md).
1. Create Amazon S3 general purpose buckets or directory buckets for storing files to back up and restore.
We recommend that you use separate buckets for database backups and for TDE certificate backups.
1. Create an IAM role for backing up and restoring files.
The IAM role must be both a user and an administrator for the AWS KMS key.
When using directory buckets, no additional permissions are required other than the permissions required for [Manually creating an IAM role for native backup and restore](SQLServer.Procedural.Importing.Native.Enabling.md#SQLServer.Procedural.Importing.Native.Enabling.IAM) with directory buckets.
When using S3 resources, the IAM role also requires the following permissions in addition to the permissions required for [Manually creating an IAM role for native backup and restore](SQLServer.Procedural.Importing.Native.Enabling.md#SQLServer.Procedural.Importing.Native.Enabling.IAM):
+ `s3:GetBucketAcl`, `s3:GetBucketLocation`, and `s3:ListBucket` on the S3 bucket resource
1. Add the `SQLSERVER_BACKUP_RESTORE` option to an option group on your DB instance.
This is in addition to the `TRANSPARENT_DATA_ENCRYPTION` (`TDE`) option.
1. Make sure that you have a symmetric encryption KMS key. You have the following options:
+ If you have an existing KMS key in your account, you can use it. No further action is necessary.
+ If you don't have an existing symmetric encryption KMS key in your account, create a KMS key by following the instructions in [Creating keys](https://docs.aws.amazon.com/kms/latest/developerguide/create-keys.html#create-symmetric-cmk) in the *AWS Key Management Service Developer Guide*.
1. Enable Amazon S3 integration to transfer files between the DB instance and Amazon S3.
For more information on enabling Amazon S3 integration, see [Integrating an Amazon RDS for SQL Server DB instance with Amazon S3](User.SQLServer.Options.S3-integration.md).
Note that directory buckets are not supported for S3 integration. This step is only required for [Backing up and restoring TDE certificates for on-premises databases](TDE.BackupRestoreOnPrem.md).
## Limitations
Using stored procedures to back up and restore TDE certificates has the following limitations:
+ Both the `SQLSERVER_BACKUP_RESTORE` and `TRANSPARENT_DATA_ENCRYPTION` (`TDE`) options must be added to the option group that you associated with your DB instance.
+ TDE certificate backup and restore aren't supported on Multi-AZ DB instances.
+ Canceling TDE certificate backup and restore tasks isn't supported.
+ You can't use a user TDE certificate for TDE encryption of any other database on your RDS for SQL Server DB instance. You can use it to restore only other databases from the source DB instance that have TDE turned on and that use the same TDE certificate.
+ You can drop only user TDE certificates.
+ The maximum number of user TDE certificates supported on RDS is 10. If the number exceeds 10, drop unused TDE certificates and try again.
+ The certificate name can't be empty or null.
+ When restoring a certificate, the certificate name can't include the keyword `RDSTDECERTIFICATE`, and must start with the `UserTDECertificate_` prefix.
+ The `@certificate_name` parameter can include only the following characters: a-z, 0-9, @, \$1, \$1, and underscore (\$1).
+ The file extension for `@certificate_file_s3_arn` must be .cer (case-insensitive).
+ The file extension for `@private_key_file_s3_arn` must be .pvk (case-insensitive).
+ The S3 metadata for the private key file must include the `x-amz-meta-rds-tde-pwd` tag. For more information, see [Backing up and restoring TDE certificates for on-premises databases](TDE.BackupRestoreOnPrem.md).
+ RDS for SQL Server does not support using cross-account keys for TDE.
## Backing up a TDE certificate
To back up TDE certificates, use the `rds_backup_tde_certificate` stored procedure. It has the following syntax.
```
EXECUTE msdb.dbo.rds_backup_tde_certificate
@certificate_name='UserTDECertificate_certificate_name | RDSTDECertificatetimestamp',
@certificate_file_s3_arn='arn:aws:s3:::bucket_name/certificate_file_name.cer',
@private_key_file_s3_arn='arn:aws:s3:::bucket_name/key_file_name.pvk',
@kms_password_key_arn='arn:aws:kms:region:account-id:key/key-id',
[@overwrite_s3_files=0|1];
```
The following parameters are required:
+ `@certificate_name` – The name of the TDE certificate to back up.
+ `@certificate_file_s3_arn` – The destination Amazon Resource Name (ARN) for the certificate backup file in Amazon S3.
+ `@private_key_file_s3_arn` – The destination S3 ARN of the private key file that secures the TDE certificate.
+ `@kms_password_key_arn` – The ARN of the symmetric KMS key used to encrypt the private key password.
The following parameter is optional:
+ `@overwrite_s3_files` – Indicates whether to overwrite the existing certificate and private key files in S3:
+ `0` – Doesn't overwrite the existing files. This value is the default.
Setting `@overwrite_s3_files` to 0 returns an error if a file already exists.
+ `1` – Overwrites an existing file that has the specified name, even if it isn't a backup file.
**Example of backing up a TDE certificate**
```
EXECUTE msdb.dbo.rds_backup_tde_certificate
@certificate_name='RDSTDECertificate20211115T185333',
@certificate_file_s3_arn='arn:aws:s3:::TDE_certs/mycertfile.cer',
@private_key_file_s3_arn='arn:aws:s3:::TDE_certs/mykeyfile.pvk',
@kms_password_key_arn='arn:aws:kms:us-west-2:123456789012:key/AKIAIOSFODNN7EXAMPLE',
@overwrite_s3_files=1;
```
## Restoring a TDE certificate
You use the `rds_restore_tde_certificate` stored procedure to restore (import) user TDE certificates. It has the following syntax.
```
EXECUTE msdb.dbo.rds_restore_tde_certificate
@certificate_name='UserTDECertificate_certificate_name',
@certificate_file_s3_arn='arn:aws:s3:::bucket_name/certificate_file_name.cer',
@private_key_file_s3_arn='arn:aws:s3:::bucket_name/key_file_name.pvk',
@kms_password_key_arn='arn:aws:kms:region:account-id:key/key-id';
```
The following parameters are required:
+ `@certificate_name` – The name of the TDE certificate to restore. The name must start with the `UserTDECertificate_` prefix.
+ `@certificate_file_s3_arn` – The S3 ARN of the backup file used to restore the TDE certificate.
+ `@private_key_file_s3_arn` – The S3 ARN of the private key backup file of the TDE certificate to be restored.
+ `@kms_password_key_arn` – The ARN of the symmetric KMS key used to encrypt the private key password.
**Example of restoring a TDE certificate**
```
EXECUTE msdb.dbo.rds_restore_tde_certificate
@certificate_name='UserTDECertificate_myTDEcertificate',
@certificate_file_s3_arn='arn:aws:s3:::TDE_certs/mycertfile.cer',
@private_key_file_s3_arn='arn:aws:s3:::TDE_certs/mykeyfile.pvk',
@kms_password_key_arn='arn:aws:kms:us-west-2:123456789012:key/AKIAIOSFODNN7EXAMPLE';
```
## Viewing restored TDE certificates
You use the `rds_fn_list_user_tde_certificates` function to view restored (imported) user TDE certificates. It has the following syntax.
```
SELECT * FROM msdb.dbo.rds_fn_list_user_tde_certificates();
```
The output resembles the following. Not all columns are shown here.
| | | | | | | | | | | |
| --- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |
| name | certificate\$1id | principal\$1id | pvt\$1key\$1encryption\$1type\$1desc | issuer\$1name | cert\$1serial\$1number | thumbprint | subject | start\$1date | expiry\$1date | pvt\$1key\$1last\$1backup\$1date |
| UserTDECertificate\$1tde\$1cert | 343 | 1 | ENCRYPTED\$1BY\$1MASTER\$1KEY | AnyCompany Shipping | 79 3e 57 a3 69 fd 1d 9e 47 2c 32 67 1d 9c ca af | 0x6BB218B34110388680B FE1BA2D86C695096485B5 | AnyCompany Shipping | 2022-04-05 19:49:45.0000000 | 2023-04-05 19:49:45.0000000 | NULL |
## Dropping restored TDE certificates
To drop restored (imported) user TDE certificates that you aren't using, use the `rds_drop_tde_certificate` stored procedure. It has the following syntax.
```
EXECUTE msdb.dbo.rds_drop_tde_certificate @certificate_name='UserTDECertificate_certificate_name';
```
The following parameter is required:
+ `@certificate_name` – The name of the TDE certificate to drop.
You can drop only restored (imported) TDE certificates. You can't drop RDS-created certificates.
**Example of dropping a TDE certificate**
```
EXECUTE msdb.dbo.rds_drop_tde_certificate @certificate_name='UserTDECertificate_myTDEcertificate';
```
# Backing up and restoring TDE certificates for on-premises databases
You can back up TDE certificates for on-premises databases, then later restore them to RDS for SQL Server. You can also restore an RDS for SQL Server TDE certificate to an on-premises DB instance.
**Note**
RDS for SQL Server does not support using cross-account keys for TDE.
The following procedure backs up a TDE certificate and private key. The private key is encrypted using a data key generated from your symmetric encryption KMS key.
**To back up an on-premises TDE certificate**
1. Generate the data key using the AWS CLI [generate-data-key](https://docs.aws.amazon.com/cli/latest/reference/kms/generate-data-key.html) command.
```
aws kms generate-data-key \
--key-id my_KMS_key_ID \
--key-spec AES_256
```
The output resembles the following.
```
{
"CiphertextBlob": "AQIDAHimL2NEoAlOY6Bn7LJfnxi/OZe9kTQo/XQXduug1rmerwGiL7g5ux4av9GfZLxYTDATAAAAfjB8BgkqhkiG9w0B
BwagbzBtAgEAMGgGCSqGSIb3DQEHATAeBglghkgBZQMEAS4wEQQMyCxLMi7GRZgKqD65AgEQgDtjvZLJo2cQ31Vetngzm2ybHDc3d2vI74SRUzZ
2RezQy3sAS6ZHrCjfnfn0c65bFdhsXxjSMnudIY7AKw==",
"Plaintext": "U/fpGtmzGCYBi8A2+0/9qcRQRK2zmG/aOn939ZnKi/0=",
"KeyId": "arn:aws:kms:us-west-2:123456789012:key/1234abcd-00ee-99ff-88dd-aa11bb22cc33"
}
```
You use the plain text output in the next step as the private key password.
1. Back up your TDE certificate as shown in the following example.
```
BACKUP CERTIFICATE myOnPremTDEcertificate TO FILE = 'D:\tde-cert-backup.cer'
WITH PRIVATE KEY (
FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\cert-backup-key.pvk',
ENCRYPTION BY PASSWORD = 'U/fpGtmzGCYBi8A2+0/9qcRQRK2zmG/aOn939ZnKi/0=');
```
1. Save the certificate backup file to your Amazon S3 certificate bucket.
1. Save the private key backup file to your S3 certificate bucket, with the following tag in the file's metadata:
+ Key – `x-amz-meta-rds-tde-pwd`
+ Value – The `CiphertextBlob` value from generating the data key, as in the following example.
```
AQIDAHimL2NEoAlOY6Bn7LJfnxi/OZe9kTQo/XQXduug1rmerwGiL7g5ux4av9GfZLxYTDATAAAAfjB8BgkqhkiG9w0B
BwagbzBtAgEAMGgGCSqGSIb3DQEHATAeBglghkgBZQMEAS4wEQQMyCxLMi7GRZgKqD65AgEQgDtjvZLJo2cQ31Vetngzm2ybHDc3d2vI74SRUzZ
2RezQy3sAS6ZHrCjfnfn0c65bFdhsXxjSMnudIY7AKw==
```
The following procedure restores an RDS for SQL Server TDE certificate to an on-premises DB instance. You copy and restore the TDE certificate on your destination DB instance using the certificate backup, corresponding private key file, and data key. The restored certificate is encrypted by the database master key of the new server.
**To restore a TDE certificate**
1. Copy the TDE certificate backup file and private key file from Amazon S3 to the destination instance. For more information on copying files from Amazon S3, see [Transferring files between RDS for SQL Server and Amazon S3](Appendix.SQLServer.Options.S3-integration.using.md).
1. Use your KMS key to decrypt the output cipher text to retrieve the plain text of the data key. The cipher text is located in the S3 metadata of the private key backup file.
```
aws kms decrypt \
--key-id my_KMS_key_ID \
--ciphertext-blob fileb://exampleCiphertextFile | base64 -d \
--output text \
--query Plaintext
```
You use the plain text output in the next step as the private key password.
1. Use the following SQL command to restore your TDE certificate.
```
CREATE CERTIFICATE myOnPremTDEcertificate FROM FILE='D:\tde-cert-backup.cer'
WITH PRIVATE KEY (FILE = N'D:\tde-cert-key.pvk',
DECRYPTION BY PASSWORD = 'plain_text_output');
```
For more information on KMS decryption, see [decrypt](https://docs.aws.amazon.com/cli/latest/reference/kms/decrypt.html) in the KMS section of the *AWS CLI Command Reference*.
After the TDE certificate is restored on the destination DB instance, you can restore encrypted databases with that certificate.
**Note**
You can use the same TDE certificate to encrypt multiple SQL Server databases on the source DB instance. To migrate multiple databases to a destination instance, copy the TDE certificate associated with them to the destination instance only once.
# Turning off TDE for RDS for SQL Server
To turn off TDE for an RDS for SQL Server DB instance, first make sure that there are no encrypted objects left on the DB instance. To do so, either decrypt the objects or drop them. If any encrypted objects exist on the DB instance, you can't turn off TDE for the DB instance. If a user TDE certificate for encryption was restored (imported), then it should be dropped. When you use the console to remove the TDE option from an option group, the console indicates that it's processing. In addition, an error event is created if the option group is associated with an encrypted DB instance or DB snapshot.
The following example removes the TDE encryption from a database called `customerDatabase`.
```
1. ------------- Removing TDE ----------------
2.
3. USE [customerDatabase]
4. GO
5.
6. -- Turn off encryption of the database
7. ALTER DATABASE [customerDatabase]
8. SET ENCRYPTION OFF
9. GO
10.
11. -- Wait until the encryption state of the database becomes 1. The state is 5 (Decryption in progress) for a while
12. SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys
13. GO
14.
15. -- Drop the DEK used for encryption
16. DROP DATABASE ENCRYPTION KEY
17. GO
18.
19. -- Drop a user TDE certificate if it was restored (imported)
20. EXECUTE msdb.dbo.rds_drop_tde_certificate @certificate_name='UserTDECertificate_certificate_name';
21.
22. -- Alter to SIMPLE Recovery mode so that your encrypted log gets truncated
23. USE [master]
24. GO
25. ALTER DATABASE [customerDatabase] SET RECOVERY SIMPLE
26. GO
```
When all objects are decrypted, you have two options:
1. You can modify the DB instance to be associated with an option group without the TDE option.
1. You can remove the TDE option from the option group.
# SQL Server Audit
In Amazon RDS, you can audit Microsoft SQL Server databases by using the built-in SQL Server auditing mechanism. You can create audits and audit specifications in the same way that you create them for on-premises database servers.
RDS uploads the completed audit logs to your S3 bucket, using the IAM role that you provide. If you enable retention, RDS keeps your audit logs on your DB instance for the configured period of time.
For more information, see [SQL Server Audit (database engine)](https://docs.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-database-engine) in the Microsoft SQL Server documentation.
## SQL Server Audit with Database Activity Streams
You can use Database Activity Streams for RDS to integrate SQL Server Audit events with database activity monitoring tools from Imperva, McAfee, and IBM. For more information about auditing with Database Activity Streams for RDS SQL Server, see [Auditing in Microsoft SQL Server](DBActivityStreams.md#DBActivityStreams.Overview.SQLServer-auditing)
**Topics**
+ [
## SQL Server Audit with Database Activity Streams
](#Appendix.SQLServer.DAS.Audit)
+ [
## Support for SQL Server Audit
](#Appendix.SQLServer.Options.Audit.Support)
+ [
# Adding SQL Server Audit to the DB instance options
](Appendix.SQLServer.Options.Audit.Adding.md)
+ [
# Using SQL Server Audit
](Appendix.SQLServer.Options.Audit.CreateAuditsAndSpecifications.md)
+ [
# Viewing audit logs
](Appendix.SQLServer.Options.Audit.AuditRecords.md)
+ [
## Using SQL Server Audit with Multi-AZ instances
](#Appendix.SQLServer.Options.Audit.Multi-AZ)
+ [
# Configuring an S3 bucket
](Appendix.SQLServer.Options.Audit.S3bucket.md)
+ [
# Manually creating an IAM role for SQL Server Audit
](Appendix.SQLServer.Options.Audit.IAM.md)
## Support for SQL Server Audit
In Amazon RDS, starting with SQL Server 2016, all editions of SQL Server support server-level audits, and the Enterprise edition also supports database-level audits. Starting with SQL Server 2016 (13.x) SP1, all editions support both server-level and database-level audits. For more information, see [SQL Server Audit (database engine)](https://docs.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-database-engine) in the SQL Server documentation.
RDS supports configuring the following option settings for SQL Server Audit.
| Option setting | Valid values | Description |
| --- | --- | --- |
| IAM\$1ROLE\$1ARN | A valid Amazon Resource Name (ARN) in the format arn:aws:iam::account-id:role/role-name. | The ARN of the IAM role that grants access to the S3 bucket where you want to store your audit logs. For more information, see [Amazon Resource Names (ARNs)](https://docs.aws.amazon.com/general/latest/gr/aws-arns-and-namespaces.html#arn-syntax-iam) in the AWS General Reference. |
| S3\$1BUCKET\$1ARN | A valid ARN in the format arn:aws:s3:::amzn-s3-demo-bucket or arn:aws:s3:::amzn-s3-demo-bucket/key-prefix | The ARN for the S3 bucket where you want to store your audit logs. |
| ENABLE\$1COMPRESSION | true or false | Controls audit log compression. By default, compression is enabled (set to true). |
| RETENTION\$1TIME | 0 to 840 | The retention time (in hours) that SQL Server audit records are kept on your RDS instance. By default, retention is disabled. |
# Adding SQL Server Audit to the DB instance options
Enabling SQL Server Audit requires two steps: enabling the option on the DB instance, and enabling the feature inside SQL Server. The process for adding the SQL Server Audit option to a DB instance is as follows:
1. Create a new option group, or copy or modify an existing option group.
1. Add and configure all required options.
1. Associate the option group with the DB instance.
After you add the SQL Server Audit option, you don't need to restart your DB instance. As soon as the option group is active, you can create audits and store audit logs in your S3 bucket.
**To add and configure SQL Server Audit on a DB instance's option group**
1. Choose one of the following:
+ Use an existing option group.
+ Create a custom DB option group and use that option group. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create).
1. Add the **SQLSERVER\$1AUDIT** option to the option group, and configure the option settings. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption).
+ For **IAM role**, if you already have an IAM role with the required policies, you can choose that role. To create a new IAM role, choose **Create a New Role**. For information about the required policies, see [Manually creating an IAM role for SQL Server Audit](Appendix.SQLServer.Options.Audit.IAM.md).
+ For **Select S3 destination**, if you already have an S3 bucket that you want to use, choose it. To create an S3 bucket, choose **Create a New S3 Bucket**.
+ For **Enable Compression**, leave this option chosen to compress audit files. Compression is enabled by default. To disable compression, clear **Enable Compression**.
+ For **Audit log retention**, to keep audit records on the DB instance, choose this option. Specify a retention time in hours. The maximum retention time is 35 days.
1. Apply the option group to a new or existing DB instance. Choose one of the following:
+ If you are creating a new DB instance, apply the option group when you launch the instance.
+ On an existing DB instance, apply the option group by modifying the instance and then attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
## Modifying the SQL Server Audit option
After you enable the SQL Server Audit option, you can modify the settings. For information about how to modify option settings, see [Modifying an option setting](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.ModifyOption).
## Removing SQL Server Audit from the DB instance options
You can turn off the SQL Server Audit feature by disabling audits and then deleting the option.
**To remove auditing**
1. Disable all of the audit settings inside SQL Server. To learn where audits are running, query the SQL Server security catalog views. For more information, see [Security catalog views](https://docs.microsoft.com/sql/relational-databases/system-catalog-views/security-catalog-views-transact-sql) in the Microsoft SQL Server documentation.
1. Delete the SQL Server Audit option from the DB instance. Choose one of the following:
+ Delete the SQL Server Audit option from the option group that the DB instance uses. This change affects all DB instances that use the same option group. For more information, see [Removing an option from an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption).
+ Modify the DB instance, and then choose an option group without the SQL Server Audit option. This change affects only the DB instance that you modify. You can specify the default (empty) option group, or a different custom option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
1. After you delete the SQL Server Audit option from the DB instance, you don't need to restart the instance. Remove unneeded audit files from your S3 bucket.
# Using SQL Server Audit
You can control server audits, server audit specifications, and database audit specifications the same way that you control them for on-premises database servers.
## Creating audits
You create server audits in the same way that you create them for on-premises database servers. For information about how to create server audits, see [CREATE SERVER AUDIT](https://docs.microsoft.com/sql/t-sql/statements/create-server-audit-transact-sql) in the Microsoft SQL Server documentation.
To avoid errors, adhere to the following limitations:
+ Don't exceed the maximum number of supported server audits per instance of 50.
+ Instruct SQL Server to write data to a binary file.
+ Don't use `RDS_` as a prefix in the server audit name.
+ For `FILEPATH`, specify `D:\rdsdbdata\SQLAudit`.
+ For `MAXSIZE`, specify a size between 2 MB and 50 MB.
+ Don't configure `MAX_ROLLOVER_FILES` or `MAX_FILES`.
+ Don't configure SQL Server to shut down the DB instance if it fails to write the audit record.
## Creating audit specifications
You create server audit specifications and database audit specifications the same way that you create them for on-premises database servers. For information about creating audit specifications, see [CREATE SERVER AUDIT SPECIFICATION](https://docs.microsoft.com/sql/t-sql/statements/create-server-audit-specification-transact-sql) and [CREATE DATABASE AUDIT SPECIFICATION](https://docs.microsoft.com/sql/t-sql/statements/create-database-audit-specification-transact-sql) in the Microsoft SQL Server documentation.
To avoid errors, don't use `RDS_` as a prefix in the name of the database audit specification or server audit specification.
# Viewing audit logs
Your audit logs are stored in `D:\rdsdbdata\SQLAudit`.
After SQL Server finishes writing to an audit log file—when the file reaches its size limit—Amazon RDS uploads the file to your S3 bucket. If retention is enabled, Amazon RDS moves the file into the retention folder: `D:\rdsdbdata\SQLAudit\transmitted`.
For information about configuring retention, see [Adding SQL Server Audit to the DB instance options](Appendix.SQLServer.Options.Audit.Adding.md).
Audit records are kept on the DB instance until the audit log file is uploaded. You can view the audit records by running the following command.
```
SELECT *
FROM msdb.dbo.rds_fn_get_audit_file
('D:\rdsdbdata\SQLAudit\*.sqlaudit'
, default
, default )
```
You can use the same command to view audit records in your retention folder by changing the filter to `D:\rdsdbdata\SQLAudit\transmitted\*.sqlaudit`.
```
SELECT *
FROM msdb.dbo.rds_fn_get_audit_file
('D:\rdsdbdata\SQLAudit\transmitted\*.sqlaudit'
, default
, default )
```
## Using SQL Server Audit with Multi-AZ instances
For Multi-AZ instances, the process for sending audit log files to Amazon S3 is similar to the process for Single-AZ instances. However, there are some important differences:
+ Database audit specification objects are replicated to all nodes.
+ Server audits and server audit specifications aren't replicated to secondary nodes. Instead, you have to create or modify them manually.
To capture server audits or a server audit specification from both nodes:
1. Create a server audit or a server audit specification on the primary node.
1. Fail over to the secondary node and create a server audit or a server audit specification with the same name and GUID on the secondary node. Use the `AUDIT_GUID` parameter to specify the GUID.
# Configuring an S3 bucket
The audit log files are automatically uploaded from the DB instance to your S3 bucket. The following restrictions apply to the S3 bucket that you use as a target for audit files:
+ It must be in the same AWS Region and AWS account as the DB instance.
+ It must not be open to the public.
+ The bucket owner must also be the IAM role owner.
+ Your IAM role must have permissions for the customer-managed KMS key associated with the S3 bucket server-side encryption.
The target key that is used to store the data follows this naming schema: `amzn-s3-demo-bucket/key-prefix/instance-name/audit-name/node_file-name.ext`
**Note**
You set both the bucket name and the key prefix values with the (`S3_BUCKET_ARN`) option setting.
The schema is composed of the following elements:
+ ***amzn-s3-demo-bucket*** – The name of your S3 bucket.
+ **`key-prefix`** – The custom key prefix you want to use for audit logs.
+ **`instance-name`** – The name of your Amazon RDS instance.
+ **`audit-name`** – The name of the audit.
+ **`node`** – The identifier of the node that is the source of the audit logs (`node1` or `node2`). There is one node for a Single-AZ instance and two replication nodes for a Multi-AZ instance. These are not primary and secondary nodes, because the roles of primary and secondary change over time. Instead, the node identifier is a simple label.
+ **`node1`** – The first replication node (Single-AZ has one node only).
+ **`node2`** – The second replication node (Multi-AZ has two nodes).
+ **`file-name`** – The target file name. The file name is taken as-is from SQL Server.
+ **`ext`** – The extension of the file (`zip` or `sqlaudit`):
+ **`zip`** – If compression is enabled (default).
+ **`sqlaudit`** – If compression is disabled.
# Manually creating an IAM role for SQL Server Audit
Typically, when you create a new option, the AWS Management Console creates the IAM role and the IAM trust policy for you. However, you can manually create a new IAM role to use with SQL Server Audits, so that you can customize it with any additional requirements you might have. To do this, you create an IAM role and delegate permissions so that the Amazon RDS service can use your Amazon S3 bucket. When you create this IAM role, you attach trust and permissions policies. The trust policy allows Amazon RDS to assume this role. The permission policy defines the actions that this role can do. For more information, see [Creating a role to delegate permissions to an AWS service](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-service.html) in the *AWS Identity and Access Management User Guide*.
You can use the examples in this section to create the trust relationships and permissions policies you need.
The following example shows a trust relationship for SQL Server Audit. It uses the *service principal* `rds.amazonaws.com` to allow RDS to write to the S3 bucket. A *service principal* is an identifier that is used to grant permissions to a service. Anytime you allow access to `rds.amazonaws.com` in this way, you are allowing RDS to perform an action on your behalf. For more information about service principals, see [AWS JSON policy elements: Principal](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_elements_principal.html).
**Example trust relationship for SQL Server Audit**
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}
```
We recommend using the [https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourcearn](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourcearn) and [https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourceaccount](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourceaccount) global condition context keys in resource-based trust relationships to limit the service's permissions to a specific resource. This is the most effective way to protect against the [confused deputy problem](https://docs.aws.amazon.com/IAM/latest/UserGuide/confused-deputy.html).
You might use both global condition context keys and have the `aws:SourceArn` value contain the account ID. In this case, the `aws:SourceAccount` value and the account in the `aws:SourceArn` value must use the same account ID when used in the same statement.
+ Use `aws:SourceArn` if you want cross-service access for a single resource.
+ Use `aws:SourceAccount` if you want to allow any resource in that account to be associated with the cross-service use.
In the trust relationship, make sure to use the `aws:SourceArn` global condition context key with the full Amazon Resource Name (ARN) of the resources accessing the role. For SQL Server Audit, make sure to include both the DB option group and the DB instances, as shown in the following example.
**Example trust relationship with global condition context key for SQL Server Audit**
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"aws:SourceArn": [
"arn:aws:rds:Region:my_account_ID:db:db_instance_identifier",
"arn:aws:rds:Region:my_account_ID:og:option_group_name"
]
}
}
}
]
}
```
In the following example of a permissions policy for SQL Server Audit, we specify an ARN for the Amazon S3 bucket. You can use ARNs to identify a specific account, user, or role that you want grant access to. For more information about using ARNs, see [ Amazon resource names (ARNs)](https://docs.aws.amazon.com/general/latest/gr/aws-arns-and-namespaces.html).
**Example permissions policy for SQL Server Audit**
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "s3:ListAllMyBuckets",
"Resource": "*"
},
{
"Effect": "Allow",
"Action": [
"s3:ListBucket",
"s3:GetBucketACL",
"s3:GetBucketLocation"
],
"Resource": "arn:aws:s3:::amzn-s3-demo-bucket"
},
{
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:ListMultipartUploadParts",
"s3:AbortMultipartUpload"
],
"Resource": "arn:aws:s3:::amzn-s3-demo-bucket/key_prefix/*"
}
]
}
```
**Note**
The `s3:ListAllMyBuckets` action is required for verifying that the same AWS account owns both the S3 bucket and the SQL Server DB instance. The action lists the names of the buckets in the account.
S3 bucket namespaces are global. If you accidentally delete your bucket, another user can create a bucket with the same name in a different account. Then the SQL Server Audit data is written to the new bucket.
# Support for SQL Server Analysis Services in Amazon RDS for SQL Server
Microsoft SQL Server Analysis Services (SSAS) is part of the Microsoft Business Intelligence (MSBI) suite. SSAS is an online analytical processing (OLAP) and data mining tool that is installed within SQL Server. You use SSAS to analyze data to help make business decisions. SSAS differs from the SQL Server relational database because SSAS is optimized for queries and calculations common in a business intelligence environment.
You can turn on SSAS for existing or new DB instances. It's installed on the same DB instance as your database engine. For more information on SSAS, see the Microsoft [Analysis services documentation](https://docs.microsoft.com/en-us/analysis-services).
Amazon RDS supports SSAS for SQL Server Standard and Enterprise Editions on the following versions:
+ Tabular mode:
+ SQL Server 2019, version 15.00.4043.16.v1 and higher
+ SQL Server 2017, version 14.00.3223.3.v1 and higher
+ SQL Server 2016, version 13.00.5426.0.v1 and higher
+ Multidimensional mode:
+ SQL Server 2019, version 15.00.4153.1.v1 and higher
+ SQL Server 2017, version 14.00.3381.3.v1 and higher
+ SQL Server 2016, version 13.00.5882.1.v1 and higher
**Contents**
+ [
## Limitations
](#SSAS.Limitations)
+ [
# Turning on SSAS
](SSAS.Enabling.md)
+ [
## Creating an option group for SSAS
](SSAS.Enabling.md#SSAS.OptionGroup)
+ [
## Adding the SSAS option to the option group
](SSAS.Enabling.md#SSAS.Add)
+ [
## Associating the option group with your DB instance
](SSAS.Enabling.md#SSAS.Apply)
+ [
## Allowing inbound access to your VPC security group
](SSAS.Enabling.md#SSAS.InboundRule)
+ [
## Enabling Amazon S3 integration
](SSAS.Enabling.md#SSAS.EnableS3)
+ [
# Deploying SSAS projects on Amazon RDS
](SSAS.Deploy.md)
+ [
# Monitoring the status of a deployment task
](SSAS.Monitor.md)
+ [
# Using SSAS on Amazon RDS
](SSAS.Use.md)
+ [
## Setting up a Windows-authenticated user for SSAS
](SSAS.Use.md#SSAS.Use.Auth)
+ [
## Adding a domain user as a database administrator
](SSAS.Use.md#SSAS.Admin)
+ [
## Creating an SSAS proxy
](SSAS.Use.md#SSAS.Use.Proxy)
+ [
## Scheduling SSAS database processing using SQL Server Agent
](SSAS.Use.md#SSAS.Use.Schedule)
+ [
## Revoking SSAS access from the proxy
](SSAS.Use.md#SSAS.Use.Revoke)
+ [
# Backing up an SSAS database
](SSAS.Backup.md)
+ [
# Restoring an SSAS database
](SSAS.Restore.md)
+ [
## Restoring a DB instance to a specified time
](SSAS.Restore.md#SSAS.PITR)
+ [
# Changing the SSAS mode
](SSAS.ChangeMode.md)
+ [
# Turning off SSAS
](SSAS.Disable.md)
+ [
# Troubleshooting SSAS issues
](SSAS.Trouble.md)
## Limitations
The following limitations apply to using SSAS on RDS for SQL Server:
+ RDS for SQL Server supports running SSAS in Tabular or Multidimensional mode. For more information, see [Comparing tabular and multidimensional solutions](https://docs.microsoft.com/en-us/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas) in the Microsoft documentation.
+ You can only use one SSAS mode at a time. Before changing modes, make sure to delete all of the SSAS databases.
For more information, see [Changing the SSAS mode](SSAS.ChangeMode.md).
+ Multi-AZ instances aren't supported.
+ Instances must use self-managed Active Directory or AWS Directory Service for Microsoft Active Directory for SSAS authentication. For more information, see [Working with Active Directory with RDS for SQL Server](User.SQLServer.ActiveDirectoryWindowsAuth.md).
+ Users aren't given SSAS server administrator access, but they can be granted database-level administrator access.
+ The only supported port for accessing SSAS is 2383.
+ You can't deploy projects directly. We provide an RDS stored procedure to do this. For more information, see [Deploying SSAS projects on Amazon RDS](SSAS.Deploy.md).
+ Processing during deployment isn't supported.
+ Using .xmla files for deployment isn't supported.
+ SSAS project input files and database backup output files can only be in the `D:\S3` folder on the DB instance.
# Turning on SSAS
Use the following process to turn on SSAS for your DB instance:
1. Create a new option group, or choose an existing option group.
1. Add the `SSAS` option to the option group.
1. Associate the option group with the DB instance.
1. Allow inbound access to the virtual private cloud (VPC) security group for the SSAS listener port.
1. Turn on Amazon S3 integration.
## Creating an option group for SSAS
Use the AWS Management Console or the AWS CLI to create an option group that corresponds to the SQL Server engine and version of the DB instance that you plan to use.
**Note**
You can also use an existing option group if it's for the correct SQL Server engine and version.
### Console
The following console procedure creates an option group for SQL Server Standard Edition 2017.
**To create the option group**
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 **Option groups**.
1. Choose **Create group**.
1. In the **Create option group** pane, do the following:
1. For **Name**, enter a name for the option group that is unique within your AWS account, such as **ssas-se-2017**. The name can contain only letters, digits, and hyphens.
1. For **Description**, enter a brief description of the option group, such as **SSAS option group for SQL Server SE 2017**. The description is used for display purposes.
1. For **Engine**, choose **sqlserver-se**.
1. For **Major engine version**, choose **14.00**.
1. Choose **Create**.
### CLI
The following CLI example creates an option group for SQL Server Standard Edition 2017.
**To create the option group**
+ Use one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-option-group \
--option-group-name ssas-se-2017 \
--engine-name sqlserver-se \
--major-engine-version 14.00 \
--option-group-description "SSAS option group for SQL Server SE 2017"
```
For Windows:
```
aws rds create-option-group ^
--option-group-name ssas-se-2017 ^
--engine-name sqlserver-se ^
--major-engine-version 14.00 ^
--option-group-description "SSAS option group for SQL Server SE 2017"
```
## Adding the SSAS option to the option group
Next, use the AWS Management Console or the AWS CLI to add the `SSAS` option to the option group.
### Console
**To add the SSAS option**
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 **Option groups**.
1. Choose the option group that you just created.
1. Choose **Add option**.
1. Under **Option details**, choose **SSAS** for **Option name**.
1. Under **Option settings**, do the following:
1. For **Max memory**, enter a value in the range 10–80.
**Max memory** specifies the upper threshold above which SSAS begins releasing memory more aggressively to make room for requests that are running, and also new high-priority requests. The number is a percentage of the total memory of the DB instance. The allowed values are 10–80, and the default is 45.
1. For **Mode**, choose the SSAS server mode, **Tabular** or **Multidimensional**.
If you don't see the **Mode** option setting, it means that Multidimensional mode isn't supported in your AWS Region. For more information, see [Limitations](Appendix.SQLServer.Options.SSAS.md#SSAS.Limitations).
**Tabular** is the default.
1. For **Security groups**, choose the VPC security group to associate with the option.
**Note**
The port for accessing SSAS, 2383, is prepopulated.
1. Under **Scheduling**, choose whether to add the option immediately or at the next maintenance window.
1. Choose **Add option**.
### CLI
**To add the SSAS option**
1. Create a JSON file, for example `ssas-option.json`, with the following parameters:
+ `OptionGroupName` – The name of option group that you created or chose previously (`ssas-se-2017` in the following example).
+ `Port` – The port that you use to access SSAS. The only supported port is 2383.
+ `VpcSecurityGroupMemberships` – Memberships for VPC security groups for your RDS DB instance.
+ `MAX_MEMORY` – The upper threshold above which SSAS should begin releasing memory more aggressively to make room for requests that are running, and also new high-priority requests. The number is a percentage of the total memory of the DB instance. The allowed values are 10–80, and the default is 45.
+ `MODE` – The SSAS server mode, either `Tabular` or `Multidimensional`. `Tabular` is the default.
If you receive an error that the `MODE` option setting isn't valid, it means that Multidimensional mode isn't supported in your AWS Region. For more information, see [Limitations](Appendix.SQLServer.Options.SSAS.md#SSAS.Limitations).
The following is an example of a JSON file with SSAS option settings.
```
{
"OptionGroupName": "ssas-se-2017",
"OptionsToInclude": [
{
"OptionName": "SSAS",
"Port": 2383,
"VpcSecurityGroupMemberships": ["sg-0abcdef123"],
"OptionSettings": [{"Name":"MAX_MEMORY","Value":"60"},{"Name":"MODE","Value":"Multidimensional"}]
}],
"ApplyImmediately": true
}
```
1. Add the `SSAS` option to the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--cli-input-json file://ssas-option.json \
--apply-immediately
```
For Windows:
```
aws rds add-option-to-option-group ^
--cli-input-json file://ssas-option.json ^
--apply-immediately
```
## Associating the option group with your DB instance
You can use the console or the CLI to associate the option group with your DB instance.
### Console
Associate your option group with a new or existing DB instance:
+ For a new DB instance, associate the option group with the DB instance when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, modify the instance and associate the new option group with it. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
**Note**
If you use an existing instance, it must already have an Active Directory domain and AWS Identity and Access Management (IAM) role associated with it. If you create a new instance, specify an existing Active Directory domain and IAM role. For more information, see [Working with Active Directory with RDS for SQL Server](User.SQLServer.ActiveDirectoryWindowsAuth.md).
### CLI
You can associate your option group with a new or existing DB instance.
**Note**
If you use an existing instance, it must already have an Active Directory domain and IAM role associated with it. If you create a new instance, specify an existing Active Directory domain and IAM role. For more information, see [Working with Active Directory with RDS for SQL Server](User.SQLServer.ActiveDirectoryWindowsAuth.md).
**To create a DB instance that uses the option group**
+ Specify the same DB engine type and major version that you used when creating the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--db-instance-identifier myssasinstance \
--db-instance-class db.m5.2xlarge \
--engine sqlserver-se \
--engine-version 14.00.3223.3.v1 \
--allocated-storage 100 \
--manage-master-user-password \
--master-username admin \
--storage-type gp2 \
--license-model li \
--domain-iam-role-name my-directory-iam-role \
--domain my-domain-id \
--option-group-name ssas-se-2017
```
For Windows:
```
aws rds create-db-instance ^
--db-instance-identifier myssasinstance ^
--db-instance-class db.m5.2xlarge ^
--engine sqlserver-se ^
--engine-version 14.00.3223.3.v1 ^
--allocated-storage 100 ^
--manage-master-user-password ^
--master-username admin ^
--storage-type gp2 ^
--license-model li ^
--domain-iam-role-name my-directory-iam-role ^
--domain my-domain-id ^
--option-group-name ssas-se-2017
```
**To modify a DB instance to associate the option group**
+ Use one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier myssasinstance \
--option-group-name ssas-se-2017 \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier myssasinstance ^
--option-group-name ssas-se-2017 ^
--apply-immediately
```
## Allowing inbound access to your VPC security group
Create an inbound rule for the specified SSAS listener port in the VPC security group associated with your DB instance. For more information about setting up security groups, see [Provide access to your DB instance in your VPC by creating a security group](CHAP_SettingUp.md#CHAP_SettingUp.SecurityGroup).
## Enabling Amazon S3 integration
To download model configuration files to your host for deployment, use Amazon S3 integration. For more information, see [Integrating an Amazon RDS for SQL Server DB instance with Amazon S3](User.SQLServer.Options.S3-integration.md).
# Deploying SSAS projects on Amazon RDS
On RDS, you can't deploy SSAS projects directly by using SQL Server Management Studio (SSMS). To deploy projects, use an RDS stored procedure.
**Note**
Using .xmla files for deployment isn't supported.
Before you deploy projects, make sure of the following:
+ Amazon S3 integration is turned on. For more information, see [Integrating an Amazon RDS for SQL Server DB instance with Amazon S3](User.SQLServer.Options.S3-integration.md).
+ The `Processing Option` configuration setting is set to `Do Not Process`. This setting means that no processing happens after deployment.
+ You have both the `myssasproject.asdatabase` and `myssasproject.deploymentoptions` files. They're automatically generated when you build the SSAS project.
**To deploy an SSAS project on RDS**
1. Download the `.asdatabase` (SSAS model) file from your S3 bucket to your DB instance, as shown in the following example. For more information on the download parameters, see [Downloading files from an Amazon S3 bucket to a SQL Server DB instance](Appendix.SQLServer.Options.S3-integration.using.md#Appendix.SQLServer.Options.S3-integration.using.download).
```
exec msdb.dbo.rds_download_from_s3
@s3_arn_of_file='arn:aws:s3:::bucket_name/myssasproject.asdatabase',
[@rds_file_path='D:\S3\myssasproject.asdatabase'],
[@overwrite_file=1];
```
1. Download the `.deploymentoptions` file from your S3 bucket to your DB instance.
```
exec msdb.dbo.rds_download_from_s3
@s3_arn_of_file='arn:aws:s3:::bucket_name/myssasproject.deploymentoptions',
[@rds_file_path='D:\S3\myssasproject.deploymentoptions'],
[@overwrite_file=1];
```
1. Deploy the project.
```
exec msdb.dbo.rds_msbi_task
@task_type='SSAS_DEPLOY_PROJECT',
@file_path='D:\S3\myssasproject.asdatabase';
```
# Monitoring the status of a deployment task
To track the status of your deployment (or download) task, call the `rds_fn_task_status` function. It takes two parameters. The first parameter should always be `NULL` because it doesn't apply to SSAS. The second parameter accepts a task ID.
To see a list of all tasks, set the first parameter to `NULL` and the second parameter to `0`, as shown in the following example.
```
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,0);
```
To get a specific task, set the first parameter to `NULL` and the second parameter to the task ID, as shown in the following example.
```
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,42);
```
The `rds_fn_task_status` function returns the following information.
| Output parameter | Description |
| --- | --- |
| `task_id` | The ID of the task. |
| `task_type` | For SSAS, tasks can have the following task types: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SSAS.Monitor.html) |
| `database_name` | Not applicable to SSAS tasks. |
| `% complete` | The progress of the task as a percentage. |
| `duration (mins)` | The amount of time spent on the task, in minutes. |
| `lifecycle` | The status of the task. Possible statuses are the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SSAS.Monitor.html) |
| `task_info` | Additional information about the task. If an error occurs during processing, this column contains information about the error. For more information, see [Troubleshooting SSAS issues](SSAS.Trouble.md). |
| `last_updated` | The date and time that the task status was last updated. |
| `created_at` | The date and time that the task was created. |
| `S3_object_arn` | Not applicable to SSAS tasks. |
| `overwrite_S3_backup_file` | Not applicable to SSAS tasks. |
| `KMS_master_key_arn` | Not applicable to SSAS tasks. |
| `filepath` | Not applicable to SSAS tasks. |
| `overwrite_file` | Not applicable to SSAS tasks. |
| `task_metadata` | Metadata associated with the SSAS task. |
# Using SSAS on Amazon RDS
After deploying the SSAS project, you can directly process the OLAP database on SSMS.
**To use SSAS on RDS**
1. In SSMS, connect to SSAS using the user name and password for the Active Directory domain.
1. Expand **Databases**. The newly deployed SSAS database appears.
1. Locate the connection string, and update the user name and password to give access to the source SQL database. Doing this is required for processing SSAS objects.
1. For Tabular mode, do the following:
1. Expand the **Connections** tab.
1. Open the context (right-click) menu for the connection object, and then choose **Properties**.
1. Update the user name and password in the connection string.
1. For Multidimensional mode, do the following:
1. Expand the **Data Sources** tab.
1. Open the context (right-click) menu for the data source object, and then choose **Properties**.
1. Update the user name and password in the connection string.
1. Open the context (right-click) menu for the SSAS database that you created and choose **Process Database**.
Depending on the size of the input data, the processing operation might take several minutes to complete.
**Topics**
+ [
## Setting up a Windows-authenticated user for SSAS
](#SSAS.Use.Auth)
+ [
## Adding a domain user as a database administrator
](#SSAS.Admin)
+ [
## Creating an SSAS proxy
](#SSAS.Use.Proxy)
+ [
## Scheduling SSAS database processing using SQL Server Agent
](#SSAS.Use.Schedule)
+ [
## Revoking SSAS access from the proxy
](#SSAS.Use.Revoke)
## Setting up a Windows-authenticated user for SSAS
The main administrator user (sometimes called the master user) can use the following code example to set up a Windows-authenticated login and grant the required procedure permissions. Doing this grants permissions to the domain user to run SSAS customer tasks, use S3 file transfer procedures, create credentials, and work with the SQL Server Agent proxy. For more information, see [Credentials (database engine)](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/credentials-database-engine?view=sql-server-ver15) and [Create a SQL Server Agent proxy](https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver15) in the Microsoft documentation.
You can grant some or all of the following permissions as needed to Windows-authenticated users.
**Example**
```
-- Create a server-level domain user login, if it doesn't already exist
USE [master]
GO
CREATE LOGIN [mydomain\user_name] FROM WINDOWS
GO
-- Create domain user, if it doesn't already exist
USE [msdb]
GO
CREATE USER [mydomain\user_name] FOR LOGIN [mydomain\user_name]
GO
-- Grant necessary privileges to the domain user
USE [master]
GO
GRANT ALTER ANY CREDENTIAL TO [mydomain\user_name]
GO
USE [msdb]
GO
GRANT EXEC ON msdb.dbo.rds_msbi_task TO [mydomain\user_name] with grant option
GRANT SELECT ON msdb.dbo.rds_fn_task_status TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_task_status TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_cancel_task TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_download_from_s3 TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_upload_to_s3 TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_delete_from_filesystem TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_gather_file_details TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_add_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_update_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_grant_login_to_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_revoke_login_from_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_delete_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_login_for_proxy to [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_proxy_for_subsystem TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_sqlagent_proxy TO [mydomain\user_name] with grant option
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [mydomain\user_name]
GO
```
## Adding a domain user as a database administrator
You can add a domain user as an SSAS database administrator in the following ways:
+ A database administrator can use SSMS to create a role with `admin` privileges, then add users to that role.
+ You can use the following stored procedure.
```
exec msdb.dbo.rds_msbi_task
@task_type='SSAS_ADD_DB_ADMIN_MEMBER',
@database_name='myssasdb',
@ssas_role_name='exampleRole',
@ssas_role_member='domain_name\domain_user_name';
```
The following parameters are required:
+ `@task_type` – The type of the MSBI task, in this case `SSAS_ADD_DB_ADMIN_MEMBER`.
+ `@database_name` – The name of the SSAS database to which you're granting administrator privileges.
+ `@ssas_role_name` – The SSAS database administrator role name. If the role doesn't already exist, it's created.
+ `@ssas_role_member` – The SSAS database user that you're adding to the administrator role.
## Creating an SSAS proxy
To be able to schedule SSAS database processing using SQL Server Agent, create an SSAS credential and an SSAS proxy. Run these procedures as a Windows-authenticated user.
**To create the SSAS credential**
+ Create the credential for the proxy. To do this, you can use SSMS or the following SQL statement.
```
USE [master]
GO
CREATE CREDENTIAL [SSAS_Credential] WITH IDENTITY = N'mydomain\user_name', SECRET = N'mysecret'
GO
```
**Note**
`IDENTITY` must be a domain-authenticated login. Replace `mysecret` with the password for the domain-authenticated login.
**To create the SSAS proxy**
1. Use the following SQL statement to create the proxy.
```
USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSAS_Proxy',@credential_name=N'SSAS_Credential',@description=N''
GO
```
1. Use the following SQL statement to grant access to the proxy to other users.
```
USE [msdb]
GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'SSAS_Proxy',@login_name=N'mydomain\user_name'
GO
```
1. Use the following SQL statement to give the SSAS subsystem access to the proxy.
```
USE [msdb]
GO
EXEC msdb.dbo.rds_sqlagent_proxy @task_type='GRANT_SUBSYSTEM_ACCESS',@proxy_name='SSAS_Proxy',@proxy_subsystem='SSAS'
GO
```
**To view the proxy and grants on the proxy**
1. Use the following SQL statement to view the grantees of the proxy.
```
USE [msdb]
GO
EXEC sp_help_proxy
GO
```
1. Use the following SQL statement to view the subsystem grants.
```
USE [msdb]
GO
EXEC msdb.dbo.sp_enum_proxy_for_subsystem
GO
```
## Scheduling SSAS database processing using SQL Server Agent
After you create the credential and proxy and grant SSAS access to the proxy, you can create a SQL Server Agent job to schedule SSAS database processing.
**To schedule SSAS database processing**
+ Use SSMS or T-SQL for creating the SQL Server Agent job. The following example uses T-SQL. You can further configure its job schedule through SSMS or T-SQL.
+ The `@command` parameter outlines the XML for Analysis (XMLA) command to be run by the SQL Server Agent job. This example configures SSAS Multidimensional database processing.
+ The `@server` parameter outlines the target SSAS server name of the SQL Server Agent job.
To call the SSAS service within the same RDS DB instance where the SQL Server Agent job resides, use `localhost:2383`.
To call the SSAS service from outside the RDS DB instance, use the RDS endpoint. You can also use the Kerberos Active Directory (AD) endpoint (`your-DB-instance-name.your-AD-domain-name`) if the RDS DB instances are joined by the same domain. For external DB instances, make sure to properly configure the VPC security group associated with the RDS DB instance for a secure connection.
You can further edit the query to support various XMLA operations. Make edits either by directly modifying the T-SQL query or by using the SSMS UI following SQL Server Agent job creation.
```
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'SSAS_Job',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT
GO
EXEC msdb.dbo.sp_add_jobserver
@job_name=N'SSAS_Job',
@server_name = N'(local)'
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'SSAS_Job', @step_name=N'Process_SSAS_Object',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'ANALYSISCOMMAND',
@command=N'ProcessFullUseExisting',
@server=N'localhost:2383',
@database_name=N'master',
@flags=0,
@proxy_name=N'SSAS_Proxy'
GO
```
## Revoking SSAS access from the proxy
You can revoke access to the SSAS subsystem and delete the SSAS proxy using the following stored procedures.
**To revoke access and delete the proxy**
1. Revoke subsystem access.
```
USE [msdb]
GO
EXEC msdb.dbo.rds_sqlagent_proxy @task_type='REVOKE_SUBSYSTEM_ACCESS',@proxy_name='SSAS_Proxy',@proxy_subsystem='SSAS'
GO
```
1. Revoke the grants on the proxy.
```
USE [msdb]
GO
EXEC msdb.dbo.sp_revoke_login_from_proxy @proxy_name=N'SSAS_Proxy',@name=N'mydomain\user_name'
GO
```
1. Delete the proxy.
```
USE [msdb]
GO
EXEC dbo.sp_delete_proxy @proxy_name = N'SSAS_Proxy'
GO
```
# Backing up an SSAS database
You can create SSAS database backup files only in the `D:\S3` folder on the DB instance. To move the backup files to your S3 bucket, use Amazon S3.
You can back up an SSAS database as follows:
+ A domain user with the `admin` role for a particular database can use SSMS to back up the database to the `D:\S3` folder.
For more information, see [Adding a domain user as a database administrator](SSAS.Use.md#SSAS.Admin).
+ You can use the following stored procedure. This stored procedure doesn't support encryption.
```
exec msdb.dbo.rds_msbi_task
@task_type='SSAS_BACKUP_DB',
@database_name='myssasdb',
@file_path='D:\S3\ssas_db_backup.abf',
[@ssas_apply_compression=1],
[@ssas_overwrite_file=1];
```
The following parameters are required:
+ `@task_type` – The type of the MSBI task, in this case `SSAS_BACKUP_DB`.
+ `@database_name` – The name of the SSAS database that you're backing up.
+ `@file_path` – The path for the SSAS backup file. The `.abf` extension is required.
The following parameters are optional:
+ `@ssas_apply_compression` – Whether to apply SSAS backup compression. Valid values are 1 (Yes) and 0 (No).
+ `@ssas_overwrite_file` – Whether to overwrite the SSAS backup file. Valid values are 1 (Yes) and 0 (No).
# Restoring an SSAS database
Use the following stored procedure to restore an SSAS database from a backup.
You can't restore a database if there is an existing SSAS database with the same name. The stored procedure for restoring doesn't support encrypted backup files.
```
exec msdb.dbo.rds_msbi_task
@task_type='SSAS_RESTORE_DB',
@database_name='mynewssasdb',
@file_path='D:\S3\ssas_db_backup.abf';
```
The following parameters are required:
+ `@task_type` – The type of the MSBI task, in this case `SSAS_RESTORE_DB`.
+ `@database_name` – The name of the new SSAS database that you're restoring to.
+ `@file_path` – The path to the SSAS backup file.
## Restoring a DB instance to a specified time
Point-in-time recovery (PITR) doesn't apply to SSAS databases. If you do PITR, only the SSAS data in the last snapshot before the requested time is available on the restored instance.
**To have up-to-date SSAS databases on a restored DB instance**
1. Back up your SSAS databases to the `D:\S3` folder on the source instance.
1. Transfer the backup files to the S3 bucket.
1. Transfer the backup files from the S3 bucket to the `D:\S3` folder on the restored instance.
1. Run the stored procedure to restore the SSAS databases onto the restored instance.
You can also reprocess the SSAS project to restore the databases.
# Changing the SSAS mode
You can change the mode in which SSAS runs, either Tabular or Multidimensional. To change the mode, use the AWS Management Console or the AWS CLI to modify the options settings in the SSAS option.
**Important**
You can only use one SSAS mode at a time. Make sure to delete all of the SSAS databases before changing the mode, or you receive an error.
## Console
The following Amazon RDS console procedure changes the SSAS mode to Tabular and sets the `MAX_MEMORY` parameter to 70 percent.
**To modify the SSAS option**
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 **Option groups**.
1. Choose the option group with the `SSAS` option that you want to modify (`ssas-se-2017` in the previous examples).
1. Choose **Modify option**.
1. Change the option settings:
1. For **Max memory**, enter **70**.
1. For **Mode**, choose **Tabular**.
1. Choose **Modify option**.
## AWS CLI
The following AWS CLI example changes the SSAS mode to Tabular and sets the `MAX_MEMORY` parameter to 70 percent.
For the CLI command to work, make sure to include all of the required parameters, even if you're not modifying them.
**To modify the SSAS option**
+ Use one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--option-group-name ssas-se-2017 \
--options "OptionName=SSAS,VpcSecurityGroupMemberships=sg-12345e67,OptionSettings=[{Name=MAX_MEMORY,Value=70},{Name=MODE,Value=Tabular}]" \
--apply-immediately
```
For Windows:
```
aws rds add-option-to-option-group ^
--option-group-name ssas-se-2017 ^
--options OptionName=SSAS,VpcSecurityGroupMemberships=sg-12345e67,OptionSettings=[{Name=MAX_MEMORY,Value=70},{Name=MODE,Value=Tabular}] ^
--apply-immediately
```
# Turning off SSAS
To turn off SSAS, remove the `SSAS` option from its option group.
**Important**
Before you remove the `SSAS` option, delete your SSAS databases.
We highly recommend that you back up your SSAS databases before deleting them and removing the `SSAS` option.
## Console
**To remove the SSAS option from its option group**
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 **Option groups**.
1. Choose the option group with the `SSAS` option that you want to remove (`ssas-se-2017` in the previous examples).
1. Choose **Delete option**.
1. Under **Deletion options**, choose **SSAS** for **Options to delete**.
1. Under **Apply immediately**, choose **Yes** to delete the option immediately, or **No** to delete it at the next maintenance window.
1. Choose **Delete**.
## AWS CLI
**To remove the SSAS option from its option group**
+ Use one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds remove-option-from-option-group \
--option-group-name ssas-se-2017 \
--options SSAS \
--apply-immediately
```
For Windows:
```
aws rds remove-option-from-option-group ^
--option-group-name ssas-se-2017 ^
--options SSAS ^
--apply-immediately
```
# Troubleshooting SSAS issues
You might encounter the following issues when using SSAS.
| Issue | Type | Troubleshooting suggestions |
| --- | --- | --- |
| Unable to configure the SSAS option. The requested SSAS mode is new\$1mode, but the current DB instance has number current\$1mode databases. Delete the existing databases before switching to new\$1mode mode. To regain access to current\$1mode mode for database deletion, either update the current DB option group, or attach a new option group with %s as the MODE option setting value for the SSAS option. | RDS event | You can't change the SSAS mode if you still have SSAS databases that use the current mode. Delete the SSAS databases, then try again. |
| Unable to remove the SSAS option because there are number existing mode databases. The SSAS option can't be removed until all SSAS databases are deleted. Add the SSAS option again, delete all SSAS databases, and try again. | RDS event | You can't turn off SSAS if you still have SSAS databases. Delete the SSAS databases, then try again. |
| The SSAS option isn't enabled or is in the process of being enabled. Try again later. | RDS stored procedure | You can't run SSAS stored procedures when the option is turned off, or when it's being turned on. |
| The SSAS option is configured incorrectly. Make sure that the option group membership status is "in-sync", and review the RDS event logs for relevant SSAS configuration error messages. Following these investigations, try again. If errors continue to occur, contact AWS Support. | RDS stored procedure | You can't run SSAS stored procedures when your option group membership isn't in the `in-sync` status. This puts the SSAS option in an incorrect configuration state. If your option group membership status changes to `failed` due to SSAS option modification, there are two possible reasons: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SSAS.Trouble.html) Reconfigure the SSAS option, because RDS allows only one SSAS mode at a time, and doesn't support SSAS option removal with SSAS databases present. Check the RDS event logs for configuration errors for your SSAS instance, and resolve the issues accordingly. |
| Deployment failed. The change can only be deployed on a server running in deployment\$1file\$1mode mode. The current server mode is current\$1mode. | RDS stored procedure | You can't deploy a Tabular database to a Multidimensional server, or a Multidimensional database to a Tabular server. Make sure that you're using files with the correct mode, and verify that the `MODE` option setting is set to the appropriate value. |
| The restore failed. The backup file can only be restored on a server running in restore\$1file\$1mode mode. The current server mode is current\$1mode. | RDS stored procedure | You can't restore a Tabular database to a Multidimensional server, or a Multidimensional database to a Tabular server. Make sure that you're using files with the correct mode, and verify that the `MODE` option setting is set to the appropriate value. |
| The restore failed. The backup file and the RDS DB instance versions are incompatible. | RDS stored procedure | You can't restore an SSAS database with a version incompatible to the SQL Server instance version. For more information, see [Compatibility levels for tabular models](https://docs.microsoft.com/en-us/analysis-services/tabular-models/compatibility-level-for-tabular-models-in-analysis-services) and [Compatibility level of a multidimensional database](https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/compatibility-level-of-a-multidimensional-database-analysis-services) in the Microsoft documentation. |
| The restore failed. The backup file specified in the restore operation is damaged or is not an SSAS backup file. Make sure that @rds\$1file\$1path is correctly formatted. | RDS stored procedure | You can't restore an SSAS database with a damaged file. Make sure that the file isn't damaged or corrupted. This error can also be raised when `@rds_file_path` isn't correctly formatted (for example, it has double backslashes as in `D:\S3\\incorrect_format.abf`). |
| The restore failed. The restored database name can't contain any reserved words or invalid characters: . , ; ' ` : / \$1\$1 \$1 \$1 ? \$1" & % \$1 \$1 \$1 = ( ) [ ] \$1 \$1 < >, or be longer than 100 characters. | RDS stored procedure | The restored database name can't contain any reserved words or characters that aren't valid, or be longer than 100 characters. For SSAS object naming conventions, see [Object naming rules](https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/olap-physical/object-naming-rules-analysis-services) in the Microsoft documentation. |
| An invalid role name was provided. The role name can't contain any reserved strings. | RDS stored procedure | The role name can't contain any reserved strings. For SSAS object naming conventions, see [Object naming rules](https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/olap-physical/object-naming-rules-analysis-services) in the Microsoft documentation. |
| An invalid role name was provided. The role name can't contain any of the following reserved characters: . , ; ' ` : / \$1\$1 \$1 \$1 ? \$1" & % \$1 \$1 \$1 = ( ) [ ] \$1 \$1 < > | RDS stored procedure | The role name can't contain any reserved characters. For SSAS object naming conventions, see [Object naming rules](https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/olap-physical/object-naming-rules-analysis-services) in the Microsoft documentation. |
# Support for SQL Server Integration Services in Amazon RDS for SQL Server
Microsoft SQL Server Integration Services (SSIS) is a component that you can use to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a data warehousing tool used for data extraction, transformation, and loading (ETL). You can also use this tool to automate maintenance of SQL Server databases and updates to multidimensional cube data.
SSIS projects are organized into packages saved as XML-based .dtsx files. Packages can contain control flows and data flows. You use data flows to represent ETL operations. After deployment, packages are stored in SQL Server in the SSISDB database. SSISDB is an online transaction processing (OLTP) database in the full recovery mode.
Amazon RDS for SQL Server supports running SSIS directly on an RDS DB instance. You can enable SSIS on an existing or new DB instance. SSIS is installed on the same DB instance as your database engine.
RDS supports SSIS for SQL Server Standard and Enterprise Editions on the following versions:
+ SQL Server 2022, all versions
+ SQL Server 2019, version 15.00.4043.16.v1 and higher
+ SQL Server 2017, version 14.00.3223.3.v1 and higher
+ SQL Server 2016, version 13.00.5426.0.v1 and higher
**Contents**
+ [
## Limitations and recommendations
](#SSIS.Limitations)
+ [
## Enabling SSIS
](#SSIS.Enabling)
+ [
### Creating the option group for SSIS
](#SSIS.OptionGroup)
+ [
### Adding the SSIS option to the option group
](#SSIS.Add)
+ [
### Creating the parameter group for SSIS
](#SSIS.CreateParamGroup)
+ [
### Modifying the parameter for SSIS
](#SSIS.ModifyParam)
+ [
### Associating the option group and parameter group with your DB instance
](#SSIS.Apply)
+ [
### Enabling S3 integration
](#SSIS.EnableS3)
+ [
# Administrative permissions on SSISDB
](SSIS.Permissions.md)
+ [
## Setting up a Windows-authenticated user for SSIS
](SSIS.Permissions.md#SSIS.Use.Auth)
+ [
# Deploying an SSIS project
](SSIS.Deploy.md)
+ [
# Monitoring the status of a deployment task
](SSIS.Monitor.md)
+ [
# Using SSIS
](SSIS.Use.md)
+ [
## Setting database connection managers for SSIS projects
](SSIS.Use.md#SSIS.Use.ConnMgrs)
+ [
## Creating an SSIS proxy
](SSIS.Use.md#SSIS.Use.Proxy)
+ [
## Scheduling an SSIS package using SQL Server Agent
](SSIS.Use.md#SSIS.Use.Schedule)
+ [
## Revoking SSIS access from the proxy
](SSIS.Use.md#SSIS.Use.Revoke)
+ [
# Disable and drop SSIS database
](SSIS.DisableDrop.md)
+ [
## Disabling SSIS
](SSIS.DisableDrop.md#SSIS.Disable)
+ [
## Dropping the SSISDB database
](SSIS.DisableDrop.md#SSIS.Drop)
## Limitations and recommendations
The following limitations and recommendations apply to running SSIS on RDS for SQL Server:
+ The DB instance must have an associated parameter group with the `clr enabled` parameter set to 1. For more information, see [Modifying the parameter for SSIS](#SSIS.ModifyParam).
**Note**
If you enable the `clr enabled` parameter on SQL Server 2017 or 2019, you can't use the common language runtime (CLR) on your DB instance. For more information, see [Features not supported and features with limited support](SQLServer.Concepts.General.FeatureNonSupport.md).
+ The following control flow tasks are supported:
+ Analysis Services Execute DDL Task
+ Analysis Services Processing Task
+ Bulk Insert Task
+ Check Database Integrity Task
+ Data Flow Task
+ Data Mining Query Task
+ Data Profiling Task
+ Execute Package Task
+ Execute SQL Server Agent Job Task
+ Execute SQL Task
+ Execute T-SQL Statement Task
+ Notify Operator Task
+ Rebuild Index Task
+ Reorganize Index Task
+ Shrink Database Task
+ Transfer Database Task
+ Transfer Jobs Task
+ Transfer Logins Task
+ Transfer SQL Server Objects Task
+ Update Statistics Task
+ Only project deployment is supported.
+ Running SSIS packages by using SQL Server Agent is supported.
+ SSIS log records can be inserted only into user-created databases.
+ Use only the `D:\S3` folder for working with files. Files placed in any other directory are deleted. Be aware of a few other file location details:
+ Place SSIS project input and output files in the `D:\S3` folder.
+ For the Data Flow Task, change the location for `BLOBTempStoragePath` and `BufferTempStoragePath` to a file inside the `D:\S3` folder. The file path must start with `D:\S3\`.
+ Ensure that all parameters, variables, and expressions used for file connections point to the `D:\S3` folder.
+ On Multi-AZ instances, files created by SSIS in the `D:\S3` folder are deleted after a failover. For more information, see [Multi-AZ limitations for S3 integration](User.SQLServer.Options.S3-integration.md#S3-MAZ).
+ Upload the files created by SSIS in the `D:\S3` folder to your Amazon S3 bucket to make them durable.
+ Import Column and Export Column transformations and the Script component on the Data Flow Task aren't supported.
+ You can't enable dump on running SSIS packages, and you can't add data taps on SSIS packages.
+ The SSIS Scale Out feature isn't supported.
+ You can't deploy projects directly. We provide RDS stored procedures to do this. For more information, see [Deploying an SSIS project](SSIS.Deploy.md).
+ Build SSIS project (.ispac) files with the `DoNotSavePasswords` protection mode for deploying on RDS.
+ SSIS isn't supported on Always On instances with read replicas.
+ You can't back up the SSISDB database that is associated with the `SSIS` option.
+ Importing and restoring the SSISDB database from other instances of SSIS isn't supported.
+ You can connect to other SQL Server DB instances or to an Oracle data source. Connecting to other database engines, such as MySQL or PostgreSQL, isn't supported for SSIS on RDS for SQL Server. For more information on connecting to an Oracle data source, see [Linked Servers with Oracle OLEDB](Appendix.SQLServer.Options.LinkedServers_Oracle_OLEDB.md).
+ SSIS does not support a domain joined instance with an outgoing trust to an on-premises domain. When using an outgoing trust, run the SSIS job from an account in the local AWS domain.
+ Executing file system based packages is not supported.
## Enabling SSIS
You enable SSIS by adding the SSIS option to your DB instance. Use the following process:
1. Create a new option group, or choose an existing option group.
1. Add the `SSIS` option to the option group.
1. Create a new parameter group, or choose an existing parameter group.
1. Modify the parameter group to set the `clr enabled` parameter to 1.
1. Associate the option group and parameter group with the DB instance.
1. Enable Amazon S3 integration.
**Note**
If a database with the name SSISDB or a reserved SSIS login already exists on the DB instance, you can't enable SSIS on the instance.
### Creating the option group for SSIS
To work with SSIS, create an option group or modify an option group that corresponds to the SQL Server edition and version of the DB instance that you plan to use. To do this, use the AWS Management Console or the AWS CLI.
#### Console
The following procedure creates an option group for SQL Server Standard Edition 2016.
**To create the option group**
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 **Option groups**.
1. Choose **Create group**.
1. In the **Create option group** window, do the following:
1. For **Name**, enter a name for the option group that is unique within your AWS account, such as **ssis-se-2016**. The name can contain only letters, digits, and hyphens.
1. For **Description**, enter a brief description of the option group, such as **SSIS option group for SQL Server SE 2016**. The description is used for display purposes.
1. For **Engine**, choose **sqlserver-se**.
1. For **Major engine version**, choose **13.00**.
1. Choose **Create**.
#### CLI
The following procedure creates an option group for SQL Server Standard Edition 2016.
**To create the option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-option-group \
--option-group-name ssis-se-2016 \
--engine-name sqlserver-se \
--major-engine-version 13.00 \
--option-group-description "SSIS option group for SQL Server SE 2016"
```
For Windows:
```
aws rds create-option-group ^
--option-group-name ssis-se-2016 ^
--engine-name sqlserver-se ^
--major-engine-version 13.00 ^
--option-group-description "SSIS option group for SQL Server SE 2016"
```
### Adding the SSIS option to the option group
Next, use the AWS Management Console or the AWS CLI to add the `SSIS` option to your option group.
#### Console
**To add the SSIS option**
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 **Option groups**.
1. Choose the option group that you just created, **ssis-se-2016** in this example.
1. Choose **Add option**.
1. Under **Option details**, choose **SSIS** for **Option name**.
1. Under **Scheduling**, choose whether to add the option immediately or at the next maintenance window.
1. Choose **Add option**.
#### CLI
**To add the SSIS option**
+ Add the `SSIS` option to the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--option-group-name ssis-se-2016 \
--options OptionName=SSIS \
--apply-immediately
```
For Windows:
```
aws rds add-option-to-option-group ^
--option-group-name ssis-se-2016 ^
--options OptionName=SSIS ^
--apply-immediately
```
### Creating the parameter group for SSIS
Create or modify a parameter group for the `clr enabled` parameter that corresponds to the SQL Server edition and version of the DB instance that you plan to use for SSIS.
#### Console
The following procedure creates a parameter group for SQL Server Standard Edition 2016.
**To create the parameter group**
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 **Parameter groups**.
1. Choose **Create parameter group**.
1. In the **Create parameter group** pane, do the following:
1. For **Parameter group family**, choose **sqlserver-se-13.0**.
1. For **Group name**, enter an identifier for the parameter group, such as **ssis-sqlserver-se-13**.
1. For **Description**, enter **clr enabled parameter group**.
1. Choose **Create**.
#### CLI
The following procedure creates a parameter group for SQL Server Standard Edition 2016.
**To create the parameter group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-db-parameter-group \
--db-parameter-group-name ssis-sqlserver-se-13 \
--db-parameter-group-family "sqlserver-se-13.0" \
--description "clr enabled parameter group"
```
For Windows:
```
aws rds create-db-parameter-group ^
--db-parameter-group-name ssis-sqlserver-se-13 ^
--db-parameter-group-family "sqlserver-se-13.0" ^
--description "clr enabled parameter group"
```
### Modifying the parameter for SSIS
Modify the `clr enabled` parameter in the parameter group that corresponds to the SQL Server edition and version of your DB instance. For SSIS, set the `clr enabled` parameter to 1.
#### Console
The following procedure modifies the parameter group that you created for SQL Server Standard Edition 2016.
**To modify the parameter group**
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 **Parameter groups**.
1. Choose the parameter group, such as **ssis-sqlserver-se-13**.
1. Under **Parameters**, filter the parameter list for **clr**.
1. Choose **clr enabled**.
1. Choose **Edit parameters**.
1. From **Values**, choose **1**.
1. Choose **Save changes**.
#### CLI
The following procedure modifies the parameter group that you created for SQL Server Standard Edition 2016.
**To modify the parameter group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name ssis-sqlserver-se-13 \
--parameters "ParameterName='clr enabled',ParameterValue=1,ApplyMethod=immediate"
```
For Windows:
```
aws rds modify-db-parameter-group ^
--db-parameter-group-name ssis-sqlserver-se-13 ^
--parameters "ParameterName='clr enabled',ParameterValue=1,ApplyMethod=immediate"
```
### Associating the option group and parameter group with your DB instance
To associate the SSIS option group and parameter group with your DB instance, use the AWS Management Console or the AWS CLI
**Note**
If you use an existing instance, it must already have an Active Directory domain and AWS Identity and Access Management (IAM) role associated with it. If you create a new instance, specify an existing Active Directory domain and IAM role. For more information, see [Working with Active Directory with RDS for SQL Server](User.SQLServer.ActiveDirectoryWindowsAuth.md).
#### Console
To finish enabling SSIS, associate your SSIS option group and parameter group with a new or existing DB instance:
+ For a new DB instance, associate them when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, associate them by modifying the instance. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
#### CLI
You can associate the SSIS option group and parameter group with a new or existing DB instance.
**To create an instance with the SSIS option group and parameter group**
+ Specify the same DB engine type and major version as you used when creating the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--db-instance-identifier myssisinstance \
--db-instance-class db.m5.2xlarge \
--engine sqlserver-se \
--engine-version 13.00.5426.0.v1 \
--allocated-storage 100 \
--manage-master-user-password \
--master-username admin \
--storage-type gp2 \
--license-model li \
--domain-iam-role-name my-directory-iam-role \
--domain my-domain-id \
--option-group-name ssis-se-2016 \
--db-parameter-group-name ssis-sqlserver-se-13
```
For Windows:
```
aws rds create-db-instance ^
--db-instance-identifier myssisinstance ^
--db-instance-class db.m5.2xlarge ^
--engine sqlserver-se ^
--engine-version 13.00.5426.0.v1 ^
--allocated-storage 100 ^
--manage-master-user-password ^
--master-username admin ^
--storage-type gp2 ^
--license-model li ^
--domain-iam-role-name my-directory-iam-role ^
--domain my-domain-id ^
--option-group-name ssis-se-2016 ^
--db-parameter-group-name ssis-sqlserver-se-13
```
**To modify an instance and associate the SSIS option group and parameter group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier myssisinstance \
--option-group-name ssis-se-2016 \
--db-parameter-group-name ssis-sqlserver-se-13 \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier myssisinstance ^
--option-group-name ssis-se-2016 ^
--db-parameter-group-name ssis-sqlserver-se-13 ^
--apply-immediately
```
### Enabling S3 integration
To download SSIS project (.ispac) files to your host for deployment, use S3 file integration. For more information, see [Integrating an Amazon RDS for SQL Server DB instance with Amazon S3](User.SQLServer.Options.S3-integration.md).
# Administrative permissions on SSISDB
When the instance is created or modified with the SSIS option, the result is an SSISDB database with the ssis\$1admin and ssis\$1logreader roles granted to the master user. The master user has the following privileges in SSISDB:
+ alter on ssis\$1admin role
+ alter on ssis\$1logreader role
+ alter any user
Because the master user is a SQL-authenticated user, you can't use the master user for executing SSIS packages. The master user can use these privileges to create new SSISDB users and add them to the ssis\$1admin and ssis\$1logreader roles. Doing this is useful for giving access to your domain users for using SSIS.
## Setting up a Windows-authenticated user for SSIS
The master user can use the following code example to set up a Windows-authenticated login in SSISDB and grant the required procedure permissions. Doing this grants permissions to the domain user to deploy and run SSIS packages, use S3 file transfer procedures, create credentials, and work with the SQL Server Agent proxy. For more information, see [Credentials (database engine)](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/credentials-database-engine?view=sql-server-ver15) and [Create a SQL Server Agent proxy](https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver15) in the Microsoft documentation.
**Note**
You can grant some or all of the following permissions as needed to Windows-authenticated users.
**Example**
```
-- Create a server-level SQL login for the domain user, if it doesn't already exist
USE [master]
GO
CREATE LOGIN [mydomain\user_name] FROM WINDOWS
GO
-- Create a database-level account for the domain user, if it doesn't already exist
USE [SSISDB]
GO
CREATE USER [mydomain\user_name] FOR LOGIN [mydomain\user_name]
-- Add SSIS role membership to the domain user
ALTER ROLE [ssis_admin] ADD MEMBER [mydomain\user_name]
ALTER ROLE [ssis_logreader] ADD MEMBER [mydomain\user_name]
GO
-- Add MSDB role membership to the domain user
USE [msdb]
GO
CREATE USER [mydomain\user_name] FOR LOGIN [mydomain\user_name]
-- Grant MSDB stored procedure privileges to the domain user
GRANT EXEC ON msdb.dbo.rds_msbi_task TO [mydomain\user_name] with grant option
GRANT SELECT ON msdb.dbo.rds_fn_task_status TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_task_status TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_cancel_task TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_download_from_s3 TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_upload_to_s3 TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_delete_from_filesystem TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_gather_file_details TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_add_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_update_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_grant_login_to_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_revoke_login_from_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_delete_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_login_for_proxy to [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_proxy_for_subsystem TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_sqlagent_proxy TO [mydomain\user_name] WITH GRANT OPTION
-- Add the SQLAgentUserRole privilege to the domain user
USE [msdb]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [mydomain\user_name]
GO
-- Grant the ALTER ANY CREDENTIAL privilege to the domain user
USE [master]
GO
GRANT ALTER ANY CREDENTIAL TO [mydomain\user_name]
GO
```
# Deploying an SSIS project
On RDS, you can't deploy SSIS projects directly by using SQL Server Management Studio (SSMS) or SSIS procedures. To download project files from Amazon S3 and then deploy them, use RDS stored procedures.
To run the stored procedures, log in as any user that you granted permissions for running the stored procedures. For more information, see [Setting up a Windows-authenticated user for SSIS](SSIS.Permissions.md#SSIS.Use.Auth).
**To deploy the SSIS project**
1. Download the project (.ispac) file.
```
exec msdb.dbo.rds_download_from_s3
@s3_arn_of_file='arn:aws:s3:::bucket_name/ssisproject.ispac',
@rds_file_path='D:\S3\ssisproject.ispac',
@overwrite_file=1;
```
1. Submit the deployment task, making sure of the following:
+ The folder is present in the SSIS catalog.
+ The project name matches the project name that you used while developing the SSIS project.
```
exec msdb.dbo.rds_msbi_task
@task_type='SSIS_DEPLOY_PROJECT',
@folder_name='DEMO',
@project_name='ssisproject',
@file_path='D:\S3\ssisproject.ispac';
```
# Monitoring the status of a deployment task
To track the status of your deployment task, call the `rds_fn_task_status` function. It takes two parameters. The first parameter should always be `NULL` because it doesn't apply to SSIS. The second parameter accepts a task ID.
To see a list of all tasks, set the first parameter to `NULL` and the second parameter to `0`, as shown in the following example.
```
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,0);
```
To get a specific task, set the first parameter to `NULL` and the second parameter to the task ID, as shown in the following example.
```
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,42);
```
The `rds_fn_task_status` function returns the following information.
| Output parameter | Description |
| --- | --- |
| `task_id` | The ID of the task. |
| `task_type` | `SSIS_DEPLOY_PROJECT` |
| `database_name` | Not applicable to SSIS tasks. |
| `% complete` | The progress of the task as a percentage. |
| `duration (mins)` | The amount of time spent on the task, in minutes. |
| `lifecycle` | The status of the task. Possible statuses are the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SSIS.Monitor.html) |
| `task_info` | Additional information about the task. If an error occurs during processing, this column contains information about the error. |
| `last_updated` | The date and time that the task status was last updated. |
| `created_at` | The date and time that the task was created. |
| `S3_object_arn` | Not applicable to SSIS tasks. |
| `overwrite_S3_backup_file` | Not applicable to SSIS tasks. |
| `KMS_master_key_arn` | Not applicable to SSIS tasks. |
| `filepath` | Not applicable to SSIS tasks. |
| `overwrite_file` | Not applicable to SSIS tasks. |
| `task_metadata` | Metadata associated with the SSIS task. |
# Using SSIS
After deploying the SSIS project into the SSIS catalog, you can run packages directly from SSMS or schedule them by using SQL Server Agent. You must use a Windows-authenticated login for executing SSIS packages. For more information, see [Setting up a Windows-authenticated user for SSIS](SSIS.Permissions.md#SSIS.Use.Auth).
**Topics**
+ [
## Setting database connection managers for SSIS projects
](#SSIS.Use.ConnMgrs)
+ [
## Creating an SSIS proxy
](#SSIS.Use.Proxy)
+ [
## Scheduling an SSIS package using SQL Server Agent
](#SSIS.Use.Schedule)
+ [
## Revoking SSIS access from the proxy
](#SSIS.Use.Revoke)
## Setting database connection managers for SSIS projects
When you use a connection manager, you can use these types of authentication:
+ For local database connections using AWS Managed Active Directory, you can use SQL authentication or Windows authentication. For Windows authentication, use `DB_instance_name.fully_qualified_domain_name` as the server name of the connection string.
An example is `myssisinstance.corp-ad.example.com`, where `myssisinstance` is the DB instance name and `corp-ad.example.com` is the fully qualified domain name.
+ For remote connections, always use SQL authentication.
+ For local database connections using self-managed Active Directory, you can use SQL authentication or Windows authentication. For Windows authentication, use `.` or `LocalHost` as the server name of the connection string.
## Creating an SSIS proxy
To be able to schedule SSIS packages using SQL Server Agent, create an SSIS credential and an SSIS proxy. Run these procedures as a Windows-authenticated user.
**To create the SSIS credential**
+ Create the credential for the proxy. To do this, you can use SSMS or the following SQL statement.
```
USE [master]
GO
CREATE CREDENTIAL [SSIS_Credential] WITH IDENTITY = N'mydomain\user_name', SECRET = N'mysecret'
GO
```
**Note**
`IDENTITY` must be a domain-authenticated login. Replace `mysecret` with the password for the domain-authenticated login.
Whenever the SSISDB primary host is changed, alter the SSIS proxy credentials to allow the new host to access them.
**To create the SSIS proxy**
1. Use the following SQL statement to create the proxy.
```
USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSIS_Proxy',@credential_name=N'SSIS_Credential',@description=N''
GO
```
1. Use the following SQL statement to grant access to the proxy to other users.
```
USE [msdb]
GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'SSIS_Proxy',@login_name=N'mydomain\user_name'
GO
```
1. Use the following SQL statement to give the SSIS subsystem access to the proxy.
```
USE [msdb]
GO
EXEC msdb.dbo.rds_sqlagent_proxy @task_type='GRANT_SUBSYSTEM_ACCESS',@proxy_name='SSIS_Proxy',@proxy_subsystem='SSIS'
GO
```
**To view the proxy and grants on the proxy**
1. Use the following SQL statement to view the grantees of the proxy.
```
USE [msdb]
GO
EXEC sp_help_proxy
GO
```
1. Use the following SQL statement to view the subsystem grants.
```
USE [msdb]
GO
EXEC msdb.dbo.sp_enum_proxy_for_subsystem
GO
```
## Scheduling an SSIS package using SQL Server Agent
After you create the credential and proxy and grant SSIS access to the proxy, you can create a SQL Server Agent job to schedule the SSIS package.
**To schedule the SSIS package**
+ You can use SSMS or T-SQL for creating the SQL Server Agent job. The following example uses T-SQL.
```
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'MYSSISJob',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'MYSSISJob',@server_name=N'(local)'
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'MYSSISJob',@step_name=N'ExecuteSSISPackage',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'SSIS',
@command=N'/ISSERVER "\"\SSISDB\MySSISFolder\MySSISProject\MySSISPackage.dtsx\"" /SERVER "\"my-rds-ssis-instance.corp-ad.company.com/\""
/Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
@database_name=N'master',
@flags=0,
@proxy_name=N'SSIS_Proxy'
GO
```
## Revoking SSIS access from the proxy
You can revoke access to the SSIS subsystem and delete the SSIS proxy using the following stored procedures.
**To revoke access and delete the proxy**
1. Revoke subsystem access.
```
USE [msdb]
GO
EXEC msdb.dbo.rds_sqlagent_proxy @task_type='REVOKE_SUBSYSTEM_ACCESS',@proxy_name='SSIS_Proxy',@proxy_subsystem='SSIS'
GO
```
1. Revoke the grants on the proxy.
```
USE [msdb]
GO
EXEC msdb.dbo.sp_revoke_login_from_proxy @proxy_name=N'SSIS_Proxy',@name=N'mydomain\user_name'
GO
```
1. Delete the proxy.
```
USE [msdb]
GO
EXEC dbo.sp_delete_proxy @proxy_name = N'SSIS_Proxy'
GO
```
# Disable and drop SSIS database
Use the following steps to disable or drop SSIS databases:
**Topics**
+ [
## Disabling SSIS
](#SSIS.Disable)
+ [
## Dropping the SSISDB database
](#SSIS.Drop)
## Disabling SSIS
To disable SSIS, remove the `SSIS` option from its option group.
**Important**
Removing the option doesn't delete the SSISDB database, so you can safely remove the option without losing the SSIS projects.
You can re-enable the `SSIS` option after removal to reuse the SSIS projects that were previously deployed to the SSIS catalog.
### Console
The following procedure removes the `SSIS` option.
**To remove the SSIS option from its option group**
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 **Option groups**.
1. Choose the option group with the `SSIS` option (`ssis-se-2016` in the previous examples).
1. Choose **Delete option**.
1. Under **Deletion options**, choose **SSIS** for **Options to delete**.
1. Under **Apply immediately**, choose **Yes** to delete the option immediately, or **No** to delete it at the next maintenance window.
1. Choose **Delete**.
### CLI
The following procedure removes the `SSIS` option.
**To remove the SSIS option from its option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds remove-option-from-option-group \
--option-group-name ssis-se-2016 \
--options SSIS \
--apply-immediately
```
For Windows:
```
aws rds remove-option-from-option-group ^
--option-group-name ssis-se-2016 ^
--options SSIS ^
--apply-immediately
```
## Dropping the SSISDB database
After removing the SSIS option, the SSISDB database isn't deleted. To drop the SSISDB database, use the `rds_drop_ssis_database` stored procedure after removing the SSIS option.
**To drop the SSIS database**
+ Use the following stored procedure.
```
USE [msdb]
GO
EXEC dbo.rds_drop_ssis_database
GO
```
After dropping the SSISDB database, if you re-enable the SSIS option you get a fresh SSISDB catalog.
# Support for SQL Server Reporting Services in Amazon RDS for SQL Server
Microsoft SQL Server Reporting Services (SSRS) is a server-based application used for report generation and distribution. It's part of a suite of SQL Server services that also includes SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS). SSRS is a service built on top of SQL Server. You can use it to collect data from various data sources and present it in a way that's easily understandable and ready for analysis.
Amazon RDS for SQL Server supports running SSRS directly on RDS DB instances. You can use SSRS with existing or new DB instances.
RDS supports SSRS for SQL Server Standard and Enterprise Editions on the following versions:
+ SQL Server 2022, all versions
+ SQL Server 2019, version 15.00.4043.16.v1 and higher
+ SQL Server 2017, version 14.00.3223.3.v1 and higher
+ SQL Server 2016, version 13.00.5820.21.v1 and higher
**Contents**
+ [
## Limitations and recommendations
](#SSRS.Limitations)
+ [
# Turning on SSRS
](SSRS.Enabling.md)
+ [
## Creating an option group for SSRS
](SSRS.Enabling.md#SSRS.OptionGroup)
+ [
## Adding the SSRS option to your option group
](SSRS.Enabling.md#SSRS.Add)
+ [
## Associating your option group with your DB instance
](SSRS.Enabling.md#SSRS.Apply)
+ [
## Allowing inbound access to your VPC security group
](SSRS.Enabling.md#SSRS.Inbound)
+ [
## Report server databases
](#SSRS.DBs)
+ [
## SSRS log files
](#SSRS.Logs)
+ [
# Accessing the SSRS web portal
](SSRS.Access.md)
+ [
## Using SSL on RDS
](SSRS.Access.md#SSRS.Access.SSL)
+ [
## Granting access to domain users
](SSRS.Access.md#SSRS.Access.Grant)
+ [
## Accessing the web portal
](SSRS.Access.md#SSRS.Access)
+ [
# Deploying reports and configuring report data sources
](SSRS.DeployConfig.md)
+ [
## Deploying reports to SSRS
](SSRS.DeployConfig.md#SSRS.Deploy)
+ [
## Configuring the report data source
](SSRS.DeployConfig.md#SSRS.ConfigureDataSource)
+ [
# Using SSRS Email to send reports
](SSRS.Email.md)
+ [
# Revoking system-level permissions
](SSRS.Access.Revoke.md)
+ [
# Monitoring the status of a task
](SSRS.Monitor.md)
+ [
# Disabling and deleting SSRS databases
](SSRS.DisableDelete.md)
+ [
## Turning off SSRS
](SSRS.DisableDelete.md#SSRS.Disable)
+ [
## Deleting the SSRS databases
](SSRS.DisableDelete.md#SSRS.Drop)
## Limitations and recommendations
The following limitations and recommendations apply to running SSRS on RDS for SQL Server:
+ You can't use SSRS on DB instances that have read replicas.
+ Instances must use self-managed Active Directory or AWS Directory Service for Microsoft Active Directory for SSRS web portal and web server authentication. For more information, see [Working with Active Directory with RDS for SQL Server](User.SQLServer.ActiveDirectoryWindowsAuth.md).
+ You can't back up the reporting server databases that are created with the SSRS option.
+ Importing and restoring report server databases from other instances of SSRS isn't supported. For more information, see [Report server databases](#SSRS.DBs).
+ You can't configure SSRS to listen on the default SSL port (443). The allowed values are 1150–49511, except 1234, 1434, 3260, 3343, 3389, and 47001.
+ Subscriptions through a Microsoft Windows file share aren't supported.
+ Using Reporting Services Configuration Manager isn't supported.
+ Creating and modifying roles isn't supported.
+ Modifying report server properties isn't supported.
+ System administrator and system user roles aren't granted.
+ You can't edit system-level role assignments through the web portal.
# Turning on SSRS
Use the following process to turn on SSRS for your DB instance:
1. Create a new option group, or choose an existing option group.
1. Add the `SSRS` option to the option group.
1. Associate the option group with the DB instance.
1. Allow inbound access to the virtual private cloud (VPC) security group for the SSRS listener port.
## Creating an option group for SSRS
To work with SSRS, create an option group that corresponds to the SQL Server engine and version of the DB instance that you plan to use. To do this, use the AWS Management Console or the AWS CLI.
**Note**
You can also use an existing option group if it's for the correct SQL Server engine and version.
### Console
The following procedure creates an option group for SQL Server Standard Edition 2017.
**To create the option group**
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 **Option groups**.
1. Choose **Create group**.
1. In the **Create option group** pane, do the following:
1. For **Name**, enter a name for the option group that is unique within your AWS account, such as **ssrs-se-2017**. The name can contain only letters, digits, and hyphens.
1. For **Description**, enter a brief description of the option group, such as **SSRS option group for SQL Server SE 2017**. The description is used for display purposes.
1. For **Engine**, choose **sqlserver-se**.
1. For **Major engine version**, choose **14.00**.
1. Choose **Create**.
### CLI
The following procedure creates an option group for SQL Server Standard Edition 2017.
**To create the option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-option-group \
--option-group-name ssrs-se-2017 \
--engine-name sqlserver-se \
--major-engine-version 14.00 \
--option-group-description "SSRS option group for SQL Server SE 2017"
```
For Windows:
```
aws rds create-option-group ^
--option-group-name ssrs-se-2017 ^
--engine-name sqlserver-se ^
--major-engine-version 14.00 ^
--option-group-description "SSRS option group for SQL Server SE 2017"
```
## Adding the SSRS option to your option group
Next, use the AWS Management Console or the AWS CLI to add the `SSRS` option to your option group.
### Console
**To add the SSRS option**
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 **Option groups**.
1. Choose the option group that you just created, then choose **Add option**.
1. Under **Option details**, choose **SSRS** for **Option name**.
1. Under **Option settings**, do the following:
1. Enter the port for the SSRS service to listen on. The default is 8443. For a list of allowed values, see [Limitations and recommendations](Appendix.SQLServer.Options.SSRS.md#SSRS.Limitations).
1. Enter a value for **Max memory**.
**Max memory** specifies the upper threshold above which no new memory allocation requests are granted to report server applications. The number is a percentage of the total memory of the DB instance. The allowed values are 10–80.
1. For **Security groups**, choose the VPC security group to associate with the option. Use the same security group that is associated with your DB instance.
1. To use SSRS Email to send reports, choose the **Configure email delivery options** check box under **Email delivery in reporting services**, and then do the following:
1. For **Sender email address**, enter the email address to use in the **From** field of messages sent by SSRS Email.
Specify a user account that has permission to send mail from the SMTP server.
1. For **SMTP server**, specify the SMTP server or gateway to use.
It can be an IP address, the NetBIOS name of a computer on your corporate intranet, or a fully qualified domain name.
1. For **SMTP port**, enter the port to use to connect to the mail server. The default is 25.
1. To use authentication:
1. Select the **Use authentication** check box.
1. For **Secret Amazon Resource Name (ARN)** enter the AWS Secrets Manager ARN for the user credentials.
Use the following format:
**arn:aws:secretsmanager:*Region*:*AccountId*:secret:*SecretName*-*6RandomCharacters***
For example:
**arn:aws:secretsmanager:*us-west-2*:*123456789012*:secret:*MySecret-a1b2c3***
For more information on creating the secret, see [Using SSRS Email to send reports](SSRS.Email.md).
1. Select the **Use Secure Sockets Layer (SSL)** check box to encrypt email messages using SSL.
1. Under **Scheduling**, choose whether to add the option immediately or at the next maintenance window.
1. Choose **Add option**.
### CLI
**To add the SSRS option**
1. Create a JSON file, for example `ssrs-option.json`.
1. Set the following required parameters:
+ `OptionGroupName` – The name of option group that you created or chose previously (`ssrs-se-2017` in the following example).
+ `Port` – The port for the SSRS service to listen on. The default is 8443. For a list of allowed values, see [Limitations and recommendations](Appendix.SQLServer.Options.SSRS.md#SSRS.Limitations).
+ `VpcSecurityGroupMemberships` – VPC security group memberships for your RDS DB instance.
+ `MAX_MEMORY` – The upper threshold above which no new memory allocation requests are granted to report server applications. The number is a percentage of the total memory of the DB instance. The allowed values are 10–80.
1. (Optional) Set the following parameters to use SSRS Email:
+ `SMTP_ENABLE_EMAIL` – Set to `true` to use SSRS Email. The default is `false`.
+ `SMTP_SENDER_EMAIL_ADDRESS` – The email address to use in the **From** field of messages sent by SSRS Email. Specify a user account that has permission to send mail from the SMTP server.
+ `SMTP_SERVER` – The SMTP server or gateway to use. It can be an IP address, the NetBIOS name of a computer on your corporate intranet, or a fully qualified domain name.
+ `SMTP_PORT` – The port to use to connect to the mail server. The default is 25.
+ `SMTP_USE_SSL` – Set to `true` to encrypt email messages using SSL. The default is `true`.
+ `SMTP_EMAIL_CREDENTIALS_SECRET_ARN` – The Secrets Manager ARN that holds the user credentials. Use the following format:
**arn:aws:secretsmanager:*Region*:*AccountId*:secret:*SecretName*-*6RandomCharacters***
For more information on creating the secret, see [Using SSRS Email to send reports](SSRS.Email.md).
+ `SMTP_USE_ANONYMOUS_AUTHENTICATION` – Set to `true` and don't include `SMTP_EMAIL_CREDENTIALS_SECRET_ARN` if you don't want to use authentication.
The default is `false` when `SMTP_ENABLE_EMAIL` is `true`.
The following example includes the SSRS Email parameters, using the secret ARN.
```
{
"OptionGroupName": "ssrs-se-2017",
"OptionsToInclude": [
{
"OptionName": "SSRS",
"Port": 8443,
"VpcSecurityGroupMemberships": ["sg-0abcdef123"],
"OptionSettings": [
{"Name": "MAX_MEMORY","Value": "60"},
{"Name": "SMTP_ENABLE_EMAIL","Value": "true"}
{"Name": "SMTP_SENDER_EMAIL_ADDRESS","Value": "nobody@example.com"},
{"Name": "SMTP_SERVER","Value": "email-smtp.us-west-2.amazonaws.com"},
{"Name": "SMTP_PORT","Value": "25"},
{"Name": "SMTP_USE_SSL","Value": "true"},
{"Name": "SMTP_EMAIL_CREDENTIALS_SECRET_ARN","Value": "arn:aws:secretsmanager:us-west-2:123456789012:secret:MySecret-a1b2c3"}
]
}],
"ApplyImmediately": true
}
```
1. Add the `SSRS` option to the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--cli-input-json file://ssrs-option.json \
--apply-immediately
```
For Windows:
```
aws rds add-option-to-option-group ^
--cli-input-json file://ssrs-option.json ^
--apply-immediately
```
## Associating your option group with your DB instance
Use the AWS Management Console or the AWS CLI to associate your option group with your DB instance.
If you use an existing DB instance, it must already have an Active Directory domain and AWS Identity and Access Management (IAM) role associated with it. If you create a new instance, specify an existing Active Directory domain and IAM role. For more information, see [Working with Active Directory with RDS for SQL Server](User.SQLServer.ActiveDirectoryWindowsAuth.md).
### Console
You can associate your option group with a new or existing DB instance:
+ For a new DB instance, associate the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, modify the instance and associate the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
### CLI
You can associate your option group with a new or existing DB instance.
**To create a DB instance that uses your option group**
+ Specify the same DB engine type and major version as you used when creating the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--db-instance-identifier myssrsinstance \
--db-instance-class db.m5.2xlarge \
--engine sqlserver-se \
--engine-version 14.00.3223.3.v1 \
--allocated-storage 100 \
--manage-master-user-password \
--master-username admin \
--storage-type gp2 \
--license-model li \
--domain-iam-role-name my-directory-iam-role \
--domain my-domain-id \
--option-group-name ssrs-se-2017
```
For Windows:
```
aws rds create-db-instance ^
--db-instance-identifier myssrsinstance ^
--db-instance-class db.m5.2xlarge ^
--engine sqlserver-se ^
--engine-version 14.00.3223.3.v1 ^
--allocated-storage 100 ^
--manage-master-user-password ^
--master-username admin ^
--storage-type gp2 ^
--license-model li ^
--domain-iam-role-name my-directory-iam-role ^
--domain my-domain-id ^
--option-group-name ssrs-se-2017
```
**To modify a DB instance to use your option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier myssrsinstance \
--option-group-name ssrs-se-2017 \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier myssrsinstance ^
--option-group-name ssrs-se-2017 ^
--apply-immediately
```
## Allowing inbound access to your VPC security group
To allow inbound access to the VPC security group associated with your DB instance, create an inbound rule for the specified SSRS listener port. For more information about setting up security groups, see [Provide access to your DB instance in your VPC by creating a security group](CHAP_SettingUp.md#CHAP_SettingUp.SecurityGroup).
## Report server databases
When your DB instance is associated with the SSRS option, two new databases are created on your DB instance:
+ `rdsadmin_ReportServer`
+ `rdsadmin_ReportServerTempDB`
These databases act as the ReportServer and ReportServerTempDB databases. SSRS stores its data in the ReportServer database and caches its data in the ReportServerTempDB database. For more information, see [Report Server Database](https://learn.microsoft.com/en-us/sql/reporting-services/report-server/report-server-database-ssrs-native-mode?view=sql-server-ver15) in the Microsoft documentation.
RDS owns and manages these databases, so database operations on them such as ALTER and DROP aren't permitted. Access isn't permitted on the `rdsadmin_ReportServerTempDB` database. However, you can perform read operations on the `rdsadmin_ReportServer`database.
## SSRS log files
You can list, view, and download SSRS log files. SSRS log files follow a naming convention of ReportServerService\$1*timestamp*.log. These report server logs are located in the `D:\rdsdbdata\Log\SSRS` directory. (The `D:\rdsdbdata\Log` directory is also the parent directory for error logs and SQL Server Agent logs.). For more information, see [Viewing and listing database log files](USER_LogAccess.Procedural.Viewing.md).
For existing SSRS instances, restarting the SSRS service might be necessary to access report server logs. You can restart the service by updating the `SSRS` option.
For more information, see [Working with Amazon RDS for Microsoft SQL Server logs](Appendix.SQLServer.CommonDBATasks.Logs.md).
# Accessing the SSRS web portal
Use the following process to access the SSRS web portal:
1. Turn on Secure Sockets Layer (SSL).
1. Grant access to domain users.
1. Access the web portal using a browser and the domain user credentials.
## Using SSL on RDS
SSRS uses the HTTPS SSL protocol for its connections. To work with this protocol, import an SSL certificate into the Microsoft Windows operating system on your client computer.
For more information on SSL certificates, see [Using SSL/TLS to encrypt a connection to a DB instance or cluster ](UsingWithRDS.SSL.md). For more information about using SSL with SQL Server, see [Using SSL with a Microsoft SQL Server DB instance](SQLServer.Concepts.General.SSL.Using.md).
## Granting access to domain users
In a new SSRS activation, there are no role assignments in SSRS. To give a domain user or user group access to the web portal, RDS provides a stored procedure.
**To grant access to a domain user on the web portal**
+ Use the following stored procedure.
```
exec msdb.dbo.rds_msbi_task
@task_type='SSRS_GRANT_PORTAL_PERMISSION',
@ssrs_group_or_username=N'AD_domain\user';
```
The domain user or user group is granted the `RDS_SSRS_ROLE` system role. This role has the following system-level tasks granted to it:
+ Run reports
+ Manage jobs
+ Manage shared schedules
+ View shared schedules
The item-level role of `Content Manager` on the root folder is also granted.
## Accessing the web portal
After the `SSRS_GRANT_PORTAL_PERMISSION` task finishes successfully, you have access to the portal using a web browser. The web portal URL has the following format.
```
https://rds_endpoint:port/Reports
```
In this format, the following applies:
+ *`rds_endpoint`* – The endpoint for the RDS DB instance that you're using with SSRS.
You can find the endpoint on the **Connectivity & security** tab for your DB instance. For more information, see [Connecting to your Microsoft SQL Server DB instance](USER_ConnectToMicrosoftSQLServerInstance.md).
+ `port` – The listener port for SSRS that you set in the `SSRS` option.
**To access the web portal**
1. Enter the web portal URL in your browser.
```
https://myssrsinstance.cg034itsfake.us-east-1.rds.amazonaws.com:8443/Reports
```
1. Log in with the credentials for a domain user that you granted access with the `SSRS_GRANT_PORTAL_PERMISSION` task.
# Deploying reports and configuring report data sources
Use the following procedures to deploy reports to SSRS and configure the reporting data sources:
**Topics**
+ [
## Deploying reports to SSRS
](#SSRS.Deploy)
+ [
## Configuring the report data source
](#SSRS.ConfigureDataSource)
## Deploying reports to SSRS
After you have access to the web portal, you can deploy reports to it. You can use the Upload tool in the web portal to upload reports, or deploy directly from [SQL Server data tools (SSDT)](https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt). When deploying from SSDT, ensure the following:
+ The user who launched SSDT has access to the SSRS web portal.
+ The `TargetServerURL` value in the SSRS project properties is set to the HTTPS endpoint of the RDS DB instance suffixed with `ReportServer`, for example:
```
https://myssrsinstance.cg034itsfake.us-east-1.rds.amazonaws.com:8443/ReportServer
```
## Configuring the report data source
After you deploy a report to SSRS, you should configure the report data source. When configuring the report data source, ensure the following:
+ For RDS for SQL Server DB instances joined to AWS Directory Service for Microsoft Active Directory, use the fully qualified domain name (FQDN) as the data source name of the connection string. An example is `myssrsinstance.corp-ad.example.com`, where `myssrsinstance` is the DB instance name and `corp-ad.example.com` is the fully qualified domain name.
+ For RDS for SQL Server DB instances joined to self-managed Active Directory, use `.`, or `LocalHost` as the data source name of the connection string.
# Using SSRS Email to send reports
SSRS includes the SSRS Email extension, which you can use to send reports to users.
To configure SSRS Email, use the `SSRS` option settings. For more information, see [Adding the SSRS option to your option group](SSRS.Enabling.md#SSRS.Add).
After configuring SSRS Email, you can subscribe to reports on the report server. For more information, see [Email delivery in Reporting Services](https://docs.microsoft.com/en-us/sql/reporting-services/subscriptions/e-mail-delivery-in-reporting-services) in the Microsoft documentation.
Integration with AWS Secrets Manager is required for SSRS Email to function on RDS. To integrate with Secrets Manager, you create a secret.
**Note**
If you change the secret later, you also have to update the `SSRS` option in the option group.
**To create a secret for SSRS Email**
1. Follow the steps in [Create a secret](https://docs.aws.amazon.com/secretsmanager/latest/userguide/create_secret.html) in the *AWS Secrets Manager User Guide*.
1. For **Select secret type**, choose **Other type of secrets**.
1. For **Key/value pairs**, enter the following:
+ **SMTP\$1USERNAME** – Enter a user with permission to send mail from the SMTP server.
+ **SMTP\$1PASSWORD** – Enter a password for the SMTP user.
1. For **Encryption key**, don't use the default AWS KMS key. Use your own existing key, or create a new one.
The KMS key policy must allow the `kms:Decrypt` action, for example:
```
{
"Sid": "Allow use of the key",
"Effect": "Allow",
"Principal": {
"Service": [
"rds.amazonaws.com"
]
},
"Action": [
"kms:Decrypt"
],
"Resource": "*"
}
```
1. Follow the steps in [Attach a permissions policy to a secret](https://docs.aws.amazon.com/secretsmanager/latest/userguide/auth-and-access_resource-policies.html) in the *AWS Secrets Manager User Guide*. The permissions policy gives the `secretsmanager:GetSecretValue` action to the `rds.amazonaws.com` service principal.
We recommend that you use the `aws:sourceAccount` and `aws:sourceArn` conditions in the policy to avoid the *confused deputy* problem. Use your AWS account for `aws:sourceAccount` and the option group ARN for `aws:sourceArn`. For more information, see [Preventing cross-service confused deputy problems](cross-service-confused-deputy-prevention.md).
The following example shows a permissions policy.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement" : [ {
"Effect" : "Allow",
"Principal" : {
"Service" : "rds.amazonaws.com"
},
"Action" : "secretsmanager:GetSecretValue",
"Resource" : "*",
"Condition" : {
"StringEquals" : {
"aws:sourceAccount" : "123456789012"
},
"ArnLike" : {
"aws:sourceArn" : "arn:aws:rds:us-west-2:123456789012:og:ssrs-se-2017"
}
}
} ]
}
```
------
For more examples, see [Permissions policy examples for AWS Secrets Manager](https://docs.aws.amazon.com/secretsmanager/latest/userguide/auth-and-access_examples.html) in the *AWS Secrets Manager User Guide*.
# Revoking system-level permissions
The `RDS_SSRS_ROLE` system role doesn't have sufficient permissions to delete system-level role assignments. To remove a user or user group from `RDS_SSRS_ROLE`, use the same stored procedure that you used to grant the role but use the `SSRS_REVOKE_PORTAL_PERMISSION` task type.
**To revoke access from a domain user for the web portal**
+ Use the following stored procedure.
```
exec msdb.dbo.rds_msbi_task
@task_type='SSRS_REVOKE_PORTAL_PERMISSION',
@ssrs_group_or_username=N'AD_domain\user';
```
Doing this deletes the user from the `RDS_SSRS_ROLE` system role. It also deletes the user from the `Content Manager` item-level role if the user has it.
# Monitoring the status of a task
To track the status of your granting or revoking task, call the `rds_fn_task_status` function. It takes two parameters. The first parameter should always be `NULL` because it doesn't apply to SSRS. The second parameter accepts a task ID.
To see a list of all tasks, set the first parameter to `NULL` and the second parameter to `0`, as shown in the following example.
```
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,0);
```
To get a specific task, set the first parameter to `NULL` and the second parameter to the task ID, as shown in the following example.
```
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,42);
```
The `rds_fn_task_status` function returns the following information.
| Output parameter | Description |
| --- | --- |
| `task_id` | The ID of the task. |
| `task_type` | For SSRS, tasks can have the following task types: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SSRS.Monitor.html) |
| `database_name` | Not applicable to SSRS tasks. |
| `% complete` | The progress of the task as a percentage. |
| `duration (mins)` | The amount of time spent on the task, in minutes. |
| `lifecycle` | The status of the task. Possible statuses are the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SSRS.Monitor.html) |
| `task_info` | Additional information about the task. If an error occurs during processing, this column contains information about the error. |
| `last_updated` | The date and time that the task status was last updated. |
| `created_at` | The date and time that the task was created. |
| `S3_object_arn` | Not applicable to SSRS tasks. |
| `overwrite_S3_backup_file` | Not applicable to SSRS tasks. |
| `KMS_master_key_arn` | Not applicable to SSRS tasks. |
| `filepath` | Not applicable to SSRS tasks. |
| `overwrite_file` | Not applicable to SSRS tasks. |
| `task_metadata` | Metadata associated with the SSRS task. |
# Disabling and deleting SSRS databases
Use the following procedures to disable SSRS and delete SSRS databases:
**Topics**
+ [
## Turning off SSRS
](#SSRS.Disable)
+ [
## Deleting the SSRS databases
](#SSRS.Drop)
## Turning off SSRS
To turn off SSRS, remove the `SSRS` option from its option group. Removing the option doesn't delete the SSRS databases. For more information, see [Deleting the SSRS databases](#SSRS.Drop).
You can turn SSRS on again by adding back the `SSRS` option. If you have also deleted the SSRS databases, readding the option on the same DB instance creates new report server databases.
### Console
**To remove the SSRS option from its option group**
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 **Option groups**.
1. Choose the option group with the `SSRS` option (`ssrs-se-2017` in the previous examples).
1. Choose **Delete option**.
1. Under **Deletion options**, choose **SSRS** for **Options to delete**.
1. Under **Apply immediately**, choose **Yes** to delete the option immediately, or **No** to delete it at the next maintenance window.
1. Choose **Delete**.
### CLI
**To remove the SSRS option from its option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds remove-option-from-option-group \
--option-group-name ssrs-se-2017 \
--options SSRS \
--apply-immediately
```
For Windows:
```
aws rds remove-option-from-option-group ^
--option-group-name ssrs-se-2017 ^
--options SSRS ^
--apply-immediately
```
## Deleting the SSRS databases
Removing the `SSRS` option doesn't delete the report server databases. To delete them, use the following stored procedure.
To delete the report server databases, be sure to remove the `SSRS` option first.
**To delete the SSRS databases**
+ Use the following stored procedure.
```
exec msdb.dbo.rds_drop_ssrs_databases
```
# Support for Microsoft Distributed Transaction Coordinator in RDS for SQL Server
A *distributed transaction* is a database transaction in which two or more network hosts are involved. RDS for SQL Server supports distributed transactions among hosts, where a single host can be one of the following:
+ RDS for SQL Server DB instance
+ On-premises SQL Server host
+ Amazon EC2 host with SQL Server installed
+ Any other EC2 host or RDS DB instance with a database engine that supports distributed transactions
In RDS, starting with SQL Server 2012 (version 11.00.5058.0.v1 and later), all editions of RDS for SQL Server support distributed transactions. The support is provided using Microsoft Distributed Transaction Coordinator (MSDTC). For in-depth information about MSDTC, see [Distributed Transaction Coordinator](https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms684146(v=vs.85)) in the Microsoft documentation.
**Contents**
+ [
## Limitations
](#Appendix.SQLServer.Options.MSDTC.Limitations)
+ [
# Enabling MSDTC
](Appendix.SQLServer.Options.MSDTC.Enabling.md)
+ [
## Creating the option group for MSDTC
](Appendix.SQLServer.Options.MSDTC.Enabling.md#Appendix.SQLServer.Options.MSDTC.OptionGroup)
+ [
## Adding the MSDTC option to the option group
](Appendix.SQLServer.Options.MSDTC.Enabling.md#Appendix.SQLServer.Options.MSDTC.Add)
+ [
## Creating the parameter group for MSDTC
](Appendix.SQLServer.Options.MSDTC.Enabling.md#MSDTC.CreateParamGroup)
+ [
## Modifying the parameter for MSDTC
](Appendix.SQLServer.Options.MSDTC.Enabling.md#ModifyParam.MSDTC)
+ [
## Associating the option group and parameter group with the DB instance
](Appendix.SQLServer.Options.MSDTC.Enabling.md#MSDTC.Apply)
+ [
## Modifying the MSDTC option
](Appendix.SQLServer.Options.MSDTC.Enabling.md#Appendix.SQLServer.Options.MSDTC.Modify)
+ [
## Using transactions
](#Appendix.SQLServer.Options.MSDTC.Using)
+ [
### Using distributed transactions
](#Appendix.SQLServer.Options.MSDTC.UsingXA)
+ [
### Using XA transactions
](#MSDTC.XA)
+ [
### Using transaction tracing
](#MSDTC.Tracing)
+ [
# Disabling MSDTC
](Appendix.SQLServer.Options.MSDTC.Disable.md)
+ [
# Troubleshooting MSDTC for RDS for SQL Server
](Appendix.SQLServer.Options.MSDTC.Troubleshooting.md)
## Limitations
The following limitations apply to using MSDTC on RDS for SQL Server:
+ MSDTC isn't supported on instances using SQL Server Database Mirroring. For more information, see [Transactions - availability groups and database mirroring](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/transactions-always-on-availability-and-database-mirroring?view=sql-server-ver15#non-support-for-distributed-transactions).
+ The `in-doubt xact resolution` parameter must be set to 1 or 2. For more information, see [Modifying the parameter for MSDTC](Appendix.SQLServer.Options.MSDTC.Enabling.md#ModifyParam.MSDTC).
+ MSDTC requires all hosts participating in distributed transactions to be resolvable using their host names. RDS automatically maintains this functionality for domain-joined instances. However, for standalone instances make sure to configure the DNS server manually.
+ Java Database Connectivity (JDBC) XA transactions are supported for SQL Server 2017 version 14.00.3223.3 and higher, and SQL Server 2019.
+ Distributed transactions that depend on client dynamic link libraries (DLLs) on RDS instances aren't supported.
+ Using custom XA dynamic link libraries isn't supported.
# Enabling MSDTC
Use the following process to enable MSDTC for your DB instance:
1. Create a new option group, or choose an existing option group.
1. Add the `MSDTC` option to the option group.
1. Create a new parameter group, or choose an existing parameter group.
1. Modify the parameter group to set the `in-doubt xact resolution` parameter to 1 or 2.
1. Associate the option group and parameter group with the DB instance.
## Creating the option group for MSDTC
Use the AWS Management Console or the AWS CLI to create an option group that corresponds to the SQL Server engine and version of your DB instance.
**Note**
You can also use an existing option group if it's for the correct SQL Server engine and version.
### Console
The following procedure creates an option group for SQL Server Standard Edition 2016.
**To create the option group**
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 **Option groups**.
1. Choose **Create group**.
1. In the **Create option group** pane, do the following:
1. For **Name**, enter a name for the option group that is unique within your AWS account, such as **msdtc-se-2016**. The name can contain only letters, digits, and hyphens.
1. For **Description**, enter a brief description of the option group, such as **MSDTC option group for SQL Server SE 2016**. The description is used for display purposes.
1. For **Engine**, choose **sqlserver-se**.
1. For **Major engine version**, choose **13.00**.
1. Choose **Create**.
### CLI
The following example creates an option group for SQL Server Standard Edition 2016.
**To create the option group**
+ Use one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-option-group \
--option-group-name msdtc-se-2016 \
--engine-name sqlserver-se \
--major-engine-version 13.00 \
--option-group-description "MSDTC option group for SQL Server SE 2016"
```
For Windows:
```
aws rds create-option-group ^
--option-group-name msdtc-se-2016 ^
--engine-name sqlserver-se ^
--major-engine-version 13.00 ^
--option-group-description "MSDTC option group for SQL Server SE 2016"
```
## Adding the MSDTC option to the option group
Next, use the AWS Management Console or the AWS CLI to add the `MSDTC` option to the option group.
The following option settings are required:
+ **Port** – The port that you use to access MSDTC. Allowed values are 1150–49151 except for 1234, 1434, 3260, 3343, 3389, and 47001. The default value is 5000.
Make sure that the port you want to use is enabled in your firewall rules. Also, make sure as needed that this port is enabled in the inbound and outbound rules for the security group associated with your DB instance. For more information, see [Can't connect to Amazon RDS DB instance](CHAP_Troubleshooting.md#CHAP_Troubleshooting.Connecting).
+ **Security groups** – The VPC security group memberships for your RDS DB instance.
+ **Authentication type** – The authentication mode between hosts. The following authentication types are supported:
+ Mutual – The RDS instances are mutually authenticated to each other using integrated authentication. If this option is selected, all instances associated with this option group must be domain-joined.
+ None – No authentication is performed between hosts. We don't recommend using this mode in production environments.
+ **Transaction log size** – The size of the MSDTC transaction log. Allowed values are 4–1024 MB. The default size is 4 MB.
The following option settings are optional:
+ **Enable inbound connections** – Whether to allow inbound MSDTC connections to instances associated with this option group.
+ **Enable outbound connections** – Whether to allow outbound MSDTC connections from instances associated with this option group.
+ **Enable XA** – Whether to allow XA transactions. For more information on the XA protocol, see [XA specification](https://publications.opengroup.org/c193).
+ **Enable SNA LU** – Whether to allow the SNA LU protocol to be used for distributed transactions. For more information on SNA LU protocol support, see [Managing IBM CICS LU 6.2 transactions](https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms685136(v=vs.85)) in the Microsoft documentation.
### Console
**To add the MSDTC option**
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 **Option groups**.
1. Choose the option group that you just created.
1. Choose **Add option**.
1. Under **Option details**, choose **MSDTC** for **Option name**.
1. Under **Option settings**:
1. For **Port**, enter the port number for accessing MSDTC. The default is **5000**.
1. For **Security groups**, choose the VPC security group to associate with the option.
1. For **Authentication type**, choose **Mutual** or **None**.
1. For **Transaction log size**, enter a value from 4–1024. The default is **4**.
1. Under **Additional configuration**, do the following:
1. For **Connections**, as needed choose **Enable inbound connections** and **Enable outbound connections**.
1. For **Allowed protocols**, as needed choose **Enable XA** and **Enable SNA LU**.
1. Under **Scheduling**, choose whether to add the option immediately or at the next maintenance window.
1. Choose **Add option**.
To add this option, no reboot is required.
### CLI
**To add the MSDTC option**
1. Create a JSON file, for example `msdtc-option.json`, with the following required parameters.
```
{
"OptionGroupName":"msdtc-se-2016",
"OptionsToInclude": [
{
"OptionName":"MSDTC",
"Port":5000,
"VpcSecurityGroupMemberships":["sg-0abcdef123"],
"OptionSettings":[{"Name":"AUTHENTICATION","Value":"MUTUAL"},{"Name":"TRANSACTION_LOG_SIZE","Value":"4"}]
}],
"ApplyImmediately": true
}
```
1. Add the `MSDTC` option to the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--cli-input-json file://msdtc-option.json \
--apply-immediately
```
For Windows:
```
aws rds add-option-to-option-group ^
--cli-input-json file://msdtc-option.json ^
--apply-immediately
```
No reboot is required.
## Creating the parameter group for MSDTC
Create or modify a parameter group for the `in-doubt xact resolution` parameter that corresponds to the SQL Server edition and version of your DB instance.
### Console
The following example creates a parameter group for SQL Server Standard Edition 2016.
**To create the parameter group**
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 **Parameter groups**.
1. Choose **Create parameter group**.
1. In the **Create parameter group** pane, do the following:
1. For **Parameter group family**, choose **sqlserver-se-13.0**.
1. For **Group name**, enter an identifier for the parameter group, such as **msdtc-sqlserver-se-13**.
1. For **Description**, enter **in-doubt xact resolution**.
1. Choose **Create**.
### CLI
The following example creates a parameter group for SQL Server Standard Edition 2016.
**To create the parameter group**
+ Use one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-db-parameter-group \
--db-parameter-group-name msdtc-sqlserver-se-13 \
--db-parameter-group-family "sqlserver-se-13.0" \
--description "in-doubt xact resolution"
```
For Windows:
```
aws rds create-db-parameter-group ^
--db-parameter-group-name msdtc-sqlserver-se-13 ^
--db-parameter-group-family "sqlserver-se-13.0" ^
--description "in-doubt xact resolution"
```
## Modifying the parameter for MSDTC
Modify the `in-doubt xact resolution` parameter in the parameter group that corresponds to the SQL Server edition and version of your DB instance.
For MSDTC, set the `in-doubt xact resolution` parameter to one of the following:
+ `1` – `Presume commit`. Any MSDTC in-doubt transactions are presumed to have committed.
+ `2` – `Presume abort`. Any MSDTC in-doubt transactions are presumed to have stopped.
For more information, see [in-doubt xact resolution server configuration option](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/in-doubt-xact-resolution-server-configuration-option) in the Microsoft documentation.
### Console
The following example modifies the parameter group that you created for SQL Server Standard Edition 2016.
**To modify the parameter group**
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 **Parameter groups**.
1. Choose the parameter group, such as **msdtc-sqlserver-se-13**.
1. Under **Parameters**, filter the parameter list for **xact**.
1. Choose **in-doubt xact resolution**.
1. Choose **Edit parameters**.
1. Enter **1** or **2**.
1. Choose **Save changes**.
### CLI
The following example modifies the parameter group that you created for SQL Server Standard Edition 2016.
**To modify the parameter group**
+ Use one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name msdtc-sqlserver-se-13 \
--parameters "ParameterName='in-doubt xact resolution',ParameterValue=1,ApplyMethod=immediate"
```
For Windows:
```
aws rds modify-db-parameter-group ^
--db-parameter-group-name msdtc-sqlserver-se-13 ^
--parameters "ParameterName='in-doubt xact resolution',ParameterValue=1,ApplyMethod=immediate"
```
## Associating the option group and parameter group with the DB instance
You can use the AWS Management Console or the AWS CLI to associate the MSDTC option group and parameter group with the DB instance.
### Console
You can associate the MSDTC option group and parameter group with a new or existing DB instance.
+ For a new DB instance, associate them when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, associate them by modifying the instance. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
**Note**
If you use an domain-joined existing DB instance, it must already have an Active Directory domain and AWS Identity and Access Management (IAM) role associated with it. If you create a new domain-joined instance, specify an existing Active Directory domain and IAM role. For more information, see [Working with AWS Managed Active Directory with RDS for SQL Server](USER_SQLServerWinAuth.md).
### CLI
You can associate the MSDTC option group and parameter group with a new or existing DB instance.
**Note**
If you use an existing domain-joined DB instance, it must already have an Active Directory domain and IAM role associated with it. If you create a new domain-joined instance, specify an existing Active Directory domain and IAM role. For more information, see [Working with AWS Managed Active Directory with RDS for SQL Server](USER_SQLServerWinAuth.md).
**To create a DB instance with the MSDTC option group and parameter group**
+ Specify the same DB engine type and major version as you used when creating the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--db-instance-identifier mydbinstance \
--db-instance-class db.m5.2xlarge \
--engine sqlserver-se \
--engine-version 13.00.5426.0.v1 \
--allocated-storage 100 \
--manage-master-user-password \
--master-username admin \
--storage-type gp2 \
--license-model li \
--domain-iam-role-name my-directory-iam-role \
--domain my-domain-id \
--option-group-name msdtc-se-2016 \
--db-parameter-group-name msdtc-sqlserver-se-13
```
For Windows:
```
aws rds create-db-instance ^
--db-instance-identifier mydbinstance ^
--db-instance-class db.m5.2xlarge ^
--engine sqlserver-se ^
--engine-version 13.00.5426.0.v1 ^
--allocated-storage 100 ^
--manage-master-user-password ^
--master-username admin ^
--storage-type gp2 ^
--license-model li ^
--domain-iam-role-name my-directory-iam-role ^
--domain my-domain-id ^
--option-group-name msdtc-se-2016 ^
--db-parameter-group-name msdtc-sqlserver-se-13
```
**To modify a DB instance and associate the MSDTC option group and parameter group**
+ Use one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mydbinstance \
--option-group-name msdtc-se-2016 \
--db-parameter-group-name msdtc-sqlserver-se-13 \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mydbinstance ^
--option-group-name msdtc-se-2016 ^
--db-parameter-group-name msdtc-sqlserver-se-13 ^
--apply-immediately
```
## Modifying the MSDTC option
After you enable the `MSDTC` option, you can modify its settings. For information about how to modify option settings, see [Modifying an option setting](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.ModifyOption).
**Note**
Some changes to MSDTC option settings require the MSDTC service to be restarted. This requirement can affect running distributed transactions.
## Using transactions
### Using distributed transactions
In Amazon RDS for SQL Server, you run distributed transactions in the same way as distributed transactions running on-premises:
+ Using .NET framework `System.Transactions` promotable transactions, which optimizes distributed transactions by deferring their creation until they're needed.
In this case, promotion is automatic and doesn't require you to make any intervention. If there's only one resource manager within the transaction, no promotion is performed. For more information about implicit transaction scopes, see [Implementing an implicit transaction using transaction scope](https://docs.microsoft.com/en-us/dotnet/framework/data/transactions/implementing-an-implicit-transaction-using-transaction-scope) in the Microsoft documentation.
Promotable transactions are supported with these .NET implementations:
+ Starting with ADO.NET 2.0, `System.Data.SqlClient` supports promotable transactions with SQL Server. For more information, see [System.Transactions integration with SQL Server](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/system-transactions-integration-with-sql-server) in the Microsoft documentation.
+ ODP.NET supports `System.Transactions`. A local transaction is created for the first connection opened in the `TransactionsScope` scope to Oracle Database 11g release 1 (version 11.1) and later. When a second connection is opened, this transaction is automatically promoted to a distributed transaction. For more information about distributed transaction support in ODP.NET, see [Microsoft Distributed Transaction Coordinator integration](https://docs.oracle.com/en/database/oracle/oracle-data-access-components/18.3/ntmts/using-mts-with-oracledb.html) in the Microsoft documentation.
+ Using the `BEGIN DISTRIBUTED TRANSACTION` statement. For more information, see [BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-distributed-transaction-transact-sql) in the Microsoft documentation.
### Using XA transactions
Starting from RDS for SQL Server 2017 version14.00.3223.3, you can control distributed transactions using JDBC. When you set the `Enable XA` option setting to `true` in the `MSDTC` option, RDS automatically enables JDBC transactions and grants the `SqlJDBCXAUser` role to the `guest` user. This allows executing distributed transactions through JDBC. For more information, including a code example, see [Understanding XA transactions](https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-xa-transactions) in the Microsoft documentation.
### Using transaction tracing
RDS supports controlling MSDTC transaction traces and downloading them from the RDS DB instance for troubleshooting. You can control transaction tracing sessions by running the following RDS stored procedure.
```
exec msdb.dbo.rds_msdtc_transaction_tracing 'trace_action',
[@traceall='0|1'],
[@traceaborted='0|1'],
[@tracelong='0|1'];
```
The following parameter is required:
+ `trace_action` – The tracing action. It can be `START`, `STOP`, or `STATUS`.
The following parameters are optional:
+ `@traceall` – Set to 1 to trace all distributed transactions. The default is 0.
+ `@traceaborted` – Set to 1 to trace canceled distributed transactions. The default is 0.
+ `@tracelong` – Set to 1 to trace long-running distributed transactions. The default is 0.
**Example of START tracing action**
To start a new transaction tracing session, run the following example statement.
```
exec msdb.dbo.rds_msdtc_transaction_tracing 'START',
@traceall='0',
@traceaborted='1',
@tracelong='1';
```
Only one transaction tracing session can be active at one time. If a new tracing session `START` command is issued while a tracing session is active, an error is returned and the active tracing session remains unchanged.
**Example of STOP tracing action**
To stop a transaction tracing session, run the following statement.
```
exec msdb.dbo.rds_msdtc_transaction_tracing 'STOP'
```
This statement stops the active transaction tracing session and saves the transaction trace data into the log directory on the RDS DB instance. The first row of the output contains the overall result, and the following lines indicate details of the operation.
The following is an example of a successful tracing session stop.
```
OK: Trace session has been successfully stopped.
Setting log file to: D:\rdsdbdata\MSDTC\Trace\dtctrace.log
Examining D:\rdsdbdata\MSDTC\Trace\msdtctr.mof for message formats, 8 found.
Searching for TMF files on path: (null)
Logfile D:\rdsdbdata\MSDTC\Trace\dtctrace.log:
OS version 10.0.14393 (Currently running on 6.2.9200)
Start Time
End Time
Timezone is @tzres.dll,-932 (Bias is 0mins)
BufferSize 16384 B
Maximum File Size 10 MB
Buffers Written Not set (Logger may not have been stopped).
Logger Mode Settings (11000002) ( circular paged
ProcessorCount 1
Processing completed Buffers: 1, Events: 3, EventsLost: 0 :: Format Errors: 0, Unknowns: 3
Event traces dumped to d:\rdsdbdata\Log\msdtc_.log
```
You can use the detailed information to query the name of the generated log file. For more information about downloading log files from the RDS DB instance, see [Monitoring Amazon RDS log files](USER_LogAccess.md).
The trace session logs remain on the instance for 35 days. Any older trace session logs are automatically deleted.
**Example of STATUS tracing action**
To trace the status of a transaction tracing session, run the following statement.
```
exec msdb.dbo.rds_msdtc_transaction_tracing 'STATUS'
```
This statement outputs the following as separate rows of the result set.
```
OK
SessionStatus:
TraceAll:
TraceAborted:
TraceLongLived:
```
The first line indicates the overall result of the operation: `OK` or `ERROR` with details, if applicable. The subsequent lines indicate details about the tracing session status:
+ `SessionStatus` can be one of the following:
+ `Started` if a tracing session is running.
+ `Stopped` if no tracing session is running.
+ The tracing session flags can be `True` or `False` depending on how they were set in the `START` command.
# Disabling MSDTC
To disable MSDTC, remove the `MSDTC` option from its option group.
## Console
**To remove the MSDTC option from its option group**
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 **Option groups**.
1. Choose the option group with the `MSDTC` option (`msdtc-se-2016` in the previous examples).
1. Choose **Delete option**.
1. Under **Deletion options**, choose **MSDTC** for **Options to delete**.
1. Under **Apply immediately**, choose **Yes** to delete the option immediately, or **No** to delete it at the next maintenance window.
1. Choose **Delete**.
## CLI
**To remove the MSDTC option from its option group**
+ Use one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds remove-option-from-option-group \
--option-group-name msdtc-se-2016 \
--options MSDTC \
--apply-immediately
```
For Windows:
```
aws rds remove-option-from-option-group ^
--option-group-name msdtc-se-2016 ^
--options MSDTC ^
--apply-immediately
```
# Troubleshooting MSDTC for RDS for SQL Server
In some cases, you might have trouble establishing a connection between MSDTC running on a client computer and the MSDTC service running on an RDS for SQL Server DB instance. If so, make sure of the following:
+ The inbound rules for the security group associated with the DB instance are configured correctly. For more information, see [Can't connect to Amazon RDS DB instance](CHAP_Troubleshooting.md#CHAP_Troubleshooting.Connecting).
+ Your client computer is configured correctly.
+ The MSDTC firewall rules on your client computer are enabled.
**To configure the client computer**
1. Open **Component Services**.
Or, in **Server Manager**, choose **Tools**, and then choose **Component Services**.
1. Expand **Component Services**, expand **Computers**, expand **My Computer**, and then expand **Distributed Transaction Coordinator**.
1. Open the context (right-click) menu for **Local DTC** and choose **Properties**.
1. Choose the **Security** tab.
1. Choose all of the following:
+ **Network DTC Access**
+ **Allow Inbound**
+ **Allow Outbound**
1. Make sure that the correct authentication mode is chosen:
+ **Mutual Authentication Required** – The client machine is joined to the same domain as other nodes participating in distributed transaction, or there is a trust relationship configured between domains.
+ **No Authentication Required** – All other cases.
1. Choose **OK** to save your changes.
1. If prompted to restart the service, choose **Yes**.
**To enable MSDTC firewall rules**
1. Open Windows Firewall, then choose **Advanced settings**.
Or, in **Server Manager**, choose **Tools**, and then choose **Windows Firewall with Advanced Security**.
**Note**
Depending on your operating system, Windows Firewall might be called Windows Defender Firewall.
1. Choose **Inbound Rules** in the left pane.
1. Enable the following firewall rules, if they are not already enabled:
+ **Distributed Transaction Coordinator (RPC)**
+ **Distributed Transaction Coordinator (RPC)-EPMAP**
+ **Distributed Transaction Coordinator (TCP-In)**
1. Close Windows Firewall.
# Microsoft SQL Server resource governor with RDS for SQL Server
Resource governor is a SQL Server Enterprise Edition feature that gives you precise control over your instance resources. It enables you to set specific limits on how workloads use CPU, memory, and physical I/O resources. With resource governor, you can:
+ Prevent resource monopolization in multi-tenant environments by managing how different workloads share instance resources
+ Deliver predictable performance by setting specific resource limits and priorities for different users and applications
You can enable resource governor on either an existing or new RDS for SQL Server DB instance.
Resource governor uses three fundamental concepts:
+ **Resource pool** - A container that manages your instance physical resources (CPU, memory, and I/O). You get two built-in pools (internal and default) and you can create additional custom pools.
+ **Workload group** - A container for database sessions with similar characteristics. Every workload group belongs to a resource pool. You get two built-in workload groups (internal and default) and you can create additional custom workload groups.
+ **Classification** - The process that determines which workload group handles incoming sessions based on user name, application name, database name or host name.
For additional details about resource governor functionality in SQL Server, see [Resource Governor](https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver16) in the Microsoft documentation.
**Contents**
+ [
## Supported versions and Regions
](#ResourceGovernor.SupportedVersions)
+ [
## Limitations and recommendations
](#ResourceGovernor.Limitations)
+ [
# Enabling Microsoft SQL Server resource governor for your RDS for SQL Server instance
](ResourceGovernor.Enabling.md)
+ [
## Creating the option group for `RESOURCE_GOVERNOR`
](ResourceGovernor.Enabling.md#ResourceGovernor.OptionGroup)
+ [
## Adding the `RESOURCE_GOVERNOR` option to the option group
](ResourceGovernor.Enabling.md#ResourceGovernor.Add)
+ [
## Associating the option group with your DB instance
](ResourceGovernor.Enabling.md#ResourceGovernor.Apply)
+ [
# Using Microsoft SQL Server resource governor for your RDS for SQL Server instance
](ResourceGovernor.Using.md)
+ [
## Manage resource pool
](ResourceGovernor.Using.md#ResourceGovernor.ManageResourcePool)
+ [
### Create resource Pool
](ResourceGovernor.Using.md#ResourceGovernor.CreateResourcePool)
+ [
### Alter resource pool
](ResourceGovernor.Using.md#ResourceGovernor.AlterResourcePool)
+ [
### Drop resource pool
](ResourceGovernor.Using.md#ResourceGovernor.DropResourcePool)
+ [
## Manage workload groups
](ResourceGovernor.Using.md#ResourceGovernor.ManageWorkloadGroups)
+ [
### Create workload group
](ResourceGovernor.Using.md#ResourceGovernor.CreateWorkloadGroup)
+ [
### Alter workload group
](ResourceGovernor.Using.md#ResourceGovernor.AlterWorkloadGroup)
+ [
### Drop workload group
](ResourceGovernor.Using.md#ResourceGovernor.DropWorkloadGroup)
+ [
## Create and register classifier function
](ResourceGovernor.Using.md#ResourceGovernor.ClassifierFunction)
+ [
## Drop classifier function
](ResourceGovernor.Using.md#ResourceGovernor.DropClassifier)
+ [
## De-register classifier function
](ResourceGovernor.Using.md#ResourceGovernor.DeregisterClassifier)
+ [
## Reset statistics
](ResourceGovernor.Using.md#ResourceGovernor.ResetStats)
+ [
## Resource governor configuration changes
](ResourceGovernor.Using.md#ResourceGovernor.ConfigChanges)
+ [
## Bind TempDB to a resource pool
](ResourceGovernor.Using.md#ResourceGovernor.BindTempDB)
+ [
## Unbind TempDB from a resource pool
](ResourceGovernor.Using.md#ResourceGovernor.UnbindTempDB)
+ [
## Cleanup resource governor
](ResourceGovernor.Using.md#ResourceGovernor.Cleanup)
+ [
## Considerations for Multi-AZ deployment
](#ResourceGovernor.Considerations)
+ [
## Considerations for read replicas
](#ResourceGovernor.ReadReplica)
+ [
# Monitor Microsoft SQL Server resource governor using system views for your RDS for SQL Server instance
](ResourceGovernor.Monitoring.md)
+ [
## Resource pool runtime statistics
](ResourceGovernor.Monitoring.md#ResourceGovernor.ResourcePoolStats)
+ [
# Disabling Microsoft SQL Server resource governor for your RDS for SQL Server instance
](ResourceGovernor.Disabling.md)
+ [
# Best practices for configuring resource governor on RDS for SQL Server
](ResourceGovernor.BestPractices.md)
## Supported versions and Regions
Amazon RDS supports resource governor for the following SQL Server versions and editions in all AWS Regions where RDS for SQL Server is available:
+ SQL Server 2022 Developer and Enterprise Editions
+ SQL Server 2019 Enterprise Edition
+ SQL Server 2017 Enterprise Edition
+ SQL Server 2016 Enterprise Edition
## Limitations and recommendations
The following limitations and recommendations apply to resource governor:
+ Edition and service restrictions:
+ Available only in SQL Server Enterprise Edition.
+ Resource management is limited to the SQL Server Database Engine. Resource governor for Analysis Services, Integration Services, and Reporting Services are not supported.
+ Configuration restrictions:
+ Must use Amazon RDS stored procedures for all configurations.
+ Native DDL statements and SQL Server Management Studio GUI configurations aren't supported.
+ Resource pool parameters:
+ Pool names starting with `rds_` aren't supported.
+ Internal and default resource pool modifications aren't permitted.
+ For the user-defined resource pools the following resource pool parameters aren't supported:
+ `MIN_MEMORY_PERCENT`
+ `MIN_CPU_PERCENT`
+ `MIN_IOPS_PER_VOLUME`
+ `AFFINITY`
+ Workload group parameters:
+ Workload group names starting with `rds_` aren't supported.
+ Internal workload group modification isn't permitted.
+ For the default workload group:
+ Only the `REQUEST_MAX_MEMORY_GRANT_PERCENT` parameter can be modified.
+ For the default workload group, `REQUEST_MAX_MEMORY_GRANT_PERCENT` must be between 1 and 70.
+ All other parameters are locked and can't be changed.
+ User-defined workload groups allow modification of all parameters.
+ Classifier function limitations:
+ Classifier function routes connections to custom workload groups based on specified criteria (user name, database, host, or application name).
+ Supports up to two user-defined workload groups with their respective routing conditions.
+ Combines criterion with `AND` conditions within each group.
+ Requires at least one routing criterion per workload group.
+ Only the classification methods listed above are supported.
+ Function name must start with `rg_classifier_`.
+ Default group assignment if no conditions match.
# Enabling Microsoft SQL Server resource governor for your RDS for SQL Server instance
Enable resource governor by adding the `RESOURCE_GOVERNOR` option to your RDS for SQL Server DB instance. Use the following process:
1. Create a new option group, or choose an existing option group.
1. Add the `RESOURCE_GOVERNOR` option to the option group.
1. Associate the option group with the DB instance.
**Note**
Enabling resource governor through an option group doesn't require a reboot.
## Creating the option group for `RESOURCE_GOVERNOR`
To enable resource governor, create an option group or modify an option group that corresponds to the SQL Server edition and version of the DB instance that you plan to use. To complete this procedure, use the AWS Management Console or the AWS CLI.
### Console
Use the following procedure to create an option group for SQL Server Enterprise Edition 2022.
**To create the option group**
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 **Option groups**.
1. Choose **Create group**.
1. In the **Create option group** window, do the following:
1. For **Name**, enter a name for the option group that is unique within your AWS account, such as **resource-governor-ee-2022**. The name can contain only letters, digits, and hyphens.
1. For **Description**, enter a brief description of the option group, such as **RESOURCE\$1GOVERNOR option group for SQL Server EE 2022**. The description is used for display purposes.
1. For **Engine**, choose **sqlserver-ee**.
1. For **Major engine version**, choose **16.00**.
1. Choose **Create**.
### CLI
The following procedure creates an option group for SQL Server Enterprise Edition 2022.
**To create the option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-option-group \
--option-group-name resource-governor-ee-2022 \
--engine-name sqlserver-ee \
--major-engine-version 16.00 \
--option-group-description "RESOURCE_GOVERNOR option group for SQL Server EE 2022"
```
For Windows:
```
aws rds create-option-group ^
--option-group-name resource-governor-ee-2022 ^
--engine-name sqlserver-ee ^
--major-engine-version 16.00 ^
--option-group-description "RESOURCE_GOVERNOR option group for SQL Server EE 2022"
```
## Adding the `RESOURCE_GOVERNOR` option to the option group
Next, use the AWS Management Console or the AWS CLI to add the `RESOURCE_GOVERNOR` option to your option group.
### Console
**To add the RESOURCE\$1GOVERNOR option**
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 **Option groups**.
1. Choose the option group that you just created, **resource-governor-ee-2022** in this example.
1. Choose **Add option**.
1. Under **Option details**, choose **RESOURCE\$1GOVERNOR** for **Option name**.
1. Under **Scheduling**, choose whether to add the option immediately or at the next maintenance window.
1. Choose **Add option**.
### CLI
**To add the `RESOURCE_GOVERNOR` option**
+ Add the `RESOURCE_GOVERNOR` option to the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-option-to-option-group \
--option-group-name resource-governor-ee-2022 \
--options "OptionName=RESOURCE_GOVERNOR" \
--apply-immediately
```
For Windows:
```
aws rds add-option-to-option-group ^
--option-group-name resource-governor-ee-2022 ^
--options "OptionName=RESOURCE_GOVERNOR" ^
--apply-immediately
```
## Associating the option group with your DB instance
To associate the `RESOURCE_GOVERNOR` option group with your DB instance, use the AWS Management Console or the AWS CLI.
### Console
To finish activating resource governor, associate your `RESOURCE_GOVERNOR` option group with a new or existing DB instance:
+ For a new DB instance, associate them when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, associate them by modifying the instance. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
### CLI
You can associate the `RESOURCE_GOVERNOR` option group with a new or existing DB instance.
**To create an instance with the `RESOURCE_GOVERNOR` option group**
+ Specify the same DB engine type and major version that you used when creating the option group.
**Example**
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--db-instance-identifier mytestsqlserverresourcegovernorinstance \
--db-instance-class db.m5.2xlarge \
--engine sqlserver-ee \
--engine-version 16.00 \
--license-model license-included \
--allocated-storage 100 \
--master-username admin \
--master-user-password password \
--storage-type gp2 \
--option-group-name resource-governor-ee-2022
```
For Windows:
```
aws rds create-db-instance ^
--db-instance-identifier mytestsqlserverresourcegovernorinstance ^
--db-instance-class db.m5.2xlarge ^
--engine sqlserver-ee ^
--engine-version 16.00 ^
--license-model license-included ^
--allocated-storage 100 ^
--master-username admin ^
--master-user-password password ^
--storage-type gp2 ^
--option-group-name resource-governor-ee-2022
```
**To modify an instance and associate the `RESOURCE_GOVERNOR` option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mytestinstance \
--option-group-name resource-governor-ee-2022 \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mytestinstance ^
--option-group-name resource-governor-ee-2022 ^
--apply-immediately
```
# Using Microsoft SQL Server resource governor for your RDS for SQL Server instance
After adding the resource governor option to your option group, resource governor is not yet active at the database engine level. To fully enable resource governor, you must use RDS for SQL Server stored procedures to enable it and create the necessary resource governor objects. For more information, see [Connecting to your Microsoft SQL Server DB instance](USER_ConnectToMicrosoftSQLServerInstance.md).
First, connect to your SQL Server database, then call the appropriate RDS for SQL Server stored procedures to complete the configuration. For instructions on connecting to your database, see [Connecting to your Microsoft SQL Server DB instance](USER_ConnectToMicrosoftSQLServerInstance.md).
For instructions on how to call each stored procedure, see the following topics:
**Topics**
+ [
## Manage resource pool
](#ResourceGovernor.ManageResourcePool)
+ [
## Manage workload groups
](#ResourceGovernor.ManageWorkloadGroups)
+ [
## Create and register classifier function
](#ResourceGovernor.ClassifierFunction)
+ [
## Drop classifier function
](#ResourceGovernor.DropClassifier)
+ [
## De-register classifier function
](#ResourceGovernor.DeregisterClassifier)
+ [
## Reset statistics
](#ResourceGovernor.ResetStats)
+ [
## Resource governor configuration changes
](#ResourceGovernor.ConfigChanges)
+ [
## Bind TempDB to a resource pool
](#ResourceGovernor.BindTempDB)
+ [
## Unbind TempDB from a resource pool
](#ResourceGovernor.UnbindTempDB)
+ [
## Cleanup resource governor
](#ResourceGovernor.Cleanup)
## Manage resource pool
### Create resource Pool
Once resource governor is enabled on the option group, you can create custom resource pools using `rds_create_resource_pool`. These pools let you allocate specific percentages of CPU, memory, and IOPS to different workloads.
**Usage**
```
USE [msdb]
EXEC dbo.rds_create_resource_pool
@pool_name=value,
@MAX_CPU_PERCENT=value,
@CAP_CPU_PERCENT=value,
@MAX_MEMORY_PERCENT=value,
@MAX_IOPS_PER_VOLUME=value
```
The following parameters are required:
+ `@group_name` - Is the name of an existing user-defined workload group.
+ `@pool_name` - Is the user-defined name for the resource pool. *pool\$1name* is alphanumeric, can be up to 128 characters, must be unique within a Database Engine instance, and must comply with the rules for database identifiers.
The following parameters are optional:
+ `@MAX_CPU_PERCENT` - Specifies the maximum average CPU bandwidth that all requests in resource pool receive when there's CPU contention. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@CAP_CPU_PERCENT` - Specifies a hard cap on the CPU bandwidth that all requests in the resource pool receive. Limits the maximum CPU bandwidth level to be the same as the specified value. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@MAX_MEMORY_PERCENT` - Specifies the maximum amount of query workspace memory that requests in this resource pool can use. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@MAX_IOPS_PER_VOLUME` - Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool. The allowed range for *value* is from 0 through 2^31-1 (2,147,483,647). Specify 0 to remove an IOPS limit for the pool. The default is 0.
**Examples**
Example of creating resource pool with all default values:
```
--This creates resource pool 'SalesPool' with all default values
USE [msdb]
EXEC rds_create_resource_pool @pool_name = 'SalesPool';
--Apply changes
USE [msdb]
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
--Validate configuration
select * from sys.resource_governor_resource_pools
```
Example of creating resource pool with different parameters specified:
```
--creates resource pool
USE [msdb]
EXEC dbo.rds_create_resource_pool
@pool_name='analytics',
@MAX_CPU_PERCENT = 30,
@CAP_CPU_PERCENT = 40,
@MAX_MEMORY_PERCENT = 20;
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
--Validate configuration
select * from sys.resource_governor_resource_pools
```
### Alter resource pool
**Usage**
```
USE [msdb]
EXEC dbo.rds_alter_resource_pool
@pool_name=value,
@MAX_CPU_PERCENT=value,
@CAP_CPU_PERCENT=value,
@MAX_MEMORY_PERCENT=value,
@MAX_IOPS_PER_VOLUME=value;
```
The following parameters are required:
+ `@pool_name` - Is the name of an existing user-defined resource pool. Altering default resource pool isn't allowed in Amazon RDS SQL Server.
At least one of the optional parameter must be specified:
+ `@MAX_CPU_PERCENT` - Specifies the maximum average CPU bandwidth that all requests in resource pool receive when there's CPU contention. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@CAP_CPU_PERCENT` - Specifies a hard cap on the CPU bandwidth that all requests in the resource pool receive. Limits the maximum CPU bandwidth level to be the same as the specified value. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@MAX_MEMORY_PERCENT` - Specifies the maximum amount of query workspace memory that requests in this resource pool can use. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@MAX_IOPS_PER_VOLUME` - Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool. The allowed range for *value* is from 0 through 2^31-1 (2,147,483,647). Specify 0 to remove an IOPS limit for the pool. The default is 0.
**Examples**
```
--This alters resource pool
USE [msdb]
EXEC dbo.rds_alter_resource_pool
@pool_name='analytics',
@MAX_CPU_PERCENT = 10,
@CAP_CPU_PERCENT = 20,
@MAX_MEMORY_PERCENT = 50;
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
--Validate configuration.
select * from sys.resource_governor_resource_pools
```
### Drop resource pool
**Usage**
```
USE [msdb]
EXEC dbo.rds_drop_resource_pool
@pool_name=value;
```
The following parameter is required:
+ `@pool_name` - Is the name of an existing user-defined resource pool.
**Note**
Dropping Internal or default resource pool isn't allowed in SQL Server.
**Examples**
```
--This drops resource pool
USE [msdb]
EXEC dbo.rds_drop_resource_pool
@pool_name='analytics'
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
--Validate configuration
select * from sys.resource_governor_resource_pools
```
## Manage workload groups
Workload groups, created and managed with `rds_create_workload_group` and `rds_alter_workload_group`, allow you to set importance levels, memory grants, and other parameters for groups of queries.
### Create workload group
**Usage**
```
USE [msdb]
EXEC dbo.rds_create_workload_group
@group_name = value,
@IMPORTANCE ={ LOW | MEDIUM | HIGH },
@REQUEST_MAX_MEMORY_GRANT_PERCENT =value,
@REQUEST_MAX_CPU_TIME_SEC = value ,
@REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value,
@MAX_DOP = value,
@GROUP_MAX_REQUESTS = value,
@pool_name = value
```
The following parameters are required:
+ `@pool_name` - Is the name of an existing user-defined resource pool.
+ `@group_name` - Is the name of an existing user-defined workload group.
The following parameters are optional:
+ `@IMPORTANCE` - Specifies the relative importance of a request in the workload group. The default value is `MEDIUM`.
+ `@REQUEST_MAX_MEMORY_GRANT_PERCENT` - Specifies the maximum amount of query workspace memory that a single request can take from the pool. *value* is a percentage of the resource pool size defined by `MAX_MEMORY_PERCENT`. Default value is 25.
+ `@REQUEST_MAX_CPU_TIME_SEC` - Specifies the maximum amount of CPU time, in seconds, that a batch request can use. *value* must be 0 or a positive integer. The default setting for *value* is 0, which means unlimited.
+ `@REQUEST_MEMORY_GRANT_TIMEOUT_SEC` - Specifies the maximum time, in seconds, that a query can wait for a memory grant from the query workspace memory to become available. *value* must be 0 or a positive integer. The default setting for *value*, 0, uses an internal calculation based on query cost to determine the maximum time.
+ `@MAX_DOP` - Specifies the maximum degree of parallelism (`MAXDOP`) for parallel query execution. The allowed range for *value* is from 0 through 64. The default setting for *value*, 0, uses the global setting.
+ `@GROUP_MAX_REQUESTS` = Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. *value* must be 0 or a positive integer. The default setting for *value* is 0, and allows unlimited requests.
+ `@pool_name` = Associates the workload group with the user-defined resource pool identified by *pool\$1name*, or with the `default` resource pool. If *pool\$1name* isn't provided, the workload group is associated with the built-in `default` pool.
**Examples**
```
--This creates workload group named 'analytics'
USE msdb;
EXEC dbo.rds_create_workload_group
@group_name = 'analytics',
@IMPORTANCE = 'HIGH',
@REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
@REQUEST_MAX_CPU_TIME_SEC = 0,
@REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
@MAX_DOP = 0,
@GROUP_MAX_REQUESTS = 0,
@pool_name = 'analytics';
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
--Validate configuration
select * from sys.resource_governor_workload_groups
```
### Alter workload group
**Usage**
```
EXEC msdb.dbo.rds_alter_workload_group
@group_name = value,
@IMPORTANCE = 'LOW|MEDIUM|HIGH',
@REQUEST_MAX_MEMORY_GRANT_PERCENT = value,
@REQUEST_MAX_CPU_TIME_SEC = value,
@REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value,
@MAX_DOP = value,
@GROUP_MAX_REQUESTS = value,
@pool_name = value
```
The following parameters are required:
+ `@group_name` - Is the name of default or an existing user-defined workload group.
**Note**
Changing only `REQUEST_MAX_MEMORY_GRANT_PERCENT` parameter on the default workload group is supported. For default workload group the `REQUEST_MAX_MEMORY_GRANT_PERCENT` must be between 1 and 70. No other parameters can be modified in default workload group. All parameters can be modified in the user-defined workload group.
The following parameters are optional:
+ `@IMPORTANCE` - Specifies the relative importance of a request in the workload group. The default value is MEDIUM.
+ `@REQUEST_MAX_MEMORY_GRANT_PERCENT` - Specifies the maximum amount of query workspace memory that a single request can take from the pool. *value* is a percentage of the resource pool size defined by `MAX_MEMORY_PERCENT`. Default value is 25. On Amazon RDS, `REQUEST_MAX_MEMORY_GRANT_PERCENT` must be between 1 and 70.
+ `@REQUEST_MAX_CPU_TIME_SEC` - Specifies the maximum amount of CPU time, in seconds, that a batch request can use. *value* must be 0 or a positive integer. The default setting for *value* is 0, which means unlimited.
+ `@REQUEST_MEMORY_GRANT_TIMEOUT_SEC` - Specifies the maximum time, in seconds, that a query can wait for a memory grant from the query workspace memory to become available. *value* must be 0 or a positive integer. The default setting for *value*, 0, uses an internal calculation based on query cost to determine the maximum time.
+ `@MAX_DOP` - Specifies the maximum degree of parallelism (MAXDOP) for parallel query execution. The allowed range for *value* is from 0 through 64. The default setting for *value*, 0, uses the global setting.
+ `@GROUP_MAX_REQUESTS` - Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. *value* must be 0 or a positive integer. The default setting for *value* is 0, and allows unlimited requests.
+ `@pool_name` - Associates the workload group with the user-defined resource pool identified by *pool\$1name*.
**Examples**
Example to Modify default workload group change REQUEST\$1MAX\$1MEMORY\$1GRANT\$1PERCENT:
```
--Modify default workload group (set memory grant cap to 10%)
USE msdb
EXEC dbo.rds_alter_workload_group
@group_name = 'default',
@REQUEST_MAX_MEMORY_GRANT_PERCENT=10;
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
--Validate configuration
SELECT * FROM sys.resource_governor_workload_groups WHERE name='default';
```
Example to modify non-default workload group:
```
EXEC msdb.dbo.rds_alter_workload_group
@group_name = 'analytics',
@IMPORTANCE = 'HIGH',
@REQUEST_MAX_MEMORY_GRANT_PERCENT = 30,
@REQUEST_MAX_CPU_TIME_SEC = 3600,
@REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60,
@MAX_DOP = 4,
@GROUP_MAX_REQUESTS = 100;
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
```
Example to move a Non-Default Workload Group to another resource pool:
```
EXEC msdb.dbo.rds_alter_workload_group
@group_name = 'analytics',
@pool_name='abc'
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
--Validate configuration
select * from sys.resource_governor_workload_groups
```
### Drop workload group
**Usage**
```
EXEC msdb.dbo.rds_drop_workload_group
@group_name = value
```
The following parameters are required:
+ `@group_name` - Is the name of an existing user-defined workload group.
**Examples**
```
--Drops a Workload Group:
EXEC msdb.dbo.rds_drop_workload_group
@group_name = 'analytics';
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
--Validate configuration
select * from sys.resource_governor_workload_groups
```
## Create and register classifier function
This procedure creates a resource governor classifier function in master database that routes connections to custom workload groups based on specified criteria (user name, database, host, or application name). If resource governor is enabled and a classifier function is specified in the resource governor configuration, then the function output determines the workload group used for new sessions. In the absence of a classifier function, all sessions are classified into the `default` group.
**Features:**
+ Supports up to two workload groups with their respective routing conditions.
+ Combines criterion with `AND` conditions within each group.
+ Requires at least one routing criterion per workload group.
+ Function name must start with `rg_classifier_`.
+ Default group assignment if no conditions match.
The classifier function has the following characteristics and behaviors:
+ The function is defined in the server scope (in the master database).
+ The function is defined with schema binding.
+ The function is evaluated for every new session, even when connection pooling is enabled.
+ The function returns the workload group context for the session. The session is assigned to the workload group returned by the classifier for the lifetime of the session.
+ If the function returns NULL, default, or the name of a nonexistent workload group, the session is given the default workload group context. The session is also given the default context if the function fails for any reason.
+ You can create multiple classifier functions. However, SQL Server allows only one classifier function to be registered at a time.
+ The classifier function can't be dropped unless its classifier status is removed using the de-register procedure (`EXEC dbo.msdb.rds_alter_resource_governor_configuration @deregister_function = 1;`) that sets the function name to NULL or another classifier function is registered using (`EXEC dbo.msdb.rds_alter_resource_governor_configuration @classifier_function = ;`)
+ In the absence of a classifier function, all sessions are classified into the default group.
+ You can't modify a classifier function while it is referenced in the resource governor configuration. However, you can modify the configuration to use a different classifier function. If you want to make changes to the classifier, consider creating a pair of classifier functions. For example, you might create `rg_classifier_a` and `rg_classifier_b`.
**Usage**
```
EXEC msdb.dbo.rds_create_classifier_function
@function_name = value,
@workload_group1 = value,
@user_name1 = value,
@db_name1 = value,
@host_name1 = value,
@app_name1 = value,
@workload_group2 = value,
@user_name2 = value,
@db_name2 = value,
@host_name2 = value,
@app_name2 = value
```
The following parameters are required:
+ `@function_name` - Name of the classifier function. Must start with `rg_classifier_`
+ `@workload_group1` - Name of the first workload group
The following parameters are optional:
(At least one of these criteria must be specified for group 1)
+ `@user_name1` - Login name for group 1
+ `@db_name1` - Database name for group 1
+ `@host_name1` - Host name for group 1
+ `@app_name1` - Application name for group 1
(If group 2 is specified, at least one criterion must be provided)
+ `@workload_group2` - Name of the second workload group
+ `@user_name2` - Login name for group 2
+ `@db_name2` - Database name for group 2
+ `@host_name2` - Host name for group 2
+ `@app_name2` - Application name for group 2
**Note**
System accounts, databases, applications and host are restricted.
**Examples**
Basic Example with One Workload Group:
```
/*Create a classifier to route all requests from 'PowerBI' app to workload group
'reporting_group'*/
EXEC msdb.dbo.rds_create_classifier_function
@function_name = 'rg_classifier_a',
@workload_group1 = 'reporting_group',
@app_name1 = 'PowerBI';
--Register the classifier
EXEC msdb.dbo.rds_alter_resource_governor_configuration
@classifier_function = 'rg_classifier_a';
-- Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration
/*Query sys.resource_governor_configuration to validate that resource governor is enabled and is using the classifier function we created and registered*/
use master
go
SELECT OBJECT_SCHEMA_NAME(classifier_function_id) AS classifier_schema_name,
OBJECT_NAME(classifier_function_id) AS classifier_object_name,
is_enabled
FROM sys.resource_governor_configuration;
```
## Drop classifier function
**Usage**
```
USE [msdb]
EXEC dbo.rds_drop_classifier_function
@function_name = value;
```
The following parameter is required:
+ `@function_name` - Is the name of an existing user-defined classifier function
**Example**
```
EXEC msdb.dbo.rds_drop_classifier_function
@function_name = 'rg_classifier_b';
```
## De-register classifier function
Use this procedure to de-register classifier function. After the function is de-registered, new sessions are automatically assigned to the default workload group.
**Usage**
```
USE [msdb]
EXEC dbo.rds_alter_resource_governor_configuration
@deregister_function = 1;
```
For de-registration the following parameter is required:
+ `@deregister_function` must be 1
**Example**
```
EXEC msdb.dbo.rds_alter_resource_governor_configuration
@deregister_function = 1;
GO
-- Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
```
## Reset statistics
Resource governor statistics are cumulative since the last server restart. If you need to collect statistics starting from a certain time, you can reset statistics using the following Amazon RDS stored procedure.
**Usage**
```
USE [msdb]
EXEC dbo.rds_alter_resource_governor_configuration
@reset_statistics = 1;
```
For reset stats the following parameter is required:
+ `@reset_statistics` must be 1
## Resource governor configuration changes
When resource governor isn’t enabled, `rds_alter_resource_governor_configuration` enables resource governor. Enabling resource governor has the following results:
+ The classifier function, if any, is executed for new sessions, assigning them to workload groups.
+ The resource limits that are specified in resource governor configuration are honored and enforced.
+ The resource limits that are specified in resource governor configuration are honored and enforced.
+ Requests that existed before enabling resource governor might be affected by any configuration changes made when resource governor is enabled.
+ Existing requests, before enabling resource governor, might be affected by any configuration changes made when resource governor is enabled.
+ On RDS for SQL Server, `EXEC msdb.dbo.rds_alter_resource_governor_configuration` must be executed for any resource governor configuration changes to take effect.
**Usage**
```
USE [msdb]
EXEC dbo.rds_alter_resource_governor_configuration
```
## Bind TempDB to a resource pool
You can bind tempdb memory optimized metadata to a specific resource pool using `rds_bind_tempdb_metadata_to_resource_pool` in Amazon RDS SQL Server version 2019 and above.
**Note**
Memory-optimized tempdb metadata feature must be enabled before binding tempdb metadata to resource pool. To enable this feature on Amazon RDS its a static parameter `tempdb metadata memory-optimized`.
Enable the static parameter on Amazon RDS and perform a reboot without failover for the parameter to take effect:
```
aws rds modify-db-parameter-group \
--db-parameter-group-name test-sqlserver-ee-2022 \
--parameters "ParameterName='tempdb metadata memory-optimized',ParameterValue=True,ApplyMethod=pending-reboot"
```
**Usage**
```
USE [msdb]
EXEC dbo.rds_bind_tempdb_metadata_to_resource_pool
@pool_name=value;
```
The following parameter is required:
+ `@pool_name` - Is the name of an existing user-defined resource pool.
**Note**
This change also requires sql service reboot without failover to take effect, even if Memory-optimized TempDB metadata feature is already enabled.
## Unbind TempDB from a resource pool
Unbind tempdb memory optimized metadata from a resource pool.
**Note**
This change also requires sql service reboot without failover to take effect
**Usage**
```
USE [msdb]
EXEC dbo.rds_unbind_tempdb_metadata_from_resource_pool
```
## Cleanup resource governor
This procedure is to clean up all associated objects after you have removed the resource governor option from the option group. This disables resource governor, reverts default workload group to default settings, remove custom workload groups, resource pools, and classifier functions.
**Key features**
+ Reverts default workload group to default settings
+ Disables resource governor
+ Removes custom workload groups
+ Removes custom resource pools
+ Drops classifier functions
+ Removes tempdb resource pool binding if enabled
**Important**
This cleanup can error out if there are active sessions on the workload group. Either wait for the active sessions to finish or terminate the active sessions as per your business requirement. It's recommended to run this during the maintenance window.
This cleanup can error out if a resource pool was bound to tempdb and reboot without failover hasn't been taken place yet. If you bound a resource pool to tempdb or unbound a resource pool from tempdb earlier, perform a reboot without failover to make the change effective. It's recommended to run this during the maintenance window.
**Usage**
```
USE [msdb]
EXEC dbo.rds_cleanup_resource_governor
```
## Considerations for Multi-AZ deployment
RDS for SQL Server replicates resource governor to secondary instance in a Multi-AZ deployment. You can verify when modified and new resource governor last synchronized with the secondary instance.
Use the following query to check the `last_sync_time` of the replication:
```
SELECT * from msdb.dbo.rds_fn_server_object_last_sync_time();
```
In the query results, if the sync time is past the resource governor updated or creation time, then the resource governor syncs with the secondary.
To perform a manual DB failover to confirm that the resource governor replicate, wait for the `last_sync_time` to update first. Then, proceed with the Multi-AZ failover.
## Considerations for read replicas
+ For SQL Server replicas in the same Region as the source DB instance, use the same option group as the source. Changes to the option group propagate to replicas immediately, regardless of their maintenance windows.
+ When you create a SQL Server cross-Region replica, RDS creates a dedicated option group for it.
+ You can't remove an SQL Server cross-Region replica from its dedicated option group. No other DB instances can use the dedicated option group for a SQL Server cross-Region replica.
+ Resource governor option is non-replicated options. You can add or remove non-replicated options from a dedicated option group.
+ When you promote a SQL Server cross-Region read replica, the promoted replica behaves the same as other SQL Server DB instances, including the management of its options.
**Note**
When using Resource governor on a read replica, you must manually ensure that resource governor has been configured on your read replica using Amazon RDS stored procedures after the option is added to the option group. Resource governor configurations do not automatically replicate to the read replica. Also, the workload on read replica is typically different than the primary instance. Hence, it's recommended to apply the resource configuration on the replica based on your workload and instance type. You can run these Amazon RDS stored procedures on read replica independently to configure resource governor on read replica.
# Monitor Microsoft SQL Server resource governor using system views for your RDS for SQL Server instance
Resource Governor statistics are cumulative since the last server restart. If you need to collect statistics starting from a certain time, you can reset statistics using the following Amazon RDS stored procedure:
```
EXEC msdb.dbo.rds_alter_resource_governor_configuration
@reset_statistics = 1;
```
## Resource pool runtime statistics
For each resource pool, resource governor tracks CPU and memory utilization, out-of-memory events, memory grants, I/O, and other statistics. For more information, see [ sys.dm\$1resource\$1governor\$1resource\$1pools](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-resource-governor-resource-pools-transact-sql?view=sql-server-ver17).
The following query returns a subset of available statistics for all resource pools:
```
SELECT rp.pool_id,
rp.name AS resource_pool_name,
wg.workload_group_count,
rp.statistics_start_time,
rp.total_cpu_usage_ms,
rp.target_memory_kb,
rp.used_memory_kb,
rp.out_of_memory_count,
rp.active_memgrant_count,
rp.total_memgrant_count,
rp.total_memgrant_timeout_count,
rp.read_io_completed_total,
rp.write_io_completed_total,
rp.read_bytes_total,
rp.write_bytes_total,
rp.read_io_stall_total_ms,
rp.write_io_stall_total_ms
FROM sys.dm_resource_governor_resource_pools AS rp
OUTER APPLY (
SELECT COUNT(1) AS workload_group_count
FROM sys.dm_resource_governor_workload_groups AS wg
WHERE wg.pool_id = rp.pool_id
) AS wg;
```
# Disabling Microsoft SQL Server resource governor for your RDS for SQL Server instance
When you disable resource governor on RDS for SQL Server, the service stops managing workload resources. Before you disable resource governor, review how this affects your database connections and configurations.
Disabling resource governor has the following results:
+ The classifier function isn't executed when a new connection is opened.
+ New connections are automatically classified into the default workload group.
+ All existing workload group and resource pool settings are reset to their default values.
+ No events are fired when limits are reached.
+ Resource governor configuration changes can be made, but the changes don't take effect until resource governor is enabled.
To disable resource governor, remove the `RESOURCE_GOVERNOR` option from its option group.
## Console
The following procedure removes the `RESOURCE_GOVERNOR` option.
**To remove the RESOURCE\$1GOVERNOR option from its option group**
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 **Option groups**.
1. Choose the option group with the `RESOURCE_GOVERNOR` option (`resource-governor-ee-2022` in the previous examples).
1. Choose **Delete option**.
1. Under **Deletion options**, choose **RESOURCE\$1GOVERNOR** for **Options to delete**.
1. Under **Apply immediately**, choose **Yes** to delete the option immediately, or **No** to delete it during the next maintenance window.
1. Choose **Delete**.
## CLI
The following procedure removes the `RESOURCE_GOVERNOR` option.
**To remove the RESOURCE\$1GOVERNOR option from its option group**
+ Run one of the following commands.
**Example**
For Linux, macOS, or Unix:
```
aws rds remove-option-from-option-group \
--option-group-name resource-governor-ee-2022 \
--options RESOURCE_GOVERNOR \
--apply-immediately
```
For Windows:
```
aws rds remove-option-from-option-group ^
--option-group-name resource-governor-ee-2022 ^
--options RESOURCE_GOVERNOR ^
--apply-immediately
```
# Best practices for configuring resource governor on RDS for SQL Server
To control resource consumption, RDS for SQL Server supports Microsoft SQL Server resource governor. The following best practices help you avoid common configuration issues and optimize database performance.
1. Resource governor configuration is stored in the `master` database. We recommend that you always save a copy of resource governor configuration scripts separately.
1. The classifier function extends login processing time hence it's recommended to avoid complex logic in the classifier. An overly complex function can cause login delays or connection timeouts including Amazon RDS automation sessions. This can impact the ability of Amazon RDS automation to monitor the instance health. Hence, it's always recommended to test the classifier function in a pre-production environment before implementing in production environments.
1. Avoid setting high values (above 70) for `REQUEST_MAX_MEMORY_GRANT_PERCENT` in workload groups, as this can prevent the database instance from allocating sufficient memory for other concurrent queries, potentially resulting in memory grant timeout errors (Error 8645). Conversely, setting this value too low (less than 1) or to 0 might prevent queries that need memory workspace (like those involving sort or hash operations) from executing properly in user-defined workload groups. RDS enforces these limits by restricting values to between 1 and 70 on default workload groups.
1. For binding tempdb to resource pool, after binding memory optimized tempdb metadata to a pool, the pool might reach its maximum setting, and any queries that use `tempdb` might fail with out-of-memory errors. Under certain circumstances, the SQL Server could potentially stop if an out-of-memory error occurs. To reduce the chance of this happening, set the memory pool's `MAX_MEMORY_PERCENT` to a high value.