April 9, 2009 at 5:15 pm
Should I create the maintenance plans running jobs as T-SQL or leave them as Integration Services job types. Thus, install Integration Services in a cluster environment if not being installed yet on a functional 2008 server so the jobs can run?
In another word; in a cluster environment do you recommend using Integration Services to run your maintenance plans and jobs or should you choose TSQL instead.
make sense?
thx
Cheers,
John Esraelo
April 9, 2009 at 5:26 pm
I think it depends on how comfortable you are with scripting. Personally I think maintenance plans are good basic maintenance for the general DBA, but they limit you.
I like to have more control over rebuilding indexes, managing backups, etc. I'd be very tempted to write and script my own.
Note that in SS2K5 after SP2, you shouldn't need SSIS. The packages should run without this being installed.
April 9, 2009 at 5:56 pm
Very good point, in fact my colleague and I were working on this and I have converted the "run as" line from ssis to tsql and the "parse" option / button reported an error and I just continued (while ignoring 🙂 and of course the excution failed for not understanding a / (slash) in the string. So, at this point I guess I am trying to find the most effective way of handling this. 😎
You see, I have full, differential and transactional backups going on and I have been relying on the ssis packages for the last couple of years until now that I am working on 2008.
So, I am having a mix motions and emtions being sensed here at work on whether we should use the SSIS packages for maintenance or not.
well, I guess I can write my own script, it's just that I have not done the scripting since sql2000. 🙁 and at the same time the Integration Services for some reason was skipped during the sql server 2008 (functional) cluseter server. And I
anyway, if you have more on dos and dont I would love to read them.
thx for the reply friend.
Cheers,
John Esraelo
April 9, 2009 at 6:11 pm
One of the things you might want to consider is the catch all task in SSMS Maintenance Plans. That is, you have an Execute SQL Task available that will allow you to execute any SQL you want.
For one of my systems I have the following in a maintenance plan:
First Task: Check Database Integrity - Execute SQL Task with code as:
DBCC CHECKDB(db1) WITH ALL_ERRORMSGS, PHYSICAL_ONLY;
DBCC CHECKDB(db2) WITH ALL_ERRORMSGS, PHYSICAL_ONLY;
DBCC CHECKDB(db3) WITH ALL_ERRORMSGS, PHYSICAL_ONLY;
Second Task: Rebuild/Reorganize Indexes - Execute SQL Task, with code as:
Execute msdb.dbo.AutoReindex 'db1';
Execute msdb.dbo.AutoReindex 'db2';
Execute msdb.dbo.AutoReindex 'db3';
And so on. The reason I do this is because I can get the history logging and organization of maintenance plans, but still get to run the procedures and code I need to.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 9, 2009 at 9:18 pm
useful information, thank you Jeffrey
Cheers,
John Esraelo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply