Powershell: Get all installed applications

\ by

Produce a nice output of our best guess at all the installed applications on a server or PC.

$ComputerName = 'SQLPROD'

 

# Get Software list for a 64-bit computer SOFTWARE
$remoteCommand = @"
Get-ItemProperty HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\* 
"@

$scriptBlock = [Scriptblock]::Create($remoteCommand)
# Test 1
$Software1 = Invoke-Command  -Computername $ComputerName -Scriptblock $ScriptBlock

# Get Software list for a computer Wow6432Node
$remoteCommand = @"
Get-ItemProperty HKLM:\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\* 
"@

$scriptBlock = [Scriptblock]::Create($remoteCommand)
# Test 2
$Software2 = Invoke-Command  -Computername $ComputerName -Scriptblock $ScriptBlock
 

$ComputerSoftwareList = [Pscustomobject]@()
if ($Software1) {
    $ComputerSoftwareList += $Software1 | Sort-Object DisplayName
    $ComputerSoftwareList.Count
}
if ($Software2) {
    $ComputerSoftwareList += $Software2 | Sort-Object DisplayName
    $ComputerSoftwareList.Count 
}
$ComputerSoftwareList.Count 

$OutGrid = $ComputerSoftwareList | 
    Sort-Object DisplayName |
        Select-Object DisplayName, DisplayVersion, Publisher, InstallDate -Unique  
        
$Global:seq = 1
$OutGrid | 
    Select-Object @{n=“Seq”; e={$Global:seq; $Global:seq++;}}, 
        @{n="Computer";e={"$ComputerName"}}, DisplayName, DisplayVersion, Publisher, InstallDate |
            Out-GridView
Posted in: PowerShell, Reports, Windows

PowerShell: Get Windows Update scheduled date

\ by

Look for WU install dates for all servers.

$DayOfWeek = @{n='DayOfWeek';e={ ($_.LastRunTime).DayOfWeek }}
$Computer = @{n='Computer';e={$ComputerName}}
$TimeFromNow = @{n='HoursFromNow';e={"{0}" -f (New-TimeSpan -Start (Get-date) -End $_.NextRunTime) } }


$SQLInstanceDev + $SQLInstanceTEST + $SQLInstancePROD  | %{
    $ComputerName = $_.Split('\')[0]
    Write-Host $ComputerName
    # Actually get STATE for Windows Updates for each computer

    .\adhoc\Get-ScheduledTask.ps1 -ComputerName $ComputerName  |
        Where-Object {$_.Name -in 'ProgramDataUpdater','AUScheduledInstall' } |
            Select-Object $Computer, $TimeFromNow, Name, State, Enabled, LastRunTime, NextRunTime, ComputerName, $DayOfWeek

} | Out-GridView
Posted in: Coding, PowerShell, Windows

What is System Reserved Partition

\ by

Nice easy description.

What is System Reserved Partition
The System Reserved Partition holds the Boot Configuration Database, Boot Manager Code, Windows Recovery Environment and reserves space for the startup files which may be required by BitLocker, in case you use the BitLocker Drive Encryption feature.

It is created during a clean fresh installation of Windows 8, Windows 7, Windows Server 2012 and Windows Server 2008.

Posted in: Documentation, Windows

SQL ERROR: SSPI handshake failed with error code

\ by 
DESCRIPTION: SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 10.12.23.345].

This can be related to an AD login account expiring and the user leaving SSMS or something running on their PC. The account eventually locks out and SQL sees this message until the user resets their password.

Posted in: ERRORs, Permissions, SQL Server, Windows

Windows Server settings Background Services and using for SQL Server

\ by

Here are some related discussions.

 

1) Here’s a link for a discussion about the setting.

If the value was set to 0 then that means that the Foreground and background applications equally responsive. I tested it in my LAB. on my system default value was set to 2 and when changed to Background services registry value changed decimal 24. Sometime it may happen that the settings you configure via GUI doesn’t take effect properly thus registry can edited directly. If you have value 24 in registry then your system is optimized for Background Services. No need to worry then.

Look at this section below from- http://support.microsoft.com/kb/102987

========================================================

PriorityControl Entries

The PriorityControl key defines the foreground/background priority boost differential. Change this value by choosing the Tasking button in the System dialog box in Control Panel. Values are stored under this Registry path:

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\PriorityControl

Win32PrioritySeparation REG_DWORD 0, 1, or 2

Default:   0x2

Specifies the priority to give to the application running in the forground. This application receives more process or time relative to other applications running in the background. The values here correlate to the following options in the Tasking dialog box:

Value Meaning

0          Foreground and background applications equally responsive
1          Foreground application more reponsive than background
2          Best foreground application response time

========================================================

Cheers!

Sachin Gadhave
MCP, MCSA, MCTS 

 

2) And here’s another one

 For SQL Server you can choose Background Services. But there a lot of more setting you need to configure:

  • To enable SQL Server to use large pages, enable the Lock pages in memory user right assignment for the account that will run the SQL Server: From the Group Policy MMC snap-in (Gpedit.msc), navigate to Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment. Double-click Lock pages in memory and add the accounts that have credentials to run SQL Server.
  • You can set a fixed amount of memory for the SQL Server process to use. About 3% of the total available memory is used for the system, and another 1% is used for memory management structures. SQL Server can use the rest of available memory, but not more. The following equation is available to calculate total memory to be used by SQL Server: TotalMemory – (1%memory * (numa_nodes)) – 3%memory – 1GB memory
  • Set CPU affinity for the SQL process: Set affinity mask to partition the SQL process on specific cores. To set affinity on more than 32 logical processors, use affinity64 mask. Starting with SQL Server 2008 R2, you can apply equivalent settings for configuring CPU affinity on as many as 256 logical processors using the ALTER SERVER CONFIGURATION SET PROCESS AFFINITY Data Definition Language (DDL) TSQL statement as the sp_configure affinity mask options are announced for deprecation. Use the ‘alter server configuration set process affinity cpu =’ command to set affinity to the desired range of processors for each k-group, separated by comma.

This are just a few settings, in this whitepaper you will find more settings you need to configure (choose the ones that you find relevant): Performance Tuning Guidelines for Windows Server 2008 R2

Posted in: SQL Server, Windows