I Like What’s Happening
I wanna start off by saying that I like what Microsoft has been doing with the setup process — it made a lot of sense to add the tempdb configuration screen, and having a checkbox to turn on Instant File Initialization was amazingly helpful.
Even in the cloud, people still need to install SQL Server, and even in the cloud, not everyone installing SQL Server is a DBA.
It helps to have a setup checklist like the one we put in the First Responder Kit if you fall into that category.
DBAs who have to install SQL Server a lot may have a post-install script they run. In the age of, well, pick any from a long list of buzz words: DevOps, containers, Docker, Kubern-whatever, it sucks to have another moving part that might fail or break.
Modest Proposal
Do for basic sanity settings what’s already happened for tempdb and IFI.
What’s a basic sanity setting?
- Cost Threshold for Parallelism
- MAXDOP
- Max Server Memory
- Enable the DAC
At the very least, these are settings that should be in front of people when they’re setting up a server.
If you wanna get extra fancy, you could even let people tweak settings to the model database like autogrowth and recovery model, and setup database mail and alerts.
Death Of The Boring DBA
The cloud is great, and the automation that Microsoft is building sure is nifty, but people still struggle with very basic setup items.
This post might look like dinosaur bones in a few years, but quite often a lot of problems stem from not taking care of the broom and dustpan stuff up front, and not going back to check on things later.
During consulting engagements, it’s really common to hear stuff like “I thought we did that” or “that’s the default so we left it” in really important places.
Thanks for reading!
Brent says: I’d really, really love to see a step in the setup wizard that offers to set up backups and corruption checking. These are table stakes for building a reliable server. I’m stunned by how often SQL ConstantCare® customers are struggling with these basics.
6 Comments. Leave new
And Backup compression…on by default, turn it off if you use some other method.
there are many settings that could be pre-set properly but any of those might need to be changed later on depending on edge cases, custom environments, solutions…and…I think less and less people know SQL Server (or database technologies) in-depth (that’s the trend I see) so no matter what you need to change later on, no-one will have a clue…that’s good though for us, database people 🙂 my list would be: database autogrowth, MAXDOP, and…still tempdb! Even if SQL2016 installer introduced a good change, tempdb as a whole still lacking. Actually, I still see it as the main limiting factor in SQL Server as a whole. The reality is that developers throw all sorts of anti-patterns on the server that you can think of (triggers, cursors, excessive number of temp objects etc.) while the server environment seriously lacks resources and no-one understands why performance is bad. The internal mechanics of tempdb is unheard of and there are many urban legends out there, still…so in 2018+ I would expect SQL Server set up it’s own tempdb configuration depending on the environment and have advanced analytics for anyone looking after the environment, saying that: hey, we started off like this, the workload indicates this and this, do this and this and let’s compare the results…this should be one of the primary items in a dashboard built into SSMS and have advanced DMVs underneath
…also it’s beyond me why SQL Server in 2018 does not complain in the logs about:
– not having database maintenance at all
– not having backups
also why having AUTO_SHRINK and AUTO_CLOSE still available in the product etc etc.
I think MS is focusing on it’s Azure offering a lot where many of these things are hidden but there will always be on-premise deployments in 10-20 years which will still offer the very same problems as 10 years ago…
Thanks Erik, You have prompted me to update my post-install script with a bunch of server level configuration settings. At the moment it is based around Ola’s backup and maintenance scripts, but with all of my standard alerts, operators and Database Mail setup thrown in for good measure.
If these options were added to the installer I would really like it to be able to pick up the settings from a file, text, xml, or json; i’m format agnostic. I just want all my servers set up the same, and i don’t want to have to type that lot out more than once.
Alex – yeah, I hear you. The more you have to do this stuff, the worse it is.
This all comes down to config management. Our powershell based setup.exe install wrapper reads the config for a given SQL server from our config DB. That takes our default for a version of SQL Server 2005 -> 2017, overlays any instance specific variations and then installs the server. Installing the server includes applying Server Level standard audits, revoking connect from guest and execute from public on various xps, setting our default values for various sp_configure values, setting server level settings e.g. disabling named pipes etc. and enabling HADR where the install is occurring on a cluster, as well as configuring all of the standard Agent jobs for maintenance & backup. Without this standardised installation mechanism it is impossible to manage an Enterprise deployment of SQL on 100’s installations. All SQL SERVER hosts sit in their own OU with prod and non-prod versions. This allows us to apply specific registry and other settings, like perform volume maintenance and lock pages in memory to our SQL hosts and to make changes in a controlled fashion on non-prod servers first.
The biggest shame is that Microsoft do not include this config management out of the box.
I wish master, model and msdb weren’t set to grow by 10%… seems quite odd. After install I have a script that changes these all that I run. Glad Microsoft put the TEMPDB configuration settings in the setup of 2016 but if memory serves right if you want an alternate drive you have to use the button to go out and configure that instead of having the default file structure already set there and just change the drive letters.