June 30, 2010 at 6:34 am
Hello,
We had one of our DBAs go to a SQL Server Admin class. The instructor, well known SQL Server Guru, had a discussion on maintenance plans.
From my hearing of the class, the instructor suggested that maintenance plans can be done away with by using scripts. The instructor mentioned that he doesn't use maintenance plans. He creates his scripts and then creates an SQL Agent job to run his script.
I am used to creating packages since DTS and now SSIS packages. I am familiar with branching and scripting within a package.
Do more DBAs script their own backup plans?
We need to check:
1) Is there a full backup for today, if not create one.
2) Check the disk space and if it is low, email us.
3) Check to insure there are transaction log for each database.
4) Check to see if there are at least a 7 day retention on backups and transaction logs. If so, delete all files outside of the 7 day retention.
I've always used SSIS for administering our databases. At times, I use the execute TSQL statement task . I need some pointers from some of the more senior dbas in this forum. Is SSIS just for beginners and then we evolve into administering by creating our own scripts?
Our manager wants to know if we should get rid of all of our maintenance plans and create "intelligent" scripts to do everything. So I thought I would ask the SQL community what is the prevailing thought. Use SSIS/maintenance plans or create scripts?
Thanks for the feedback!
Tony
Things will work out. Get back up, change some parameters and recode.
June 30, 2010 at 8:40 am
Tony,
There is no better, just different. Main Plans (MP) allow you to quickly set up maintenance. It works in a predictable manner, so that anyone coming in can understand what is happening. Limitations and bugs are documented, and it's easy to get help if you leave the job.
However there are limitations. Scripting gives you more capabilities, at the expense of a (less) well tested solution. Building something outside of an MP means that you are also spending time on this. Is that important enough to justify the time? do you have something else to do? It's Build v Buy (MP).
If you can do your own, I'd encourage you to do it since you learn things, and you can build in more of the flexibility that you have below. Lots of scripts here in T-SQL to help, and you can add in some of the tasks from MPs to fill in places if you want them. Or use something like PowerShell.
June 30, 2010 at 8:46 am
WebTechie38 (6/30/2010)
From my hearing of the class, the instructor suggested that maintenance plans can be done away with by using scripts. The instructor mentioned that he doesn't use maintenance plans. He creates his scripts and then creates an SQL Agent job to run his script.
I would tend to agree with him. Maint plans are fine for smaller DBs, shops that don't have dedicated DBAs, but there's so much that you can't do with maint plans
eg: diff backups. Backups with checksum. Compressed backups (unless it's the server default) Selective index rebuilds. Selective stats updates, etc
For places without dedicated DBAs, maint plans are probably best. For places with small databases, maint plans are usually fine. Bigger databases where you don't want to rebuild all indexes, or want specific options on backup you're likely to need to write (or locate) an appropriate script.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2010 at 8:53 am
Gail,
Thank you very much!!
I was going to buck the idea, but your advice was what I needed. I will be open to the idea of creating our own scripts. As my manager said he needed, "intelligent scripts" to do our administering.
Thank you for the great feedback.
Things will work out. Get back up, change some parameters and recode.
June 30, 2010 at 8:56 am
Steve,
Thanks for you feedback.
I am busy and by no means just have time to waste. However, if it will benefit our company in the end, the I will look at scripting some more. Actually, I am pretty competent at scripting.
I've created some very good stored procedures. I just had never thought of scripting jobs that do what is done in maintenance plans.
Thanks again for your thoughts!
Tony
Things will work out. Get back up, change some parameters and recode.
June 30, 2010 at 9:08 am
WebTechie38 (6/30/2010)
As my manager said he needed, "intelligent scripts" to do our administering.
Don't script for the shear sake of scripting. Take a look at your environment, decide if you need 'intelligent' script and, if so, for what. Don't just go and replace all maint plans with scripts that do exactly the same because the manager feels they're better. Scripts give you more options to customise the maintenance, decide where (if) you need that customisation.
Are the index rebuilds taking too long and using too much log? Consider switching to a script that rebuilds just what's fragmented
Would it be nice to verify that a backup is not backing up corrupt data? Consider switching to a script that backs up the databases with the checksum option
etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2010 at 9:14 am
I'd agree with Gail here, it doesn't have to be one or the other. It can be a mix. Do what makes sense for the business.
June 30, 2010 at 10:20 am
Excellent point Gail. That was exactly the impression that I got.
If it is not broken, don't fix it unless there are more options we could add.
Got it.
This has been very helpful.
Thanks Again.
Tony
Things will work out. Get back up, change some parameters and recode.
June 30, 2010 at 10:24 am
Steve,
I will do an impact analysis for each server. I will weigh pros and cons about getting rid of the maintenance plan and creating a new script.
I appreciate the help and points from you and Gail.
Thanks,
Tony.
Things will work out. Get back up, change some parameters and recode.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply