By: Pablo Echeverria | Comments | Related: > Database Configurations
Problem
As you may know, there are some performance boosters for SQL Server, and you need to check if you have them set up, or determine if you need to enable them on some of your servers. So after reading this article, I wondered if there was a way to check SQL Server versions prior to 2016? Certainly there is a way, but building the tool requires you to know: PowerShell, C#, system DLL’s, pointers, Active Directory, and parallel execution. Is there an easier option?
Solution
As a lot of articles point out, there isn’t a direct way to query the system, but I found this article that uses, through PowerShell and C# classes, a system library named “advapi32.dll”, described as an “Advanced services library supporting numerous APIs including many security and registry calls”. The main advantage is that it allows you to query remote servers.
So the first step was to separate the C# class in the article, in order to benefit from a clear separation of functions and have code highlighting and intellisense, into the “advapi.cs” code below but with a slight change; I don’t have .NET 4, so the function “IntPtr.Add” doesn’t exist. The solution was, with the help of this article, to replace it with what it does have, perform a basic pointer addition and creating a new pointer.
The second step was to find the commands to get the users contained in a remote machine local group, which is:
$acct = [adsi]"WinNT://$serverName/$acct,group"
$accts = $acct.psbase.Invoke("Members") | select @{Name="Name"; Expression={$_.GetType().InvokeMember("AdsPath", "GetProperty", $null, $_, $null).replace("WinNT://", "").replace("/","\")}}
And the command to get the users contained in a domain group, which is:
$result = net group $acct /domain
This is required because sometimes the SQL Server account isn’t granted the permissions directly, but instead, through a local group (like “Administrators”) or through a domain group (like “Database administrators”), or even more often, the local Windows “Administrators” group that contains the domain group “Database administrators” which contains the SQL Server account.
The result is the below script named “GetInstantFileInitLockPagesMemory.ps1”, which works in the following way:
- Query a local inventory of instances, in which I have the server name and the environment (Production, Test, or Development). I get only unique server names, because there are several instances within the same server and I don’t want to query them more than once.
- Define the path where I have located the file “advapi.cs”, because multiple threads are going to be launched as separate processes and these don’t know where they’re being launched from.
- Define the domain name and the SQL Server account. Note that if you don’t have a domain account, this script won’t work for you, and you may need to tune it for your needs.
- Define the permissions we want to check, for our purposes we will check for “SeManageVolumePrivilege” and “SeLockMemoryPrivilege”. You can check others as defined in the file “advapi.cs” in the enumeration “Rights”.
- Launch several parallel threads, for each of the servers:
- Get the accounts with a user permission, i.e. manage volume privileges.
- Check if the SQL Server account is contained in this list. If not, proceed with next step. If no accounts are granted, continue with the next permission.
- For all local groups:
- Get the members and check if the account is contained in this list. If not, proceed to the next step.
- Get the members of the domain groups assigned to the remote machine local group and check if the account is contained in this list.
- Note that, if the nesting of groups has more levels, i.e. there is another group within the “Database administrators” and the SQL Server account is assigned to it, this script won’t be able to find it.
- For all domain groups:
- Get the members and check if the account is contained in this list.
- Note that, if the nesting of groups has more levels, this script won’t be able to find it.
- Start again doing the same for the next permission, i.e. lock pages in memory.
- If there is a missing permission, this script will return the environment, the server name, and all missing permission. If all permissions are granted, nothing is reported. If there is an error, it shows the environment, server name, last exit code and all error messages.
Download the source code here.
Code for advapi.cs
using System;
namespace PS_LSA
{
using System.ComponentModel;
using System.Runtime.InteropServices;
using System.Security;
using System.Security.Principal;
using LSA_HANDLE = IntPtr;
public enum Rights
{
SeTrustedCredManAccessPrivilege, // Access Credential Manager as a trusted caller
SeNetworkLogonRight, // Access this computer from the network
SeTcbPrivilege, // Act as part of the operating system
SeMachineAccountPrivilege, // Add workstations to domain
SeIncreaseQuotaPrivilege, // Adjust memory quotas for a process
SeInteractiveLogonRight, // Allow log on locally
SeRemoteInteractiveLogonRight, // Allow log on through Remote Desktop Services
SeBackupPrivilege, // Back up files and directories
SeChangeNotifyPrivilege, // Bypass traverse checking
SeSystemtimePrivilege, // Change the system time
SeTimeZonePrivilege, // Change the time zone
SeCreatePagefilePrivilege, // Create a pagefile
SeCreateTokenPrivilege, // Create a token object
SeCreateGlobalPrivilege, // Create global objects
SeCreatePermanentPrivilege, // Create permanent shared objects
SeCreateSymbolicLinkPrivilege, // Create symbolic links
SeDebugPrivilege, // Debug programs
SeDenyNetworkLogonRight, // Deny access this computer from the network
SeDenyBatchLogonRight, // Deny log on as a batch job
SeDenyServiceLogonRight, // Deny log on as a service
SeDenyInteractiveLogonRight, // Deny log on locally
SeDenyRemoteInteractiveLogonRight, // Deny log on through Remote Desktop Services
SeEnableDelegationPrivilege, // Enable computer and user accounts to be trusted for delegation
SeRemoteShutdownPrivilege, // Force shutdown from a remote system
SeAuditPrivilege, // Generate security audits
SeImpersonatePrivilege, // Impersonate a client after authentication
SeIncreaseWorkingSetPrivilege, // Increase a process working set
SeIncreaseBasePriorityPrivilege, // Increase scheduling priority
SeLoadDriverPrivilege, // Load and unload device drivers
SeLockMemoryPrivilege, // Lock pages in memory
SeBatchLogonRight, // Log on as a batch job
SeServiceLogonRight, // Log on as a service
SeSecurityPrivilege, // Manage auditing and security log
SeRelabelPrivilege, // Modify an object label
SeSystemEnvironmentPrivilege, // Modify firmware environment values
SeManageVolumePrivilege, // Perform volume maintenance tasks
SeProfileSingleProcessPrivilege, // Profile single process
SeSystemProfilePrivilege, // Profile system performance
SeUnsolicitedInputPrivilege, // "Read unsolicited input from a terminal device"
SeUndockPrivilege, // Remove computer from docking station
SeAssignPrimaryTokenPrivilege, // Replace a process level token
SeRestorePrivilege, // Restore files and directories
SeShutdownPrivilege, // Shut down the system
SeSyncAgentPrivilege, // Synchronize directory service data
SeTakeOwnershipPrivilege // Take ownership of files or other objects
}
[StructLayout(LayoutKind.Sequential)]
struct LSA_OBJECT_ATTRIBUTES
{
internal int Length;
internal IntPtr RootDirectory;
internal IntPtr ObjectName;
internal int Attributes;
internal IntPtr SecurityDescriptor;
internal IntPtr SecurityQualityOfService;
}
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Unicode)]
struct LSA_UNICODE_STRING
{
internal ushort Length;
internal ushort MaximumLength;
[MarshalAs(UnmanagedType.LPWStr)]
internal string Buffer;
}
[StructLayout(LayoutKind.Sequential)]
struct LSA_ENUMERATION_INFORMATION
{
internal IntPtr PSid;
}
internal sealed class Win32Sec
{
[DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
internal static extern uint LsaOpenPolicy(
LSA_UNICODE_STRING[] SystemName,
ref LSA_OBJECT_ATTRIBUTES ObjectAttributes,
int AccessMask,
out IntPtr PolicyHandle
);
[DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
internal static extern uint LsaAddAccountRights(
LSA_HANDLE PolicyHandle,
IntPtr pSID,
LSA_UNICODE_STRING[] UserRights,
int CountOfRights
);
[DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
internal static extern uint LsaRemoveAccountRights(
LSA_HANDLE PolicyHandle,
IntPtr pSID,
bool AllRights,
LSA_UNICODE_STRING[] UserRights,
int CountOfRights
);
[DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
internal static extern uint LsaEnumerateAccountRights(
LSA_HANDLE PolicyHandle,
IntPtr pSID,
out IntPtr /*LSA_UNICODE_STRING[]*/ UserRights,
out ulong CountOfRights
);
[DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
internal static extern uint LsaEnumerateAccountsWithUserRight(
LSA_HANDLE PolicyHandle,
LSA_UNICODE_STRING[] UserRights,
out IntPtr EnumerationBuffer,
out ulong CountReturned
);
[DllImport("advapi32")]
internal static extern int LsaNtStatusToWinError(int NTSTATUS);
[DllImport("advapi32")]
internal static extern int LsaClose(IntPtr PolicyHandle);
[DllImport("advapi32")]
internal static extern int LsaFreeMemory(IntPtr Buffer);
}
internal sealed class Sid : IDisposable
{
public IntPtr pSid = IntPtr.Zero;
public SecurityIdentifier sid = null;
public Sid(string account)
{
try { sid = new SecurityIdentifier(account); }
catch { sid = (SecurityIdentifier)(new NTAccount(account)).Translate(typeof(SecurityIdentifier)); }
Byte[] buffer = new Byte[sid.BinaryLength];
sid.GetBinaryForm(buffer, 0);
pSid = Marshal.AllocHGlobal(sid.BinaryLength);
Marshal.Copy(buffer, 0, pSid, sid.BinaryLength);
}
public void Dispose()
{
if (pSid != IntPtr.Zero)
{
Marshal.FreeHGlobal(pSid);
pSid = IntPtr.Zero;
}
GC.SuppressFinalize(this);
}
~Sid() { Dispose(); }
}
public sealed class LsaWrapper : IDisposable
{
enum Access : int
{
POLICY_READ = 0x20006,
POLICY_ALL_ACCESS = 0x00F0FFF,
POLICY_EXECUTE = 0X20801,
POLICY_WRITE = 0X207F8
}
const uint STATUS_ACCESS_DENIED = 0xc0000022;
const uint STATUS_INSUFFICIENT_RESOURCES = 0xc000009a;
const uint STATUS_NO_MEMORY = 0xc0000017;
const uint STATUS_OBJECT_NAME_NOT_FOUND = 0xc0000034;
const uint STATUS_NO_MORE_ENTRIES = 0x8000001a;
IntPtr lsaHandle;
public LsaWrapper() : this(null) { } // local system if systemName is null
public LsaWrapper(string systemName)
{
LSA_OBJECT_ATTRIBUTES lsaAttr;
lsaAttr.RootDirectory = IntPtr.Zero;
lsaAttr.ObjectName = IntPtr.Zero;
lsaAttr.Attributes = 0;
lsaAttr.SecurityDescriptor = IntPtr.Zero;
lsaAttr.SecurityQualityOfService = IntPtr.Zero;
lsaAttr.Length = Marshal.SizeOf(typeof(LSA_OBJECT_ATTRIBUTES));
lsaHandle = IntPtr.Zero;
LSA_UNICODE_STRING[] system = null;
if (systemName != null)
{
system = new LSA_UNICODE_STRING[1];
system[0] = InitLsaString(systemName);
}
uint ret = Win32Sec.LsaOpenPolicy(system, ref lsaAttr, (int)Access.POLICY_ALL_ACCESS, out lsaHandle);
if (ret == 0) return;
if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
}
public void AddPrivilege(string account, Rights privilege)
{
uint ret = 0;
using (Sid sid = new Sid(account))
{
LSA_UNICODE_STRING[] privileges = new LSA_UNICODE_STRING[1];
privileges[0] = InitLsaString(privilege.ToString());
ret = Win32Sec.LsaAddAccountRights(lsaHandle, sid.pSid, privileges, 1);
}
if (ret == 0) return;
if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
}
public void RemovePrivilege(string account, Rights privilege)
{
uint ret = 0;
using (Sid sid = new Sid(account))
{
LSA_UNICODE_STRING[] privileges = new LSA_UNICODE_STRING[1];
privileges[0] = InitLsaString(privilege.ToString());
ret = Win32Sec.LsaRemoveAccountRights(lsaHandle, sid.pSid, false, privileges, 1);
}
if (ret == 0) return;
if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
}
public Rights[] EnumerateAccountPrivileges(string account)
{
uint ret = 0;
ulong count = 0;
IntPtr privileges = IntPtr.Zero;
Rights[] rights = null;
using (Sid sid = new Sid(account))
{
ret = Win32Sec.LsaEnumerateAccountRights(lsaHandle, sid.pSid, out privileges, out count);
}
if (ret == 0)
{
rights = new Rights[count];
for (int i = 0; i < (int)count; i++)
{
LSA_UNICODE_STRING str = (LSA_UNICODE_STRING)Marshal.PtrToStructure(
//IntPtr.Add(privileges, i * Marshal.SizeOf(typeof(LSA_UNICODE_STRING))),
new IntPtr(privileges.ToInt64() + (i * Marshal.SizeOf(typeof(LSA_UNICODE_STRING)))),
typeof(LSA_UNICODE_STRING));
rights[i] = (Rights)Enum.Parse(typeof(Rights), str.Buffer);
}
Win32Sec.LsaFreeMemory(privileges);
return rights;
}
if (ret == STATUS_OBJECT_NAME_NOT_FOUND) return null; // No privileges assigned
if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
}
public string[] EnumerateAccountsWithUserRight(Rights privilege)
{
uint ret = 0;
ulong count = 0;
LSA_UNICODE_STRING[] rights = new LSA_UNICODE_STRING[1];
rights[0] = InitLsaString(privilege.ToString());
IntPtr buffer = IntPtr.Zero;
string[] accounts = null;
ret = Win32Sec.LsaEnumerateAccountsWithUserRight(lsaHandle, rights, out buffer, out count);
if (ret == 0)
{
accounts = new string[count];
for (int i = 0; i < (int)count; i++)
{
LSA_ENUMERATION_INFORMATION LsaInfo = (LSA_ENUMERATION_INFORMATION)Marshal.PtrToStructure(
//IntPtr.Add(buffer, i * Marshal.SizeOf(typeof(LSA_ENUMERATION_INFORMATION))),
new IntPtr(buffer.ToInt64() + (i * Marshal.SizeOf(typeof(LSA_ENUMERATION_INFORMATION)))),
typeof(LSA_ENUMERATION_INFORMATION));
try {
accounts[i] = (new SecurityIdentifier(LsaInfo.PSid)).Translate(typeof(NTAccount)).ToString();
} catch (System.Security.Principal.IdentityNotMappedException) {
accounts[i] = (new SecurityIdentifier(LsaInfo.PSid)).ToString();
}
}
Win32Sec.LsaFreeMemory(buffer);
return accounts;
}
if (ret == STATUS_NO_MORE_ENTRIES) return null; // No accounts assigned
if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
}
public void Dispose()
{
if (lsaHandle != IntPtr.Zero)
{
Win32Sec.LsaClose(lsaHandle);
lsaHandle = IntPtr.Zero;
}
GC.SuppressFinalize(this);
}
~LsaWrapper() { Dispose(); }
// helper functions:
static LSA_UNICODE_STRING InitLsaString(string s)
{
// Unicode strings max. 32KB
if (s.Length > 0x7ffe) throw new ArgumentException("String too long");
LSA_UNICODE_STRING lus = new LSA_UNICODE_STRING();
lus.Buffer = s;
lus.Length = (ushort)(s.Length * sizeof(char));
lus.MaximumLength = (ushort)(lus.Length + sizeof(char));
return lus;
}
}
public sealed class TokenManipulator
{
[StructLayout(LayoutKind.Sequential, Pack = 1)]
internal struct TokPriv1Luid
{
public int Count;
public long Luid;
public int Attr;
}
internal const int SE_PRIVILEGE_DISABLED = 0x00000000;
internal const int SE_PRIVILEGE_ENABLED = 0x00000002;
internal const int TOKEN_QUERY = 0x00000008;
internal const int TOKEN_ADJUST_PRIVILEGES = 0x00000020;
internal sealed class Win32Token
{
[DllImport("advapi32.dll", ExactSpelling = true, SetLastError = true)]
internal static extern bool AdjustTokenPrivileges(
IntPtr htok,
bool disall,
ref TokPriv1Luid newst,
int len,
IntPtr prev,
IntPtr relen
);
[DllImport("kernel32.dll", ExactSpelling = true)]
internal static extern IntPtr GetCurrentProcess();
[DllImport("advapi32.dll", ExactSpelling = true, SetLastError = true)]
internal static extern bool OpenProcessToken(
IntPtr h,
int acc,
ref IntPtr phtok
);
[DllImport("advapi32.dll", SetLastError = true)]
internal static extern bool LookupPrivilegeValue(
string host,
string name,
ref long pluid
);
[DllImport("kernel32.dll", ExactSpelling = true)]
internal static extern bool CloseHandle(
IntPtr phtok
);
}
public static void AddPrivilege(Rights privilege)
{
bool retVal;
int lasterror;
TokPriv1Luid tp;
IntPtr hproc = Win32Token.GetCurrentProcess();
IntPtr htok = IntPtr.Zero;
retVal = Win32Token.OpenProcessToken(hproc, TOKEN_ADJUST_PRIVILEGES | TOKEN_QUERY, ref htok);
tp.Count = 1;
tp.Luid = 0;
tp.Attr = SE_PRIVILEGE_ENABLED;
retVal = Win32Token.LookupPrivilegeValue(null, privilege.ToString(), ref tp.Luid);
retVal = Win32Token.AdjustTokenPrivileges(htok, false, ref tp, Marshal.SizeOf(tp), IntPtr.Zero, IntPtr.Zero);
Win32Token.CloseHandle(htok);
lasterror = Marshal.GetLastWin32Error();
if (lasterror != 0) throw new Win32Exception();
}
public static void RemovePrivilege(Rights privilege)
{
bool retVal;
int lasterror;
TokPriv1Luid tp;
IntPtr hproc = Win32Token.GetCurrentProcess();
IntPtr htok = IntPtr.Zero;
retVal = Win32Token.OpenProcessToken(hproc, TOKEN_ADJUST_PRIVILEGES | TOKEN_QUERY, ref htok);
tp.Count = 1;
tp.Luid = 0;
tp.Attr = SE_PRIVILEGE_DISABLED;
retVal = Win32Token.LookupPrivilegeValue(null, privilege.ToString(), ref tp.Luid);
retVal = Win32Token.AdjustTokenPrivileges(htok, false, ref tp, Marshal.SizeOf(tp), IntPtr.Zero, IntPtr.Zero);
Win32Token.CloseHandle(htok);
lasterror = Marshal.GetLastWin32Error();
if (lasterror != 0) throw new Win32Exception();
}
}
}
Code for GetInstantFileInitLockPagesMemory.ps1
$ErrorActionPreference = "Stop" #stop when an error is encountered
# Declare variables
$server = "."
$database = "master"
$query = @"
SELECT DISTINCT [Environment], [ServerName]
FROM [Inventory]
"@
$classPath = ".\advapi.cs"
$domain = "Domain\"
$account = "ServiceAccount"
$rights = "SeManageVolumePrivilege, SeLockMemoryPrivilege" -Split ", "
$SleepTimer = 1000 #after X milliseconds, check if the jobs have finished. 1000 is every second.
$MaxResultTime = 300 #after X seconds, all jobs are killed. 300 is 5 minutes.
$Maxthreads = 20 #number of parallel jobs
# import modules
Import-Module SqlPs -DisableNameChecking
$error.clear() #clear error generated by last command
# get list of servers
$objects = @(Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query)
#environment setup
$output = @()
$errors = ""
$Jobs = @()
$ISS = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$RunspacePool = [runspacefactory]::CreateRunspacePool(1, $MaxThreads, $ISS, $Host)
$RunspacePool.Open()
$script = {
Param ([string]$environment, [string]$serverName, [string]$classPath, [string]$domain, [string]$account, [string[]]$rights)
$ErrorActionPreference = "Stop"
try {
try {
Add-Type (Get-Content $classPath | Out-String)
} catch { }
$lsa = New-Object PS_LSA.LsaWrapper($serverName)
$missingRights = @()
foreach ($right in $rights) {
$accounts = $lsa.EnumerateAccountsWithUserRight($right)
if (!$accounts) { # no account has this permission assigned
$missingRights += $right
continue
}
if ($accounts -contains $domain+$account) { continue } # permission assigned explicitly to this account
$found = $FALSE
foreach ($val in ($accounts | where {$_.startsWith("BUILTIN\")})) { # only local groups
$acct = $val.replace("BUILTIN\", "")
$acct = [adsi]"WinNT://$serverName/$acct,group"
$accts = $acct.psbase.Invoke("Members") | select @{Name="Name"; Expression={$_.GetType().InvokeMember("AdsPath", "GetProperty", $null, $_, $null).replace("WinNT://", "").replace("/","\")}}
if ($accts -like "*"+$domain+$account+"*") { # user belongs to a group that has this permission assigned
$found = $TRUE
break
}
foreach ($val2 in ($accts | where {$_.Name.startsWith($domain) -and !$_.Name.contains($serverName)})) { # only domain groups
$acct = $val2.Name.replace($domain, "")
$result = $null
try {
$result = net group $acct /domain 2>&1 | where {$_.contains($account)}
} catch { }
if ($result) {
$found = $TRUE
break
}
}
if ($found) {
break
}
}
foreach ($val in ($accounts | where {$_.startsWith($domain)})) { # only domain groups
$acct = $val.replace($domain, "")
$result = $null
try {
$result = net group $acct /domain 2>&1 | where {$_.contains($account)}
} catch { }
if ($result) {
$found = $TRUE
break
}
}
if (!$found) { # user not assigned to a group that has this permission assigned
$missingRights += $right
}
}
"" #indicate there was no error
if ($missingRights.count -gt 0) {
@{'Environment'=$environment; 'ServerName'=$serverName; 'MissingRights'=$missingRights;}
}
}
catch {
return $environment + " " + $serverName + ": " + $LastExitCode + " " + $_ | foreach { $_.ToString() } | Out-String
}
}
function CreateThread() {
param ([string]$environment, [string]$serverName, [ref]$Jobs)
$PowershellThread = [powershell]::Create().AddScript($script) #scriptToRun
$PowershellThread.AddArgument($environment) | out-null
$PowershellThread.AddArgument($serverName) | out-null
$PowershellThread.AddArgument($classPath) | out-null
$PowershellThread.AddArgument($domain) | out-null
$PowershellThread.AddArgument($account) | out-null
$PowershellThread.AddArgument($rights) | out-null
$PowershellThread.RunspacePool = $RunspacePool
$Handle = $PowershellThread.BeginInvoke()
$Job = "" | select Handle, Thread, object
$Job.Handle = $Handle; $Job.Thread = $PowershellThread
$Jobs.value += $Job
}
$ResultTimer = Get-Date #start time
#start processing first task for each server
for ($i=0; $i -lt $objects.length; $i++) {
CreateThread $objects[$i]."Environment" $objects[$i]."ServerName" ([ref]$Jobs)
}
while (@($Jobs | where {$_.Handle -ne $Null}).count -gt 0) {
#update completed jobs, get errors and result, and dispose them
foreach ($Job in @($Jobs | where {$_.Handle -ne $Null -and $_.Handle.IsCompleted -eq $True})) {
$results = $Job.Thread.EndInvoke($Job.Handle)
if ($results[0] -and $results[0] -ne "") {
$errors += $results[0]
}
if ($results[1]) {
$output += New-Object -Typename PSObject -Prop $results[1]
}
#end thread
$Job.Thread.Dispose()
$Job.Thread = $Null
$Job.Handle = $Null
}
#show progress
$inProgress = @($Jobs | where {$_.Handle.IsCompleted -eq $False}).count
Write-Progress `
-Activity "Gathering data" `
-PercentComplete (($objects.length - $inProgress) * 100 / $objects.length) `
-Status "$inProgress pending"
#exit on timeout
$currentTime = Get-Date
if (($currentTime - $ResultTimer).totalseconds -gt $MaxResultTime) {
Write-Error "Child script appears to be frozen, try increasing MaxResultTime"
break
}
#sleep
Start-Sleep -Milliseconds $SleepTimer
}
#dispose thread pools
$RunspacePool.Close() | Out-Null
$RunspacePool.Dispose() | Out-Null
$errors #return errors
$output | Format-Table -Auto #return results
Code Execution
The result is going to complete in less than 30 seconds for 153 servers, and while it is running, it shows the progress complete and the number of servers remaining:
And at the end, it shows the results which you need to check:
Next Steps
- Download the scripts from this tip.
- Learn more with these tips:
- Learn more about lock pages in memory.
About the author
Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
