Database maintenance with SSIS or T-SQL?

Question

Is there any benefit to using SSIS over T-SQL to check integrity, defrag/rebuild indexes, and backup databases? Doesn't SSIS break down its internal tasks and containers into basic T-SQL statements anyways?

Answer 1

Use what you're comfortable with, a rebuild is a rebuild. Just standardize on one and keep everything in one or the other (not both) or you'll duplicate/miss something.

Answer 2

You got it. When you use the maintenance wizard, it actually creates SSIS packages and T-SQL statements in the background to do your bidding.

The wizard is pretty good in that it covers most of the things that you need.

Answer 3

SSIS would be a good solution if you've got a single person (say, you) who is both doing DBA tasks for the server (e.g. backups) as well as other more application-driven tasks (higher level consistency checks related to business logic, ETL tasks for big data sets, etc). It'll allow you to keep everything together, which is convenient. It also allows you more control over how the things are kicked off, scheduled, and monitored (you can write the results into custom tables, have it send email, run arbitrary code, etc).

I'm not sure how many pure DBAs use SSIS for their tasks ... but for that matter, I'm not sure how many "pure" DBAs are left in the MS SQL Server world anyway, since so much of it is automated these days. :)

Answer 4

I think SSIS is easier to read and maintain than "pure" SQL, and it supports scripting (only in VB, but at least that's a start), so you can take leverage the CLR. Also, it's much nicer than DTS, which was the old way of maintaining those types of tasks in MS-Land. The tradeoff is that you have to learn all the stupid Microsoft jargon, instead of just writing SQL... Which is more fun. If you maintain a lot of servers, and you've made the investment in MS SQL server, why not take advantage of it?