Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Abu-Dina
Posting Yak Master
206 Posts |
 Posted - 2012-09-21 : 09:08:14
|
| Good afternoon,Apologies for this long post but I would really appreciate some feedback on my approach to solving the following problem of monitoring backups. This process will need to monitor database backups on 28 SQL Servers but for the time being I would like to get the process working for 1 server.Current setup:1 SQL Server - 10 Databases Backup schedule: Fortnightly full backups + nightly differentials.Full backups:There is an unscheduled SQL Agent job setup on each SQL Server instance which is manually run by our IT manager every 2 weeks. This job loops through the list of databases and does a full backup of each one.Differentials:Scheduled job on each SQL Server instance which runs every day at midnight.Unfortunately DB mail is disabled on ALL servers and due to security reason. So I’m unable to use notifications to send mail if the backup job fails.Solving the problem:1) Schedule the full backup jobs to run every two weeks on Saturdays at 6PM and the daily diffs to run at midnight every day.2) Setup a utilities server which I will use to hold my DBA repository, this will have SQL Server DB engine + SSIS + SSRS installed on it.3)Trigger a scheduled job on my utilitties server to run at 8AM every morning to check for database backups from the last 24 hours. If there no rows returned then trigger an email notification. (I will enable DB mail on this utilities server).4) Trigger another scheduled job to gather information about the previous days backup, this will be information like backup start and end time, backup size, duration etc..Any advice would be much appreciated.Thanks. |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-22 : 02:41:43
|
| For step 3 - does this mean if 1 row is resturned then an email will not be sent? Ensure the logic is : loop through every sql server instance and report on any exception.Are you checking the success of the job or the the backupset table? If using backup set this cript may help:http://www.sqlserver-dba.com/2012/06/display-sql-backup-history-for-a-single-database.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-09-24 : 04:54:42
|
quote:
Originally posted by jackv
For step 3 - does this mean if 1 row is resturned then an email will not be sent? Ensure the logic is : loop through every sql server instance and report on any exception.Are you checking the success of the job or the the backupset table? If using backup set this cript may help:http://www.sqlserver-dba.com/2012/06/display-sql-backup-history-for-a-single-database.htmlJack Vamvas--------------------http://www.sqlserver-dba.com
I think I know what you mean. So the logic should be something like this:1) Loop through each SQL Server and check the status of the scheduled backup job. a) If Success then poll backup information from last 24 hours.b) If failed then update someTable (Daily_Job_Checker table?) in central DB.2) Create a job on the central server to check the Daily_Job_Checker and generate an email for each server with a failed backup job?What do you think? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-24 : 11:40:56
|
| 1a) yes 1b) It may be better to check if every database has a relevant backup in the last 24 hrs (or whatever criteria you feel is appropriate. Add a join to sys.databases2) or each database that doesn't have a backup in the last 24 hrs.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
m.shaghaghi
Starting Member
1 Post |
Posted - 2012-09-25 : 04:16:06
|
| Hi Abu-DinaWe Read your solution about job monitoring. That’s great. Also we have 6 servers with about 80 databases and had such problem. WeSolve this with programming that registered all server on that for one time and after that every time that run program , Connect to all servers and check jobs and report last fail job status. Then DBA could see at a moment what servers have errors and what job on that server failed, without Connect to every server for each time. Could you say me more about last step (Generate an email for a record on Daily_Job_Checker table) , How do you Generate email per failed record.Thanks |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-09-25 : 07:41:12
|
quote:
Originally posted by m.shaghaghi
Hi Abu-DinaWe Read your solution about job monitoring. That’s great. Also we have 6 servers with about 80 databases and had such problem. WeSolve this with programming that registered all server on that for one time and after that every time that run program , Connect to all servers and check jobs and report last fail job status. Then DBA could see at a moment what servers have errors and what job on that server failed, without Connect to every server for each time. Could you say me more about last step (Generate an email for a record on Daily_Job_Checker table) , How do you Generate email per failed record.Thanks
Hello,Thanks for your reply.I have databse mail enabled so my stored procedure will use EXEC msdb.dbo.sp_send_dbmail to send the mail.Thanks. |
|
|
|
|
|
|
|
|
Monitoring SQL Server backups