Best Practice for Centrally Managing Backup Jobs (or other maintenance)

Question

Best Practice for Centrally Managing Backup Jobs (or other maintenance)

michelle.kraeger

SSC Enthusiast

Points: 163
More actions
August 11, 2010 at 3:08 pm

#225816

I am currently the supervisor of a SQL Server database adminstration team supporting over 4500+ databases and 400+ physical servers. Prior to my current role I was an Oracle database administrator for over 10 years. In Oracle we had methods of centrally managing/scheduling database backups (OEM, RMAN, etc.). Is there a best practice or a recommendation on how we could architect a solution to manage all of our backup jobs/schedules from one central location that we could report against? Currently each server/database has their own jobs. Their is no way to look at an enterprise-wide view of schedules and/or failures. While SQL 2008 is our preferred standard version, we support all versions of SQL currently (2000,2005,2008) so we would prefer a solution that would work for any version; however if it wouldn't work for 2000 that would be acceptable since we are phasing that version out. Microsoft DPM (Data Protection Manager) is not a viable option at this point due to costs and some constraints in writing to DFS (distributed file systems). Any help or suggestions would be greatly appreciated!!
Thanks,
Michelle

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

I worked for a company not long ago, and I was the only SQL DBA there. I had many servers with many databases on some, and only a few to a single on others.

I created a SQL database on one of the boxes, that went out and gathered the schedules of all the jobs on each server. Along with other things from each server and each database on each server. If I added a database to a server, or a complete server to the list, everything was dynamic. I did not have to code anything any where.

I created an ASP.NET front-end that I could easily see all at once, it contained all the completed jobs, I could see any that had failed. I used SSIS for the backups, so if anything went wrong, it would restart itself. I had a great report of everything that I needed, all in one place. I could also see drive space of each server. I could see each server singularly, or I could see a page of all servers that had something wrong. The list is almost endless.

I had created this because of the Oracle group that I worked with. If I wanted to take a day off, they monitored everything for me. I had set up notifications, so if anything was down hard, I would get notified via email, as would the group. This was created so that at anytime, any person in the Database group could easily be notified, see the results, and if needed, start or stop a SQL job from the front end. They could also run a SSIS package at will.

I found that worked out really well, I could control all my servers from any where on earth(with an Internet connection any way.), and there was no software to purchase. So everything was free. No license to purchase.

I did code in a safe guard, if my user account was every deleted or disabled from Active Directory, all the code would self delete in the databases. I had encrypted all the stored procedures, so no one could see them any way. The web pages would delete and the dll file would become corrupt. The same named dll file in the .NET app could not be used. Keeps them from restoring from backup. Keeps everyone honest. I developed the entire database and app at home. So they could not sue me for destroying "their" software.

Andrew SQLDBA

pankushmehta Ten Centuries Points: 1217 More actions · Answer 2

Hi,

you can have a centralized server for Database Administration, where you can create SSIS package to monitor the job status for every server.

You can also create a centralized backup strategy, but if the server goes down, that means the backups will not be taken and if you do not have appropriate alerting set up to inform you, you can loose a lot of valuable data if a disaster strikes!

I have backup jobs on each server, with centralized job monitoring, which saves me from going to each server and monitor the job individually.

Paindu SSC Enthusiast Points: 112 More actions · Answer 3

Hi,

I am an Oracle DBA and have been given the responsibility to manager SQL Server databases as well. I am also looking for some centralized backup job monitoring solution and would appreciate if you could share your experiences.

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

There is a master/target type of relationship for managing jobs in SQL Agent, but I have never liked it.

I have preferred to have each box manage it's own backups, and store information about it's state. After all, if the central server is down, then do you forego checking reports?

I would have a central box somewhere, or maybe a central box for each group/class of servers that does roll up the data from other servers. Then build a report from that, but if it's down, you can check each box, and still get a report of data.

crazy4sql SSCoach Points: 19590 More actions · Answer 5

Agree with Steve, you should let the backup on specific server. But in addition, you can schedule another job which will copy the backup from all server to that single tape. And you can maintain only latest day or 2 backup in that location.

----------
Ashish

Paindu SSC Enthusiast Points: 112 More actions · Answer 6

Hi,

Can MS Operation Manager be used to schedule and mange backups from single point? Any other tool one can recommend?

Thanks

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 7

for something of this size give your friendly Symantec sales rep a call and tell him you want to buy Netbackup Enterprise Server

we use an HP tape library to store our backups. played with disk to disk backups but i still prefer tape. LTO-4 tape is fast, cheap and i've seen a single tape store 3TB of data. the official numbers are 800GB/1.6TB but it's actually less than what a lot of people see in the real world. i thought i was crazy when i noticed this but people on backup forums told me that in this case the manufacturers underestimate the real world performance.

there are other good products out there like Commvault, but Netbackup has the most market share and it's pretty good. just don't use it before the first service pack, really

and unless you are running Windows 2008 R2 everywhere you would have to be crazy to backup across servers or copy backup files since the original SMB protocol is crap

as an example my fastest restore time from tapes is a database of 250GB-300GB in 60-90 minutes. we have restored to this server using the native SQL backup and it's like watching trees grow

for backups the same database will take about 90 minutes to backup