Showing posts with label Shell. Show all posts
Showing posts with label Shell. Show all posts

Get SQL Server TCP Connections from Operating System Level

Problem

Our systems (server) administrator who got local administrator rights to our SQL Server wanted to get the remote TCP connections on SQL Server instance.

This can be done by connecting to the instance and use the Dynamic Mangement View (DMV) called dm_exec_connections to query the information. This will also require us to give VIEW SERVER STATE permission to the user. We want to give "just enough" permission to the user that needs it and by providing VIEW SERVER STATE you give the user permission to all DMVs.

Solution

Knowing that the server admins already have local administrator rights to our servers, this solution can be achieved without giving them access to the database instance.

This solution can be achieved in 3 steps using PowerShell:

1. Get all the process that are running in the server using Get-Process and filter all the SQL Server related processes.

PowerShell

Get-Process -ComputerName $servername | Where-Object {$_.ProcessName -in ('smss','SQLAGENT','sqlceip','sqlservr','sqlwriter')}
2. Get all the TCP connection of the processes that were returned on step 1 using Get-NetTCPConnectionYou need to relate the Id from Get-Process with OwningProcess from Get-NetTCPConnection. This will return the IP addresses of the remote TCP connections. 

PowerShell

Get-NetTCPConnection -OwningProcess "$SQLServerProcessID"
3. Get hostname of the remote TCP connections returned on step 2 by using Resolve-DnsName

PowerShell

Resolve-DnsName "$TCPRemoteAddress"
Now you can combine all these steps to create a function that will return SQL Server's remote TCP connection without accessing the database instance.

You can download the function Get-SQLServerTCPConnectionsOSLevel from github.

Format-Volume Bug or Feature?

Format-Volume Bug or Feature?

Problem

We just recently added an additional node to our existing Availability Group cluster. 
Our team uses a PowerShell script to easily format multiple drives in a specific allocation unit size.

The issue happened when we formatted the drive on the new node. It is supposed to only format the drive on the server where it was ran. But for some reason, it also formatted the drive on the existing nodes.

This wiped out the drives on the existing nodes. The existing system went down and we have to restore everything from our backup.

We have to do a RCA (Root Cause Analysis) because it affected our production system.

Root Cause Analysis

We have to build a similar environment to simulate the issue.

To cut the story short, we were able to pin point the issue on Format-Volume command in PowerShell.

This particular issue only affects servers that are part of Windows cluster and have same drive letters on each node.

For SQL Server Always On:
  • This does NOT affect Failover Cluster Instance (FCI) since it is using shared storage.
  • This only affects the Availability Group configuration because each node have it's own storage and they can have same drive letters.
As a test we ran the Format-Volume to format the Z drive on node1 (of a 3 node cluster).

Format-Volume









It formatted the Z drive on of all the nodes instead of just formatting the drive on node1.

Solution

Open a case with Microsoft to check if this is a bug or if there is a different method that we can do to run the Format-Volume only on the node where it is executed. (At the time of this writing, we have not heard back from Microsoft.)

As a precautionary measure for our team, we only use this feature on the following situation:
  • Standalone servers
  • Always On Failover Cluster Instance servers
  • New server builds of Always On Availability Group

Get SQL Server Windows Cluster Information

Get SQL Server Windows Cluster Information

Problem

I have already identified that there is a SQL Server installed on a server. Using PowerShell, I wanted to know if the server is using windows clustered WITHOUT connecting the database instance. 

This is an option that system administrators since they have admin rights on the Operating System but don't have access to the SQL Server instance.

I need to know the following:
1. I need to know if the server is clustered or not.
2. If the server is clustered, I need to know why type of clustering is configured on the server. 
    SQL Server have 2 types of Windows Clustering:
  • Failover Cluster Instance
  • Availability Group
    You can also combine Failover Cluster Instance with Availability Group.
    I also wanted to know if this type of configuration is configured on the server.
3. If the server is clustered, I also need to know if the server is active or passive node

Solution

One of the requirements to be able to get windows clustering information is to ensure that you have FailoverClusters PowerShell module installed on the machine that you are using to run the PowerShell commands that are specified below.

Let us step through the requirements:
1. How to check if the server is clustered or not?
    Using HKEY_LOCAL_MACHINE check if there is a "Cluster" entry in the registry
PowerShell

Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\Cluster}
    To check a server remotely you need to use Invoke-Command
PowerShell

Invoke-Command -Computer $servername -ScriptBlock {Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\Cluster}
    If no value is returned, the SQL Server is standalone.
    If the command returns a value, then SQL Server is clustered using windows clustering.

2. How to check what type of clustering is configured on the server?
    This is where the FailoverClusters module will come-in handy.
    We need to get/check the following:
    a. Check WMIObject MSCluster_ResourceGroup for root\mscluster namespace
    b. Get-Cluster information of the server
    c. Get-ClusterResource information of the server

    Check root\mscluster namespace
PowerShell

Get-WMIObject -Class MSCluster_ResourceGroup -Namespace root\mscluster}
    To check a server remotely you need to use Invoke-Command
PowerShell

Invoke-Command -Computer $servername -ScriptBlock {Get-WMIObject -Class MSCluster_ResourceGroup -Namespace root\mscluster}
    Get the cluster and cluster resource information of the server
PowerShell

Get-Cluster -Name $servername | Get-ClusterResource
    If both root\mscluster namespace and cluster resource does not return a value then the server is configured as a "single node" cluster.

    If both root\mscluster namespace and cluster resource returns a value, we need to figure out what type of SQL Server windows clustering is configured.

3. How to check if "Availability Group" is configured?
    Using the cluster resource information, check if there is a resource type called "SQL Server Availability Group".
PowerShell

Get-Cluster -Name $servername | Get-ClusterResource | Where-Object ResourceType -eq 'SQL Server Availability Group'
    If no results were returned then, the server is configured with "Failover Cluster Instance".
    If one result is returned, the server is configured with "Availability Group/Listener".
    If more than one result is returned, the server is configured with multiple "Availability Group/Listener".

4. How to check if "FCI with Availability Group" is configured?
    First you need to make sure that the server is configured with "Availability Group". 
    Using the cluster resource information, check if there is a resource type called "SQL Server Availability Group" or resource type called "Network Name" and resource name that starts with "SQL Network Name".
PowerShell

Get-Cluster -Name $servername | Get-ClusterResource | Where-Object { (($_.ResourceType -eq 'Network Name') -and ($_.Name -Like 'SQL Network Name *')) -or ($_.ResourceType -eq 'SQL Server Availability Group')  }
    If 2 different resource types are returned, the server is configured with "FCI with Availability Group".
    NOTE: Ensure that you filter the uniqueness of the returned resource type value.

5. How to check if the server is the active or passive node?
    Using the cluster resource information, get the OwnerNode for resource type called "SQL Server".
PowerShell

Get-Cluster -Name $servername | Get-ClusterResource | Where-Object ResourceType -eq 'SQL Server' | Select-Object OwnerNode
    If the returned OwnerNode value is the same as the $servername, then it is the "active" node, otherwise it is the passive node.

Now you can combine all of the commands above to create a function that will return the SQL Server Windows Cluster Information.

You can now download the Get-SQLServer-WindowsClusterInfo function from github.

SQL Server Agent Job - $RawUI.CursorPosition Error

SQL Server Agent Job - $RawUI.CursorPosition Error

Problem

I had this issue a long time ago, but did not have time to write about this.

I am trying to run some PowerShell script to cleanup old backup files in the database server using SQL Server Agent job.

But when I tried running the job, I got this error below.

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  The step failed.

Solution

Let's walk through my initial troubleshooting steps:

1. I read the first 2 sentences and it seems to be obvious that the error is in line 3.
    Line 3 of my code just gets the current date and puts it in a variable:
    
    $currdate = Get-Date

    It seems straight forward, I ran it in Powershell and it runs fine.
    I isolated the code by adding Write-Output to display the value right after getting the date.

Write-Output "Current Date: $currdate"

    I also deleted the rest of my code after the Write-Output since the error is just in line 3.
    
2. I ran the job again and I still got the same error.
    Now I tried to read and understand the error and read the rest of the error message.
    I tried to understand what each sentence.

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script.

The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. 

Correct the script and reschedule the job. 

The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interactionTry a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  

The step failed.    

    Looking at the code, there is no command that seems to be prompting a user and waiting for user interaction.

    The only command that seems to be interacting with the PowerShell Console or ISE is Write-Output which I added in step 1.

3. I tried running the job again with 3 the job again and I still got the same error on line 3.

    I decided to just remove the Get-Date code, just for the heck of it.

4. I ran the SQL Server Agent job with just "clear" on the code. 

clear

    I still got the same error:

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  The step failed.

    There is no line 3 in the code but it is still telling me that the error is in line 3.
    
    This was just frustrating because the error message is not giving the right information.
    The "clear" command is not prompting a user, but it is somewhat interacting with the console and ISE.

    The command "clear" is and alias for "Clear-Host". Another alias for "Clear-Host is "cls". 

5. I removed the "clear" command and tested again the Get-Date and Write-Output code, and it ran without error.

$currdate = Get-Date

Write-Output "Current Date: $currdate

6. I added back the rest of my code to complete the SQL Agent job that will clean old backup files.

Lesson's learned: Don't forget to remove the "Clear-Host", "clear" or "cls" when running your PowerShell commands in SQL Server Agent job.


Check Installed Windows Operating System Patch On Servers Using Powershell

Check Installed Windows Operating System Patch On Servers Using Powershell

Problem

Do you always patch your Windows Operating System every month?

We do this regularly in our servers. But this task is being done by the server administrator.

In short, for us to know what patches were installed we have to go into each server and verify them.

We should be able to do this using powershell.

The script should ask the user to provide the server name or server list file.

It also should ask the user to enter the patch information they want to search for.

Below are some of the information that I immediately wanted to know after the servers are patched:
  1. When did the Windows OS patching happened?
  2. What patches were installed?
  3. Were the patches successfully installed?
  4. Does the server require a reboot after the patching?
  5. If the latest patch were not installed, when was the last Windows OS patching happened and what patches were last installed?
  6. If the server doesn't have the latest patch, the server name needs to be stands out from the list.

Solution

I will try to explain the sections of the script that I used for each requirements.

I need the user to provide the search string information and the server name and server list.

Recently, Microsoft have started naming their Windows OS Patch with YYYY-MM prefix on the patch name/title/description.

Example title for one of the September patches: 
2018-09 Update for Windows Server 2016 (1803) for x64-based Systems (KB4100347).

Now we can just provide the string 2018-09 if we want to know what September patches were installed on the server.
To get this information we need to add a parameter in our script:

PowerShell

Param( 
    [Parameter(Mandatory=$True)] 
    [string]$KBNumber_or_TitleString, 
    [Parameter(Mandatory=$True)] 
    [string]$ServerName_or_ServerList 
)
I also added the Mandatory argument to force the user to provide this information.

Now lets see how we can get the other script requirements:

1. When did the Windows OS patching happened?
2. What patches were installed?
3. Were the patches successfully installed?
    I was able to find the example to get all these three information from this URL:

    The ResultCode to know if the patches were installed successfully is being returned as numbers. 
    I have to transpose them to human readable values in the script below.

    PowerShell

    $Session = New-Object -ComObject Microsoft.Update.Session 
    $Searcher = $Session.CreateUpdateSearcher() 
    $HistoryCount = $Searcher.GetTotalHistoryCount() 
    if ($HistoryCount -gt 0) 
    { 
        $Searcher.QueryHistory(0,$HistoryCount| ForEach-Object -Process { 
        $Title = $null 
        $Title = $_.Title 
        $Result = $null 
        Switch ($_.ResultCode) 
        { 
            0 { $Result = 'NotStarted'} 
            1 { $Result = 'InProgress' } 
            2 { $Result = 'Succeeded' } 
            3 { $Result = 'SucceededWithErrors' } 
            4 { $Result = 'Failed' } 
            5 { $Result = 'Aborted' } 
            default { $Result = $_ } 
        } 
    } 
    
    I also found out that not all the Windows Updates are stored on Microsoft.Update.Session.

    Some of the Windows Patch information are installed as Hotfix. 

    I have to use the powershell Get-Hofix command to get the Hotfix information.

    PowerShell

    Get-Hotfix | Sort-Object InstalledOn,HotFixID -Descending
    4. Does the server require a reboot after the patching?

    When a patch needs a reboot, its ResultCode value will be 1 (In Progress) until the machine is rebooted.

    This can be confirmed and verified by querying the Windows Registry Settings.

    There are 3 different settings that will indicate that reboot is required but not all of them exists in the registry.

    This is the reason why I used -ErrorAction Ignore in the script below.

    PowerShell

    $PendingRebootStatus = $null 
    if ($Result -eq 'InProgress') 
    { 
        if (Get-ChildItem "HKLM:\Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending" -EA Ignore) { $PendingRebootStatus=$true } 
        if (Get-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired" -EA Ignore) { $PendingRebootStatus=$true } 
        if (Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager" -Name PendingFileRenameOperations -EA Ignore) { $PendingRebootStatus=$true } 
        try {  
            $util = [wmiclass]"\\.\root\ccm\clientsdk:CCM_ClientUtilities" 
            $status = $util.DetermineIfRebootPending() 
            if(($status -ne $null-and $status.RebootPending) 
            { 
                $PendingRebootStatus = $true 
            } 
            } 
        catch{} 
    } 
     
    if ($PendingRebootStatus -eq 'True') 
    { 
        $Result = 'Pending restart' 
    } 
    5. If the latest patch were not installed, when was the last Windows OS patching happened and what patches were last installed?
    6. If the server doesn't have the latest patch, the server name needs to be stands out from the list.

    These 2 requirements will have the same process as requirements 1,2, and 3. But this just needs to return the last Windows OS patching information.

    For the servers to be easily identifiable, I placed an * (astirisk) in front of the server name when the report gets generated.

    It is now time to merge them all together in one script.

    NOTE: If you are interested in making it a GUI based script you can refer to my previous posts.
    - Additonal information that can be added are cluster status and Operating Systems version.

    UPDATE: Technet Gallery has been closed by Microsoft, you can view the archived version on Technet Gallery Archive


    PowerShell

    Param( 
        [Parameter(Mandatory=$True)] 
        [string]$KBNumber_or_TitleString, 
        [Parameter(Mandatory=$True)] 
        [string]$ServerName_or_ServerList 
    ) 
     
    clear 
     
    'Microsoft Windows Server Patch Number or Part of Patch Title String: ' + $KBNumber_or_TitleString  
     
    if (($ServerName_or_ServerList.Contains("\") -eq $True) -or ($ServerName_or_ServerList.Contains(".") -eq $True)) 
    #-eq $True) -or (($ServerName_or_ServerList.Contains(".") -eq $True) 
    { 
        $serverlist = get-content "$ServerName_or_ServerList" 
    } 
    else 
    { 
        $serverlist = $ServerName_or_ServerList 
    } 
     
    $KBs = @() 
     
    foreach ($svr in $serverlist) 
    { 
        $svr = $svr.Trim() 
        $AllServerUpdates = $null 
     
        $AllServerUpdates = Invoke-Command -ComputerName $svr -ScriptBlock { 
            $KBNum = $args[0] 
            $Session = New-Object -ComObject Microsoft.Update.Session 
            $Searcher = $Session.CreateUpdateSearcher() 
            $HistoryCount = $Searcher.GetTotalHistoryCount() 
            if ($HistoryCount -gt 0) 
            { 
                $Searcher.QueryHistory(0,$HistoryCount| ForEach-Object -Process { 
                    $Title = $null 
                    $Title = $_.Title 
                    $Result = $null 
                    Switch ($_.ResultCode) 
                    { 
                        0 { $Result = 'NotStarted'} 
                        1 { $Result = 'InProgress' } 
                        2 { $Result = 'Succeeded' } 
                        3 { $Result = 'SucceededWithErrors' } 
                        4 { $Result = 'Failed' } 
                        5 { $Result = 'Aborted' } 
                        default { $Result = $_ } 
                    } 
     
                    $PendingRebootStatus = $null 
                    if ($Result -eq 'InProgress') 
                    { 
                        if (Get-ChildItem "HKLM:\Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending" -EA Ignore) { $PendingRebootStatus=$true } 
                        if (Get-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired" -EA Ignore) { $PendingRebootStatus=$true } 
                        if (Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager" -Name PendingFileRenameOperations -EA Ignore) { $PendingRebootStatus=$true } 
                        try {  
                                $util = [wmiclass]"\\.\root\ccm\clientsdk:CCM_ClientUtilities" 
                                $status = $util.DetermineIfRebootPending() 
                                if(($status -ne $null-and $status.RebootPending) 
                                { 
                                    $PendingRebootStatus = $true 
                                } 
                            } 
                        catch{} 
                    } 
     
                    if ($PendingRebootStatus -eq 'True') 
                    { 
                        $Result = 'Pending restart' 
                    }  
     
                    $details = New-Object PSObject  
                    $details | Add-Member -MemberType NoteProperty -Name Date -Value $_.Date 
                    $details | Add-Member -MemberType NoteProperty -Name Title -Value $Title 
                    $details | Add-Member -MemberType NoteProperty -Name Status -Value $Result 
                    $details  
                } | Sort-Object -Descending:$true -Property Date  
            } else { 
                Get-Hotfix | Sort-Object InstalledOn,HotFixID -Descending | ForEach-Object -Process { 
                    $Title = $null 
                    $Title = $_.Description + ' - ' + $_.HotFixID 
                    $Result = $null 
                    $Result = 'No Status when using Get-Hotfix' 
     
                    $details = New-Object PSObject  
                    $details | Add-Member -MemberType NoteProperty -Name Date -Value $_.InstalledOn 
                    $details | Add-Member -MemberType NoteProperty -Name Title -Value $Title 
                    $details | Add-Member -MemberType NoteProperty -Name Status -Value $Result 
                    $details  
                } 
            }  
     
            #Select-Object -Property * -ExcludeProperty Name | Format-Table -AutoSize -Wrap 
            $details | Where-Object {$_.PSComputerName -ne $null| Select-Object -Property Date, Status, Title #| Format-List | Out-String).Trim()  
        #} -ArgumentList $KBNumber_or_TitleString 
        } -ErrorAction SilentlyContinue 
     
        #$AllServerUpdates.Count  
     
        #$AllServerUpdates | Where-Object {$_.Title -like "*$KBNumber_or_TitleString*"} 
     
        $ServerUpdates = $AllServerUpdates | Where-Object {$_.Title -like "*$KBNumber_or_TitleString*"} 
     
        if ($AllServerUpdates -eq $null) 
        { 
            $KB = New-Object -TypeName PSObject  
            $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value *$svr 
            $KB | Add-Member -MemberType NoteProperty -Name PatchStatus -Value 'Cannot Access Server' 
            $KB | Add-Member -MemberType NoteProperty -Name PatchDate -Value '-----' 
            $KB | Add-Member -MemberType NoteProperty -Name PatchName -Value '-----' 
            $KBs +$KB 
        }  
     
        if ($ServerUpdates -ne $null) 
        { 
            $counter = 0 
            foreach ($ServerUpdate in $ServerUpdates) 
            { 
                $KB = New-Object -TypeName PSObject  
                 
                if ($counter -eq 0) 
                { 
                    $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value $svr 
                } else { 
                    $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value '' 
                } 
                $counter++ 
     
                $KB | Add-Member -MemberType NoteProperty -Name PatchStatus -Value $ServerUpdate.Status 
                $KB | Add-Member -MemberType NoteProperty -Name PatchDate -Value $ServerUpdate.Date  
                $KB | Add-Member -MemberType NoteProperty -Name PatchName -Value $ServerUpdate.Title 
                $KBs +$KB 
            } 
        } else { 
             
            $LatestServerUpdateDate = $AllServerUpdates | Select-Object -First 1 Date | Get-Date -Hour 0 -Minute 0 -Second 0 
            #$LatestServerUpdateDate 
     
            $ServerUpdates = $AllServerUpdates | Where-Object {$_.Date -ge $LatestServerUpdateDate} 
            #$ServerUpdates.Count 
            #$ServerUpdates 
     
            $counter = 0 
            foreach ($ServerUpdate in $ServerUpdates) 
            { 
                $KB = New-Object -TypeName PSObject  
                if ($counter -eq 0) 
                { 
                    $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value *$svr 
                } else { 
                    $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value '' 
                } 
                $counter++ 
     
                $KB | Add-Member -MemberType NoteProperty -Name PatchStatus -Value $ServerUpdate.Status 
                $KB | Add-Member -MemberType NoteProperty -Name PatchDate -Value $ServerUpdate.Date  
                $KB | Add-Member -MemberType NoteProperty -Name PatchName -Value $ServerUpdate.Title 
                $KBs +$KB 
            } 
        } 
     
    } 
     
    $KBs | ft -AutoSize -Wrap 
     
    read-host “Press ENTER to continue...”