During the setup wizard, SQL Server should ask for your email address. It should suggest that you use a distribution list for people on your team, so that someone gets the alerts when you’re out on vacation. Then, it should send these common failure alerts to you rather than making you set them up manually.
It should ask where you want your backups written to. It should suggest that you back up to a UNC path, not store the backups locally, lest the box crash and you lose everything.
It should ask if this is a new server, or replacing an existing server. Your phone probably does this – shouldn’t your expensive database server? I’m not saying it has to migrate the existing data and settings over – that’s genuinely hard, I get it. (However, I’ll say that the fact that Apple phones & laptops do this means that I upgrade more often. Upgrading is painless – go buy the new one, your stuff transfers over, boom, done. The pain of moving to a new SQL Server means we don’t do upgrades as often, and I know Microsoft wants to spur adoption. This kind of enhancement would make that happen.) I get that we’re not going to get automated moves, but the very least, go connect to that SQL Server to get a rough idea of how many databases and how much size is involved for this next step…
It should ask how much backup history you want to keep in that backup folder. As you move a slider back and forth between, say, 1 day and 1 year, it should show you how much space will be required on your backup target, and whether you have enough space for that now. Sure, you probably have something to sweep those backups off to a more long-term destination, but this is a good start.
It should ask how much data you’re willing to lose. As you move a slider back and forth between 1 minute and 1 week, it should show you the full, differential, and log backup schedule it’s going to use in order to meet your goal, plus corruption checking jobs. If you’re replacing an existing server, it should also show you roughly how long your restores are going to take. At the very least, it could open the maintenance plan wizard with suggestions already filled out. (I know, you prefer Ola Hallengren’s maintenance scripts, and I do too, but…baby steps, right?)
Shouldn’t we start users off by helping them protect the data?
34 Comments. Leave new
It should ask where you want your backups written to. It should suggest that you back up to a UNC path, not store the backups locally, lest the box crash and you lose everything.
Could have sworn it already asks you to specify a backup location? UNC path is just common sense isn’t it? I guess that’s just a sign of the time in which we live; kinda the same reason Tide shouldn’t have to put “DO NOT EAT” on the side of a box of those laundry detergent pods, but does anyway.
Common sense would lead one to believe the world is flat. Let us instead be led by the experience of experts and the benefits of education and research.
Not true, common sense tells us the earth can’t be flat, because if it were cats would have knocked everything over the edge by now. 😛
HAH! Now that’s an argument that’s tough to poke holes in. 😀
Why didn’t the sea drain off the edge of the earth after the great flood ?
They where there even in my time.
Government should tax what it does not want, and fund what it does want. Hence tax the poor and fund the rich – er right ? Maybe not
If it’s replacing a server, odds are you are going to want the same instance config so why can’t you export/import those settings, or better yet have the new server get them from the old server. That would save a lot of time.
As an accidental DBA in the midst of setting up a new prod and test SQL Server with their gazillion settings, YES!!
As a consultant I usually prefer to start clean and figure out what is actually needed. Most of the instances I come into are a huge mess with no shortage of configs that in-house staff implemented after trying every bad suggestion on spiceworks and then leave behind after it didn’t work
Probably going out on a limb now but how about trace flags or some database scope settings that you want for all your user databases. Would those perhaps go into model?
One of the first things I typically configure are the settings in the model database.
out of curiosity, what traceflags are you using in 2016+? 3226 is the only thing I can think of I would like to use but never seem to get around to turning it on
3042 as we compress backups – stops full sized backups that get compressed when they are completely written
4199 as more optimizations where added after RTM- could use the database scoped now but didn’t
Had 3427 on but now removed
Also had 9567 on for a particular exercise
Very interesting. I thought that 4199 didn’t do anything anymore. I had gotten rid of them everywhere for cleanliness. Going to have to switch it back on.
Microsoft even suggest 4199 or its database scoped version for their applications
On the backups, once upon a time I used to run the backup locally so it finished faster, and then copied it off somewhere. These days bandwidth is *much* better, but hey I mostly use Azure and don’t worry about things like that anymore.
Completely agree with your comments… the install should recommend all of the recommended setup settings or do it automatically if using PoSh.
PoSh = PS, PS ise? =:-)
Eh, I really don’t like this. SQL has too wide a range of configurations that can be viable and make sense. I’d rather it not configure anything at all and force going through a check list. The vast majority of SQL servers I build I end up getting a VM with only an OS drive, or maybe only a couple other drives before the rest of the drives get added. As much as I advocate for physical servers that would come preconfigured when building an AAG, (which almost all SQL builds I do are) I haven’t gotten one yet. Would end up specifying a bunch of configs that aren’t needed and then having to go through a checklist again anyways.
suggested settings also causes complacency, which is a particular problem if you have more than one person working on a box who all may not work in the same facility.
Good points. Yes we have standard drive letters for SQL, two temp db drives, logs, data1, data2, backups.
Backups? UNCs and backup software fail so I like to have a thin provisioned backup drive.
Result hearing “Drive G is only 30% free” means a log issue, not a data or temp db issue.
How is it supposed to estimate how much space my backups will use if it’s an installation routine, done before there are any databases at all? And before it knows anything about how much daily churn there is?
Urgh, I didn’t read carefully enough. The previous point mentioned connecting to the old server to look at its databases.
It wasn’t even a long post, man. 😉
I am not a fan of products that require extensive knowledge of the business usage at install time. Yes, MS should have better maintenance services and include all areas of system maintenance. The installer should stay lightweight, install the services, then optionally ask if you want to run begin any of the maintenance tasks. Sometimes (like DEV), I don’t care about maintenance since the instance will be destroyed at end of sprint.
I’d be totally on board with that. Install with defaults and then ask you if you want to do a config prompt. It wouldn’t be hard for MS to develop that at all as SQL PowerShell becomes more useful. They could most likely write the entire config wizard in PowerShell, which when needed could generate .tsql scripts for options that arent configurable in powershell and then have the PowerShell script run all of the scripts and do service stops and starts in the order it needs.
Exchange has used an install and configuration like this for years. Even 2007 was heavily driven by powershell, ldifde and vbs scripts that the set wizard executed.
“It should ask how much data you’re willing to lose” – This phrase works best for me. I still have to fight for system database backups. “Just reinstall SQL” – wrong.
User IDs, permissions, scheduled jobs, notification lists are all in MSDB or Master.
Ever try starting SQL with a corrupt TempDB or Model ?? No go. Hard to find another server at the same patch level.
SQL should also check for 64K clusters on drives. I still get four or eight kb clusters from the server folks. “Because it is the Microsoft default and recommendation” Well SQL works much faster at 64Kb.
Someone setting up a professional SQL server shouldn’t be going through the GUI, or resort to simple version of configuring important tasks. There is much more to a good backup strategy than selecting a network path and specifying a retention period. Also, many backup solutions take folder snapshots, so storing locally is perfectly fine if you’re using a solution that makes backups of selected drives/folders.
I love it, specially the back up retention option. Agree on the rest. Nice post
There is really only one thing to say about this: Yes, please.
But… Make sure that the preparation instructions make very clear that you should make a mail group or specific email address ahead of time, and that leaving it blank leaves a manual setup available.
And… Allow adding the email address after the fact, and have the process still configure the alerts if they are unconfigured.
And… Allow changing an existing email address through the same location, and ask if you want to update all alerts using that email to use the new email.
But other than that, nothing else needs to be said
I really don’t want to tweak backup settings at install. Most will vary based upon the individual database and user/business/compliance requirements
I hear you but “How much data do you want to loose?” Do you wish to be able to restore a database they did not back up?
Backups have brought grief. Restores bring joy. Those complaining about backups and space shed tears of joy when their data and system was restored. One PeopleSoft financial bug was proven keeping the DBA, who can bypass Peoplesoft controls, out of jail.
Your mileage may vary. the SQL “backup all user databases” is a great addition.
Hear, hear
How about errorlog files? The default 6 along with how often to run sp_cycle_errorlog could certainly be setup options. We set to 16 and run sp-cycle_errorlog twice a week to keep the individual log file sizes down and still keep at least 1 month of data on disk.
Agree with this.
Also check or not the backup compression
if we want to restore databases directly (just give a path where .bck files were stored)
Like Rudy says, man-min memory, default recovery model
and more : fill factor, max DOP, // threshold
In new features :
the instance role Backup Operator
The right to tune different cache library (like in Oracle)
Joke : Integrate the Hallengren scripts…
I would like to see the following:
1. Should auto calculate the max and min memory and give you the option to apply settings
2. Should ask default recovery model