… 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 sp_configure changes.
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 suppository toolkit.
4 Comments. Leave new
And if not hosted on RDS, you could you execute a command against a group of servers in the SSMS central management server…?
You definitely could do that.
Of course, you’d have to set up the CMS in advance and that does require additional work. You’ll also have to make sure that you add new SQL Servers to the CMS and the right group; they don’t get added automatically.
With the RDS solution, you have to specify a parameter group on creation (it can be changed later) and changes will be applied to all SQL Servers in a parameter group automatically when they’re restarted – the only way a server can be missed is to not have the SQL Server in the parameter group.
I love RDS. 1 click vertical scaling. built in clustering with multi-az. param groups.
not to mention… Amazon = 59% market share Azure = 12%
Yeah… I know… “were all in with azure” says Microsoft… well… RDS is also.
Another option to change configuration on a bunch of SQL Servers is to use SSIS package which iterates through each server and does what you want. There is a lot of flexibility in this approach.