Understanding the Different States of SQL Database

Overview

There are multiple numbers states of a SQL Database. These states determine the current status or mode of database in SQL server.

At once, the database is only in one state, which may be any one of the following:

  • Online
  • Offline
  • Restoring
  • Recovering
  • Recovery Pending
  • Emergency
  • Suspect

In this blog, we are going to discuss about all these states in detail.

Description Of Several States of SQL Database

Online: This server state indicates that the database is accessible for end users. The primary filegroup is online, even if the recovery process of undo phase has not been finished.

Offline: The state illustrates that the database is unavailable. This state of server is caused by explicit user action and it remains in offline mode until & unless users don’t perform any action on database.

Note: To determine whether the server is in offline state or in an online state, one can run the following command:

SELECT * FROM sys.databases

After running this command, you will find a table with ColumnName and Value attributes. In this table, search for state_desc option in ColumnName and check the Value attribute that whether it is Online or Offline.

State of SQL Database

Restoring: In this state of SQL database one or more data files are being restored from primary filegroup. Apart from this, the state also occurs when one or more secondary files are being restored in offline mode (i.e., database is unavailable). After completion of restoration procedure, the database is modified into an useable state and hence, users can now access the restored database.

Recovering: At the time of restarting the SQL server, all database undergoes “Recovery” process. In this state, the database comes in its consistent state i.e., during the process its state changes from recovery state to online state. Moreover, the recovering procedure is performed in three phases i.e., Discovery, roll forward, and Rollback

  • Discovery Phase: The first phase discovers that where will the server proceed through log file. During its discovery procedure, it built-in the memory structure for determining the memory required for going to next phases.
  • Roll Forward Phase: The second phase determines the transactions that were committed while shutting down the database but not yet written to MDF file through checkpoints.
  • Rollback Phase: Last phase of this recovering process determines whether there are any uncommitted transactions in database or not. If yes, then they first roll back those transactions and than bring database into consistent state.

TIP: With help of following command, one can recover a database, which is in restoring state:

RESTORE DATABASE ADventureWorks WITH RECOVERY
GO

Recovery Pending: While working with the server, users encounter an error that are related to resources during recovery process. In such cases, database is not corrupted or damaged. This state occurs when some data files are missing or there is some problem with system resources. Therefore, some external operations are being required by users to resolve this error state and continue the recovery process until its end.

Suspect: In this state, the recovery gets started but does not reach to its completion. It does not allow users to connect with database and can be considered as an inconsistent state. However, the common reason behind occurrence of such state is that the primary filegroup may be corrupted & hence, in suspect. Therefore, this state also requires some additional actions that repair the corrupted file & recover data from it.

Emergency: This state is enable by users by making some default changes. Such state implementation can only be performed in single-user mode and could be restored or repaired. In such state, database is in READ_ONLY state, logging is disabled, and access is restricted. Suppose a database is find out as a suspect can be set to emergency state, which will allow admin to perform read-only operation within it. Hence, no one can perform any updation in such database that is in emergency mode.

Conclusion

One can conclude with the fact that some of the states of SQL database are caused due to damage in database files therefore, users are recommended to use a recovery utility in order to recover data from SQL server database files and export that recovered data into SQL server database. One such utility is SQL Database Repair Tool, which recovers entire data from corrupted database file by performing scanning operation on source file. After completion of scanning operation, it convert the source file into SQL server database file. Moreover, it supports MDF and NDF files of the server. Therefore, the server users could use this third-party utility to take out the database from an inconsistent state.

Posted in Backup and Restore, databases_files, SQL | Leave a comment

Disk Space GUI Tool – Multi Server(s) – Grid and Email Output

Monitoring server resources is critical and important job of any Administrator. Keeping things organized might improve application availability and server availability. Being a database administrator for 1o years have faced and handled/managed lot of issues with disk space. There are many times we end up with I/O bottleneck and disk ran out space for transaction log files. To get around that have designed a simple GUI tool which has various features and quickly take us through the disk space utilization details of server(s). You’ll basically feed it a list of servers to watch over, and it will report back on these for you, meaning you could also use it as a more general “daily server disk space report”

The Powershell DiskSpace GUI tool has

  • Accept Input file which contains list of server or type the server name(s) separated by comma
  • Manage disk threshold values
  • Provides Grid or Email view or both
  • Search options

This tool helps you get the disk space information on the computers of your choice. Either you can type the path of an input file or hit ‘Browse Button’ to select the file using dialog option or you can enter the server names in the text box.

Put the select input file and hit ‘GetDisk’. It will query the disk space information from the computer and present it to you in tabular formats. The Grid view output can be sorted on any selected columns shows the amount of disk space in use, the name of the server, the volume name, the total size of the disk, the available free space, the partition identifier and the percentage of free space.

You can do a search by placing a right keyword and hit ‘Search’ button.

Also, Email can be sent to an intended users by making an entry in the corresponding fields in a below portion of the tool and hit ‘E-Mail Output’ button.

The different layouts are explained below

Layout 1 : Default the Input file check box is enabled. Hit browse button to select the input file and select the Get-Disk button.

diskSpace_5

Layout 2:  Displaying the threshold entries. If you want to use the default values then select ‘Cancel’ button. In case you want to define new threshold then set it as per your requirement and hit ‘Ok’ button.

diskSpace_1

Layout 3:  Show you the use Input file(C:\Server.txt), default threshold value(25 and 10), Sorting (On Drive field),Search(Apps key word) and Emailing.

diskSpace_3

 

Layout 4:  Show you the use Input server names and change of threshold value(30 and 15), Sorting (On DriveLabel),Search(OS key word) and Emailing.

diskSpace_4

 

Code

##################################
#Disk Space Report Module - Report Module
#######################################
function Get-DiskSpace_Report
{
function OnApplicationLoad {
return $true
}
function OnApplicationExit {
#Note: This function runs after the form is closed
#TODO: Add custom code to clean up and unload snapins when the application exits
}
#endregion Application Functions
#----------------------------------------------
# Generated Form Function
#----------------------------------------------
function Call-SystemInformation_pff {
    #----------------------------------------------
    #region Import the Assemblies
    #----------------------------------------------
    [void][reflection.assembly]::Load("System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
    [void][reflection.assembly]::Load("System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
    [void][reflection.assembly]::Load("mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
     [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
    [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")
    #get-pssnapin -Registered -ErrorAction silentlycontinue
    #endregion Import Assemblies
    #----------------------------------------------
    #region Generated Form Objects
    #----------------------------------------------
    [System.Windows.Forms.Application]::EnableVisualStyles()
    $form1 = New-Object System.Windows.Forms.Form
    $btnRefresh = New-Object System.Windows.Forms.Button
   
    $btnsearch = New-Object System.Windows.Forms.Button
    $btngetdata=New-Object System.Windows.Forms.Button
    $rtbPerfData = New-Object System.Windows.Forms.RichTextBox
    #$pictureBox1 = New-Object System.Windows.Forms.PictureBox
    $lblServicePack = New-Object System.Windows.Forms.Label
    $lblDBName= New-Object System.Windows.Forms.Label
    $lblOS = New-Object System.Windows.Forms.Label
    $lblExpire = New-Object System.Windows.Forms.Label
    $statusBar1 = New-Object System.Windows.Forms.StatusBar
    $btnClose = New-Object System.Windows.Forms.Button
    #$comboServers = New-Object System.Windows.Forms.ComboBox
    $InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState
    $txtComputerName1 = New-Object System.Windows.Forms.TextBox
    $txtComputerName2 = New-Object System.Windows.Forms.TextBox
    $datagridviewResults = New-Object System.Windows.Forms.DataGridview
    $Chart = New-object System.Windows.Forms.DataVisualization.Charting.Chart
    $labelSubject = New-Object 'System.Windows.Forms.Label'
    $labelFrom = New-Object 'System.Windows.Forms.Label'
    $labelSMTPServer = New-Object 'System.Windows.Forms.Label'
    $labelTo = New-Object 'System.Windows.Forms.Label'
    $txtSubject = New-Object System.Windows.Forms.TextBox
    $txtFrom = New-Object System.Windows.Forms.TextBox
    $txtSMTPServer = New-Object System.Windows.Forms.TextBox
    $txtTo = New-Object System.Windows.Forms.TextBox
    $buttonEMailOutput = New-Object System.Windows.Forms.Button
    $checkbox=New-Object System.Windows.Forms.CheckBox
    $Warningcheckbox=New-Object System.Windows.Forms.CheckBox
    $Criticalcheckbox=New-Object System.Windows.Forms.CheckBox
    $txtWarning = New-Object System.Windows.Forms.TextBox
    $txtCritical = New-Object System.Windows.Forms.TextBox
   #$dataGrid1 = new-object System.windows.forms.DataGridView
    #endregion Generated Form Objects
    #----------------------------------------------
    # User Generated Script
    #----------------------------------------------
function Sort-ListViewColumn
{
    <#
    .SYNOPSIS
        Sort the ListView's item using the specified column.
    .DESCRIPTION
        Sort the ListView's item using the specified column.
        This function uses Add-Type to define a class that sort the items.
        The ListView's Tag property is used to keep track of the sorting.
    .PARAMETER ListView
        The ListView control to sort.
    .PARAMETER ColumnIndex
        The index of the column to use for sorting.
        
    .PARAMETER  SortOrder
        The direction to sort the items. If not specified or set to None, it will toggle.
    
    .EXAMPLE
        Sort-ListViewColumn -ListView $listview1 -ColumnIndex 0
#>
    param( 
            [ValidateNotNull()]
            [Parameter(Mandatory=$true)]
            [System.Windows.Forms.ListView]$ListView,
            [Parameter(Mandatory=$true)]
            [int]$ColumnIndex,
            [System.Windows.Forms.SortOrder]$SortOrder = 'None')
    
    if(($ListView.Items.Count -eq 0) -or ($ColumnIndex -lt 0) -or ($ColumnIndex -ge $ListView.Columns.Count))
    {
        return;
    }
    
    #region Define ListViewItemComparer
        try{
        $local:type = [ListViewItemComparer]
    }
    catch{
    Add-Type -ReferencedAssemblies ('System.Windows.Forms') -TypeDefinition  @"
    using System;
    using System.Windows.Forms;
    using System.Collections;
    public class ListViewItemComparer : IComparer
    {
        public int column;
        public SortOrder sortOrder;
        public ListViewItemComparer()
        {
            column = 0;
            sortOrder = SortOrder.Ascending;
        }
        public ListViewItemComparer(int column, SortOrder sort)
        {
            this.column = column;
            sortOrder = sort;
        }
        public int Compare(object x, object y)
        {
            if(column >= ((ListViewItem)x).SubItems.Count)
                return  sortOrder == SortOrder.Ascending ? -1 : 1;
        
            if(column >= ((ListViewItem)y).SubItems.Count)
                return sortOrder == SortOrder.Ascending ? 1 : -1;
        
            if(sortOrder == SortOrder.Ascending)
                return String.Compare(((ListViewItem)x).SubItems[column].Text, ((ListViewItem)y).SubItems[column].Text);
            else
                return String.Compare(((ListViewItem)y).SubItems[column].Text, ((ListViewItem)x).SubItems[column].Text);
        }
    }
"@  | Out-Null
    }
    #endregion
    
    if($ListView.Tag -is [ListViewItemComparer])
    {
        #Toggle the Sort Order
        if($SortOrder -eq [System.Windows.Forms.SortOrder]::None)
        {
            if($ListView.Tag.column -eq $ColumnIndex -and $ListView.Tag.sortOrder -eq 'Ascending')
            {
                $ListView.Tag.sortOrder = 'Descending'
            }
            else
            {
                $ListView.Tag.sortOrder = 'Ascending'
            }
        }
        else
        {
            $ListView.Tag.sortOrder = $SortOrder
        }
        
        $ListView.Tag.column = $ColumnIndex
        $ListView.Sort()#Sort the items
    }
    else
    {
        if($Sort -eq [System.Windows.Forms.SortOrder]::None)
        {
            $Sort = [System.Windows.Forms.SortOrder]::Ascending
        }
        
        #Set to Tag because for some reason in PowerShell ListViewItemSorter prop returns null
        $ListView.Tag = New-Object ListViewItemComparer ($ColumnIndex, $SortOrder)
        $ListView.ListViewItemSorter = $ListView.Tag #Automatically sorts
    }
}
function Add-ListViewItem
{
<#
    .SYNOPSIS
        Adds the item(s) to the ListView and stores the object in the ListViewItem's Tag property.
    .DESCRIPTION
        Adds the item(s) to the ListView and stores the object in the ListViewItem's Tag property.
    .PARAMETER ListView
        The ListView control to add the items to.
    .PARAMETER Items
        The object or objects you wish to load into the ListView's Items collection.
        
    .PARAMETER  ImageIndex
        The index of a predefined image in the ListView's ImageList.
    
    .PARAMETER  SubItems
        List of strings to add as Subitems.
    
    .PARAMETER Group
        The group to place the item(s) in.
    
    .PARAMETER Clear
        This switch clears the ListView's Items before adding the new item(s).
    
    .EXAMPLE
        Add-ListViewItem -ListView $listview1 -Items "Test" -Group $listview1.Groups[0] -ImageIndex 0 -SubItems "Installed"
#>
    
    Param(
    [ValidateNotNull()]
    [Parameter(Mandatory=$true)]
    [System.Windows.Forms.ListView]$ListView,
    [ValidateNotNull()]
    [Parameter(Mandatory=$true)]
    $Items,
    [int]$ImageIndex = -1,
    [string[]]$SubItems,
    [System.Windows.Forms.ListViewGroup]$Group,
    [switch]$Clear)
    
    if($Clear)
    {
        $ListView.Items.Clear();
    }
    
    if($Items -is [Array])
    {
        $ListView.BeginUpdate()
        foreach ($item in $Items)
        {      
            $listitem  = $ListView.Items.Add($item.ToString(), $ImageIndex)
            #Store the object in the Tag
            $listitem.Tag = $item
            
            if($SubItems -ne $null)
            {
                $listitem.SubItems.AddRange($SubItems)
            }
            
            if($Group -ne $null)
            {
                $listitem.Group = $Group
            }
        }
        $ListView.EndUpdate()
    }
    else
    {
        #Add a new item to the ListView
        $listitem  = $ListView.Items.Add($Items.ToString(), $ImageIndex)
        #Store the object in the Tag
        $listitem.Tag = $Items
        
        if($SubItems -ne $null)
        {
            $listitem.SubItems.AddRange($SubItems)
        }
        
        if($Group -ne $null)
        {
            $listitem.Group = $Group
        }
    }
}
function Load-DataGridView
{
    <#
    .SYNOPSIS
        This functions helps you load items into a DataGridView.
    .DESCRIPTION
        Use this function to dynamically load items into the DataGridView control.
    .PARAMETER  DataGridView
        The ComboBox control you want to add items to.
    .PARAMETER  Item
        The object or objects you wish to load into the ComboBox's items collection.
    
    .PARAMETER  DataMember
        Sets the name of the list or table in the data source for which the DataGridView is displaying data.
    #>
    Param (
        [ValidateNotNull()]
        [Parameter(Mandatory=$true)]
        [System.Windows.Forms.DataGridView]$DataGridView,
        [ValidateNotNull()]
        [Parameter(Mandatory=$true)]
        $Item,
        [Parameter(Mandatory=$false)]
        [string]$DataMember
    )
    $DataGridView.SuspendLayout()
    $DataGridView.DataMember = $DataMember
    
    
    if ($Item -is [System.ComponentModel.IListSource]`
    -or $Item -is [System.ComponentModel.IBindingList] -or $Item -is [System.ComponentModel.IBindingListView] )
    {
        $DataGridView.DataSource = $Item
      
    }
    else
    {
        $array = New-Object System.Collections.ArrayList
        
        if ($Item -is [System.Collections.IList])
        {
            $array.AddRange($Item)
        }
        else
        {  
            $array.Add($Item)  
        }
        $DataGridView.DataSource = $array
        
       
    }
    
    $DataGridView.ResumeLayout()
   
}
function Convert-ToDataTable
{
    <#
        .SYNOPSIS
            Converts objects into a DataTable.
    
        .DESCRIPTION
            Converts objects into a DataTable, which are used for DataBinding.
    
        .PARAMETER  InputObject
            The input to convert into a DataTable.
    
        .PARAMETER  Table
            The DataTable you wish to load the input into.
    
        .PARAMETER RetainColumns
            This switch tells the function to keep the DataTable's existing columns.
        
        .PARAMETER FilterWMIProperties
            This switch removes WMI properties that start with an underline.
    
        .EXAMPLE
            Convert-ToDataTable -InputObject (Get-Process) -Table $DataTable
    #>
    
    param(
    $InputObject,
    [ValidateNotNull()]
    [System.Data.DataTable]$Table,
    [switch]$RetainColumns,
    [switch]$FilterWMIProperties)
    
    if($InputObject -is [System.Data.DataTable])
    {
        $Table = $InputObject
        return
    }
    
    if(-not $RetainColumns -or $Table.Columns.Count -eq 0)
    {
        #Clear out the Table Contents
        $Table.Clear()
        if($InputObject -eq $null){ return } #Empty Data
        
        $object = $null
        #find the first non null value
        foreach($item in $InputObject)
        {
            if($item -ne $null)
            {
                $object = $item
                break  
            }
        }
        if($object -eq $null) { return } #All null then empty
        
        #Get all the properties in order to create the columns
        $properties = Get-Member -MemberType 'Properties' -InputObject $object
        foreach ($prop in $properties)
        {
            if(-not $FilterWMIProperties -or -not $prop.Name.StartsWith('__'))#filter out WMI properties
            {
                #Get the type from the Definition string
                $index = $prop.Definition.IndexOf(' ')
                $type = $null
                if($index -ne -1)
                {
                    $typeName = $prop.Definition.SubString(0, $index)
                    try{ $type = [System.Type]::GetType($typeName) } catch {}
                }
                if($type -ne $null -and [System.Type]::GetTypeCode($type) -ne 'Object')
                {
                    [void]$table.Columns.Add($prop.Name, $type)
                }
                else #Type info not found
                {
                    [void]$table.Columns.Add($prop.Name)   
                }
            }
        }
    }
    else
    {
        $Table.Rows.Clear()
    }
    
    $count = $table.Columns.Count
    foreach($item in $InputObject)
    {
        $row = $table.NewRow()
        
        for ($i = 0; $i -lt $count;$i++)
        {
            $column = $table.Columns[$i]
            $prop = $column.ColumnName 
            
            $value = Invoke-Expression ('$item.{0}' -f $prop)
            if($value -ne $null)
            {
                $row[$i] = $value
            }
        }
        
        [void]$table.Rows.Add($row)
         
    }
 
}
#endregion
#region Search Function
function SearchGrid()
{
    $RowIndex = 0
    $ColumnIndex = 0
    $seachString = $txtComputerName2.Text
    
    if($seachString -eq "")
    {
        return
    }
    
    if($datagridviewResults.SelectedCells.Count -ne 0)
    {
        $startCell = $datagridviewResults.SelectedCells[0];
        $RowIndex = $startCell.RowIndex
        $ColumnIndex = $startCell.ColumnIndex + 1
    }
    
    $columnCount = $datagridviewResults.ColumnCount
    $rowCount = $datagridviewResults.RowCount
    for(;$RowIndex -lt $rowCount; $RowIndex++)
    {
        $Row = $datagridviewResults.Rows[$RowIndex]
        
        for(;$ColumnIndex -lt $columnCount; $ColumnIndex++)
        {
            $cell = $Row.Cells[$ColumnIndex]
            
            if($cell.Value -ne $null -and $cell.Value.ToString().IndexOf($seachString, [StringComparison]::OrdinalIgnoreCase) -ne -1)
            {
                $datagridviewResults.CurrentCell = $cell
                return
            }
        }
        
        $ColumnIndex = 0
    }
    
    $datagridviewResults.CurrentCell = $null
    [void][System.Windows.Forms.MessageBox]::Show("The search has reached the end of the grid.","String not Found")
    
}
#endregion
Function sendEmail 
{
param($from,$to,$subject,$smtphost,$htmlFileName) 
[string]$receipients="$to"
$body = Get-Content $htmlFileName
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtphost)
$validfrom= Validate-IsEmail $from
if($validfrom -eq $TRUE)
{
$validTo= Validate-IsEmail $to
if($validTo -eq $TRUE)
{
$smtp.Send($body)
write-output "Email Sent!!"
}
}
else
{
write-output "Invalid entries, Try again!!"
}
}
function Validate-IsEmail ([string]$Email)
{
                
                return $Email -match "^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$"
}
$FormEvent_Load={
    #TODO: Initialize Form Controls here
    
}
$buttonExit_Click={
    #TODO: Place custom script here
    $formMain.Close()
}
$buttonQuery_Click={
    #TODO: Place custom script here
#   ---------------------------------
#   Sample Code to Load Grid
#   ---------------------------------
#   $processes = Get-WmiObject Win32_Process -Namespace "Root\CIMV2"
#   Load-DataGridView -DataGridView $datagridviewResults -Item $processes
#   ---------------------------------
#   Sample Code to Load Sortable Data
#   ---------------------------------
if($checkbox.checked -eq $TRUE)
{
if ($txtComputerName1.text -eq '')
{
$statusBar1.text="Select Input file or Enter Input file path...Try Again!!!"
}
else
{
#>
$script:Object1 = @()
$freeSpaceFileName = "C:\FreeSpace.htm"
Get-threshold
$warning = $script:Warning
$critical = $script:critical
write-host  $warning,$critical
New-Item -ItemType file $freeSpaceFileName -Force
# Getting the freespace info using WMI
#Get-WmiObject win32_logicaldisk  | Where-Object {$_.drivetype -eq 3 -OR $_.drivetype -eq 2 } | format-table DeviceID, VolumeName,status,Size,FreeSpace | Out-File FreeSpace.txt
# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString('yyyy/MM/dd')
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title>DiskSpace Report</title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName  "<!--"
add-content $fileName  "td {"
add-content $fileName  "font-family: Tahoma;"
add-content $fileName  "font-size: 11px;"
add-content $fileName  "border-top: 1px solid #999999;"
add-content $fileName  "border-right: 1px solid #999999;"
add-content $fileName  "border-bottom: 1px solid #999999;"
add-content $fileName  "border-left: 1px solid #999999;"
add-content $fileName  "padding-top: 0px;"
add-content $fileName  "padding-right: 0px;"
add-content $fileName  "padding-bottom: 0px;"
add-content $fileName  "padding-left: 0px;"
add-content $fileName  "}"
add-content $fileName  "body {"
add-content $fileName  "margin-left: 5px;"
add-content $fileName  "margin-top: 5px;"
add-content $fileName  "margin-right: 0px;"
add-content $fileName  "margin-bottom: 10px;"
add-content $fileName  ""
add-content $fileName  "table {"
add-content $fileName  "border: thin solid #000000;"
add-content $fileName  "}"
add-content $fileName  "-->"
add-content $fileName  "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
add-content $fileName  "<table width='100%'>"
add-content $fileName  "<tr bgcolor='#CCCCCC'>"
add-content $fileName  "<td colspan='7' height='25' align='center'>"
add-content $fileName  "<font face='tahoma' color='#003399' size='4'><strong>DiskSpace Report - $date</strong></font>"
add-content $fileName  "</td>"
add-content $fileName  "</tr>"
add-content $fileName  "</table>"
}
# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='10%' align='center'>Drive</td>"
Add-Content $fileName "<td width= 10%' align='center'>Drive Label</td>"
Add-Content $fileName "<td width='10%' align='center'>Total Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Used Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Free Space(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Freespace %</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function writeDiskInfo
{
param($fileName,$devId,$volName,$frSpace,$totSpace)
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
if ($freePercent -gt $warning)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td>$freePercent</td>"
Add-Content $fileName "</tr>"
}
elseif ($freePercent -le $critical)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FF0000' align=center>$freePercent</td>"
#<td bgcolor='#FF0000' align=center>
Add-Content $fileName "</tr>"
}
else
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FBB917' align=center>$freePercent</td>"
# #FBB917
Add-Content $fileName "</tr>"
}
}
writeHtmlHeader $freeSpaceFileName
# Email our report out
foreach ($computer in (get-content $txtComputerName1.text))
{
write-host $computer
if(Test-Connection -ComputerName $computer -Count 1 -ea 0) {
Add-Content $freeSpaceFileName "<table width='100%'><tbody>"
Add-Content $freeSpaceFileName "<tr bgcolor='#CCCCCC'>"
Add-Content $freeSpaceFileName "<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $computer </strong></font></td>"
Add-Content $freeSpaceFileName "</tr>"
writeTableHeader $freeSpaceFileName
$dp = Get-WmiObject win32_logicaldisk -ComputerName $computer |  Where-Object {$_.drivetype -eq 3 }
foreach ($item in $dp)
{
Write-Host  $item.DeviceID  $item.VolumeName $item.FreeSpace $item.Size
writeDiskInfo $freeSpaceFileName $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
}
  Add-Content $freeSpaceFileName "</table>"
writeHtmlFooter $freeSpaceFileName
 
$statusBar1.text="Getting  $computer Information...Please wait"
$D=Get-WmiObject win32_logicalDisk -ComputerName $computer -ErrorAction silentlycontinue|where {$_.DriveType -eq 3}|
select __SERVER,DeviceID,VolumeName,
@{Expression={$_.Size /1Gb -as [int]};Label=”TotalSize”},
@{Expression={($_.Size /1Gb -as [int]) – ($_.Freespace / 1Gb -as [int])};Label=”InUse”} ,
@{Expression={$_.Freespace / 1Gb -as [int]};Label=”FreeSize”},
@{Expression={(($_.Freespace /1Gb -as [int]) / ($_.Size / 1Gb -as [int]))*100};Label=”PerFreeSpace”}
foreach($disk in $D)
{
$script:Object1 += New-Object PSObject -Property @{
AppSrvName= $Disk.__Server;
Drive= $Disk.DeviceID;
DriveLabel=$Disk.VolumeName;
DriveCapacityGB=$Disk.TotalSize;
DriveInUseGB=$Disk.InUse;
DriveFreeGB=$Disk.FreeSize;
DrivePercentFree=$Disk.PerFreeSpace;
}
}
}
 
 else
    {
    $statusBar1.text="Could not connect to $computer ...Try Again!!!"
    }
    
$statusBar1.text="Ready"    
}
    $table = New-Object System.Data.DataTable
    Convert-ToDataTable -InputObject ($object1) -Table $table
    Load-DataGridView -DataGridView $datagridviewResults -Item $table
   
}
}
}
$buttonQuery_NoCheckBoxClick={
$freeSpaceFileName = "C:\FreeSpace.htm"
Get-threshold
$warning = $script:Warning
$critical = $script:critical
write-host  $warning,$critical
New-Item -ItemType file $freeSpaceFileName -Force
# Getting the freespace info using WMI
#Get-WmiObject win32_logicaldisk  | Where-Object {$_.drivetype -eq 3 -OR $_.drivetype -eq 2 } | format-table DeviceID, VolumeName,status,Size,FreeSpace | Out-File FreeSpace.txt
# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString('yyyy/MM/dd')
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title>DiskSpace Report</title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName  "<!--"
add-content $fileName  "td {"
add-content $fileName  "font-family: Tahoma;"
add-content $fileName  "font-size: 11px;"
add-content $fileName  "border-top: 1px solid #999999;"
add-content $fileName  "border-right: 1px solid #999999;"
add-content $fileName  "border-bottom: 1px solid #999999;"
add-content $fileName  "border-left: 1px solid #999999;"
add-content $fileName  "padding-top: 0px;"
add-content $fileName  "padding-right: 0px;"
add-content $fileName  "padding-bottom: 0px;"
add-content $fileName  "padding-left: 0px;"
add-content $fileName  "}"
add-content $fileName  "body {"
add-content $fileName  "margin-left: 5px;"
add-content $fileName  "margin-top: 5px;"
add-content $fileName  "margin-right: 0px;"
add-content $fileName  "margin-bottom: 10px;"
add-content $fileName  ""
add-content $fileName  "table {"
add-content $fileName  "border: thin solid #000000;"
add-content $fileName  "}"
add-content $fileName  "-->"
add-content $fileName  "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
add-content $fileName  "<table width='100%'>"
add-content $fileName  "<tr bgcolor='#CCCCCC'>"
add-content $fileName  "<td colspan='7' height='25' align='center'>"
add-content $fileName  "<font face='tahoma' color='#003399' size='4'><strong>DiskSpace Report - $date</strong></font>"
add-content $fileName  "</td>"
add-content $fileName  "</tr>"
add-content $fileName  "</table>"
}
# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='10%' align='center'>Drive</td>"
Add-Content $fileName "<td width= 10%' align='center'>Drive Label</td>"
Add-Content $fileName "<td width='10%' align='center'>Total Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Used Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Free Space(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Freespace %</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function writeDiskInfo
{
param($fileName,$devId,$volName,$frSpace,$totSpace)
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
if ($freePercent -gt $warning)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td>$freePercent</td>"
Add-Content $fileName "</tr>"
}
elseif ($freePercent -le $critical)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FF0000' align=center>$freePercent</td>"
#<td bgcolor='#FF0000' align=center>
Add-Content $fileName "</tr>"
}
else
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FBB917' align=center>$freePercent</td>"
# #FBB917
Add-Content $fileName "</tr>"
}
}
writeHtmlHeader $freeSpaceFileName
    #TODO: Place custom script here
#   ---------------------------------
#   Sample Code to Load Grid
#   ---------------------------------
#   $processes = Get-WmiObject Win32_Process -Namespace "Root\CIMV2"
#   Load-DataGridView -DataGridView $datagridviewResults -Item $processes
#   ---------------------------------
#   Sample Code to Load Sortable Data
#   ---------------------------------
if($checkbox.checked -eq $FALSE)
{
if ($txtComputerName1.text -eq '')
{
$statusBar1.text="Enter the Server...Try Again!!!"
}
else
{
$l=$txtComputerName1.text
$split1 = $l.split(",")
#>
$script:Object2 = @()
For($i=0; $i -le $split1.length -1 ;$i++)
{
write-host $split1[$i]
$computer=$split1[$i]
if(Test-Connection -ComputerName $computer -Count 1 -ea 0) {
 
Add-Content $freeSpaceFileName "<table width='100%'><tbody>"
Add-Content $freeSpaceFileName "<tr bgcolor='#CCCCCC'>"
Add-Content $freeSpaceFileName "<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $computer </strong></font></td>"
Add-Content $freeSpaceFileName "</tr>"
writeTableHeader $freeSpaceFileName
$dp = Get-WmiObject win32_logicaldisk -ComputerName $computer |  Where-Object {$_.drivetype -eq 3 }
foreach ($item in $dp)
{
Write-Host  $item.DeviceID  $item.VolumeName $item.FreeSpace $item.Size
writeDiskInfo $freeSpaceFileName $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
}
  Add-Content $freeSpaceFileName "</table>"
writeHtmlFooter $freeSpaceFileName
$statusBar1.text="Getting  $computer Information...Please wait"
$D=Get-WmiObject win32_logicalDisk -ComputerName $computer -ErrorAction silentlycontinue|where {$_.DriveType -eq 3}|
select __SERVER,DeviceID,VolumeName,
@{Expression={$_.Size /1Gb -as [int]};Label=”TotalSize”},
@{Expression={($_.Size /1Gb -as [int]) – ($_.Freespace / 1Gb -as [int])};Label=”InUse”} ,
@{Expression={$_.Freespace / 1Gb -as [int]};Label=”FreeSize”},
@{Expression={(($_.Freespace /1Gb -as [int]) / ($_.Size / 1Gb -as [int]))*100};Label=”PerFreeSpace”}
foreach($disk in $D)
{
$script:Object2 += New-Object PSObject -Property @{
AppSrvName= $Disk.__Server;
Drive= $Disk.DeviceID;
DriveLabel=$Disk.VolumeName;
DriveCapacityGB=$Disk.TotalSize;
DriveInUseGB=$Disk.InUse;
DriveFreeGB=$Disk.FreeSize;
DrivePercentFree=$Disk.PerFreeSpace
}
}
}
 else
    {
    $statusBar1.text="Could not connect to $computer ...Try Again!!!"
    }
    
$statusBar1.text="Ready"    
}
    $table = New-Object System.Data.DataTable
    Convert-ToDataTable -InputObject ($object2) -Table $table
    Load-DataGridView -DataGridView $datagridviewResults -Item $table
   
}
}
}
$buttonSearch_Click={
    #TODO: Place custom script here
    SearchGrid
}
$datagridviewResults_ColumnHeaderMouseClick=[System.Windows.Forms.DataGridViewCellMouseEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellMouseEventArgs]
    if($datagridviewResults.DataSource -is [System.Data.DataTable])
    {
        $column = $datagridviewResults.Columns[$_.ColumnIndex]
        $direction = [System.ComponentModel.ListSortDirection]::Ascending
        
        if($column.HeaderCell.SortGlyphDirection -eq 'Descending')
        {
            $direction = [System.ComponentModel.ListSortDirection]::Descending
        }
        $datagridviewResults.Sort($datagridviewResults.Columns[$_.ColumnIndex], $direction)
    }
}
$listviewSort_ColumnClick=[System.Windows.Forms.ColumnClickEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.ColumnClickEventArgs]
    Sort-ListViewColumn -ListView $this -ColumnIndex $_.Column
}
$listviewSort_ColumnClick2=[System.Windows.Forms.ColumnClickEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.ColumnClickEventArgs]
    Sort-ListViewColumn -ListView $this -ColumnIndex $_.Column
}
function Get-threshold{
        [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
$objForm = New-Object System.Windows.Forms.Form
$objForm.Text = "Threshold Data Entry Form"
$objForm.Size = New-Object System.Drawing.Size(300,200)
$objForm.StartPosition = "CenterScreen"
$objForm.KeyPreview = $True
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Enter")
    {$x=$objTextBox1.Text;$y=$objTextBox2.Text;$objForm.Close()}})
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Escape")
    {$objForm.Close()}})
$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Size(75,120)
$OKButton.Size = New-Object System.Drawing.Size(75,23)
$OKButton.Text = "OK"
$OKButton.Add_Click({$x=$objTextBox.Text;$objForm.Close()})
$objForm.Controls.Add($OKButton)
$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Size(150,120)
$CancelButton.Size = New-Object System.Drawing.Size(75,23)
$CancelButton.Text = "Cancel"
$CancelButton.Add_Click({$objForm.Close()})
$objForm.Controls.Add($CancelButton)
$objLabel3 = New-Object System.Windows.Forms.Label
$objLabel3.Location = New-Object System.Drawing.Size(10,6)
$objLabel3.Size = New-Object System.Drawing.Size(280,20)
$objLabel3.Text = "Default - Warning:25 Critical:10 ,Click OK"
$objForm.Controls.Add($objLabel3)
$objLabel1 = New-Object System.Windows.Forms.Label
$objLabel1.Location = New-Object System.Drawing.Size(10,35)
$objLabel1.Size = New-Object System.Drawing.Size(150,20)
$objLabel1.Text = "Warning Threshold Value"
$objForm.Controls.Add($objLabel1)
$objTextBox1 = New-Object System.Windows.Forms.TextBox
$objTextBox1.Location = New-Object System.Drawing.Size(160,35)
$objTextBox1.Size = New-Object System.Drawing.Size(100,20)
$objForm.Controls.Add($objTextBox1)
$objLabel2 = New-Object System.Windows.Forms.Label
$objLabel2.Location = New-Object System.Drawing.Size(10,65)
$objLabel2.Size = New-Object System.Drawing.Size(150,20)
$objLabel2.Text = "Critical Threshold Value:"
$objForm.Controls.Add($objLabel2)
$objTextBox2 = New-Object System.Windows.Forms.TextBox
$objTextBox2.Location = New-Object System.Drawing.Size(160,65)
$objTextBox2.Size = New-Object System.Drawing.Size(100,20)
$objForm.Controls.Add($objTextBox2)
$x=$objTextBox1.Text;
$y=$objTextBox2.Text;
$objForm.Topmost = $True
$objForm.Add_Shown({$objForm.Activate()})
[void] $objForm.ShowDialog()
if ($objTextBox1.Text -eq '')
{
$Script:warning=25
}
else
{
$Script:warning=$objTextBox1.Text;
}
if ($objTextBox2.Text -eq '')
{
$Script:critical=10
}
else
{
$Script:critical=$objTextBox2.Text;
}
<#
$x1= $objTextBox1.Text;
$y1= $objTextBox2.Text;
write-host "the value is $x1"
write-host "the vakue is $y1"
$Script:warning
$Script:critical
#>
        Start-Sleep -Milliseconds 200
    }
    
    $Close={
        $form1.close()
    
    }
    
 $GetData={
        $statusBar1.text=" Checking Email Entries ...Please wait"
            if ($txtfrom.Text -ne '' -and $txtto.Text -ne '' -and $txtSMTPServer.Text -ne '')
           {
           $status1=Validate-IsEmail $txtfrom.Text
           #write-host $status1
           $status2=Validate-IsEmail $txtto.Text
           #write-host $status1
           if($status1 -eq $FALSE)
           {
           [void][System.Windows.Forms.MessageBox]::Show("Check From Email Entries")
           if($status2 -eq $FALSE)
           {
           [void][System.Windows.Forms.MessageBox]::Show(" Check To Email Entries")
           }
           }
           ELSE
           {
           $statusBar1.Text="Sending Email...Please wait"
           #write-host "executing this batch"
           #write-host $txtComputerName1.text
           #$data=Get-DiskSpaceReport -list $txtComputerName1.text -From $txtfrom.text -To $txtTo.text -SMTPMail $txtSMTPServer.text
           $date = ( get-date ).ToString('yyyy/MM/dd')
           sendEmail -from $txtfrom.Text -to $txtto.Text -subject "Disk Space Report - $Date" -smtphost $txtSMTPServer.Text -htmlfilename "C:\FreeSpace.htm"
           $statusBar1.Text="email sent"
           }
           }
       
      $statusBar1.Text="Ready"
     }
 
    #>
    
    
    # --End User Generated Script--
    #----------------------------------------------
    # Generated Events
    #----------------------------------------------
    
    $Form_StateCorrection_Load=
    {
        #Correct the initial state of the form to prevent the .Net maximized form issue
        $form1.WindowState = $InitialFormWindowState
        
      
        
      
    }
    #----------------------------------------------
    #region Generated Form Code
    #----------------------------------------------
    #
    # form1
    #
    $saveFileDialog1 = New-Object System.Windows.Forms.SaveFileDialog
    $form1.Controls.Add($btnRefresh)
    $form1.Controls.Add($btnsearch)
    #$form1.Controls.Add($rtbPerfData)
    #$form1.Controls.Add($pictureBox1)
    $form1.Controls.Add($lblServicePack)
    $form1.Controls.Add($lblOS)
    $form1.Controls.Add($lblDBName)
    $form1.Controls.Add($statusBar1)
    $form1.Controls.Add($btnClose)
    $form1.Controls.Add($txtComputerName1)
    $Form1.controls.add($Chart)
    $Form1.controls.add($txtComputerName2)
    $Form1.controls.add($labelSubject)
    $Form1.controls.add($labelFrom)
    $Form1.controls.add($labelSMTPServer)
    $Form1.controls.add($labelTo)
    $Form1.controls.add($lblExpire)
    $Form1.controls.add($txtFrom)
    $Form1.controls.add($txtTo)
    $Form1.controls.add($txtSubject)
    $Form1.controls.add($txtSMTPServer)
    $Form1.controls.add($buttonEMailOutput)
    $Form1.controls.add($checkBox)
    #$Form1.controls.add($Warningcheckbox)
    #$Form1.controls.add($Criticalcheckbox)
    #$Form1.controls.add($txtWarning)
    #$Form1.controls.add($txtCritical)
    $form1.ClientSize = New-Object System.Drawing.Size(900,650)
    $form1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    #$form1.FormBorderStyle = [System.Windows.Forms.FormBorderStyle]::SizableToolWindow
    $form1.Name = "form1"
    $form1.Text = "Drive Information Tool "
    $form1.add_Load($PopulateList)
    
   
    
# create chart object
    
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 850
$System_Drawing_Size.Height = 450
$datagridviewResults.Size = $System_Drawing_Size
$datagridviewResults.DataBindings.DefaultDataSourceUpdateMode = 0
#$datagridviewResults.HeaderForeColor = [System.Drawing.Color]::FromArgb(255,0,0,0)
$datagridviewResults.Name = "dataGrid1"
$datagridviewResults.DataMember = ""
$datagridviewResults.TabIndex = 0
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X =13
$System_Drawing_Point.Y = 72
$datagridviewResults.Location = $System_Drawing_Point
$Chart.visible=$FALSE
$form1.Controls.Add($datagridviewResults)
#$datagridviewResults.CaptionText='Service Comparison'
    #$CheckBox
    $CheckBox.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $CheckBox.Enabled = $TRUE
    $CheckBox.CHECKED = $TRUE
    $CheckBox.Location = New-Object System.Drawing.Point(440,10)
    $CheckBox.Name = "CheckBox"
    $CheckBox.Size = New-Object System.Drawing.Size(72,20)
    $CheckBox.TabIndex = 3
    $CheckBox.Text = "Input File"
    $CheckBox.UseVisualStyleBackColor = $True
    $btnRefresh.add_Click($buttonQuery_Click)
    #$BrowseButton.Enabled = $TRUE;
    
  #  $checkbox.Add_Click({$BrowseButton.Enabled = $TRUE; $btnRefresh.add_Click($buttonQuery_Click)})
    $checkbox.Add_CheckStateChanged({
    If ($Checkbox.Checked -eq $TRUE  ) {
        $BrowseButton.Enabled = $TRUE;
        $txtComputerName1.text=''
        $btnRefresh.add_Click($buttonQuery_Click)
    }
    If ($Checkbox.Checked -eq $FALSE )
    {
        $txtComputerName1.text=''
        $BrowseButton.Enabled = $FALSE;
        $btnRefresh.add_Click($buttonQuery_NoCheckBoxClick)
        
    }
})
    
    
    
    
    
    #$CheckBox.add_Click($buttonQuery_Click)
    
    # btnRefresh
    #
    $btnRefresh.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $btnRefresh.Enabled = $TRUE
    $btnRefresh.Location = New-Object System.Drawing.Point(600,10)
    $btnRefresh.Name = "btnRefresh"
    $btnRefresh.Size = New-Object System.Drawing.Size(72,20)
    $btnRefresh.TabIndex = 3
    $btnRefresh.Text = "GetDisk"
    $btnRefresh.UseVisualStyleBackColor = $True
    #$btnRefresh.add_Click($buttonQuery_Click)
    #
    #
    #
    # btnsearch
    #
    #$btnsearch.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $btnsearch.Enabled = $TRUE
    $btnsearch.Location = New-Object System.Drawing.Point(570,35)
    $btnsearch.Name = "btnsearch"
    $btnsearch.Size = New-Object System.Drawing.Size(72,20)
    $btnsearch.TabIndex = 6
    $btnsearch.Text = "Search"
    $btnsearch.UseVisualStyleBackColor = $True
    $btnsearch.add_Click($buttonSearch_Click)
    #
  
    # btnClose
    #
    
    $btnClose.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $btngetdata.Enabled = $TRUE
    $btnClose.Location = New-Object System.Drawing.Point(673,10)
    $btnClose.Name = "btnClose"
    $btnClose.Size = New-Object System.Drawing.Size(72,20)
    $btnClose.TabIndex = 4
    $btnClose.Text = "Close"
    $btnClose.UseVisualStyleBackColor = $True
    $btnClose.add_Click($Close)
    #
    
    # lblDBName
    #
    $lblDBName.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $lblDBName.Font = New-Object System.Drawing.Font("Lucida Console",8.25,1,3,1)
    $lblDBName.Location = New-Object System.Drawing.Point(13,10)
    $lblDBName.Name = "lblDBName"
    $lblDBName.Size = New-Object System.Drawing.Size(178,23)
    $lblDBName.TabIndex = 0
    $lblDBName.Text = "Select Input file  "
    $lblDBName.Visible = $TRUE
    #
    
    #$txtComputerName1.text
    #txtComputerName1
    $txtComputerName1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtComputerName1.Location = New-Object System.Drawing.Point(200, 10)
    $txtComputerName1.Name = "txtComputerName1"
    $txtComputerName1.TabIndex = 1
    $txtComputerName1.Size = New-Object System.Drawing.Size(200,70)
    $txtComputerName1.visible=$TRUE
    #
    
    #
    # lblExpire
    #
    $lblExpire.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $lblExpire.Font = New-Object System.Drawing.Font("Lucida Console",8.25,1,3,1)
    $lblExpire.Location = New-Object System.Drawing.Point(13,35)
    $lblExpire.Name = "lblExpire"
    $lblExpire.Size = New-Object System.Drawing.Size(178,23)
    $lblExpire.TabIndex = 0
    $lblExpire.Text = "Enter the search String"
    $lblExpire.Visible = $TRUE
    #
    #$txtComputerName2.text
    #txtComputerName2
    $txtComputerName2.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtComputerName2.Location = New-Object System.Drawing.Point(200,35)
    $txtComputerName2.Name = "txtComputerName2"
    $txtComputerName2.TabIndex = 5
    $txtComputerName2.Size = New-Object System.Drawing.Size(400,70)
    $txtComputerName2.visible=$TRUE
    #
    # lblServicePack
    #
    $lblServicePack.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $lblServicePack.Font = New-Object System.Drawing.Font("Lucida Console",8.25,1,3,1)
    $lblServicePack.Location = New-Object System.Drawing.Point(13,100)
    $lblServicePack.Name = "lblServicePack"
    $lblServicePack.Size = New-Object System.Drawing.Size(278,23)
    $lblServicePack.TabIndex = 0
    $lblServicePack.Text = "ServicePack"
    $lblServicePack.Visible = $False
    #
    # lblOS
    #
    $lblOS.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $lblOS.Font = New-Object System.Drawing.Font("Lucida Console",8.25,1,3,1)
    $lblOS.Location = New-Object System.Drawing.Point(12,77)
    $lblOS.Name = "lblOS"
    $lblOS.Size = New-Object System.Drawing.Size(278,23)
    $lblOS.TabIndex = 2
    $lblOS.Text = "User Information"
    $lblOS.Visible = $False
    #
    # statusBar1
    #
    $statusBar1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $statusBar1.Location = New-Object System.Drawing.Point(0,365)
    $statusBar1.Name = "statusBar1"
    $statusBar1.Size = New-Object System.Drawing.Size(390,22)
    $statusBar1.TabIndex = 5
    $statusBar1.Text = "statusBar1"
    #
    #>
    # labelSubject
    #
    $labelSubject.Location = '350, 578'
    $labelSubject.Name = "labelSubject"
    $labelSubject.Size = '50, 19'
    #$labelSubject.TabIndex = 18
    $labelSubject.Text = "Subject"
    #
    # labelFrom
    #
    $labelFrom.Location = '17, 538'
    $labelFrom.Name = "labelFrom"
    $labelFrom.Size = '50, 19'
    $labelFrom.Text = "From"
    #
    # label1
    #
    $labelTo.Location = '17, 578'
    $labelTo.Name = "labelTo"
    $labelTo.Size = '50, 19'
    #$labelTo.TabIndex = 16
    $labelTo.Text = "To"
    #
    # labelSMTPServer
    #
    $labelSMTPServer.Location = '350, 538'
    $labelSMTPServer.Name = "labelSMTPServer"
    $labelSMTPServer.Size = '50, 19'
    #$labelSMTPServer.TabIndex = 15
    $labelSMTPServer.Text = "SMTP"
    #
    #txtSubject
    #
    $txtSubject.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtSubject.Location = New-Object System.Drawing.Point(420,578)
    $txtSubject.Name = "txtSubject"
    $txtSubject.Size = New-Object System.Drawing.Size(200,70)
    $txtSubject.visible=$TRUE
    #
    #txtFrom
    #
    $txtFrom.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtFrom.Location = New-Object System.Drawing.Point(67,538)
    $txtFrom.Name = "txtFrom"
    #$txtFrom.TabIndex = 1
    $txtFrom.Size = New-Object System.Drawing.Size(200,70)
    $txtFrom.visible=$TRUE
    
     #
    #txtSMTPServer
    #
    $txtSMTPServer.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtSMTPServer.Location = New-Object System.Drawing.Point(420,538)
    $txtSMTPServer.Name = "txtSMTPServer"
    $txtSMTPServer.Size = New-Object System.Drawing.Size(200,70)
    $txtSMTPServer.visible=$TRUE
    
        #
    #txtTo
    #
    $txtTo.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtTo.Location = New-Object System.Drawing.Point(67,578)
    $txtTo.Name = "txtTo"
    $txtTo.Size = New-Object System.Drawing.Size(200,70)
    $txtTo.visible=$TRUE
    <#
    
    $Warningcheckbox.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $Warningcheckbox.Location = New-Object System.Drawing.Point(630,538)
    $Warningcheckbox.Name = "CheckBox1"
    $Warningcheckbox.Size = New-Object System.Drawing.Size(65,20)
    $Warningcheckbox.TabIndex = 3
    $Warningcheckbox.Text = "Warning"
    $Warningcheckbox.UseVisualStyleBackColor = $True
    #
    $txtWarning.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtWarning.Location = New-Object System.Drawing.Point(700,538)
    $txtWarning.Name = "txtWarning"
    $txtWarning.Size = New-Object System.Drawing.Size(50,25)
    $txtWarning.visible=$FALSE
    #
    $Criticalcheckbox.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $Criticalcheckbox.Location = New-Object System.Drawing.Point(630,578)
    $Criticalcheckbox.Name = "CheckBox1"
    $Criticalcheckbox.Size = New-Object System.Drawing.Size(65,20)
    $Criticalcheckbox.TabIndex = 3
    $Criticalcheckbox.Text = "Critical"
    $Criticalcheckbox.UseVisualStyleBackColor = $True
    #
    
     $Criticalcheckbox.Add_CheckStateChanged({
    If ($Criticalcheckbox.Checked -eq $TRUE  ) {
        $txtcritical.visible=$TRUE;
        $script:critical=$txtcritical.text;
          write-host $script:critical;
        }
        else
        {
        $script:critical = 20;
        $txtcritical.visible=$FALSE
        }})
        
    $warningcheckbox.Add_CheckStateChanged({
    If ($warningcheckbox.Checked -eq $TRUE  ) {
        $txtwarning.visible=$TRUE;
        $script:Warning=$txtwarning.text;
         write-host $script:Warning;
        }
        else
        {
        $script:Warning = 50;
        $txtwarning.visible=$FALSE;
        }})
    
  
     
    
    $txtcritical.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtcritical.Location = New-Object System.Drawing.Point(700,578)
    $txtcritical.Name = "txtcritical"
    $txtcritical.Size = New-Object System.Drawing.Size(50,25)
    $txtcritical.visible=$FALSE
    
   #>
    
    # buttonEMailOutput
    #
    $buttonEMailOutput.Location = '660, 538'
    $buttonEMailOutput.Name = "buttonEMailOutput"
    $buttonEMailOutput.Size = '60, 60'
    #$buttonEMailOutput.TabIndex = 10
    $buttonEMailOutput.Text = "E-Mail Output"
    $buttonEMailOutput.UseVisualStyleBackColor = $True
    $buttonEMailOutput.add_Click($GetData)
        
    
$rtbPerfData.BackColor = [System.Drawing.Color]::FromArgb(255,255,255,255)
$rtbPerfData.BorderStyle = [System.Windows.Forms.BorderStyle]::None
$rtbPerfData.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$rtbPerfData.Font = New-Object System.Drawing.Font("Lucida Console",8.25,0,3,1)
$rtbPerfData.Location = New-Object System.Drawing.Point(13,120)
$rtbPerfData.Name = "rtbPerfData"
$rtbPerfData.Size = New-Object System.Drawing.Size(450,200)
$rtbPerfData.TabIndex = 6
$rtbPerfData.Text = ""
$BrowseButton = New-Object System.Windows.Forms.Button
$BrowseButton.Location = New-Object System.Drawing.Size(520,10)
$BrowseButton.Size = New-Object System.Drawing.Size(75,20)
$BrowseButton.TabIndex = 2
$BrowseButton.enabled = $TRUE
$BrowseButton.Text = "Browse"
$BrowseButton.Add_Click({
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$dialog = New-Object System.Windows.Forms.OpenFileDialog
$dialog.DefaultExt = '.txt'
$dialog.Filter = 'All Files|*.*'
$dialog.FilterIndex = 0
$dialog.InitialDirectory = $home
$dialog.Multiselect = $false
$dialog.RestoreDirectory = $true
$dialog.Title = "Select a Input file"
$dialog.ValidateNames = $true
$dialog.ShowDialog()
$txtComputerName1.Text = $dialog.FileName;})
$form1.Controls.Add($BrowseButton)
    #Save the initial state of the form
    $InitialFormWindowState = $form1.WindowState
   
    #Init the OnLoad event to correct the initial state of the form
    $form1.add_Load($Form_StateCorrection_Load)
    #Show the Form
    return $form1.ShowDialog()
} #End Function
#Call OnApplicationLoad to initialize
if(OnApplicationLoad -eq $true)
{
    #Create the form
    Call-SystemInformation_pff | Out-Null
    #Perform cleanup
    OnApplicationExit
}
}
Get-DiskSpace_Report
Posted in PowerShell, Uncategorized | Tagged , , , | Leave a comment

Minimizing SQL Server DBCC CHECKDB Performance Impacts

Overview

SQL Server is the most popular relational database management system, which is used by many organizations to maintain their data. Every user expects that data should be stored in a consistent and integrated form. SQL Server is equipped with an in-built DBCC command, which allow users to check the integrity of the database. One of the command, DBCC CHECKDB helps to check integrity and allocation of specific objects in a database. This command should be executed on a regular basis to check if there is any corruption issue in the database files. It will analyze the entire database and provides the report regarding consistency of files, integrity of indexes, catalog consistency. So, it can be considered as an alert to detect the corruption in the database. However, the performance related issues associated with this command are a major area of concern for most of the SQL users. The following section aims to discuss on how to Minimize Performance Impact of SQL Server DBCC CHECKDB.

Impact of DBCC CHECKDB on SQL Server Performance

Although DBCC CHECKDB command is quite useful command to check the consistency but a number of issues are associated with this command, which hampers the performance of SQL Server. The top three performance related issues faced due to DBCC CHECKDB include:

1. System Process Delay:

When DBCC CHECKDB command is issued, it performs a thorough page-by-page analysis of the database files. Therefore, it acquires many resources at a time that slow down other system processes.

2. Slow Backup Process:

Since backup is a bit-to-bit copy of the existing database, it consumes much of the allocated resources as well as I/O devices. The backup file has to maintain every header, so it consumes lots of time.

3. Slow Recovery Process:

During the database recovery process, a hidden screen shot is created by DBCC. However, the consistency check of the screen shot is much time taking and slows down the recovery process.

Ways to Minimize Performance Impact of SQL Server DBCC CHECKDB

DBCC CHECKDB command plays a crucial role in the corruption issues, so it cannot be avoided due to its impact on performance. However, users can opt for measures to reduce the impact on performance of SQL Server. Some of the practices that can be followed while using DBCC CHECKDB are:

1. TempDb Maintenance:

DBCC makes a continuous use of TempDB database. Hence, it is advisable to store the TempDB on its own spindles so that any of the I/O spooling process does not get disturbed due to database access.

2. Database Mirroring:

Users can create a mirror image of the database using Database Mirroring setup. This will allow users to run DBCC CHECKDB on that version of database. It will keep the performance of primary database intact.

3. Avoid Information Messages:

Make sure that you use use DBCC CHECKDB with WITH NO_INFOMSGS to suppress all the irrelevant outputs generated during DBCC processing. It will somehow improve the performance rate of command processing.

4. Use Trace Flags:

In order to improve the performance, DBCC CHECKDB can be used with the following trace flags:

  1. Trace Flag 2549: This trace flag optimizes the performance of SQL Server by treating each database file as it resides on a different disk. Thus, it makes a faster scan of the database files.
  2. Trace Flag 2562: This flag treats the entire database as a batch and makes the use of better page reading algorithms. It will lead to a faster database processing.

5. Reduce CPU Impact:

DBCC CHECKDB command supports multithreading by default. In order to limit its CPU usage, users can reduce the parallelism in following ways:

  • Trace Flag 2528 can be used to turn off parallelism during processing of DBCC CHECKDB.
  • User can use SQL Server in-built feature “Maximum Degree of Parallelism” to specify the limit of CPU allocation.

6. Use Optimized Options

On a regular basis, DBCC CHECKDB can be used with other attributes, which can optimize the DBCC processing. Various options such as PHYSICAL_ONLY combined with CHECKSUM allows user to evaluate only physical record, thus helps in saving time and resource allocation.

Conclusion

Database corruption is a major hack, which damages user’s relevant data. It is always advised to perform a regular check to detect any corruption related issue. DBCC CHECKDB command is a significant utility to acknowledge the corruption in database. Once a corruption issue is detected in the files, users can take immediate steps to perform database recovery. However, it is always advisable to maintain an updated backup, which can be used in case of database failure or database corruption.

Posted in Uncategorized | Tagged | 1 Comment

PowerSQL – Get SQL TraceFlag SQL Service and Config Information

Get SQL Server configuration, service info, start type along with enabled Trace Flags of local or remote servers.

This script outputs sql server configuration, service, start type, Service Account along with startup trace flags. The script will work on SQL Server 2005 and up.

function Get-SQLAndGlobalTraceFlagsDetails { 
<# 
.SYNOPSIS 
Returns SQL Server Configuration, Service and global TraceFlag details 
 
.DESCRIPTION 
Get-SQLAndGlobalTraceFlagsDetails is a function that returns server high level server configuration information for 
one or more Microsoft SQL Server 
 
.PARAMETER ComputerName 
The computer that is running Microsoft SQL Server that you’re targeting 
 
.EXAMPLE 
Get-SQLAndGlobalTraceFlagsDetails -ComputerName sql01 
 
.EXAMPLE 
Get-SQLAndGlobalTraceFlagsDetails -ComputerName HQDBSP18 
 
.EXAMPLE 
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails -ServerName $_ } 
 
.EXAMPLE 
 
ForEach ($server in Get-ChildItem C:\server.txt) 
{ 
Get-SQLAndGlobalTraceFlagsDetails -ServerName $server 
 
} 
 
.INPUTS 
String 
 
.OUTPUTS 
PSCustomObject , console or CSV 
#> 
[CmdletBinding()] 
param ( 
[Parameter(Mandatory=$true, 
ValueFromPipelineByPropertyName=$true)] 
[Alias('ServerName','PSComputerName','ComputerName')] 
[string[]]$Ser 
) 
 
 
# Used to store the result 
 
$Results = @() 
 
# The below array used to concatenate the Trace flag details 
 
$TraceFlag=@() 
 
#create an smo object for the SQL Server 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null 
 
$SQLServer = new-object ('Microsoft.SQLServer.Management.Smo.Server') $Ser 
$SQL= $SQLServer | select Name, Edition, BuildNumber, Product, ProductLevel, Version, Processors, PhysicalMemory, DefaultFile, DefaultLog, MasterDBPath, MasterDBLogPath, BackupDirectory, ServiceAccount,ServiceStartMode,State 
 
$SQLServer.EnumActiveGlobalTraceFlags()|%{ 
[string]$b=$_.status 
[string]$c=$_.TraceFlag 
$TraceFlag+=$c.ToString() +'->'+ $b.ToString() 
 
} 
 
$Properties = @{ 
Name = $SQL.name 
Edition = $SQL.Edition 
BuildNumber = $SQL.BuildNumber 
Product=$SQL.Product 
ProductLevel=$SQL.ProductLevel 
Version=$SQL.Version 
Processors=$SQL.Processors 
DefaultLog=$SQL.DefaultLog 
MasterDBPath=$SQL.MasterDBPath 
MasterDBLogPath=$SQL.MasterDBLogPath 
BackupDirectory=$SQL.BackupDirectory 
ServiceAccount=$SQL.ServiceAccount 
StartupMode=$SQL.ServiceStartMode 
State=$SQL.State 
TraceAndStatus=($TraceFlag -join ',')} 
 
 
 
$Results += New-Object psobject -Property $properties  
$Results | Select-Object Name, Edition, BuildNumber,TraceAndStatus,Product,ProductLevel,Version,Processors,DefaultLog,MasterDBPath,MasterDBLogPath,BackupDirectory,ServiceAccount,StartupMode,State 
 
 
} 
 
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails1 -ServerName $_ }
 SQLTraceFlag
Posted in PowerShell, SQL | Tagged , , | Leave a comment

SQL – MultiServer SQL Inventory – PowerShell

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.This inventory can take any number of forms but, ideally, will allow some aggregation of information. When server hardware or software changes are taking place, it’s easy to forget that the inventory has to be updated as well. For this reason, I like to have a script that creates the inventory, rather than have to keep a manual log of changes as they occur.

It has seven parameters

  • Mandatory -InputFileName – Text File contains a list of SQL Servers – C:\Server.txt(Example)
  • Mandatory -DirectoryToSave – Folder where you want to store the file
  • Mandatory -OutputType – CSV or Excel
  • Mandatory – Email – Yes or No – If Yes, Requires below three parameter entries
  • ToID – To Email Address – One ore more recipients
  • FromID – From Email
  • SMTP – Mail Server Name

 

Inventory_2

Pre-requisites are –

  1. Permission to access all SQL instances
  2. Permission to create a output file
  3. You must install a SQL Server feature that includes the SQL Server PowerShell (SQLPS) module and client libraries. The easiest way to do this is by installing SQL Server Management Studio, which includes the PowerShell feature and client libraries automatically.The SQL Server PowerShell (SQLPS) module contains the PowerShell providers and cmdlets for all SQL Server features.
  4. Replication Management Objects

The advantage of this implementation

  • Excel or CSV
  • Auto email option for one ore more recipients.
  • Console output

Examples

# EXAMPLE 1 :Output CSV

#PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'csv' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com  

# EXAMPLE 2 :Output Excel 
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'excel' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com  

# EXAMPLE 3 : One or More recipients 
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'excel' -email 'Yes' -To "pram@app.com,pjayaram@app.vom" -From pram@app.com 
-SMTP mail.app.com

# EXAMPLE 4 :  Save the Copy under c:\
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'csv' -email 'No' 

I usually recommend to generate CSV file from the server where you have loaded all sqlps modules(SMO and RMO). While generating Excel, make sure excel components are installed on the machine from which the code is being called.

It gathers 41 very useful information such as Server Configuration(SMO),DB Count(SMO), Traceflag(EnumActiveGlobalTraceFlags()), Replication(RMO) and Mirroring, Service Account Details.

Partial display of items in the console is due the property of FormatEnumerationLimit. The $FormatEnumerationLimit preference variable has a value of 4, and it determines how many items are displayed when a property contains more than a single item. You can set it as per your requirement. If you set it to -1 then it’s unlimited, it will display all the items.
If you wish to display the result in the console, then select output type csv, refer EXAMPLE 2

The traceflags items are concatenated into a single array variable. To Avoid System.Object[] (or Similar Output) using Export-Csv have used join.

  1. Name
  2. ComputerNamePhysicalNetBIOS
  3. NetName
  4. OSVer
  5. OSVersion
  6. Platform
  7. Product
  8. edition
  9. SQLServer
  10. VersionString
  11. ProductLevel
  12. DbCount
  13. HasNullSaPassword
  14. IsCaseSensitive
  15. IsFullTextInstalled
  16. Language
  17. LoginMode
  18. Processors
  19. PhysicalMemory
  20. MaxMem
  21. MinMem
  22. IsSingleUser
  23. IsClustered
  24. Collation
  25. MasterDBLogPath
  26. MasterDBPath
  27. ErrorLogPath
  28. BackupDirectory
  29. DefaultLog
  30. ResourceLastUpdateDateTime
  31. AuditLevel
  32. DefaultFile
  33. xp_cmdshell
  34. FQDN
  35. IPAddress
  36. Traceflag
  37. Replication
  38. Mirroring
  39. ServiceAccount
  40. ServiceStartMode
  41. State

 

Code:  Input SQL Server

To get 41 desired configuration details of server by replacing the $instancename parameter in the below code

$instanceName ='<InstanceName>'
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null
$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer($instanceName) -erroraction'silentlycontinue'
$s=$server1.Information.Properties |Select Name, Value
$st=$server1.Settings.Properties |Select Name, Value
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue
$dbs=$server1.Databases.count
$BuildNumber=$s | where {$_.name -eq "BuildNumber"}|select value
$edition=$s | where {$_.name -eq "edition"}|select value
$ErrorLogPath =$s | where {$_.name -eq "ErrorLogPath"}|select value
$HasNullSaPassword =$s | where {$_.name -eq "HasNullSaPassword"}|select value
$IsCaseSensitive =$s | where {$_.name -eq "IsCaseSensitive"}|select value
$Platform =$s | where {$_.name -eq "Platform"}|select value
$IsFullTextInstalled =$s | where {$_.name -eq "IsFullTextInstalled"}|select value
$Language =$s | where {$_.name -eq "Language"}|select value
$MasterDBLogPath =$s | where {$_.name -eq "MasterDBLogPath"}|select value
$MasterDBPath =$s | where {$_.name -eq "MasterDBPath"}|select value
$NetName =$s | where {$_.name -eq "NetName"}|select value
$OSVersion =$s | where {$_.name -eq "OSVersion"}|select value
$PhysicalMemory =$s | where {$_.name -eq "PhysicalMemory"}|select value
$Processors =$s | where {$_.name -eq "Processors"}|select value
$IsSingleUser =$s | where {$_.name -eq "IsSingleUser"}|select value
$Product =$s | where {$_.name -eq "Product"}|select value
$VersionString =$s | where {$_.name -eq "VersionString"}|select value
$Collation =$s | where {$_.name -eq "Collation"}|select value
$IsClustered =$s | where {$_.name -eq "IsClustered"}|select value
$ProductLevel =$s | where {$_.name -eq "ProductLevel"}|select value
$ComputerNamePhysicalNetBIOS =$s | where {$_.name -eq "ComputerNamePhysicalNetBIOS"}|select value
$ResourceLastUpdateDateTime =$s | where {$_.name -eq "ResourceLastUpdateDateTime"}|select value
$AuditLevel =$st | where {$_.name -eq "AuditLevel"}|select value
$BackupDirectory =$st | where {$_.name -eq "BackupDirectory"}|select value
$DefaultFile =$st | where {$_.name -eq "DefaultFile"}|select value
$DefaultLog =$st | where {$_.name -eq "DefaultLog"}|select value
$LoginMode =$st | where {$_.name -eq "LoginMode"}|select value
$min=$CP | where {$_.Displayname -like "*min server memory*"}|select configValue
$max=$CP | where {$_.Displayname -like "*max server memory*"}|select configValue
$xp_cmdshell=$CP | where {$_.Displayname -like "*xp_cmdshell*"}|select configValue
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress
$a=''
$server1.EnumActiveGlobalTraceFlags()| % {  
    [string]$b=$_.status
    [string]$c=$_.TraceFlag
    $a+=$c.ToString() +'->'+ $b.ToString()+ '  '
    
      }
      
     
If ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
$replication= "1"
}
else
{
$replication= "0"
}
$SQL= $server1 | select ServiceAccount,ServiceStartMode,State
 
 
Foreach($Database in $server1.Databases)
   {
       if($Database.IsMirroringEnabled){$ismirror="1"} else { $ismirror="0"}
   }
  
if ($HasNullSaPassword.value -eq $NULL)
{
    $HasNullSaPassword.value='No'
}
if($DefaultFile.value -eq '')
{
    $DefaultFile.value='NA'
}
if ($VersionString.value -like '8*')
{
    $SQLServer='SQL SERVER 2000'
}
elseif ($VersionString.value -like '9*')
{
    $SQLServer='SQL SERVER 2005'
}
elseif ($VersionString.value -like '10.0*')
{
    $SQLServer='SQL SERVER 2008'
}
elseif ($VersionString.value -like '10.5*')
{
    $SQLServer='SQL SERVER 2008 R2'
}
elseif ($VersionString.value -like '11*')
{
    $SQLServer='SQL SERVER 2012'
}
else
{
    $SQLServer='Invalid'
}
  
  
if ($OSVersion.value -like '5.0*')
{
    $OSVer='Windows 2000'
}
elseif ($OSVersion.value -like '5.1*')
{
    $OSVer='Windows XP'
}
elseif ($OSVersion.value -like '5.2*')
{
    $OSVer='Windows Server 2003'
}
elseif ($OSVersion.value -like '6.0*')
{
    $OSVer='Windows Server 2008'
}
elseif ($OSVersion.value -like '6.1*')
{
    $OSVer='Windows Server 2008 R2'
}
elseif ($OSVersion.value -like '6.2*')
{
    $OSVer='Windows Server 2012'
}
else
{
    $OSVer='NA'
}
 
 
  $Properties = @{Name=$instanceName
        ComputerNamePhysicalNetBIOS   =$ComputerNamePhysicalNetBIOS.value
        NetName   =$NetName.value
        OSVer   =$OSVer
        OSVersion   =$OSVersion.value
        Platform   = $Platform.value
        Product   = $Product.value
        edition   = $edition.value
        SQLServer   = $SQLServer
        VersionString = $VersionString.value
        ProductLevel  = $ProductLevel.value
        DbCount  = $Dbs
        HasNullSaPassword  = $HasNullSaPassword.value
        IsCaseSensitive  = $IsCaseSensitive.value
       IsFullTextInstalled  = $IsFullTextInstalled.value
        Language  = $Language.value
        LoginMode  = $LoginMode.value
        Processors  = $Processors.value
        PhysicalMemory  = $PhysicalMemory.value
        MaxMem  = $Max.Configvalue
        MinMem  = $Min.Configvalue
        IsSingleUser  = $IsSingleUser.value
        IsClustered  = $IsClustered.value
        Collation  = $Collation.value
        MasterDBLogPath  = $MasterDBLogPath.value
        MasterDBPath  = $MasterDBPath.value
        ErrorLogPath  = $ErrorLogPath.value
        BackupDirectory  = $BackupDirectory.value
        DefaultLog  = $DefaultLog.value
        ResourceLastUpdateDateTime  = $ResourceLastUpdateDateTime.value
        AuditLevel  = $AuditLevel.value
        DefaultFile = $DefaultFile.value
        xp_cmdshell = $xp_cmdshell.Configvalue
        FQDN = $FQDN
        IPAddress = ($IPAddress  -join ',')
        Traceflag = ($a -join ',')
        Replication = $replication
        Mirroring = $isMirror
        ServiceAccount = $SQL.ServiceAccount
        ServiceStartMode = $SQL.ServiceStartMode
        State = $SQL.State
        }
 
$Results += New-Object psobject -Property $properties
####Comment the below line, if you wish to not to display an output to the console
$Results
#####
$results | select Name ,ComputerNamePhysicalNetBIOS,NetName ,OSVer,OSVersion,Platform,Product,edition,SQLServer,VersionString,ProductLevel,DbCount,HasNullSaPassword,IsCaseSensitive,IsFullTextInstalled,Language,LoginMode,Processors,PhysicalMemory,MaxMem,MinMem,IsSingleUser,IsClustered,Collation,MasterDBLogPath,MasterDBPath,ErrorLogPath,BackupDirectory,DefaultLog,ResourceLastUpdateDateTime,AuditLevel,DefaultFile,xp_cmdshell,FQDN,IPAddress,Traceflag,Replication,Mirroring,ServiceAccount,ServiceStartMode,State| export-csv -Path $filename -NoTypeInformation

Inventory_1

Code

<#================================= 
# Generated On: 07/18/2016 
# Generated By: Prashanth Jayaram 
# Version : 1.1 
# Desc : SQL Inventory Generation 
# EXAMPLE 1 :Output CSV
#PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\PowerSQL\ -OutputType 'csv' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com 
# EXAMPLE 2 :Output Excel
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'excel' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com 
# EXAMPLE 3 :Multiple receipients
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\Test\ -OutputType 'excel' -email 'Yes' -To "pram@app.com,pjayaram@app.vom" -From pram@app.com -SMTP mail.app.com 
# EXAMPLE 4 :No Email
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt 
-DirectoryToSaveTo C:\123\ -OutputType 'csv' -Email 'No'


#================================= 
#> 
[CmdletBinding()] 
Param( 
 [Parameter(Mandatory=$True,Position=1)] 
 [string]$InputFileName, 
 
 [Parameter(Mandatory=$True,Position=2)] 
 [string]$DirectoryToSaveTo, 
 
 [Parameter(Mandatory=$True,Position=3)] 
 [string]$OutputType,
 
 [Parameter(Mandatory=$True,Position=4)] 
 [string]$Email,
 
 [Parameter(Position=5)] 
 [string]$To, 
 
 [Parameter(Position=6)] 
 [string]$From, 
 
 [Parameter(Position=7)] 
 [string]$SMTP 
 
) 
 
$Filename='SQLInventory' 

$Results = @()
 
 $a=@()

# before we do anything else, are we likely to be able to save the file? 
# if the directory doesn't exist, then create it 
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing 
 { 
 New-Item "$DirectoryToSaveTo" -type directory | out-null 
 } 
 
 If( $OutputType -eq 'Excel')
 {
 
#Create a new Excel object using COM 
$Excel = New-Object -ComObject Excel.Application 
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add() 
$Sheet = $Excel.Worksheets.Item(1) 
 
#Counter variable for rows 
$intRow = 1 
$xlOpenXMLWorkbook=[int]51 
 
#Read thru the contents of the SQL_Servers.txt file 
$Sheet.Cells.Item($intRow,1) ="InstanceName" 
$Sheet.Cells.Item($intRow,2) ="ComputerNamePhysicalNetBIOS" 
$Sheet.Cells.Item($intRow,3) ="NetName" 
$Sheet.Cells.Item($intRow,4) ="OS" 
$Sheet.Cells.Item($intRow,5) ="OSVersion" 
$Sheet.Cells.Item($intRow,6) ="Platform" 
$Sheet.Cells.Item($intRow,7) ="Product" 
$Sheet.Cells.Item($intRow,8) ="edition" 
$Sheet.Cells.Item($intRow,9) ="Version" 
$Sheet.Cells.Item($intRow,10) ="VersionString" 
$Sheet.Cells.Item($intRow,11) ="ProductLevel" 
$Sheet.Cells.Item($intRow,12) ="DatabaseCount" 
$Sheet.Cells.Item($intRow,13) ="HasNullSaPassword" 
$Sheet.Cells.Item($intRow,14) ="IsCaseSensitive" 
$Sheet.Cells.Item($intRow,15) ="IsFullTextInstalled" 
$Sheet.Cells.Item($intRow,16) ="Language" 
$Sheet.Cells.Item($intRow,17) ="LoginMode" 
$Sheet.Cells.Item($intRow,18) ="Processors" 
$Sheet.Cells.Item($intRow,19) ="PhysicalMemory" 
$Sheet.Cells.Item($intRow,10) ="MaxMemory" 
$Sheet.Cells.Item($intRow,21) ="MinMemory" 
$Sheet.Cells.Item($intRow,22) ="IsSingleUser" 
$Sheet.Cells.Item($intRow,23) ="IsClustered" 
$Sheet.Cells.Item($intRow,24) ="Collation" 
$Sheet.Cells.Item($intRow,25) ="MasterDBLogPath" 
$Sheet.Cells.Item($intRow,26) ="MasterDBPath" 
$Sheet.Cells.Item($intRow,27) ="ErrorLogPath" 
$Sheet.Cells.Item($intRow,28) ="BackupDirectory" 
$Sheet.Cells.Item($intRow,29) ="DefaultLog" 
$Sheet.Cells.Item($intRow,20) ="ResourceLastUpdatetime" 
$Sheet.Cells.Item($intRow,31) ="AuditLevel" 
$Sheet.Cells.Item($intRow,32) ="DefaultFile" 
$Sheet.Cells.Item($intRow,33) ="xp_cmdshell" 
$Sheet.Cells.Item($intRow,34) ="Domain" 
$Sheet.Cells.Item($intRow,35) ="IPAddress" 
$Sheet.Cells.Item($intRow,36) ="TraceFlag" 
$Sheet.Cells.Item($intRow,37) ="Mirror" 
$Sheet.Cells.Item($intRow,38) ="Replication" 
$Sheet.Cells.Item($intRow,39) ="ServiceAccount" 
$Sheet.Cells.Item($intRow,40) ="ServiceStartMode" 
$Sheet.Cells.Item($intRow,41) ="State" 



 
 for ($col = 1; $col –le 41; $col++) 
 { 
 $Sheet.Cells.Item($intRow,$col).Font.Bold = $True 
 $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48 
 $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34 
 } 
 
$intRow++ 
 
foreach ($instanceName in Get-Content $InputFileName) 
{ 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null

$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName 
$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer($instanceName) 
$s=$server1.Information.Properties |Select Name, Value 
$st=$server1.Settings.Properties |Select Name, Value 
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue 
$dbs=$server1.Databases.count 
$BuildNumber=$s | where {$_.name -eq "BuildNumber"}|select value 
$edition=$s | where {$_.name -eq "edition"}|select value 
$ErrorLogPath =$s | where {$_.name -eq "ErrorLogPath"}|select value 
$HasNullSaPassword =$s | where {$_.name -eq "HasNullSaPassword"}|select value 
$IsCaseSensitive =$s | where {$_.name -eq "IsCaseSensitive"}|select value 
$Platform =$s | where {$_.name -eq "Platform"}|select value 
$IsFullTextInstalled =$s | where {$_.name -eq "IsFullTextInstalled"}|select value 
$Language =$s | where {$_.name -eq "Language"}|select value 
$MasterDBLogPath =$s | where {$_.name -eq "MasterDBLogPath"}|select value 
$MasterDBPath =$s | where {$_.name -eq "MasterDBPath"}|select value 
$NetName =$s | where {$_.name -eq "NetName"}|select value 
$OSVersion =$s | where {$_.name -eq "OSVersion"}|select value 
$PhysicalMemory =$s | where {$_.name -eq "PhysicalMemory"}|select value 
$Processors =$s | where {$_.name -eq "Processors"}|select value 
$IsSingleUser =$s | where {$_.name -eq "IsSingleUser"}|select value 
$Product =$s | where {$_.name -eq "Product"}|select value 
$VersionString =$s | where {$_.name -eq "VersionString"}|select value 
$Collation =$s | where {$_.name -eq "Collation"}|select value 
$IsClustered =$s | where {$_.name -eq "IsClustered"}|select value 
$ProductLevel =$s | where {$_.name -eq "ProductLevel"}|select value 
$ComputerNamePhysicalNetBIOS =$s | where {$_.name -eq "ComputerNamePhysicalNetBIOS"}|select value 
$ResourceLastUpdateDateTime =$s | where {$_.name -eq "ResourceLastUpdateDateTime"}|select value 
$AuditLevel =$st | where {$_.name -eq "AuditLevel"}|select value 
$BackupDirectory =$st | where {$_.name -eq "BackupDirectory"}|select value 
$DefaultFile =$st | where {$_.name -eq "DefaultFile"}|select value 
$DefaultLog =$st | where {$_.name -eq "DefaultLog"}|select value 
$LoginMode =$st | where {$_.name -eq "LoginMode"}|select value 
$min=$CP | where {$_.Displayname -like "*min server memory*"}|select configValue 
$max=$CP | where {$_.Displayname -like "*max server memory*"}|select configValue 
$xp_cmdshell=$CP | where {$_.Displayname -like "*xp_cmdshell*"}|select configValue 
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name 
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress 
$a=''
$server1.EnumActiveGlobalTraceFlags()| % {
 [string]$b=$_.status
 [string]$c=$_.TraceFlag
 $a+=$c.ToString() +'->'+ $b.ToString() + ' '
 
 }
 
If ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
$replication= "1"
}
else
{
$replication= "0"
}
$SQL= $server1 | select ServiceAccount,ServiceStartMode,State



Foreach($Database in $server1.Databases)
 {
 if($Database.IsMirroringEnabled){$ismirror="1"} else { $ismirror="0"}
 }
 
if ($HasNullSaPassword.value -eq $NULL) 
{ 
 $HasNullSaPassword.value='No' 
} 
if($DefaultFile.value -eq '') 
{ 
 $DefaultFile.value='NA' 
} 
if ($VersionString.value -like '8*') 
{ 
 $SQLServer='SQL SERVER 2000' 
} 
elseif ($VersionString.value -like '9*') 
{ 
 $SQLServer='SQL SERVER 2005' 
} 
elseif ($VersionString.value -like '10.0*') 
{ 
 $SQLServer='SQL SERVER 2008' 
} 
elseif ($VersionString.value -like '10.5*') 
{ 
 $SQLServer='SQL SERVER 2008 R2' 
} 
elseif ($VersionString.value -like '11*') 
{ 
 $SQLServer='SQL SERVER 2012' 
} 
else 
{ 
 $SQLServer='Invalid' 
} 
 
 
if ($OSVersion.value -like '5.0*') 
{ 
 $OSVer='Windows 2000' 
} 
elseif ($OSVersion.value -like '5.1*') 
{ 
 $OSVer='Windows XP' 
} 
elseif ($OSVersion.value -like '5.2*') 
{ 
 $OSVer='Windows Server 2003' 
} 
elseif ($OSVersion.value -like '6.0*') 
{ 
 $OSVer='Windows Server 2008' 
} 
elseif ($OSVersion.value -like '6.1*') 
{ 
 $OSVer='Windows Server 2008 R2' 
} 
elseif ($OSVersion.value -like '6.2*') 
{ 
 $OSVer='Windows Server 2012' 
} 
else 
{ 
 $OSVer='NA' 
} 
 $Sheet.Cells.Item($intRow,1) =$instanceName 
 $Sheet.Cells.Item($intRow,2) =$ComputerNamePhysicalNetBIOS.value 
 $Sheet.Cells.Item($intRow,3) =$NetName.value 
 $Sheet.Cells.Item($intRow,4) =$OSVer 
 $Sheet.Cells.Item($intRow,5) =$OSVersion.value 
 $Sheet.Cells.Item($intRow,6) = $Platform.value 
 $Sheet.Cells.Item($intRow,7) = $Product.value 
 $Sheet.Cells.Item($intRow,8) = $edition.value 
 $Sheet.Cells.Item($intRow,9) = $SQLServer 
 $Sheet.Cells.Item($intRow,10) = $VersionString.value 
 $Sheet.Cells.Item($intRow,11) = $ProductLevel.value 
 $Sheet.Cells.Item($intRow,12) = $Dbs 
 $Sheet.Cells.Item($intRow,13) = $HasNullSaPassword.value 
 $Sheet.Cells.Item($intRow,14) = $IsCaseSensitive.value 
 $Sheet.Cells.Item($intRow,15) = $IsFullTextInstalled.value 
 $Sheet.Cells.Item($intRow,16) = $Language.value 
 $Sheet.Cells.Item($intRow,17) = $LoginMode.value 
 $Sheet.Cells.Item($intRow,18) = $Processors.value 
 $Sheet.Cells.Item($intRow,19) = $PhysicalMemory.value 
 $Sheet.Cells.Item($intRow,10) = $Max.Configvalue 
 $Sheet.Cells.Item($intRow,21) = $Min.Configvalue 
 $Sheet.Cells.Item($intRow,22) = $IsSingleUser.value 
 $Sheet.Cells.Item($intRow,23) = $IsClustered.value 
 $Sheet.Cells.Item($intRow,24) = $Collation.value 
 $Sheet.Cells.Item($intRow,25) = $MasterDBLogPath.value 
 $Sheet.Cells.Item($intRow,26) = $MasterDBPath.value 
 $Sheet.Cells.Item($intRow,27) = $ErrorLogPath.value 
 $Sheet.Cells.Item($intRow,28) = $BackupDirectory.value 
 $Sheet.Cells.Item($intRow,29) = $DefaultLog.value 
 $Sheet.Cells.Item($intRow,20) = $ResourceLastUpdateDateTime.value 
 $Sheet.Cells.Item($intRow,31) = $AuditLevel.value 
 $Sheet.Cells.Item($intRow,32) = $DefaultFile.value 
 $Sheet.Cells.Item($intRow,33) = $xp_cmdshell.Configvalue 
 $Sheet.Cells.Item($intRow,34) = $FQDN 
 $Sheet.Cells.Item($intRow,35) = ($IPAddress -join ',') 
 $Sheet.Cells.Item($intRow,36) = ($a -join ',')
 $Sheet.Cells.Item($intRow,37) = $replication 
 $Sheet.Cells.Item($intRow,38) = $isMirror
 $Sheet.Cells.Item($intRow,39) = $SQL.ServiceAccount 
 $Sheet.Cells.Item($intRow,40) = $SQL.ServiceStartMode
 $Sheet.Cells.Item($intRow,41) = $SQL.State
 
$intRow ++ 
 
} 
 
$filename = "$DirectoryToSaveTo$filename.xlsx" 
if (test-path $filename ) { rm $filename } #delete the file if it already exists 
$Sheet.UsedRange.EntireColumn.AutoFit() 
cls 
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) 
$Excel.Saved = $True 
$Excel.Close() 
 
 }
 ElseIf( $OutputType -eq 'csv')
 {
 $filename = "$DirectoryToSaveTo$filename.csv" 
 if (test-path $filename ) { rm $filename } #delete the file if it already exists 
 foreach ($instanceName in Get-Content $InputFileName) 
{ 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null
$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName 
$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer($instanceName) -erroraction 'silentlycontinue'
$s=$server1.Information.Properties |Select Name, Value 
$st=$server1.Settings.Properties |Select Name, Value 
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue 
$dbs=$server1.Databases.count 
$BuildNumber=$s | where {$_.name -eq "BuildNumber"}|select value 
$edition=$s | where {$_.name -eq "edition"}|select value 
$ErrorLogPath =$s | where {$_.name -eq "ErrorLogPath"}|select value 
$HasNullSaPassword =$s | where {$_.name -eq "HasNullSaPassword"}|select value 
$IsCaseSensitive =$s | where {$_.name -eq "IsCaseSensitive"}|select value 
$Platform =$s | where {$_.name -eq "Platform"}|select value 
$IsFullTextInstalled =$s | where {$_.name -eq "IsFullTextInstalled"}|select value 
$Language =$s | where {$_.name -eq "Language"}|select value 
$MasterDBLogPath =$s | where {$_.name -eq "MasterDBLogPath"}|select value 
$MasterDBPath =$s | where {$_.name -eq "MasterDBPath"}|select value 
$NetName =$s | where {$_.name -eq "NetName"}|select value 
$OSVersion =$s | where {$_.name -eq "OSVersion"}|select value 
$PhysicalMemory =$s | where {$_.name -eq "PhysicalMemory"}|select value 
$Processors =$s | where {$_.name -eq "Processors"}|select value 
$IsSingleUser =$s | where {$_.name -eq "IsSingleUser"}|select value 
$Product =$s | where {$_.name -eq "Product"}|select value 
$VersionString =$s | where {$_.name -eq "VersionString"}|select value 
$Collation =$s | where {$_.name -eq "Collation"}|select value 
$IsClustered =$s | where {$_.name -eq "IsClustered"}|select value 
$ProductLevel =$s | where {$_.name -eq "ProductLevel"}|select value 
$ComputerNamePhysicalNetBIOS =$s | where {$_.name -eq "ComputerNamePhysicalNetBIOS"}|select value 
$ResourceLastUpdateDateTime =$s | where {$_.name -eq "ResourceLastUpdateDateTime"}|select value 
$AuditLevel =$st | where {$_.name -eq "AuditLevel"}|select value 
$BackupDirectory =$st | where {$_.name -eq "BackupDirectory"}|select value 
$DefaultFile =$st | where {$_.name -eq "DefaultFile"}|select value 
$DefaultLog =$st | where {$_.name -eq "DefaultLog"}|select value 
$LoginMode =$st | where {$_.name -eq "LoginMode"}|select value 
$min=$CP | where {$_.Displayname -like "*min server memory*"}|select configValue 
$max=$CP | where {$_.Displayname -like "*max server memory*"}|select configValue 
$xp_cmdshell=$CP | where {$_.Displayname -like "*xp_cmdshell*"}|select configValue 
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name 
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress 
$a=''
$server1.EnumActiveGlobalTraceFlags()| % { 
 [string]$b=$_.status
 [string]$c=$_.TraceFlag
 $a+=$c.ToString() +'->'+ $b.ToString()+ ' '
 
 }
 
 
If ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
$replication= "1"
}
else
{
$replication= "0"
}
$SQL= $server1 | select ServiceAccount,ServiceStartMode,State


Foreach($Database in $server1.Databases)
 {
 if($Database.IsMirroringEnabled){$ismirror="1"} else { $ismirror="0"}
 }
 
if ($HasNullSaPassword.value -eq $NULL) 
{ 
 $HasNullSaPassword.value='No' 
} 
if($DefaultFile.value -eq '') 
{ 
 $DefaultFile.value='NA' 
} 
if ($VersionString.value -like '8*') 
{ 
 $SQLServer='SQL SERVER 2000' 
} 
elseif ($VersionString.value -like '9*') 
{ 
 $SQLServer='SQL SERVER 2005' 
} 
elseif ($VersionString.value -like '10.0*') 
{ 
 $SQLServer='SQL SERVER 2008' 
} 
elseif ($VersionString.value -like '10.5*') 
{ 
 $SQLServer='SQL SERVER 2008 R2' 
} 
elseif ($VersionString.value -like '11*') 
{ 
 $SQLServer='SQL SERVER 2012' 
} 
else 
{ 
 $SQLServer='Invalid' 
} 
 
 
if ($OSVersion.value -like '5.0*') 
{ 
 $OSVer='Windows 2000' 
} 
elseif ($OSVersion.value -like '5.1*') 
{ 
 $OSVer='Windows XP' 
} 
elseif ($OSVersion.value -like '5.2*') 
{ 
 $OSVer='Windows Server 2003' 
} 
elseif ($OSVersion.value -like '6.0*') 
{ 
 $OSVer='Windows Server 2008' 
} 
elseif ($OSVersion.value -like '6.1*') 
{ 
 $OSVer='Windows Server 2008 R2' 
} 
elseif ($OSVersion.value -like '6.2*') 
{ 
 $OSVer='Windows Server 2012' 
} 
else 
{ 
 $OSVer='NA' 
} 


 $Properties = @{Name=$instanceName 
 ComputerNamePhysicalNetBIOS =$ComputerNamePhysicalNetBIOS.value 
 NetName =$NetName.value 
 OSVer =$OSVer 
 OSVersion =$OSVersion.value 
 Platform = $Platform.value 
 Product = $Product.value 
 edition = $edition.value 
 SQLServer = $SQLServer 
 VersionString = $VersionString.value 
 ProductLevel = $ProductLevel.value 
 DbCount = $Dbs 
 HasNullSaPassword = $HasNullSaPassword.value 
 IsCaseSensitive = $IsCaseSensitive.value 
 IsFullTextInstalled = $IsFullTextInstalled.value 
 Language = $Language.value 
 LoginMode = $LoginMode.value 
 Processors = $Processors.value 
 PhysicalMemory = $PhysicalMemory.value 
 MaxMem = $Max.Configvalue 
 MinMem = $Min.Configvalue 
 IsSingleUser = $IsSingleUser.value 
 IsClustered = $IsClustered.value 
 Collation = $Collation.value 
 MasterDBLogPath = $MasterDBLogPath.value 
 MasterDBPath = $MasterDBPath.value 
 ErrorLogPath = $ErrorLogPath.value 
 BackupDirectory = $BackupDirectory.value 
 DefaultLog = $DefaultLog.value 
 ResourceLastUpdateDateTime = $ResourceLastUpdateDateTime.value 
 AuditLevel = $AuditLevel.value 
 DefaultFile = $DefaultFile.value 
 xp_cmdshell = $xp_cmdshell.Configvalue 
 FQDN = $FQDN 
 IPAddress = ($IPAddress -join ',')
 Traceflag = ($a -join ',')
 Replication = $replication 
 Mirroring = $isMirror
 ServiceAccount = $SQL.ServiceAccount 
 ServiceStartMode = $SQL.ServiceStartMode
 State = $SQL.State
 }

$Results += New-Object psobject -Property $properties 
####Comment the below line, if you wish to not to display an output to the console
$Results
#####
$results | select Name ,ComputerNamePhysicalNetBIOS,NetName ,OSVer,OSVersion,Platform,Product,edition,SQLServer,VersionString,ProductLevel,DbCount,HasNullSaPassword,IsCaseSensitive,IsFullTextInstalled,Language,LoginMode,Processors,PhysicalMemory,MaxMem,MinMem,IsSingleUser,IsClustered,Collation,MasterDBLogPath,MasterDBPath,ErrorLogPath,BackupDirectory,DefaultLog,ResourceLastUpdateDateTime,AuditLevel,DefaultFile,xp_cmdshell,FQDN,IPAddress,Traceflag,Replication,Mirroring,ServiceAccount,ServiceStartMode,State| export-csv -Path $filename -NoTypeInformation

 }
 
 }
 
 
 
 
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath) 
{ 
#initate message 
$email = New-Object System.Net.Mail.MailMessage 
$email.From = $emailFrom 
$email.To.Add($emailTo) 
$email.Subject = $subject 
$email.Body = $body 
# initiate email attachment 
$emailAttach = New-Object System.Net.Mail.Attachment $filePath 
$email.Attachments.Add($emailAttach) 
#initiate sending email 
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
$smtp.Send($email) 
} 


If( $Email -eq 'Yes')
{
If (!($to) -and !($smtp))
{ 
#Call Function 
sendEmail -emailFrom $from -emailTo $to "SQL INVENTORY" "SQL INVENTORY DETAILS - COMPLETE DETAILS" -smtpServer $SMTP -filePath $filename 
}
else
{
write-host 'To or SMTP Invalid'
}
}




 

Output

CSV:

Inventory

 

CSV output to saved under f:\PowerSQL folder

Inventory_1

 

Excel

Inventory_3

Conclusion

  • Effectively perform administrative tasks
  • Effective Asset Management
  • Inventory is important for DB Migration/Upgradation
  • Maintain Corporate standards
  • License Validation
  • Easy to provide road-map
Posted in PowerShell, SQL, Uncategorized | Tagged , , , , , | Leave a comment

SQL and NoSQL

Despite the traditional relational DB world, a new trend is emerging which is called NoSQL. Its really hard to define NoSQL. You will get lot of information, opinions on what NoSQL is all about. This is just my understanding and research on NoSQL.

Its all about

  • Non-relational,
  • Open source,
  • Cluster friendly,
  • Schema Independent,
  • No Join,
  • Heterogeneous,
  • Humongous Volume of data,
  • Easy Development,
  • Easy Administration

and other factor such as License cost, Business Process, Organization decision plays a major role in opting for this aggregated brand of the NoSQL umbrella but relational DB’s are still very good for various scenarios and answers all our long lasting questions.

NoSQL, without ACID it is very difficult to write reliable software though it follows CAP theorem , it is clear that without ACID, the developers have to spent a significant fraction of their time building extremely complex and error-prone systems.Also, this is totally depend on business decision and use cases.

There is lot of new development and research is taking place to come out with a stable NoSQL products.There is always pros and Cons while choosing the database product but its all driven by business case, cost involved and decision taken at the management level.

There is a new mindset in my organization to try and go for NoSQL products thinking of lower expense and maintenance overhead. Most of the time during Project life cycle process may give us an insight about success or failure of the implementation.

The problem is that when you had a tremendous amount of data scattered across multiple systems it’s nearly impossible to keep SQL and ACID in this scenario. But as technology grows and matures, they are working on implementing and providing various features to provide data consistency and integrity. Also, adding SQL support to underlying data layer gives an flexibility to query the data.This is the only reason and hard to say it’s a schema-less design as it’s indirectly bound to schema.

NoSQL, the Non-Relational db is classified under Key-Value pair, Document Oriented type, Column family and Graph.

Further Reading:-

http://highscalability.com/blog/2010/12/6/what-the-heck-are-you-actually-using-nosql-for.

Thanks for reading.

Posted in MongoDB, NoSQL | Tagged , , , | Leave a comment

Tips to Fix Database Consistency Errors – Reported By DBCC CHECKDB

Overview

While working on SQL Server, sometimes users get stuck in annoying situations when database become inaccessible. After executing DBCC CHECKDB command to check the problem, user gets database consistency errors.

Causes Behind DBCC CHECKDB Consistency Errors

The reason behind the database consistency error can be varied from file system corruption, corrupted pages in memory, underlying the hardware system issues, drive issues, or some problem with SQL Server Engine.

How to Fix Database Consistency Errors Reported By DBCC CHECKDB ?

DBCC CHECKDB checks the physical as well as logical consistency of the database pages, rows allocation pages, system table referential integrity, index relationship, and other structure. If any of these checks fails then, errors will be described as part of command. To resolve the inconsistency errors, users first need restore the data from the backup of data. However, if users are not able restore from backup then CHECKDB gives a feature to repair errors. Many times, the problem arises due to file system or hardware issue so user should correct these first before restoring and running repair. There are some resolutions that discussed below to help users to fix SQL database consistency errors.

Windows System Event Log

Windows System Event Log errors indicate possible I/O problems that can be associated with inconsistency of database. It contains various Event IDs depend upon the inaccessibility of data. Every event ID has different resolution to fix the occurrence of an error.

Integrity of File System

Many times, the users face the inconsistency error due to the file system integration. To check the integrity of file system, user can use the chdsk command. This command helps to create and displays a status report for disk. It also lists all the errors on the disks. It is available with different parameters from the command prompt.

I/O Requirements

There are various counters, which are related to I/O and all are located under Logical and Physical Disk. The physical Disk performance object consists of counters that monitors hard or fixed disk on system. The logical disk performance of an object consists of counters, which monitors the logical portion of fixed or hard disk drives.

SQLIOSim Utility

There is a utility known as SQLIOSim that also reports the consistency errors. SQLIOSim is a tool, which is independent of SQL Server Engine for testing the integrity of I/O for the disk system. It can be downloaded from the web for utilization.

Note: SQLIOSim ships on SQL Server 2008 does not require separate download.

Verify Checksum Option

Make sure that the database, which is using PAGE_VERIFY CHECKSUM option. If the checksum error is reported then, it indicates that the consistency error has occurred. After writing the SQL Server, pages to disk the error occurred so that the disk system should be checked thoroughly. User gets 824 error in SQL Server, which is caused due to some external conditions. Some external conditions have caused the modification on database page outside the SQL Server engine code. It can be resolved if the user by running any hardware or system checks to determine if CPU, memory or other hardware related issues exist. User can update all system drivers, Operating system, or hardware if required on the system.

Minimum Repair Option

When user runs DBCC CHECKDB, a recommendation is used to indicate the minimum repair option, which is essential to repair all errors. Its message appears as mentioned below:
CHECKDB found 0 allocation errors and 15 consistency errors in database 'adventureworks'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (adventureworks

The repair recommendation is the minimum level of repair to attempt to resolve all the errors from CHECKDB. It does not mean that all the errors are fixed by repair option. It means that all the errors that are not reported by CHECKDB. When repair_allow_data_loss is recommended will result in loss of data. Repair must be run to determine if there is resolution of an error that will result in data loss. It is a technique to help narrow down if the repair level for each table is to access DBCC CHECKTABLE for any table that is reporting an error. It will display the minimum level of repair for given table.

Conclusion

In the above discussion, the problem and cause of the consistency error is described. Along with this, solution on to how to troubleshoot database consistency errors reported by DBCC CHECKB is discussed. It makes easy for users the error occurrence.

Posted in Backup and Restore | Leave a comment

Why MongoDB?

MongoDB is Object-Oriented, simple, dynamic and scalable NoSQL database. It is based on the NoSQL document store model, in which data objects are stored as separate documents inside a collection instead of storing the data into columns and rows of a traditional relational database. The motivation of the MongoDB language is to implement a data store that provides high performance, high availability, and automatic scaling. MongoDB is extremely simple to install and implement. The core of MongoDB storage is documents and its stored as  JSON or BSON objects.  General distributions for MongoDB support Windows, Linux, Mac OS X, and Solaris

Terminology and Concepts

SQL Server MongoDB
Database Database
Table Collection
Index Index
Row Document
Column Field
Joining Linking & Embedding
Partition Sharding (Range Partition)
Replication ReplSet

Choice of database is always a decision based pros and cons.

Pros:

  • Document oriented
  • High performance
  • High availability -Replication
  • High scalability – Sharding
  • Dynamic- No Rigid Schema.
  • Flexible – field addition/deletion have less or no impact on the application
  • Heterogeneous Data
  • No Joins
  • Distributed
  • Data Representation in JSON or BSON
  • Geospatial support
  • Easy Integration with BigData Hadoop
  • Document-based query language that’s nearly as powerful as SQL
  • Cloud distributions such as AWS, Microsoft, RedHat,dotCloud and SoftLayer etc:-. In fact, MongoDB is built for the cloud. Its native scale-out architecture, enabled by ‘sharding,’ aligns well with the horizontal scaling and agility afforded by cloud computing.

Cons:

  • A downside of NoSQL is that most solutions are not as strong in ACID (Atomic, Consistency, Isolation, Durability) as in the more well-established RDBMS systems.
  • Complex transaction
  • No function or Stored Procedure exists where you can bind the logic

 

Good for:

  • Ecommerce product catalog
  • Blogs and Content Management
  • Real-time analytics and high-speed logging, caching and high scalability
  • Configuration Management
  • Maintain location based data – Geospatial data
  • Mobile and Social networking sites
  • Evolving data requirements
  • Loosely coupled objectives – the design may change by over time

Not good for:

  • Highly transactional system and data model is designed upfront
  • Tightly coupled systems

Reference

https://docs.mongodb.com/ecosystem/use-cases/

 

 

 

 

 

 

 

 

 

 

Posted in MongoDB | Tagged , | Leave a comment

Securing MongoDB – User Administration

The db.createUser(user, writeConcern) method used to create users.We need to provide the username, password and roles

The definition of createUser as follows

{ 
user: "<name>",
pwd: "password>",
customData: { <User Tag> },
roles: [
    { role: "<role>", db: "<database>" },
    { role: "<role>", db: "<database>"},    ...
        ]
}

Role

Role is an approach to restricting system/DB access to authorized users.The security hierarchy is similar to various DB technologies. There are various roles are

Database User Roles

  • read
  • readWrite

Database Administration Roles

  • dbAdmin
  • dbOwner
  • userAdmin

Cluster Administration Roles

  • clusterAdmin
  • clusterManager
  • clusterMonitor
  • hostManager

Backup and Restoration Roles

  • backup
  • restore

All-Database Roles

  • readAnyDatabase
  • readWriteAnyDatabase
  • userAdminAnyDatabase
  • dbAdminAnyDatabase

Superuser Roles

  • root

Internal Role

  • –system

The Roles are a self explanatory. For further reading, read the following MongoDB reference manual Roles

Create User

 

db.createUser(
 {
 user: "reportUser",
 pwd: "12345678",
 roles: [
             {role: "read", db :"northwind"},
             {role: "readWrite", db: "records"},
             {role: "backup", db: "admin"},
             {role:"clusterAdmin", db: "admin"},
             {role:"readAnyDatabase", db: "admin"}
         ]
 }
)

 

Identify the user roles by using db.getUser()

db.getUser("reportUser")

CreateUser1

Change Password

>db.changeUserPassword("reportUser","!@#$1234Mongo")

Drop a user from mongodb using the db.dropUser()

>db.dropUser("reportUser")

Revoke a role from the user using revokeRolesFromUser()

>db.revokRolesFromUser(
"reportUser",
[
{role: "readWrite", db:" northwind"},
{role: "backup", db: "admin"}
]
)

CreateUser2.jpg
Posted in MongoDB | Tagged , , , , | Leave a comment

PowerShell – Install PowerShell

Recently got a question regarding PowerShell installation hence this post took an existence.

How to Install PowerShell various versions

http://technet.microsoft.com/en-us/library/hh847837.aspx

http://social.technet.microsoft.com/wiki/contents/articles/21016.how-to-install-windows-powershell-4-0.aspx

Can we have more than one version of Powershell?

Yes, You can have multiple versions PowerShell.

How to switch between Powershell version?

ps:\>Powershell -version 4

ps:\>Powershell -version 3

ps:\>Powershell -version 2

Hope this simple post solve various doubts.

Happy Learning:-)

 

 

 

Posted in PowerShell | Leave a comment
Follow

Get every new post delivered to your Inbox.

Join 59 other followers

%d bloggers like this: