Setting SQL Server Configuration Options with AWS RDS Parameter Groups

By:   |   Comments (1)   |   Related: > Amazon AWS RDS


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.

sp_configure

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.

rds parameter groups

Here is the screen to setup a new parameter group.

rds create 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.

rds edit parameter

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.

rds parameter group error

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.

set rds parameter with PowerShell
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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Douglas Correa Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, November 25, 2020 - 10:54:00 AM - Gilbert Oppong Kyeremateng Back To Top (87845)
Thank you very much for the tip. It solved my issue. I appreciate you taking the time to share. Once again, thank you!














get free sql tips
agree to terms