Amazon – AWS – RDS – DB Parameter

Background

Amazon RDS Offers an ability to access some of the more system level configurations of the hosted database.

In MS SQL Server, some of those options are available via sp_configure.

Here is how to view exposed configuration via AWS\RDS CLI Commands.

Parameter Groups

List DB Parameter Groups

Code


aws rds describe-db-parameter-groups

Output

ListDBParameterGroups

 

List Specific DB Parameter Group

Code


aws rds describe-db-parameter-groups --db-parameter-group-name default.sqlserver-ex-12.0

Output

ListSpecificDBParameterGroup

 

Parameter Values

List all DB Parameter Values

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table --query "Parameters[*].[ParameterName, ParameterValue]" | more

Output

ListDBParameterValues

 

List DB Parameter – Scan for startup procs

Code


aws rds describe-db-parameters  --db-parameter-group-name default.sqlserver-ex-12.0 --output table  --query "Parameters[?ParameterName==`scan for startup procs`]"

Output

ListDBParameterValue-ScanForStartupProcs

 

List DB Parameter – Optimize for adhoc workloads

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?ParameterName==`optimize for ad hoc workloads`].[ParameterName, ParameterValue, ApplyMethod]"

Output

ListDBParameterValue-OptimizeForAdhoc

 

List DB Parameter – Max Server Memory

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?ParameterName==`max server memory (mb)`].[ParameterName, ParameterValue, ApplyMethod]"

Output

ListDBParameterValue-MaxServerMemory

Explanation

  1. We are using free AWS and so our max memory is 1GB // SQL Server Express

 

List DB Parameter – Trace Flags

Code


@rem 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
aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?ParameterName==`1222`].[ParameterName, ParameterValue, ApplyMethod]"

Output

— Empty —

 

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?DataType==`boolean`].[ParameterName, ParameterValue, ApplyMethod]" 

Output

DescribeDBParameters-Boolean

 

Explanation

  1. One workaround for accessing Trace Flags figures is to look at records whose datatype is Boolean

 

Change DB Parameter – Trace Flags

Code


@rem Returns the resources and types of locks participating in a deadlock and also the current command affected.
aws rds modify-db-parameter-group --db-parameter-group-name default.sqlserver-ex-12.0 --parameters "ParameterName=1222,ParameterValue=1, ApplyType=immediate"

Output – Textual

A client error (InvalidParameterValue) occurred when calling the ModifyDBParameterGroup operation: Cannot modify a default parameter group.

Output – Image

CannotModify-DefaultParameterGroup

 

Copy DB Parameter

Code


aws rds copy-db-parameter-group ^
 --source-db-parameter-group-identifier "default.sqlserver-ex-12.0" ^
 --target-db-parameter-group-identifier "corp.sqlserver-ex-12.0" ^
 --target-db-parameter-group-description "corp.sqlserver-ex-12.0" 


Output-Text


A client error (InvalidParameterValue) occurred when calling the CopyDBParameterGroup operation: The parameter DBParamet
erGroupName is not a valid identifier. Identifiers must begin with a letter; must contain only ASCII letters, digits, an
d hyphens; and must not end with a hyphen or contain two consecutive hyphens.

 

Output-Image

NotAValididentifier

Bug Reports

  1. aws_db_parameter_group name argument error #6044
    https://github.com/hashicorp/terraform/issues/6044

 

Create DB Parameter

Let us create a new DB Parameter group – corp.vMSSQL12

Code


aws rds create-db-parameter-group ^
 --db-parameter-group-name "corp.vMSSQL12" ^
 --db-parameter-group-family "sqlserver-ex-12.0" ^
 --description "corp.sqlserver-ex-12.0" 


Output-Text


A client error (InvalidParameterValue) occurred when calling the CreateDBParameterGroup operation: The parameter DBParameterGroupName is not a valid identifier. 
Identifiers must begin with a letter; must contain only ASCII letters, digits, and hyphens; and must not end with a hyphen or contain two consecutive hyphens.

Output-Image

CreateDBParameterGroup-WithPeriod

Explanation

  1. DB Parameter group name, corp.vMSSQL12, has a period in it
  2. And, unfortunately the code fails when the parameter has a period

 

Create DB Parameter

Let us create a new DB Parameter group – corp.vMSSQL12

Code


     aws rds create-db-parameter-group ^
        --db-parameter-group-name "corp" ^
        --db-parameter-group-family "sqlserver-ex-12.0" ^
        --description "corp.sqlserver-ex-12.0" 


Output-Text

{
 "DBParameterGroup": {
 "DBParameterGroupName": "corp",
 "DBParameterGroupFamily": "sqlserver-ex-12.0",
 "Description": "corp.sqlserver-ex-12.0"
 }
}

Output-Image

CreateDBParameterGroup-Successful

Explanation

  1. When we supplied  a simpler name, corp rather than corp.vMSSQL12, we are good.

 

Change DB Parameter

 

Code


aws rds modify-db-parameter-group  --db-parameter-group-name corp  --parameters "ParameterName=1222,ParameterValue=1, ApplyMethod=immediate"

Output-Text

{
 "DBParameterGroupName": "corp"
}

Output-Image

ChangeDBParameterGroup-Successful

 

Modify DB Instance

Code


aws rds modify-db-instance  --db-instance-identifier adriel --db-parameter-group-name=corp

Output

modifyDBInstanceGroup

 

Textual

Here are some worthy values…

  1. PendingModifiedValues – Empty
  2. DBParameterGroups
    • DBParameterGroupName :- corp
    • ParameterApplyStatus :- applying

Restart DB Instance

Code


aws rds reboot-db-instance  --db-instance-identifier adriel

Output

restartDBInstance

 

Textual

Here are some worthy values…

  1. PendingModifiedValues – Empty
  2. DBParameterGroups
    • DBParameterGroupName :- corp
    • ParameterApplyStatus :- pending-reboot

Conclusion

Couple of noteworthy points …

  1. It is best to create and craft DB Parameter groups before hand
    • If you do not assign a DB Instance to a user created Parameter group, you will be assigned a default parameter group
    • Unfortunately, default parameter groups, are not modifiable, and as such one has little flexibility for configuring instances assigned to them
    • Assigning DB instances to them during creation will reduce the number of modifications and reboot thereafter
  2. Since different versions of your database will support and expose different configurable parameters, it is likely best to create DBMS Version parameter groups

 

References

Amazon AW Documentation

  1. AWS Documentation » AWS Command Line Interface » AWS Command Line Interface » Using the AWS Command Line Interface » Controlling Command Output from the AWS Command Line Interface
    http://docs.aws.amazon.com/cli/latest/userguide/controlling-output.html
  2. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Amazon RDS DB Instance Lifecycle » Working with DB Parameter Groups
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Viewing
  3. AWS Documentation » Amazon Relational Database Service (RDS) Documentation » API Reference » Actions » DescribeDBParameters
    http://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeDBParameters.html

 

Implementation

  1. Why You Should Always Use a Custom DB Parameter Group When Creating an RDS Instance
    http://www.stratalux.com/blog/always-use-custom-db-parameter-group-creating-rds-instance/
  2. VMware Continuent Documentation Center – Changing Amazon RDS Instance Configurations
    https://pubs.vmware.com/continuent/tungsten-replicator-2.2/deployment-amazonrds-rdsconfig.html

 

One thought on “Amazon – AWS – RDS – DB Parameter

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s