… is to use a SQL Server where a robot does it for you!
I stole that from Kendra – someone else already wrote the sentence for me and why not keep re-using things, right?
Configuring SQL Servers the Usual Way
What’s the usual way to do this?
There are a few commercial tools on the market that let you deploy scripts to multiple servers, they all vary in how they apply the changes and deal with errors.
You could also roll your own tools. This gives you full control of the process, but you have to spend time implementing and maintaining code over time.
Finally, you could just hire junior DBAs and make them implement the changes individually. This will give all of your servers a delightful hand-crafted flair. Sure, the changes may or may not all be applied with equal rigor, but hey, you’re a job creator now.
SQL Server RDS Parameter Groups
SQL Server RDS is part of Amazon’s database as a service offering. We’ve covered RDS a couple times before and we’re still in love with RDS all these years later.
One of my favorite features of RDS is the idea of parameter groups. Parameter groups are a unified set of parameters. A parameter group contains a bunch of different things that we’d normally set up through trace flags and
How Can I Change a Parameter Group?
Changing parameters in a parameter group is incredibly easy. The first step is to create a new parameter group.
Once that parameter group is created, we can highlight the parameter group and click “Edit Parameters”.
The UI makes it relatively easy to sort and filter. Here, I’m looking to adjust how parallelism is handled on these particular RDS instances. These parameters are applied to all instances assigned to the parameter group; make sure that you put similar SQL Server instances in the same parameter group.
If you’re unsure about what you’ve changed, you can even select “Preview Changes” and get a quick overview of current and new values.
Applying Changes with a Parameter Group
Once you’ve changed the parameter groups, you’d be tempted to think the changes will be immediately applied to the servers. They won’t be applied immediately. You’ll still need to schedule a restart to each SQL Server RDS instance for the new parameter group settings to be applied.
This approach works well for many applications – you can restart a small portion of the servers in the system, make sure that the application is performing correctly, and then restart more servers. By leveraging the power of the cloud – scale out, not up – we can make operations easier and simpler.
What’s it mean for you?
Once you’ve gotten the hang of parameter groups, new SQL Server RDS instances can be created with the parameter group already applied. Existing SQL Server RDS instances can be moved into a parameter group. Managing settings across 10s or 100s of instances just got significantly easier – change a parameter group and you’re off to the races.
Brent says: But I put all this work into learning PowerShell! Oh, wait, no, I didn’t. Carry on.
Jeremiah retorts: Good news! There’s a PowerShell