Last week I started to look at SQL RDS. Coming from a database admin background I found myself on a very steep learning curve with the differences between a typical on prem SQL server! AWS RDS is a fully managed database service and as a DBA who’s very use to having full control over on prem it will take a little getting use to.. which doesn’t always mean a bad thing! It definitely gives me a new challenge and new ways of working!
One thing I’ve come across is the use of parameter groups. Parameter groups let’s you store your system configuration and even covers things like trace flags. (However it’s worth noting that not all Microsoft documented trace flags are supported). This is great for management as it provides a central configuration point. You can assign as many databases to the parameter group (as long as they are part of the same family).Which is great for standardising your resources.
One great benefit of using SQL parameter groups is you can add them to your cloud formation templates! Infrastructure as code is definitely the future so having the ability to incorporate advanced settings really goes a long way in helping ease manageability and also the speed in which you are able to deploy!
What are the basis of a parameter group?
Name: The name of the parameter group
Family: The family represents what major version and product the parameter group belongs to eg “sqlserver-se-13.0” which means SQL server standard edition 2016. Parameters: once the parameter group Is enabled you can add Parameters to the group. In SQL servers case this group consists of trace flags and system configuration Eg max server memory, max degree of parallelism and more! Basically if you can see the option within sp_configure in the AWS RDS world it is most likely to be within the parameter group.
How to create a custom database parameter group (manually):
1. Navigate to RDS from the AWS management console
2. On the left hand side select parameter groups.
3. Select Create parameter group
4. You'll need to select the relevant parameter group family. This will bring up a whole lot of different options! To explain these in further detail:
The first bit of the name will always start with the product in this case "sqlserver"
The 2nd part will be the relevant edition. You'll have a few to choose from:
ee (enterprise edition)
ex (express edition)
se (standard edition)
we (web edition)
The last part will be the major version for the database instance:
11.0 (SQL 2012)
12.0 (SQL 2014)
13.0 (SQL 2016)
14.0 (SQL 2017)
15.0 (SQL 2019)
In this blog example we'll want SQL Server standard edition 2016 so we'll select "sqlserver-se-13.0".
5. Enter a group name
6. Enter a description
By the end you should have something that looks like the below:
7. Once your happy click create! It'll then take you back to the parameter group screen and you should see your newly created group
8. The parameter group will be created with default values in it. To amend this select your parameter group. Under parameter group actions select edit.
9. You'll be presented with a whole heap of different configuration values. It's worth adding that anything with Modifiable 'false' means you won't be able to change it. RDS will just use the default values. Even if it does say it has different allowed values!
10. You can use the top search box to filter down. Eg we want to enable the use of database mail. Type in "database mail" and you'll see it'll bring back the parameter group for this. On the values section select 1 to enable. Then click save changes.
You can make any relevant amendments you like based on your requirements (just remember if modifiable is set to false your stuck with the default option!)
Cloudformation creation example:
You can take the below and just customize it based on your parameter needs. Remember all options are case sensitive!
Resources:
SQLRDSBeckyParameterGroup:
Type: 'AWS::RDS::DBParameterGroup'
Properties:
Description: RDSTestGroup
Family: sqlserver-se-13.0
Parameters:
'1204': '1'
'2528': '1'
'max degree of parallelism': '8'
'rds.force_ssl': '1'
'remote login timeout (s)': '1000'How to assign an RDS database to a parameter group:
Browse to databases within your RDS console
Select your relevant instance and select modify
Under additional configuration you'll see the option to select the relevant parameter group
You can choose to apply immediately or within the next maintenance window. It's worth reviewing Amazon documentation here. In order to check what effect your change will have and if the database instance may need rebooting.
That’s all folks!
Well that’s it for this weeks blog and that’s it for the rest of the year! I’m taking the next week off to slow down and enjoy what’s left of what has been a very challenging year. I’ll be back in 2021 with more blog posts than ever! I have some pretty exciting things coming up which I can’t wait to share with you all! Thank you so much for all the support from these posts so far. I really appreciate every single one of you who has clicked on my website and reviewed my posts! I wish everyone a fantastic Christmas and an even better new year!
Comments