You can download the next preview of SQL Server 2016 today, and the announcement blog post touted a few improvements to maintenance plans. Oddly, the screenshots in the announcement were apparently done with Microsoft Paint, so I fired up a VM to see how things really look.
The DBCC CHECKDB step in the wizard now has checkboxes for physical only and tablock:
The index reorg task now lets you do LOB compaction, scan index stats, and then only optimize the index based on statistics:
The rebuild task has similar options in the GUI, plus options about keeping the index online:
Updating statistics – I honestly don’t remember what older versions looked like. (I don’t step through that wizard often.)
The options on the full backup task got some attention as well:
While improvements are usually a good thing, I gotta be honest: these screens are pretty haphazard. Stuff doesn’t line up, warnings don’t fit into boxes, and it feels terribly slapped together. It doesn’t give me a high level of confidence, certainly not enough to steer me away from Ola Hallengren’s much more powerful and flexible free scripts. I get it – not everybody wants to use add-on scripts – but I’m just not filled with confidence when I see these screens.
That could mean we’re still quite a ways away from a SQL Server 2016 release, which is good news and bad news.
14 Comments. Leave new
I can’t imagine navigating away from Ola H’s solution. It’s much easier to use and implement and has many more knobs to turn.
Minion Reindex won’t suffice as it needs xp_cmdshell permissions, a security hole many of us just aren’t willing to open.
This is a step in the right direction, but it’s going to take more than a few haphazardly assembled GUI screens and checkboxes to convince me to change off of Ola’s scripts.
Frankly, I’m getting really tired of the cmdshell debate. I don’t really think that Ola’s sqlcmd method is really any different in terms of opening you up to being able to mess up a box. It requires the job step to have a cmdshell step, which you can also do anything you want in. So how’s that honestly any different? You can lock them both down to specific users, and those users are still bound by their OS-level perms.
Do you seriously think you can’t lockdown xp_cmdshell?
And for the greatly enhanced features you get in both Minion Redinex and Minion Backup it’s worth locking down xp_cmdshell and turning it on. Plus, you can use the Pre and Post code sections to turn xp_cmdshell on/off so it’s only on when you need it.
Sean – I totally understand where you’re coming from, and I thought, “Ah, I bet there’s a page on MinionWare.net that explains how it works.” So I did a quick search, and it’s mentioned here:
http://minionware.net/reindex/
There’s a line that says “Here’s a link to a blog on the topic if you’d like further reading.” but it doesn’t seem to be linked to anything – can you link to your instructions on how to lock down xp_cmdshell?
I’ll have to fix that link on the site. As for cmdshell security, here’s something I just pulled off the web: http://www.practicalsqldba.com/2012/03/how-do-you-secure-sql-server-instance.html
It’s not only a decent piece on locking down cmdshell, but there’s some good discussion with 1 nice long reply.
Basically, it’s naïve to think that cmdshell is the panacea of keeping sql safe and it’s so easy to turn on/off. When you enable it, it’s only enabled for SAs, and they can do anything they want anyway. And you have to define a proxy acct for non-SAs to be able to use it.
So when I see people coming on and saying they don’t like cmdshell because it’s insecure, it tells me instantly that they’ve only listened to fear mongering for the last 2 decades and have never really bothered looking into it themselves. So use it, but lock it down to SAs and you’ll be fine.
And for everyone’s sake, stop perpetuating this myth that cmdshell is any more dangerous than many other things admins can do, including powershell. Any powerful technique can do major damage and cmdshell isn’t any more or less of a risk than any of the other things that you don’t seem to have a problem with. What, because you call cmdshell from tsql? Because it can be wrapped inside an SP? I consider that an extra layer of security as my users won’t be given perms to that SP or even that schema possibly.
OK, deep calming breaths – I wanna work *with* you and help, man.
If your product requires a non-default config on SQL Server, you’re always going to have people with knee-jerk reactions saying, “I don’t wanna do that.” It’s on *you*, not the end user, to do that education. Build a page explaining why the option is safe to enable, and how you’d lock it down yourself.
You do this every day, right? You’ve used Minion for years, and you know exactly how you’d lock it down in your own environments. It’ll be a piece of cake for you to write that. I know you’re good at documentation, and I want you to be able to quickly say, “Yep, I understand why you’re concerned, and here’s a post I wrote explaining how to do it the right way.”
Fair enough, you made some good points there.
I’ve been happy with it turned off for quite some time, I don’t see a need to enable it as it isn’t warranted in my environment. Remember, everyone’s environments are completely different. Some shops have teams of DBAs, some have one, some have none.
Thanks for the info, maybe you could write a blog post on this topic and shed some light on this information that we’re missing and address some of the misconceptions surrounding it (you seem to have quite a few and clearly feel passionate about it, that’s always a recipe for a great blog post).
Suhweet. I like the passion. I know Sean is a huge advocate of PoSH and I can really appreciate his point on cmdshell and security. Securing cmdshell shouldn’t be a reason to not use Minion – imho. The use of a credential, or turning it on/off when needed or not is easy enough, and the creation of a proxy account shouldn’t be terribly adverse to security.
I imagine Sean will have that blog post wrapped up about securing cmdshell soon enough. It is a tool after-all that when used properly can be good.
On the topic of maintenance routines, I am disappointed in the updates in SQL Server for the “canned” package(s). Despite the improvements they have made, I agree they seem haphazard. That is one big reason I am happy to see tools like Minion out there to help the “Accidental” dba or NoDBA shops – well even enterprise DBA shops for that matter.
Thanks Kris… of course every shop is different. But that doesn’t change the science of how security works. I wish you’d give the Minion tools a try. Our backup routine especially has features nobody else has. I won’t go into it now, but we’ve got a webinar with an overview of the product and we’ve got lots of individual tutorial vids. Here’s the beginning webinar for Minion Backup: http://www.minionware.net/wp-content/uploads/Vids/MinionBackupIntro/lib/playback.html
Yeah, I need to write another blog on the cmdshell topic. I have one already, but it’s very ranty. I tend to only write those blogs when someone gets my dander up. I’ll bang something out soon and put it up on the site in the FAQs for the products.
And of course, feel free to email me if you want to discuss further or if you have any questions about the products themselves. We may be getting slightly off-topic here and I don’t want to monopolize Brent’s comments.
Makes me wonder whether they made the emailing … useful. A subject of “SQL Server Message” (which is the default for sp_send_dbmail when you don’t specify a @subject) isn’t very helpful. And you can’t specify condition for the email (success/failure). Sure you can define tasks *within* the plan and use the Notify Operator task, but how much work can there be for a programmer to add a subject to the email?
(Yepp, I also use Ola’s procedures! )
Anyone else find it interesting that the Backup Task has an option to take a ‘Copy-only backup’ – in a *Maintenance* Plan? (i.e. routine / scheduled) While I can’t think of a scenario where this could actually impact a Recovery scenario, it seems to deviate from the ‘set and forget’ intent of Maintenance Plans.
CopyOnly comes in handy when you want to take a full backup on a non-primary AG replica.
Hi Brent, which SQL Server Edition doesn’t shows copy only backup option in maintenance plans.
Dinesh – can you rephrase your question? I can’t quite understand what you’re asking. Thanks!
Regarding screen shots: try Window Clippings. It’s perfect – even keeps window shadow without background behind it 🙂