Problem
Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. But, RDS does not currently support all features of SQL Server that you might be used to. In order to deliver a managed service experience, Amazon RDS does not provide shell access to database instances and it restricts access to certain system procedures and tables that require advanced privileges. Amazon RDS supports access to databases on an instance using any standard SQL client application, but some system tools, like sp_configure are not available as a way to set server level settings. You can run sp_configure and see the configuration values, but you can’t change them directly in SQL Server.
In this tip we look at how you can still manage some of these settings even though some tools are locked down.
Solution
When using AWS RDS, you manage your database engine configuration through the use of parameters in a database parameter group. Default parameter groups are automatically created from a default parameter template when you create a database instance for a particular database engine and version. These default parameter groups contain preferred parameter settings and cannot be modified. When you create a custom parameter group, you can modify parameter settings.
Using SP_CONFIGURE on AWS RDS
You can still run sp_configure to see the configuration values, such as cost threshold for parallelism, but if you try to change you will get an error saying you don’t have permission to perform the action.
RDS Parameter Groups
To set parameters, you need to setup a parameter group. You can see the parameter groups available in the image below and if you don’t have any setup you can click on "Create parameter group" to create a new group where you can configure parameters.
Here is the screen to setup a new parameter group.
With the new parameter group you can now change configuration values that you used to previously change using sp_configure. For instance, I’m going to change the cost threshold for parallelism from the default value of 5.
Type "parallelism" in the parameter group filter to show only the parallelism options. Once we find the parameter we want to adjust, we can click on "Edit parameters" and then save the value.
Complete List of AWS RDS Parameters for SQL Server
Here is a list of the parameters that can be adjusted using this approach.
| Parameter | Description |
|---|---|
| 1204 | Returns the resources and types of locks participating in a deadlock and also the current command affected. |
| 1211 | Disables lock escalation based on memory pressure or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks. |
| 1222 | Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. |
| 1224 | Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. |
| 2528 | Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. |
| 3205 | Disables hardware compression for tape drivers. |
| 3226 | Suppresses log entries for backup operations. |
| 3625 | Limits the amount of information returned in error messages. |
| 4199 | Controls multiple query optimizer changes previously made under multiple trace flags. |
| 4616 | Makes server-level metadata visible to application roles. |
| 6527 | Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration. |
| 7806 | Enables a dedicated administrator connection (DAC) on SQL Server Express. |
| access check cache bucket count | Number of buckets used by the internal access check result cache |
| access check cache quota | Number of entries used by the internal access check result cache |
| ad hoc distributed queries | Enables ad hoc distributed queries using OPENROWSET and OPENDATASOURCE |
| affinity i/o mask | Binds disk I/O to specified subset of CPUs |
| affinity mask | Dynamically controls CPU affinity |
| agent xps | Enables the SQL Server Agent extended stored procedures on this serve |
| allow polybase export | Allows INSERT into a Hadoop external table |
| allow updates | Setting has no effect |
| automatic soft-numa disabled | Automatic soft-NUMA is enabled by default |
| c2 audit mode | Enables C2 auditing |
| clr enabled | Whether assemblies can be run by SQL Server |
| clr strict security | clr strict security |
| contained database authentication | Enables contained databases authentication to create or attach contained databases to Database Engine without authenticating a login at the Database Engine level |
| cost threshold for parallelism | Threshold at which Microsoft SQL Server creates and runs parallel plans for queries |
| cross db ownership chaining | Configures cross-database ownership chaining for an instance of Microsoft SQL Server |
| cursor threshold | Number of rows in the cursor set at which cursor keysets are generated asynchronously |
| database mail xps | Enables Database Mail on the server |
| default full-text language | Default language value for full-text indexed columns |
| default language | Default language for all newly created logins |
| default trace enabled | Enables or disable the default trace log files |
| disallow results from triggers | Whether triggers can return result sets |
| external scripts enabled | Allows execution of external scripts |
| filestream access level | Changes the FILESTREAM access level for this instance of SQL Server |
| fill factor (%) | Server-wide default fill-factor value |
| ft crawl bandwidth (max) | Maximum size to which the pool of large memory buffers can grow for full-text searching |
| ft crawl bandwidth (min) | Minimum size to which the pool of large memory buffers can grow for full-text searching |
| ft notify bandwidth (max) | Maximum size to which the pool of small memory buffers can grow for full-text searching |
| ft notify bandwidth (min) | Minimum size to which the pool of small memory buffers can grow for full-text searching |
| in-doubt xact resolution | Controls default outcome of transactions that the Microsoft Distributed Transaction Coordinator (MS DTC) is unable to resolve |
| index create memory (kb) | Maximum amount of memory initially allocated for creating indexes |
| lightweight pooling | Whether to switch to fiber mode scheduling |
| locks | Maximum number of available locks |
| max degree of parallelism | Number of processors to use in a parallel plan execution |
| max full-text crawl range | Number of partitions that Microsoft SQL Server should use during a full index crawl |
| max server memory (mb) | Maximum amount of memory in megabytes in the buffer pool used by an instance of Microsoft SQL Server |
| max text repl size (b) | Maximum size in bytes of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement |
| max worker threads | Number of worker threads available to Microsoft SQL Server processes |
| media retention | System-wide default length of time to retain each backup set |
| min memory per query (kb) | Minimum amount of memory in kilobytes that are allocated for the execution of a query |
| min server memory (mb) | Minimum amount of memory in megabytes in the buffer pool used by an instance of Microsoft SQL Server |
| nested triggers | Controls whether an AFTER trigger can cascade |
| network packet size (b) | Packet size (in bytes) used across the entire network |
| ole automation procedures | Whether OLE Automation objects can be instantiated within Transact-SQL batches |
| open objects | Setting has no effect |
| optimize for ad hoc workloads | Improve efficiency of the plan cache for workloads that contain many single use ad hoc batches |
| ph timeout (s) | Time, in seconds, that the full-text protocol handler should wait to connect to a database before timing-out |
| polybase network encryption | Configures SQL Server to encrypt control and data channels when using PolyBase |
| priority boost | Whether Microsoft SQL Server should run at a higher Windows Server scheduling priority than other processes on the same computer |
| query governor cost limit | Upper limit on the time period in which query can run |
| query wait (s) | Time in seconds that a query waits for resources before timing out |
| rds.fips | FIPS enforcement. |
| rds.force_ssl | Force SSL connections. |
| recovery interval (min) | Maximum number of minutes per database that Microsoft SQL Server needs to recover databases |
| remote access | Control the execution of stored procedure from local or remote servers on which instances of Microsoft SQL Server are running |
| remote admin connections | Enables client applications on remote computers to use the dedicated administrator connection (DAC) |
| remote data archive | Allows the use of the REMOTE_DATA_ARCHIVE data access for databases |
| remote login timeout (s) | Number of seconds to wait before returning from a failed attempt to log in to a remote server |
| remote proc trans | Protects the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction |
| remote query timeout (s) | How long, in seconds, a remote operation can take before Microsoft SQL Server times out |
| replication xps | Internal use only |
| scan for startup procs | Scans for automatic execution of stored procedures at Microsoft SQL Server startup time |
| server trigger recursion | Whether to allow server-level triggers to fire recursively |
| set working set size | Setting has no effect |
| show advanced options | Display the sp_configure system stored procedure advanced options |
| smo and dmo xps | Enables SQL Server Management Object (SMO) and SQL Distributed Management Object (SQL-DMO) extended stored procedures on this server |
| transform noise words | Suppresses an error message if noise words cause a Boolean operation on a full-text query to return zero rows |
| two digit year cutoff | Cutoff year for interpreting two-digit years as four-digit years |
| user connections | Maximum number of simultaneous user connections. Please note that the service may use up to 40 connections for system maintenance |
| user options | Specify global default query processing options for all users |
| xp_cmdshell | Enables whether the xp_cmdshell extended stored procedure can be executed on the system |
You can see a few trace flags and xp_cmdshell, but not everything can be adjusted.
If we try to change xp_cmdshell as shown below, an error will appear.
Using PowerShell to Change AWS RDS Parameters
I suggest using PowerShell to change parameters. For example, I quickly created a script in PowerShell to change the cost thresholds for parallelism as shown below.
Conclusion
Amazon RDS has limitations to manage database configurations, but there are parameters you can work with. To configure your environment, parameter groups can solve some of the limitations that RDS creates like setting parallelism on SQL Server, although you cannot change everything as we saw above.
As I described, there are a couple of ways to change the configurations that you don’t have direct access on RDS using parameter groups. Following the above steps might increase the number of parameter groups, because the defaults aren’t the best configuration for your database.
In addition, there is a question about security because anyone with rights to change parameter groups can change your database configurations. So, it’s good to stay alert when making configuration changes and keep a log of the changes.
Next Steps
- AWS Relational Database Services introduction
- More Cloud tips
- Capturing and Alerting on SQL Server Configuration Changes
Douglas Correa is a database professional focused on performance tuning database, high-availability, and infrastructure. He has been working with SQL Server for more than ten years. He has performance-tuned databases ranging from 1 gigabyte to terabytes working with PowerShell, SQL Server and Oracle. He has certifications from Microsoft (MCSA SQL Server, MTA Network) and Oracle (OCE, OCA 11g).
