SQL Server Backup Dashboard


Situation:
   
            For production support DBAs, its always be a requirement to publish backups and maintenance jobs daily/weekly dashboards. In some organisations, it is a mendatory requirement to meet the compliance.

           I am proving the powershell script that can produce this dashboard in Excel format. This script reads the SQL Server instance names from the external text file and produces the consolidated output for these instances. DBAs can scheule this script through windows task scheduler or they can send the output file as an attachment to the intended audience.


Solution:

 #################################################################################
# This script is use to generate SQL Server DBA maintenence jobs last run Dashbord in EXCEL format.
# This script uses different currColor coding schemes to quickly visualise if the last run of the job successful, failed or not run.
# This scipt uses EXCEL object to create the dashboard.
#Author : Ashish Gupta
#################################################################################

# Create a new Excel object
$myxl = new-object -comobject excel.application

#Set is TRUE if you want to see the real time data fill
$myxl.Visible = $False

$mywb = $myxl.Workbooks.Add()
$myWorkSheet = $mywb.Worksheets.Item(1)
$myWorkSheet.Activate()| Out-NULL


$date = Get-Date
$nextFileName = (get-date ).ToString('ddMMMMyyyhhmm')

$OldSqlServer = ""


#Creating Main Header
$myWorkSheet.Cells.item(1,1).font.bold=$TRUE
$myWorkSheet.Cells.item(1,1).font.size=20
$myWorkSheet.Cells.item(1,1)="Report on DBA Maintenence Jobs on :  $date"
$range=$myWorkSheet.Range("A1","E1")
$range.Style = 'Title'
$range.Merge() | Out-Null
#$range.VerticalAlignment = -4160
$range.Font.Bold=$True


#Color Coding scheme for job outcomes
$myWorkSheet.Cells.item(2,1)="Last Job Run Older than 1 Day"
$myWorkSheet.Cells.item(2,1).font.size=7
$myWorkSheet.Cells.item(2,1).Interior.ColorIndex = 43
$myWorkSheet.Cells.item(2,2)="Last Job Run Older than 7 Days"
$myWorkSheet.Cells.item(2,2).font.size=7
$myWorkSheet.Cells.item(2,2).Interior.ColorIndex = 53
$myWorkSheet.Cells.item(2,3)="Successful Job"
$myWorkSheet.Cells.item(2,3).font.size=7
$myWorkSheet.Cells.item(2,3).Interior.ColorIndex = 4
$myWorkSheet.Cells.item(2,4)="Failed Job"
$myWorkSheet.Cells.item(2,4).font.size=7
$myWorkSheet.Cells.item(2,4).Interior.ColorIndex = 3
$myWorkSheet.Cells.item(2,5)="Job Status Unknown"
$myWorkSheet.Cells.item(2,5).font.size=7
$myWorkSheet.Cells.item(2,5).Interior.ColorIndex = 15


$currRow=4
$currCol=1
$countSuccess = 0
$countFailed = 0
$count1DayOlder = 0
$count7DayOlder=0

$myWorkSheet.Cells.item($currRow,$currCol)="Server"
$myWorkSheet.Cells.item($currRow,$currCol).font.size=14
$myWorkSheet.Cells.item($currRow,$currCol).font.Bold=$True
$currCol=$currCol+1
$myWorkSheet.Cells.item($currRow,$currCol)="Job Name"
$myWorkSheet.Cells.item($currRow,$currCol).font.size=14
$myWorkSheet.Cells.item($currRow,$currCol).font.Bold=$True
$currCol=$currCol+1
$myWorkSheet.Cells.item($currRow,$currCol)="Job Enabled"
$myWorkSheet.Cells.item($currRow,$currCol).font.size=14
$myWorkSheet.Cells.item($currRow,$currCol).font.Bold=$True
$currCol=$currCol+1
$myWorkSheet.Cells.item($currRow,$currCol)="History"
$myWorkSheet.Cells.item($currRow,$currCol).font.size=14
$myWorkSheet.Cells.item($currRow,$currCol).font.Bold=$True
$currCol=$currCol+1
$myWorkSheet.Cells.item($currRow,$currCol)="Last Run Time"
$myWorkSheet.Cells.item($currRow,$currCol).font.size=14
$myWorkSheet.Cells.item($currRow,$currCol).font.Bold=$True
$currCol=$currCol+1
$currRow=$currRow+1


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

# Load the list of sql server instances from external file
$SqlServers = Get-Content 'C:\Script\Server_List.txt';#Change this path as per your convenience

# Loop through each sql server from Server_List.txt
foreach($currSqlServer in $SqlServers)
{
      # Create an SMO Server object
      $SQLSvr = New-Object "Microsoft.SqlServer.Management.Smo.Server" $currSqlServer;

      # For each jobs on the server
      foreach($job in $SQLSvr.JobServer.Jobs)
      {


   if (($job.Name -Like "*Backup*") -Or ($job.Name -Like "*Integrity*") -Or ($job.Name -Like "*Index*") )
           {
            $jName = $job.Name ;
            if ($job.IsEnabled -eq "TRUE")
                {$jEnabled = "True"}
            elseif ($job.IsEnabled -eq "FALSE")
                {$jEnabled = "False"}
            $jEnabled = $job.IsEnabled;
            $jLastRunOutcome = $job.LastRunOutcome;
            $jLastRunTime = $job.LastRunDate ;

         
            if($jEnabled -eq "False")
            { $Colourenabled = "2" }
            else
            {$Colourenabled = "48"}

            # Set Cell Colour RED for Failed jobs
            if($jLastRunOutcome -eq "Failed")
              { $currColour = "3"
                $countFailed = $countFailed + 1
              }
           
            # Set Cel Colour GRAY for Uknown jobs
            Elseif($jLastRunOutcome -eq "Unknown")
                  { $currColour = "15"
                  }

            # Set Cell Color GREEN for successful Jobs
               else
         {$currColour ="4"
                  $countSuccess = $countSuccess + 1
                 }

    $currRow=$currRow+1
    $currCol=1
    $myWorkSheet.Cells.item($currRow,$currCol)=$currSqlServer
    $currCol=$currCol+1
    $myWorkSheet.Cells.item($currRow,$currCol)=$jName
    $currCol=$currCol+1
    $myWorkSheet.Cells.item($currRow,$currCol)=$jEnabled 
   # $myWorkSheet.Cells.item($currRow,$currCol).Interior.ColorIndex = $ColourEnabled

       if ($Colourenabled -eq "48")     
    {
        $myWorkSheet.Cells.item($currRow ,1 ).Interior.ColorIndex = 48
        $myWorkSheet.Cells.item($currRow ,2 ).Interior.ColorIndex = 48
        $myWorkSheet.Cells.item($currRow ,3 ).Interior.ColorIndex = 48
        $myWorkSheet.Cells.item($currRow ,4 ).Interior.ColorIndex = 48
        $myWorkSheet.Cells.item($currRow ,5 ).Interior.ColorIndex = 48
     }
    $currCol=$currCol+1

    $myWorkSheet.Cells.item($currRow,$currCol)="$jLastRunOutcome"
    $myWorkSheet.Cells.item($currRow,$currCol).Interior.ColorIndex = $currColour

 
    $currCol=$currCol+1

    $myWorkSheet.Cells.item($currRow,$currCol)=$jLastRunTime

    #Set the Fill currColour for Time currCell
     If($jLastRunTime -lt ($(Get-Date).AddDays(-1)))
    { $myWorkSheet.Cells.item($currRow,$currCol).Interior.ColorIndex = 43
      $count1DayOlder =$count1DayOlder + 1
    }
        If($jLastRunTime -lt ($(Get-Date).AddDays(-7)))
            { $myWorkSheet.Cells.item($currRow,$currCol).Interior.ColorIndex = 53
              $count7DayOlder = $count7DayOlder + 1
            }
         
    }
    }
 
    }


$myWorkSheet.Cells.item($currRow+2,1) = "Total successful jobs:"
$myWorkSheet.Cells.item($currRow+2,1).font.bold=$True
$myWorkSheet.Cells.item($currRow+2,2) = $countSuccess
$myWorkSheet.Cells.item($currRow+2,2).font.bold=$True
$myWorkSheet.Cells.item($currRow+3,1)= "Total failed jobs:"
$myWorkSheet.Cells.item($currRow+3,1).font.bold=$True
$myWorkSheet.Cells.item($currRow+3,2) = $countFailed
$myWorkSheet.Cells.item($currRow+3,2).font.bold=$True
$myWorkSheet.Cells.item($currRow+4,1) = "# of Jobs last run more than 1 days:"
$myWorkSheet.Cells.item($currRow+4,1).font.bold=$True
$myWorkSheet.Cells.item($currRow+4,2) = $count1DayOlder
$myWorkSheet.Cells.item($currRow+4,2).font.bold = $True
$myWorkSheet.Cells.item($currRow+5,1) = "# of jobs last run more than 7 days:"
$myWorkSheet.Cells.item($currRow+5,1).font.bold=$True
$myWorkSheet.Cells.item($currRow+5,2) = $count7DayOlder
$myWorkSheet.Cells.item($currRow+5,2).font.bold=$True


#Check if file exist
$fileName = "C:\Script\DashBoard$nextFileName.xlsx"

if (Test-Path $fileName)
{Remove-Item $fileName}

$mywb.Saveas($fileName)
$myxl.quit()
Stop-Process -Name EXCEL

Comments

Post a Comment

Popular posts from this blog

Property Size is not available for Database '[tempdb]'

Image
Situation: Many times, DBA try to view the TEMPDB properties but they get the following error: The user is unable to view the TEMPDB property even if he has proper rights on the SQL Server instance. In fact, sometimes SYSADMIN role user is also unable to view the property and getting the same error. Solution: For user databases, this error can be resolved by changing the database ownership. We can run this command to check the Database ownership: sp_hepldb 'database name' user databases ownership can be SET by using the following command: USE 'db name' go sp_changedbowner 'sa' However, you can not change ownership of SYSTEM databases (tempdb, master, model & msdb) through the sp_changedbowner command. 'sa' is the default owner of all the system databases. I workaround this problem and found that this error frequently arises when tempdb is highly utilised and lots of contention is there on tempdb. In this scenario, SQL server...
Read more

Split backup files for faster SQL Backups

Image
Situation: SQL backups for large database are always be a challenging task. Depending upon the available system resources, a typical 5 TB database backup may take 6 - 7 hrs to complete. In addition to this, it is very difficult for DBAs to copy a single large backup file over the network. Solution: SQL Server provides a flexibility to split single backup file to multiple files. This would allow SQL server to split the workload by creating multiple threads. The advantages of this is backups will run faster and creates smaller files that can be easily copied across the network. Additionally, in most of the cases, Database administrator takes compressed backups which reduce the backup file size. However, for large database backups, overall backups time would be large as SQL server uses single thread to complete the backup operation. In a critical production environment it is always be a requirement to complete the backups before business starts next day. This would ensure the succe...
Read more

Resolve SQL Server I/O bottleneck issues

Image
Situation: Nowadays, most of the SQL Server environment is running on virtualized environment.  VMWare, Hyperviser, Xen, Elastic compute cloud etc. are the most popular virtualization platform available in the market. As per the recommended best practice, we should have separate dedicated drives for  SQLTemp, SQLData, SQLLog, SQLBinary & SQLBackups. All these drives should be presented as individual LUNs to provide the best I/O throughput to the SQL Server. In-spite of implementing these best practices, SQL Server frequently encounters disk I/O latencies issues in most of the environments. We can see following error in the SQL Server error logs: SQL Server has encountered xxx occurrence(s) of I/O requests taking longer than 50 seconds to complete on file [D:\MSSQL\MSSQL.1\MSSQL\Data\MyProdDB.mdf] in database [database].  The OS file handle is 0x00000000.  The offset of the latest long I/O is: 0x00000000000000 Solution: There are many...
Read more