SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Monitoring SQL Server backups
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Abu-Dina
Posting Yak Master

United Kingdom
206 Posts

Posted - 09/21/2012 :  09:08:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2046 Posts

Posted - 09/22/2012 :  02:41:43  Show Profile  Visit jackv's Homepage  Reply with Quote
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.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Abu-Dina
Posting Yak Master

United Kingdom
206 Posts

Posted - 09/24/2012 :  04:54:42  Show Profile  Reply with Quote
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.html

Jack 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?


Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2046 Posts

Posted - 09/24/2012 :  11:40:56  Show Profile  Visit jackv's Homepage  Reply with Quote
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.databases
2) or each database that doesn't have a backup in the last 24 hrs.



Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

m.shaghaghi
Starting Member

Iran
1 Posts

Posted - 09/25/2012 :  04:16:06  Show Profile  Reply with Quote
Hi Abu-Dina
We Read your solution about job monitoring. That’s great.
Also we have 6 servers with about 80 databases and had such problem. We
Solve 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
Go to Top of Page

Abu-Dina
Posting Yak Master

United Kingdom
206 Posts

Posted - 09/25/2012 :  07:41:12  Show Profile  Reply with Quote
quote:
Originally posted by m.shaghaghi

Hi Abu-Dina
We Read your solution about job monitoring. That’s great.
Also we have 6 servers with about 80 databases and had such problem. We
Solve 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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000