June 18, 2009 at 7:12 pm
Comments posted to this topic are about the item Going Native
June 19, 2009 at 12:06 am
I'm a fan of the maintenance plans. It offers an easy way to implement a basic maintenance regime, which is useful for people who aren't full-time DBA's. Sure, you can get into trouble using the maintenance plans if you don't know what you are doing. But you can also get into trouble by NOT doing maintenance at all! Plus, it gives the new DBA an idea of the types of maintenance they can/should do. For more complex maintenance, the plans don't really offer enough flexibility but I still think they have their place.
James Stover, McDBA
June 19, 2009 at 12:33 am
As someone who has had no formal training on SQL Server of any version and was dumped into the DBA world by the business, I can say that the maintenance plans are a very tasty little inclusion.
People are right in that they aren't flexible and that scripts can do more, but they "should" be able to keep your server running along fairly smoothly if you have no idea what you are doing and no time/inclination to delve into its depths (keeping scale in mind here...).
Remember, not every business can afford the services of a professional DBA (however briefly), regardless of how much of a good idea it might be.
Personally, I still don't delve into things unless I need to, it's not my forte. But I am glad that if I need to find a better way to do something then I have already stumbled across a useful community that collectively knows pretty much anything you could every want to know about any version of SQL Server.
June 19, 2009 at 1:26 am
I'm a full time DBA who is capable of scripting and understands many of the intricacies of SQL Server but I still like and use maintenance plans. If I need more control over a certain aspect of SQL maintenance, a script running as a job can be used on an ad-hoc basis. My philosophy is why make work for yourself?
June 19, 2009 at 6:38 am
Just because we can script it doesn't mean we should. It is very similar to those who use utilities to make changes vs scripting it. Some argue you aren't good at your job if you can't script it. I say, if someone gives me a more efficient way to do something i will.
One issue i saw recently with scripting is a customer of ours that has a team of their own dba's said they would take care of setting up their backups because she has a script that will cycle through and create the backup files the way she likes them. Problem with that is her script is complex and way overkill for something as simple as a nightly full backup where a maintenance plan or a simple backup step and verify step.
I try to choose the best path possible and i know i get stuck in my favorite ways as well. Scripting gives you control to the n'th degree but do you need it. There is a reason that we create functions and dll's for repeatable tasks. I see wizards and gui apps as no different here. Someone created a useful, repeatable engine to do the same task over and over.
June 19, 2009 at 6:52 am
I'm more than capable of "rolling my own" when it comes to maintenance scripts, but I don't bother. SQL 2k5 maintenance plans work just fine for me, and I use them.
Yeah, the scripts aren't as flexible, intelligent, etc., as what I might do myself. I haven't been in a situation yet where that actually mattered.
When/if I'm in such a situation, I'll customize the scripts. Till then, I use maintenance plans.
I don't use a wizard to build them, if such even still exists (I haven't looked). I drag-and-drop the items I want, set the sequence, and then create the schedule.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2009 at 7:13 am
I agree with what everyone else has been saying so far. I use the maintenance plans for most of my databases, except for the few that need a little something extra. Then I write my own scripts. I do think it is important though that DBA's know how the maintenance plans work and what SQL Server is doing behind the scenes. Don't just run them blindly.
June 19, 2009 at 7:40 am
For someone who's an "accidental" or ocasional DBA, Maintenance plans are fine. They allow them to simply setup the basic maintenance of the database with little understanding of what these features are really doing.
For someone who's intermediate or higher though, scripting is definately the way to go. Some exaples of why follow:
- Backups
The features available here have some side effects which could be problems in larger environments, such as deleting old backup files is an all or nothing operation, where what would be preferable is that old backup files would be deleted for the databases who's current backup succeeded, and not deleted for databases who's current backup failed. The only way to avoid these side effects is to create a separate Backup Database Task and separate Maintenance Cleanup Task for each database instead of using the multi-select form, and make sure you get all of the process flow connections right, which can be alot of work. For creating easily repeatable and maintainable items scripting can be easier.
- Index Maintenance
The Reorganize and Rebuild Index Tasks allow you to select all or specific indexes, but what would be preferable is deciding to reorganize or rebuild or do nothing based on the actual fragmentation of each individual index.
- Notify Operator
A separate Notify Operator Task needs to be created in the maintenance plan for each potential point of failure. If multiple failure process flow connections are pointed to the same Task, they must all fail for the notice to be sent.
Scripting can also provide you with more advanced options in many cases for complex environments. I've also seen situations, (for unknown reasons) where maintenance plans seem to become corrupt and stop functioning properly. I've had to rebuild or replace a number of them over the years.
June 19, 2009 at 8:07 am
It's like so many other things. The wizard will take you a long way. It may even save your "life" (note the quotes) a time or two. But, like other wizards, You are left at the mercy of the wizard. Your plans are only as good as the folks that wrote the wizard.
I needed to change a bunch of widths on VARCHAR columns recently. I thought "SSMS will let me make the change using the GUI to my development box and I can save the script to do the production change." Hmm. The resulting script made a new table, did an update to copy the data, dropped the old table, and then did a rename. HOLD ON! There are millions of rows in these tables and multiple production servers. This will take hours PER SERVER to run for each table. I have to have the disk space for two copies of my largest table. That update is a logged activity and the whole thing is wrapped in a transaction. Oh, my aching log space! :w00t: ALTER TABLE ALTER COLUMN to the rescue. Takes around a second per table and does not touch existing data at all. Sweet.
As to maintenance plans: Sure that wizard can automate your backups and even have the file named for the date and time. What happens when you run out of disk space? You know that you will. How does it alert when if fails? You are watching your backups, right? Even the ones your custom scripts pump out. Especially those!
Take another lesson from central heating. It was cold in the house so my other half turned up the thermostat. Still cold. Cranked it up again. And again. Still again. Finally I hear this voice. "How's come I can't turn this up further?" What the ...? Sure enough it was against the stop. The pilot light was out and the furnace was not coming on at all.
Our congregation has a computer controlled heating and cooling system with micro-zones and a double fail safe controller. Very effective and very efficient. Still we were getting complaints that some of the rooms were too hot while the one across the hall was too cold. The sensors showed that they were within one half a degree F of each other. We went to the DIY store an bought a bunch of cheap thermostats. One in each room and several in the large gathering rooms. They are not hooked to anything at all, but no more complaints. (And you thought that this weird stuff only happens to Phil Factor.)
So would I recommend the wizard to an intermediate DBA? Sure, but with the "Dutch Uncle" advice that you watch what is does cuz' it's still your private parts on the block when things go wrong.
ATBCharles Kincaid
June 19, 2009 at 8:19 am
Just because you use a wizzard or maintenance plan to run jobs does not mean you should stop monitoring your systems. Even if you run your own scripts you should still monitor them. Running out of space can happen no matter which option you choose. Notifications can be set up either way as well. Problems occurr no matter how a job is set up. I don't think this discussion is about how jobs can fail, I think it is about whether we think maintenance plans can be useful to use and if they provide the functionality that we need.
June 19, 2009 at 8:24 am
I have more experience of 2000's maintenance plan facility, and of DTS. We're only now moving to 2005. Of the tools I know better, I prefer scripts because all of the options on system maintenance commands are clearly exposed if you want them to be, and because they're easier to control with quick-and-dirty programming: a trick that I use often is to compose a string in a script containing a SQL command with terms such as @{db} for database name. Then SET @workstring = REPLACE(REPLACE(@template, N'@{db}', @dbname), ...) to get the version that is executed. So where there isn't supposed to be a variable, I can have a variable. Elsewhere I have queries whose output is entire SQL commands to be used elsewhere.
The structure of maintenance plans is a completely separate, sophisticated, programmable way of doing the same thing. But why do that if you can already do it in SQL?
Right now I'm obviously also interested in portability of commands and routines to a new SQL Server version. (No one uses the latest version of any server for serious work, right?) Well, I don't trust Microsoft not to invalidate any maintenance plans I might ever be using. After all, Enterprise Manager is gone. I don't even trust service packs. (I use 'em, crazy not to, but I don't trust 'em. And you want to let other folks install them and have problems before you join them - SQL packs 2000-3 and 2000-4 both broke some people's systems. But you backed up before, right?) Transact-SQL system routines, however, I think MS will keep those running for me. And if they're programmed in a modular way, anything that I do need to change (DATA_PURITY) is a relatively small job.
June 19, 2009 at 8:36 am
I think it comes down to personality. I find two classes of personalities in this business: those that overgeneralize, and those that don't. 😛
Okay, seriously, the personality trait I look for is interest. If my protege has got the interest to want to grow and learn, I would recommend scripting -- and I would check their work for a while. If they are doing this job for the money, and don't really want to grow, throw the wizard at them -- and then check their work for a while.
And, of course, as said before, neither option negates the need to monitor the maintenance, regardless of its type.
June 19, 2009 at 9:10 am
I'm a big fan of the 2005 (and 08) maintenance plans. Huge improvement over 2000. They are very straightforward and can accomodate most of the scenarios that DBAs run into. If not - you can always add in your own custom T-SQL at any place in the plan. Less scripting work, less testing, more time for more pressing projects...
June 19, 2009 at 9:13 am
Interesting replies. I thought more people would go for scripting, but good to see them using maintenance plans.
The interest of the person, does make a difference, but I think I'd still have them start with maintenance plans and then move to scripts to enhance or replace them.
June 19, 2009 at 9:27 am
As an Oracle DBA, who had to become a SQL Server DBA too--the Maintenance Plans were very valuable. However, I did have to babysit them for a while. For example, one of the SQL Server versions didn't delete the old backups from the subfolders (since fixed with a Service Pack). And I had to figure out that the "Cleanup" Tasks clean up different things (not necessarily old backups).
It also took me a while to get a feel for "best practices"--which tasks to run and in what order.
For my purposes, the Maintenance Plans work just fine, but you can't just set 'em and forget 'em.
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply