Friday, 13 April 2012

Veeam Backup and Replication: Obtaining a Gantt Chart Report to Identify Runtime Conflicts

Scenario:
A SysAdmin needs a graphical report displaying when all the backup and replication jobs run, and for how long, in order to identify if there is any runtime overlaps within the current job setup, and recommend if any changes are needed to the current schedules to optimize the backup process.
Fig. 1: Example report showing what we are setting out to achieve

Walkthrough:

Part 1: Obtaining a CSV Report from SQL
1.1 From a machine with a suitable version of SQL Server Management Studio installed (here using 'Microsoft SQL Server 2008 R2 – Management Studio Express',) and using a suitable set of credentials; connect to the SQL Server homing the VeeamBackup Database.
1.2 Click on the 'New Query' button, and in the SQLQuery pane, paste the following:

SELECT TOP 1000 [job_name]
,[creation_time]
,[end_time]
FROM [VeeamBackup].[dbo].[ReportSessionsView]
where [creation_time] > '2012-04-04 17:00:00.000' and [creation_time] < '2012-04-11 17:00:00.000'
order by [creation_time] desc;

1.3 Edit the creation_time start time and end time as per requirements, then click on the '! Execute' button.
1.4 By default, this will display the results in a grid. Click on the top left corner of the grid to select all –
– then from the File menu > Save Results As... > and save the file as a CSV (Comma delimited) file.

Part 2: Creating the Gantt Chart using Excel 2010
The steps to create the Gantt chart only need going through once, after setup can simply import new data.
2.1 Open the CSV file from Part 1 using Excel 2010.
2.2 In D1, input the formula =c1-b1 and use the Fill button on the Home Ribbon to complete column D

2.3 Format column B as dd/mm/yy hh:mm, column D as hh:mm:ss, and we can hide column C
2.4 Select the range A1:B? (where ? is the last row you want to include in your data) and from the Insert Ribbon select Bar > 2-D Bar > Stacked Bar
In the example below we are using A1:B14
2.5 From Chart Tools > Layout Ribbon > Legend > Select None : Turn off Legend
2.6 On the Design Ribbon click 'Switch Row/Column'
2.7 Right-click the horizontal axis, and select 'Format Axis...' Tick the box for 'Categories in reverse order', select the radio button for 'Horizontal axis crosses: At maximum category', and Close the 'Format Axis' dialog box
2.8 Right-click on any bar and select 'Format Data Series...'
Check that the 'Series Options: Series Overlap' is set to 100% Overlapped
Set the Fill radio button to 'No fill'
Set the Border Color radio button to 'No line'
Close the 'Format Data Series' dialog box.
2.9 On the Chart Tools > Design Ribbon, click on 'Select Data'
Click on Add
Give the Series a name such as 'Duration'
Click the button to the right of the 'Series values' field, then select the range D1:D? (where ? is the last row you want to include in your data) on the spreadsheet.
In our example below we are using D1:D14
Finally, click the button to the right of the Edit Series dialogue box, click OK to close the 'Edit Series' dialog box, click OK to close the 'Select Data Source' box, and voila!

Credits:

APPENDIX
To see the complete list of ReportSessionsView column titles:
SQL Server > Databases > VeeamBackup > Views > dbo.ReportSessionsView > Columns

7 comments:

  1. Wow, that's incredible! I work with both SQL Server and Excel and have never thought of doing that. I'll have to give it a try.
    Thanks for the step by step process as well!

    ReplyDelete
  2. Just tried this out. Very nice. I never really had decent visibility into job overlap and duration from a timeline perspective. Thank you.

    ReplyDelete
  3. Why not just let Veeam manage the desired job concurrency automatically? No matter how finely you tune your schedule, it will be immediately ruined by any fluctuation in the environment (big disk changes on some VM, network/storage congestion etc), resulting in multiple jobs overlapping. This would never happen if you let Veeam manage job concurrency though.

    ReplyDelete
    Replies
    1. Hi Anonymous, thank you for the comment. You raise a good point. It is not necessarily a problem to have job overlap, this method is just a tool to get visibility of what is going on. Cheers!

      Delete
  4. This is great stuff! I just went through a backup evaluation and at the start of it I thought that I was going to go with Unitrends hands down. After the long process of evaluating Unitrends new UEB software, I ended up going with Veeam and have not been happier. People live and die by their backups in IT, so if you found this page while thinking about switching backup solutions go with veeam.

    Also, another tip is to stay far far away from Backup Exec!

    ReplyDelete
  5. interesting stuff, i might search for more information about this, thanks a lot friend.

    cloud backup

    ReplyDelete
  6. Thanks it really works! It will save my life!

    ReplyDelete