When I picture Paul Randal, I picture a guy who likes to run up to a bees nest at a crowded picnic and give it a good couple whacks. You know what I mean, it’s that laughing picture in his twitter profile. Or maybe it’s the devil’s advocate style he has. Maybe it’s his controversial facebook thoughts? Either way, he likes to call things out and make people think. That’s a great trait, all kidding aside. He started a blog meme asking what 5 “features” you’d like to see retired in SQL Server. His responses were great and he tagged some folks. I’ve looked at Brent Ozar’s response and was then tagged by Aaron Bertrand.
After reading those three responses so far, I have to say I am not going to be that original so most of these will be a hearty “Amen!” to earlier sentiments.
Auto Shrink
I made my entrance into SQL Blogging with a series of rants about shrinking databases. I still get hits to articles like “Don’t Touch That Shrink Button!” from web searches or forums. The simple fact of the matter is.. Well to put it simply, “raise your hand if you think regular shrinking of production databases is a good idea.” Okay. Look around. Any hands raised? Not many. So why, on earth, would Microsoft make that a stinking setting option?
I would like to see this retired, and doing it in a Service Pack would be alright with me. Or at least rename it to “I’m not dangerous enough by myself, please automatically add to performance frustrations and index fragmentation while keeping my database size tidy, even though it will grow again right after the shrink but then again I never liked right sizing databases that requires too much work – Yes/No” (Yeah, I know… Not the best grammar there, Jeremiah, but…) This was already suggested by Paul and Brent.
Full Recovery by Default (Or something…)
Again I’m stealing ideas (but I’m doing it to help people!) – Brent and Paul both also suggested this. This has been like this for a long time. It still trips countless people up each day. Another hand raising question – how many of you have experienced, helped someone with or laughed at someone experiencing uncontrolled transaction log growth caused by a lack of log backups in full recovery model? All of the hands that were down in the earlier question were just up. We’ve all seen it, hopefully we haven’t really laughed though. It happens All. The. Time.
A good tactical move would be to remove this as a default. Start folks out in Simple. Or perhaps a strategic option would be a “Recovery Model Wizard” as a required step of installations? It would be nice to not need it but I think a lot of folks could benefit. As long as there was some way to override it with an advanced configuration, I wouldn’t mind a tutorial explaining pros/cons and costs of settings so people could make <gasp> informed decisions about their recovery models. Or maybe that is enabling companies to not hire experienced DBAs? I don’t know, what do you think?
No Simple Way to Truncate Backup History
Why make someone write a script or know to go look in a maintenance plan to find this option? Backup history isn’t critical to restores, sure it makes GUI based restores easier but how many GUI based restores are done going past 30 days? Why not make a default rolling window and let SQL cleanup that history. Provide a configuration option to change this window, even a configuration screen during setup asking other questions that should be asked but never get looked at by a lot of the SQL Server installations out there (backup history, recovery modes, heck even error log recycles)
Auditing – Enterprise Only?
Aaron talked about licensing models and I agree. One area is Auditing. Nowadays, even smaller companies with smaller budgets are being subject to increasing scrutiny and audit standards. In some ways, this can help a larger company that has a budget for Enterprise edition everything and an audit staff. What about the smaller company that is cash strapped? They can get most of the performance they need out of Standard and handle most questions/problems just fine, a $15k difference (or so) in per CPU licensing is a lot to ask for to better handle some of the auditing abilities in SQL Server.
Next, Next, Next, Finish
Aaron talked about the installation process in his response. He was talking about some of the bad defaults like I mentioned when talking about cleaning up backup history. This is perhaps more of a tongue-in-cheek response but it has some sort of a serious underpinning. I’ve always complained (raise your hand one last time if you’ve shared this complaint) that SQL Server is too easy to install. Accepting defaults and clicking next, next, finish gives folks a SQL Server instance that they put all of their most sensitive and important data. It also gives them:
- No backups
Brings back memories of 386DX, "Boost Priority" is not a turbo button - Full Recovery mode with no log backups
- No thought of DR
- No thought of right sizing a database
- Options that look tempting to select like “boost SQL priority”
- Data and Logs on the same drive (performance concern and recoverability concern)
- False confidence that everything is fine
That list could go on but the point is maybe SQL Server is a little too easy to install? I’ve heard it in a joking manner before like, “why do we need a SQL Server DBA? Any moron can install it!” I’ve seen the results of any old IT person (or business person) installing SQL. It inspired a series of posts on thinking about SQL installations.
I am going to take a cue from Brent and not tag anyone but welcome you to respond in your own post, leave a comment arguing with me (or agreeing) below or leave a comment on any of the blog posts that are sparked from the original post by Paul. I’d love to hear your thoughts, maybe I’ve gone too far? Maybe I missed something critical. I’d love to hear your thoughts. Also feel free to subscribe to the feed here for more posts like this.
Related Posts At StraightpathSQL.Com
- Why I think you focus too much on your backups – As discussed in the presentation, the focus should be on restores. May seem like semantics, but the focus on restore means you are thinking of more questions.
- How Do You Install SQL Server? – The first of a 3 part series where we discuss some tips for installing SQL Server and planning for a successful implementation.
- SQL Server Benchmarking Tips – I introduced the Performance Analysis For Logs tool in this presentation, this post talks about that tool.
- Find All SQL Server Instances On Your Network – I talk about using the Microsoft Assessment and Planning toolkit to locate all of your SQL Server instances. This post describes my discovery and use of this great (and free) tool.
Ooo, the Boost Priority and Fiber Mode – those are both good ones too. Truncate backup history is a great one, very good catch.
Have to agree with Next, Next, Next, Finish. I’ve been saying this as well. I think it’s partially to blame when people don’t consider SQL Server to be Enterprise ready. It’s not the server, it’s the people you’ve got running it.
Express edition, though, will need to be kept simple.
I agree with SQL installation…at most they could give an option in there to configure the major things.
– backups: since model is a “model” of a new database, make a “model” of a backup, maybe a SQL agent job. So anytime a database is created a default backup job (at least full every week) is created. I would think this could get out of hand if ignored, example someone creating 100 databases, but it gives you something for those small companies to work with. Then maybe make it a sp_configure option that can be disabled at the user’s option when they want to start creating their own detailed jobs.
– along the lines of backup I would like to see the installations (initial installation, service packs, and cumulative update installations) take a backup of the system DBs prior to and after installation. Some kind of prompt comes up to specify the location of the “pre” and “post” backups.
– I agree with the backup history setting. They could do something similar to how you keep the error logs rolled over (keep 6 logs, 20 logs, 99 logs), the number of backup history days, weeks, months to keep could be an acceptable default setting of 30 days. if the user wants more, go change it.
Thanks for the comments Eric and Shawn. I also agree with Eric that Express should be kept relatively simple and agree with Shawn that those kind of settings could be toggled in an advanced configuration in SP_Configure. You figure the people that know how to disable the setting will likely (hopefully?) know a bit about what a proper installation looks like. I was almost picturing some sort of a wizard/tutorial combination for recovery models/backup strategy. Again, able to disable it but help those people who are hurting themselves and their environments with SQL Server installations even as we type these comments 🙂
Have to agree with Next, Next, Next, Finish. I’ve been saying this as well. I think it’s partially to blame when people don’t consider SQL Server to be Enterprise ready. It’s not the server, it’s the people you’ve got running it.
Express edition, though, will need to be kept simple.
Regards,
http://www.richonet.com
http://www.oracledba.in
I agree about cutting away old backup history. I wrote a proc to do this for SQLS 2000 for my last employer, so I know it’s very easy to do. But it appears that the maintenance plan wizard can include a backup history clean step, so I think MS has now done that one.
Another feature that is missing is the ability to script maintenance plans. I wrote a scripter for maintenance plans for SQLS2000 too, because I had many servers each running the same collection of databases (I mean same schemata but different data on each server) and using the gui on that many servers would have been a real pain. Not sure whether it would be possible for me to write such a thing for later versions because I’m not sure it’s still possible to write te tables concerned (haven’t looked see – too lazy now that I don’t need such scripts). MS have still provided nothing.