Jeff Moden (2/26/2015)

---

Moin Ul Haque (2/26/2015)

---

Jeff Moden (2/25/2015)

---

Moin Ul Haque (2/25/2015)

---

I can quote my own experience here - Out of need, I was asked to develop a database maintenance solution which would monitor several things on our SQL server infrastructure e.g. free drive space, index fragmentation and recommendation, manage backups, archive data and some other tasks. Since we didn't want to spend loads of money on off the shelf solutions, i built the whole thing in SSIS. It is configurable so you can add servers to the "config" database and it will perform the configured maintenance on all target servers. It used PowerShell scripts and standard SSIS components. In all honestly I would say that SSIS solution was FAR from "just an ETL".

Just curious... what happens to backups on other machines if the machine this SSIS package is on is down/offline?

I missunderstood your question initially 🙂 The backups are copied over to a shared backup server and deleted from the servers' local drives. Unfortunately there's no failover mechanism currently in place if the SSIS machine itsellf is down. Luckily we have SCOM monitoring on the admin servers and I get alerts on my phone if it goes down so it kinda works well in my scenario. Besides if the admin servers are down, then I have more important things to worry about than just backups to be honest 🙂 I will certainly think about adding some redundancy around this. Thanks for pointing it out.

Thanks for the feedback. I'd seriously worry about the backups on the other machines if the SSIS machine is down because if no PIT backups are being taken, all of the logfiles are going to grow well beyond what you want them and then you'll have to fix them all. You also stand a chance of the ones "on the fringe" of actually running out of disk space depending (of course) on what they do and how much extra room you have on the log file drive(s).

That's why I won't rely on any such "central management" for backups even if it's thoughtfully designed. I'll admit that I also don't have 100s of servers but, if I did, I'd find a way to easily make each of them autonomous for backups.