Search This Blog

Friday, June 28, 2019

Powershell script to find SQL Server instances on remote servers

Powershell script to find SQL Server instances on remote servers

This article shows how to find SQL Server Database Engine instances on remote Windows servers with PowerShell, and how that compares with the dbatools command Find-DbaInstance.

We're looking only for the Database Engine, the service that actually hosts your databases. Reporting Services, Integration Services, Analysis Services, Full-Text Search and the rest are out of scope, even when they share the same box. The examples target SQL Server 2017 and later on Windows.

The basic idea

Every Windows-hosted Database Engine instance, default or named, runs as a Windows service whose program file is sqlservr.exe. So instead of guessing at service names, we ask each server one question: which of your services run that program?

Windows keeps that information in its service list, and you can query or read it remotely through the Win32_Service WMI class. WMI (Windows Management Instrumentation) is Windows' built-in management interface. Win32_Service has one row per service, and its PathName property holds the full path to the service's program file. That's the column we filter on.

Why not just filter on service names that start with MSSQL? Because the Database Engine isn't the only service with that prefix. The Full-Text filter daemon launcher (MSSQLFDLauncher) and Analysis Services (MSSQLServerOLAPService) both have it, and neither one is a Database Engine. The program path is the more reliable signal. It isn't perfect, though. A default instance's service is named MSSQLSERVER, a named instance's service is MSSQL$InstanceName, and both point to sqlservr.exe.

Checking one server

If you only need to check one server, a one-liner will do.

# Windows PowerShell 5.1 only (Get-WmiObject was removed in PowerShell 7):
Get-WmiObject -Class Win32_Service -Filter "PathName LIKE '%sqlservr.exe%'" -ComputerName 'SQLSERVERVM1' |
    Format-Table -Property PSComputerName, Name, DisplayName, StartMode, State
# Windows PowerShell 5.1, and PowerShell 7 on Windows:
Get-CimInstance -ClassName Win32_Service -Filter "PathName LIKE '%sqlservr.exe%'" -ComputerName 'SQLSERVERVM1' |
    Format-Table -Property PSComputerName, Name, DisplayName, StartMode, State





The PowerShell code in those two look nearly identical, but they don't travel the same way over the network, and that matters when one works and the other doesn't. Get-WmiObject connects over DCOM/RPC.  On the other hand, the Get-CimInstance -ComputerName command opens a temporary session over WS-Management, which means WinRM (TCP 5985 by default). One more catch: the CIM cmdlets are Windows-only. In my own check with PowerShell 7.4.6 on Linux, Get-CimInstance and New-CimSession weren't there at all. So every CIM example in this article, including the next two, needs either Windows PowerShell 5.1 or PowerShell 7 on Windows.

If WinRM is blocked but DCOM is allowed, you can tell Get-CimInstance to use DCOM instead:

$session = New-CimSession -ComputerName 'SQLSERVERVM1' -SessionOption (New-CimSessionOption -Protocol Dcom)
Get-CimInstance -CimSession $session -ClassName Win32_Service -Filter "PathName LIKE '%sqlservr.exe%'" |
    Format-Table -Property PSComputerName, Name, DisplayName, StartMode, State
Remove-CimSession $session

Keep in mind that a service isn't the same as a usable instance. On a failover cluster instance, the Database Engine service is normally stopped and set to Manual on every node that doesn't currently own the role. That's how the cluster is supposed to work, so don't count it as a problem. In the other direction, two things won't show up at all:

  • SQL Server on Linux, because this method only reads Windows services.
  • SQL Server Express LocalDB. It runs the same sqlservr.exe, but it doesn't create a Windows service. When an application connects, LocalDB starts as a separate process under that user's account, and it shuts down a few minutes after the last connection closes.

Checking several servers at once

-ComputerName takes more than one name. It's a string array in both Get-WmiObject and Get-CimInstance, so you can pass a comma-separated list:

$servers = 'SQLSERVERVM1', 'SQLSERVERVM2', 'SQLSERVERVM3'
Get-CimInstance -ClassName Win32_Service -Filter "PathName LIKE '%sqlservr.exe%'" -ComputerName $servers |
    Format-Table -Property PSComputerName, Name, DisplayName, StartMode, State

The PSComputerName column matters here. Once results from several servers end up in one table, it's the only thing telling you which instance lives where.

An inline list like that is fine for a handful of servers. What it doesn't handle well is the server that's offline, firewalled or renamed. You get a red error in the middle of your output, and the only record of the failure is your scrollback. The script further down handles that.

Where this fits in the series

This is part 2 of a series on finding SQL Server instances in an Active Directory domain automatically. You don't need part 1 to follow along. Any list of server names works.

Part 1 finds servers in Active Directory. It searches one domain, starting at an OU (OU=SERVERS by default), for computers running Windows Server. Anything outside that OU, outside the domain, or not running Windows won't appear in either part.

The split comes down to cost. Getting a list of servers from Active Directory in Part 1 takes one query. Asking every server about its services takes a network round trip per server, plus a timeout for each one that doesn't answer. In a large environment, that per-server step is the one worth limiting. The trade-off is that a new instance on an existing server is only found if that server is on the list you check. One way to cover both cases is to check new servers daily and the full list less often.

This part never logs in or connects to SQL Server. It tells you that a Database Engine service exists on a server, and what state it's in. It doesn't tell you the version, the edition, the configuration, or whether you can connect. Those are separate checks over a different connection. Being able to read a server's service list over WMI is not the same as having a SQL Server login on it, and in practice the two permissions often don't match.

Where the server list comes from

The script reads its targets from a CSV file. I'm using new_servers.csv, the output of the part 1 script. Any CSV works, as long as it has a DNSHostName or Name column holding the server names. If both are there, the script uses DNSHostName. The name column can be the only column:

Name
SQLSERVERVM1
SQLSERVERVM2
SQLSERVERVM3

Don't skip the header row. Import-Csv treats the first row as column headers unless you pass -Header. In a file with only server names, the first server would end up as the header, and the script would look for a Name column that doesn't exist.

Servers in another domain need their FQDN. If a server is in a different domain from the account you're running as, or your servers are spread across several domains, list each one by its fully qualified domain name (FQDN). That means SQLSERVERVM1.sales.contoso.com, not SQLSERVERVM1. Microsoft's New-CimSession documentation says this directly: short (NetBIOS) names are accepted, but a computer in a different domain must be given by FQDN. Over WinRM, the short name fails at the Kerberos step. Here's the error from the Get-CimInstance one-liner (server name changed):

Get-CimInstance : WinRM cannot process the request. The following error occurred
while using Kerberos authentication: Cannot find the computer SQLSERVERVM1. Verify
that the computer exists on the network and that the name provided is spelled correctly.
    + CategoryInfo          : NotSpecified: (:) [Get-CimInstance], CimException
    + FullyQualifiedErrorId : HRESULT 0x80070035,Microsoft.Management.Infrastructure.CimCmdlets.GetCimInstanceCommand
    + PSComputerName        : SQLSERVERVM1

The message makes it sound like the server is offline or misspelled. Most likely it's neither. Kerberos needs to know which domain the computer belongs to, and a short name doesn't say. The DNSHostName column from the part 1 script already holds FQDNs, so this mainly bites when you build a Name list by hand. That error came over WinRM. I haven't checked how the script's default DCOM path handles a short name for a server in another domain, but using FQDNs is the safe choice either way.

What the scripts do

There are three pieces:

  1. Discovery asks each server for its Database Engine services, keeps going when a server fails, and writes the results to sql_server_instances.csv.
  2. Load copies that CSV into a SQL Server staging table, so you can query the results instead of scrolling back through console output.
  3. Reporting is a few sample queries against the staging table.

Copy them, but please read them before you run them in production. Yes, I know. We all say that and then paste straight into an elevated prompt.

Before you run anything

  • Discovery script: runs in Windows PowerShell 5.1 or PowerShell 7.x on Windows (I've run it under both). It needs rights to read the service list on each target (see the next section) and firewall access for its connection method: DCOM/RPC by default, or WinRM if you set $CimProtocol = 'Wsman'. It's read-only on the targets.
  • Load script: needs the SqlServer PowerShell module. In the target database it needs CREATE TABLE (first run only), ALTER on the staging table (for TRUNCATE), plus INSERT and SELECT. Every run replaces the table's contents. In SqlServer module v22 and later, Invoke-Sqlcmd encrypts connections by default (-Encrypt Mandatory). If your utility instance uses a certificate the client doesn't trust, the connection will fail until you fix the certificate or, in a lab, set $TrustServerCertificate.
  • SQL table script: optional, and destructive. It drops the table. The load script creates the table if it doesn't exist, so most readers can skip this one.

One more thing about the results: if a server is missing from the table, that doesn't mean it has no SQL Server. It may just have failed the check. Look at the failed-servers list before you report "no SQL Server here."

What "rights to read the service list" means

These rights belong to whichever account the discovery script connects as. That's the account running the script, or the alternate credential if you set $UseAlternateCredential = $true.

The easy way is a domain account in each target's local Administrators group. In practice, use a domain account. By default, User Account Control (UAC) strips admin rights from local accounts on remote connections, even when they're in the Administrators group (the LocalAccountTokenFilterPolicy registry value controls this, and it's 0 by default). Domain accounts in Administrators get a full admin token.

Without admin rights, things get more involved. Before your query returns anything, it goes through three separate checks on the target, and each one has its own permission:

  1. DCOM. This is how the discovery script (with its default $CimProtocol = 'Dcom') and Get-WmiObject reach the remote machine. The account needs Remote Launch and Remote Activation on the target, which you set in dcomcnfg. If this check fails, you'll usually see 0x80070005 (access denied).
  2. The WMI namespace. Win32_Service lives in root\cimv2, and remote queries need the Remote Enable permission on that namespace, set in the WMI Control console. By default only administrators have it.
  3. The Service Control Manager (SCM). This is the Windows component that keeps the service list, and WMI asks it on your behalf. It's the one that's easy to miss. Microsoft documents that users who authenticate over the network, without logging on interactively, can connect to the SCM but can't do anything beyond that. That's been the default since Windows Server 2003 SP1. So a non-admin account can pass the first two checks and still be refused at this step.

Microsoft's own pages don't quite agree on step 3. The Win32_Service page lists only the SCM connect right (SC_MANAGER_CONNECT) as the requirement for remote enumeration. The SCM documentation describes the stricter limit on network logons. Vendor guides that grant enumeration rights suggest you'll run into the stricter limit in practice, but test it on one server rather than taking either page's word for it.

The usual fix for step 3 is to change the SCM's security descriptor with sc.exe sdset scmanager. Treat that as a security change, not a quick tweak:

  • sdset replaces the entire descriptor, not just one entry. A lot of guides online include a ready-made string written for Windows Server 2003. Pasting it onto a modern server gives enumeration rights to every authenticated user, not just your group, and it removes entries that newer Windows versions added.
  • Run sc.exe sdshow scmanager first and save what it returns. Then add one entry for a dedicated group to the DACL, the part before S:, instead of rewriting the whole string.
  • Test on one server, and put the change through change control like any other permission change.

Each service also has its own security descriptor, so an account that's allowed into the SCM can still be blocked from querying a particular service. I couldn't find Microsoft documentation on what SQL Server's service descriptors allow by default, or on whether WMI returns an error or just leaves the service out. That second question matters, because if the service is left out, the script records "answered, 0 services" and there's no error to catch. So before you switch to a least-privilege account, run the discovery script on a few servers with both the new account and an admin account, and compare the results.

All of this applies to the discovery script with its default DCOM setting, the Get-WmiObject one-liner and the DCOM session example. Over WinRM ($CimProtocol = 'Wsman', or Get-CimInstance -ComputerName), WinRM replaces step 1 and does its own access check. Steps 2 and 3 most likely still apply, but I haven't verified that path.

Discovery script

<#
.SYNOPSIS
  Finds SQL Server Database Engine services on the servers listed in a CSV file.

.DESCRIPTION
  For each server, opens a CIM session (DCOM by default) and queries
  Win32_Service for services whose binary path contains sqlservr.exe.
  Servers that fail (no ping reply, access denied, RPC/WinRM unavailable,
  timeout) are logged and skipped, and the script moves on.

  Output:
    - Console lists of servers that answered and servers that failed
    - sql_server_instances.csv, one row per Database Engine service
      (the columns the load script expects)

  Requirements:
    - Windows PowerShell 5.1, or PowerShell 7.x on Windows
      (the CIM cmdlets are not available on Linux or macOS)
    - Rights to enumerate services on each target, plus firewall access
      for the chosen protocol: DCOM/RPC (default) or WinRM (Wsman)
    - ActiveDirectory module, only if $UseAlternateCredential is $true

  Read-only on target servers.
#>

$csv_file_name          = 'new_servers.csv'           # needs a DNSHostName or Name column
$export_file_name       = 'sql_server_instances.csv'
$UseAlternateCredential = $false   # $true prompts for a Domain\UserName credential
$SkipPingCheck          = $false   # $true if your network blocks ICMP
$CimProtocol            = 'Dcom'   # 'Dcom' (DCOM/RPC, like Get-WmiObject) or 'Wsman' (WinRM)
$OperationTimeoutSec    = 60       # per-server limit for the service query; 0 = server default

# Windows PowerShell 5.1 (Desktop) or PowerShell 7+ (Core) on Windows only.
# $IsWindows doesn't exist in 5.1, so check the edition first.
$isDesktop51 = $PSVersionTable.PSEdition -eq 'Desktop' -and $PSVersionTable.PSVersion -ge [version]'5.1'
$isCore7Win  = $PSVersionTable.PSEdition -eq 'Core' -and $PSVersionTable.PSVersion.Major -ge 7 -and $IsWindows
if (-not ($isDesktop51 -or $isCore7Win)) {
    throw "This script needs Windows PowerShell 5.1 or PowerShell 7.x on Windows. Current: $($PSVersionTable.PSEdition) $($PSVersionTable.PSVersion)"
}
if ($CimProtocol -notin 'Dcom', 'Wsman') {
    throw "CimProtocol must be 'Dcom' or 'Wsman'."
}

(Get-Date).ToString() + ': Begin'
try {
    $sessionParams = @{
        SessionOption = New-CimSessionOption -Protocol $CimProtocol
        ErrorAction   = 'Stop'   # lets catch see access-denied and RPC/WinRM errors
    }
    $queryParams = @{
        ClassName   = 'Win32_Service'
        Filter      = "PathName LIKE '%sqlservr.exe%'"
        ErrorAction = 'Stop'
    }
    if ($OperationTimeoutSec -gt 0) { $queryParams.OperationTimeoutSec = $OperationTimeoutSec }

    if ($UseAlternateCredential) {
        $credentials = Get-Credential -Message 'Windows account in Domain\UserName format'
        $user   = $credentials.UserName
        if ($user -notmatch '^[^\\]+\\[^\\]+$') {
            throw 'Enter the account as Domain\UserName (not user@domain).'
        }
        $domain = $user.Split('\')[0]

        # Fail once here instead of once per server (helps avoid account lockouts)
        "Authenticating $user against AD domain $domain"
        $domainPath = 'LDAP://' + (Get-ADDomain $domain).DNSRoot
        $domainBind = New-Object System.DirectoryServices.DirectoryEntry(
            $domainPath, $user, $credentials.GetNetworkCredential().Password)
        if ($null -eq $domainBind.distinguishedName) {
            throw "Could not authenticate $user against $domain. Check the password and that the account exists and is enabled."
        }
        "Authenticated $user against $domain"
        $sessionParams.Credential = $credentials
    }

    $CSVData = @(Import-Csv -Path $csv_file_name)
    (Get-Date).ToString() + ": Total rows in the CSV file: " + $CSVData.Count

    # Prefer DNSHostName (as in new_servers.csv); fall back to Name
    $servers = @(foreach ($row in $CSVData) {
        if ($row.PSObject.Properties['DNSHostName'] -and $row.DNSHostName) { $row.DNSHostName }
        elseif ($row.PSObject.Properties['Name'] -and $row.Name) { $row.Name }
    })
    if ($servers.Count -eq 0) {
        throw "No server names found. $csv_file_name needs a DNSHostName or Name column."
    }

    $SqlInstancesList = @()
    $SuccessServers   = @()
    $ErrorServers     = @()

    foreach ($server in $servers) {
        ''
        "Searching for SQL Server Database Engine services on: $server"
        $session = $null
        try {
            # -ComputerName works in both versions (it's an alias of -TargetName in 7.x)
            if (-not $SkipPingCheck -and -not (Test-Connection -ComputerName $server -Count 2 -Quiet)) {
                throw 'No ping reply (offline, name not resolvable, or ICMP blocked)'
            }

            $session     = New-CimSession @sessionParams -ComputerName $server
            $SqlServices = @(Get-CimInstance @queryParams -CimSession $session)
            $SqlInstancesList += $SqlServices
            $SuccessServers   += $server
            "Database Engine services found on $server : $($SqlServices.Count)" |
                Write-Host -ForegroundColor Green
        }
        catch {
            $em = $_.Exception.Message
            "Skipping $server ($em)" | Write-Host -ForegroundColor Yellow
            $ErrorServers += "$server ($em)"
        }
        finally {
            if ($session) { Remove-CimSession -CimSession $session -ErrorAction SilentlyContinue }
        }
    }

    ''
    "Servers that answered: $($SuccessServers.Count)" | Write-Host -ForegroundColor Green
    $SuccessServers

    if ($ErrorServers.Count -gt 0) {
        ''
        "Servers that failed: $($ErrorServers.Count)" | Write-Host -ForegroundColor Red
        '--------------------------------------------------------'
        $ErrorServers
    }

    ''
    if ($SqlInstancesList.Count -eq 0) {
        # Remove the old export so the load script can't reload stale results
        Remove-Item -Path $export_file_name -ErrorAction SilentlyContinue
        Write-Warning "No Database Engine services found. $export_file_name was not created."
    }
    else {
        "Exporting to file: $export_file_name"
        $SqlInstancesList |
            Select-Object -Property PSComputerName,
                @{n = 'SqlInstance'; e = { $_.Name -replace '^MSSQL\$', '' }},
                Name, ProcessID, StartMode, State, Status, ExitCode, PathName |
            Export-Csv -Path $export_file_name -NoTypeInformation -Encoding UTF8

        ''
        'SQL Server Database Engine services found:' | Write-Host -ForegroundColor Green
        '------------------------------------------'
        Import-Csv -Encoding UTF8 -Path $export_file_name | Format-Table -AutoSize
    }

    (Get-Date).ToString() + ': Complete'
}
catch {
    (Get-Date).ToString() + ': Error occurred'
    throw
}

For each server, the script opens its own CIM session, runs the same Win32_Service query as the one-liners, and closes the session in a finally block, so a failure halfway through doesn't leave sessions open. The session uses DCOM by default, which goes over the same network path, and needs the same firewall rules, as Get-WmiObject. Switch $CimProtocol to 'Wsman' if your servers allow WinRM and block DCOM.

The script keeps two kinds of "nothing" apart, and that's on purpose. A server that answers with zero services goes in the "answered" list. A server that times out or refuses the connection goes in the "failed" list along with the error message. If those two ended up in the same bucket, a firewall rule could quietly hide an instance from you. That covers the failures that raise an error. It doesn't cover a permission setting that quietly hides a service, which is why the permission section suggests comparing a least-privilege account with an admin account.

Two more things can trip you up here:

  • A server that answers but hangs. The script caps each server's service query with $OperationTimeoutSec (60 seconds by default), so one stuck server shouldn't hold up the whole run. Microsoft describes that timeout in WinRM terms, though, and I haven't confirmed exactly how it behaves over DCOM, so give the script a slow server or two in your own tests. Set it to 0 to use the server's default instead.
  • The alternate credential and the machine you run it from. WMI generally won't accept a supplied credential on a local connection ("User credentials cannot be used for local connections"). So if the computer running the script is also in your CSV and $UseAlternateCredential is $true, that computer will probably show up in the failed list. Run the script from a machine that isn't on the target list, or expect that entry.

The credential prompt wants Domain\UserName, and the script rejects user@domain up front with a clear message. The domain lookup (Get-ADDomain) runs as the logged-on user, not as the alternate credential, so that account needs to be able to read the target domain too.

The SqlInstance column removes the MSSQL$ prefix from named instances, so MSSQL$SALES becomes SALES. A default instance stays MSSQLSERVER.

Load script

<#
.SYNOPSIS
  Loads sql_server_instances.csv into a SQL Server staging table.

.DESCRIPTION
  Replaces the staging table's contents with the latest discovery run.
  TRUNCATE and the INSERTs run in one transaction, so a failure leaves
  the previous contents in place. Uses Windows authentication as the
  account running the script.

  Requirements:
    - SqlServer PowerShell module
    - In the target database: CREATE TABLE (first run only), ALTER on the
      table (TRUNCATE), INSERT and SELECT

  DESTRUCTIVE: each run deletes the table's existing rows.
#>

$ErrorActionPreference = 'Stop'

$sql_instance_name       = 'SQLADMIN01'     # your DBA utility instance
$db_name                 = 'AdminDBA'
$destination_schema_name = 'dbo'
$destination_table_name  = 'sql_server_instances_stage'
$export_file_name        = 'sql_server_instances.csv'
$TrustServerCertificate  = $false  # v22+ only; for self-signed certs in a lab

(Get-Date).ToString() + ': Begin loading data into SQL staging table'

Import-Module SqlServer
$sqlParams = @{
    ServerInstance   = $sql_instance_name
    Database         = $db_name
    DisableVariables = $true   # don't treat $(...) in data as sqlcmd variables
}
if ($TrustServerCertificate) {
    $sqlModuleMajor = (Get-Module SqlServer | Sort-Object Version -Descending | Select-Object -First 1).Version.Major
    if ($sqlModuleMajor -ge 22) { $sqlParams.TrustServerCertificate = $true }
    else { Write-Warning 'TrustServerCertificate ignored: requires SqlServer module v22 or later.' }
}

# Read and check the CSV BEFORE touching the table
if (-not (Test-Path -Path $export_file_name)) {
    throw "$export_file_name not found. Run the discovery script first."
}
$SqlServices = @(Import-Csv -Encoding UTF8 -Path $export_file_name)
if ($SqlServices.Count -eq 0) {
    throw "$export_file_name has no rows. Staging table left unchanged."
}

function ConvertTo-SqlString ([string]$Value) {
    if ([string]::IsNullOrEmpty($Value)) { return 'NULL' }
    return "'" + $Value.Replace("'", "''") + "'"
}
function ConvertTo-SqlInt ([string]$Value) {
    $n = 0
    if ([int]::TryParse($Value, [ref]$n)) { return $n.ToString() }
    return 'NULL'
}

$table = "[$destination_schema_name].[$destination_table_name]"

# Create the table if it doesn't exist (no USE/GO; -Database sets the context)
$sql_create_table = @"
IF OBJECT_ID(N'$destination_schema_name.$destination_table_name', N'U') IS NULL
CREATE TABLE $table (
    [id]             int IDENTITY(1,1) PRIMARY KEY,
    [PSComputerName] varchar(500)  NULL,
    [ServiceName]    varchar(500)  NULL,
    [InstanceName]   varchar(500)  NULL,
    [PathName]       varchar(1500) NULL,
    [ExitCode]       int           NULL,
    [ProcessID]      int           NULL,
    [StartMode]      varchar(500)  NULL,
    [State]          varchar(500)  NULL,
    [Status]         varchar(500)  NULL,
    [InsertedDate]   datetime      NULL DEFAULT GETDATE()
);
"@
Invoke-Sqlcmd @sqlParams -Query $sql_create_table

# One batch: truncate + inserts, all or nothing
$sb = New-Object System.Text.StringBuilder
[void]$sb.AppendLine('SET XACT_ABORT ON;')
[void]$sb.AppendLine('BEGIN TRANSACTION;')
[void]$sb.AppendLine("TRUNCATE TABLE $table;")
foreach ($s in $SqlServices) {
    $values = @(
        (ConvertTo-SqlString $s.PSComputerName), (ConvertTo-SqlString $s.Name),
        (ConvertTo-SqlString $s.SqlInstance),    (ConvertTo-SqlString $s.PathName),
        (ConvertTo-SqlInt    $s.ExitCode),       (ConvertTo-SqlInt    $s.ProcessID),
        (ConvertTo-SqlString $s.StartMode),      (ConvertTo-SqlString $s.State),
        (ConvertTo-SqlString $s.Status)
    ) -join ', '
    [void]$sb.AppendLine("INSERT INTO $table (PSComputerName, ServiceName, InstanceName, PathName, ExitCode, ProcessID, StartMode, State, Status) VALUES ($values);")
}
[void]$sb.AppendLine('COMMIT TRANSACTION;')

Invoke-Sqlcmd @sqlParams -Query $sb.ToString()

$rcount = Invoke-Sqlcmd @sqlParams -Query "SELECT COUNT(*) AS rcount FROM $table;"
''
"Rows in CSV: $($SqlServices.Count)  |  Rows in staging table: $($rcount.rcount)"
(Get-Date).ToString() + ': Complete loading data into SQL staging table'

The order matters. The script checks the CSV before it touches the table. Then it runs the truncate and all the inserts in one transaction with SET XACT_ABORT ON, so if any statement fails, the whole batch rolls back and the previous run's rows stay in place. Without that, a bad run would leave you with an empty table that looks a lot like "we have no SQL Servers." That would be good news for your workload and terrible news for your accuracy.

SQL script for the table (optional)

-- OPTIONAL and DESTRUCTIVE: drops the staging table and all its rows.
-- The load script creates this table automatically if it doesn't exist.
USE [AdminDBA];
GO
DROP TABLE IF EXISTS [dbo].[sql_server_instances_stage];
GO
CREATE TABLE [dbo].[sql_server_instances_stage](
    [id]             int IDENTITY(1,1) PRIMARY KEY,
    [PSComputerName] varchar(500)  NULL,
    [ServiceName]    varchar(500)  NULL,
    [InstanceName]   varchar(500)  NULL,
    [PathName]       varchar(1500) NULL,
    [ExitCode]       int           NULL,
    [ProcessID]      int           NULL,
    [StartMode]      varchar(500)  NULL,
    [State]          varchar(500)  NULL,
    [Status]         varchar(500)  NULL,
    [InsertedDate]   datetime      NULL DEFAULT GETDATE()
);
GO

Sample report queries

Each load replaces the staging table, so it only holds the latest run. These queries describe what's there right now, not trends over time. If you want history, keep it in a separate table.

-- All discovered Database Engine services, by server
SELECT PSComputerName, ServiceName, InstanceName, PathName,
       State, StartMode, InsertedDate
FROM dbo.sql_server_instances_stage
ORDER BY PSComputerName, ServiceName;

-- Count of services by state
SELECT State, COUNT(*) AS ServiceCount
FROM dbo.sql_server_instances_stage
GROUP BY State
ORDER BY ServiceCount DESC;

-- Set to start automatically but not running: usually worth a look
SELECT PSComputerName, ServiceName, InstanceName, State, StartMode, InsertedDate
FROM dbo.sql_server_instances_stage
WHERE StartMode = 'Auto' AND State <> 'Running'
ORDER BY PSComputerName, ServiceName;

-- All services not running (includes Manual and Disabled, e.g. passive FCI nodes)
SELECT PSComputerName, ServiceName, InstanceName, State, StartMode, InsertedDate
FROM dbo.sql_server_instances_stage
WHERE State <> 'Running'
ORDER BY PSComputerName, ServiceName;

-- When was this data collected, and how much is there?
SELECT MIN(InsertedDate) AS CollectedFrom,
       MAX(InsertedDate) AS CollectedTo,
       COUNT(DISTINCT PSComputerName) AS ServersWithSqlServer,
       COUNT(*) AS ServiceCount
FROM dbo.sql_server_instances_stage;

The "Auto but not running" query is the one I'd look at first. A service that's supposed to start with Windows but isn't running is either broken or was stopped deliberately and never set back. Either way, someone should know.

One timing caveat. Win32_Service has no separate StartMode value for "Automatic (Delayed Start)". The documented values are Boot, System, Auto, Manual and Disabled. Windows reports the delay in a separate DelayedAutoStart property, available on Windows Server 2016 and later, and a delayed service starts a little after the other automatic services. So a delayed-start engine that's checked shortly after a reboot can show up here as Auto and not running, even though it's simply waiting its turn. If the server has just rebooted, run the check again before you raise the alarm. If you want to tell the two cases apart in the table, you'd need to add DelayedAutoStart to the discovery script's Select-Object and a matching column to the staging table. The scripts above don't do that.

What about Find-DbaInstance?

If you use dbatools, the free community PowerShell module for SQL Server, you may be wondering why you'd write any of this yourself, since Find-DbaInstance exists. It's a fair question. The short answer is that the two tools answer different questions, and the differences show up exactly where an inventory tends to go wrong.

How Find-DbaInstance works

Find-DbaInstance works in two phases.

  1. Discovery builds the list of computers to check. You can hand it names with -ComputerName (or pipe in Get-ADComputer output), or let it build the list with -DiscoveryType. The discovery types are SPN lookups in Active Directory, the same network browse SSMS uses, scanning IP ranges, and listing enabled Windows Server computers in AD.
  2. Scan probes each computer in several ways. It queries the SQL Server Browser service, lists SQL Server services over CIM/WMI, tests TCP ports (1433 by default), resolves DNS, pings, and optionally attempts a real SQL Server connection. The Default scan type runs everything except the SQL connection attempt.

Each result is one object per instance, with a Confidence rating. According to the dbatools documentation, High means the service scan or a SQL connection succeeded, Medium means a Browser reply or an open port that matches an SPN, and Low means a single weak signal such as an open port. By default it returns everything rated Low and up.

Here's a version that's closest to what the script above does: read the same CSV and look at services.

# Requires the dbatools module: Install-Module dbatools -Scope CurrentUser
# (this also installs dbatools.library, which dbatools needs; plan for both if you install modules offline)

# Same DNSHostName-then-Name rule as the discovery script
$servers = @(Import-Csv -Path .\new_servers.csv | ForEach-Object {
    if ($_.PSObject.Properties['DNSHostName'] -and $_.DNSHostName) { $_.DNSHostName }
    elseif ($_.Name) { $_.Name }
})

Find-DbaInstance -ComputerName $servers -ScanType SqlService |
    Select-Object ComputerName, InstanceName, SqlInstance, Confidence, Availability |
    Format-Table -AutoSize

And here's the wider net, which also catches instances where the service scan is blocked but the Browser or TCP port answers:

# Add any non-default ports your instances actually use, e.g. -TCPPort 1433, 50100
Find-DbaInstance -ComputerName $servers -ScanType Browser, SqlService, TCPPort -TCPPort 1433 |
    Select-Object ComputerName, InstanceName, Port, Confidence, Availability |
    Format-Table -AutoSize

You might expect 1434 on that port list. Leave it off. The Browser scan already checks UDP 1434, the SQL Server Browser port. TCP 1434 is normally the Dedicated Admin Connection (DAC), and by default the DAC only accepts connections from the server itself. So a remote probe of TCP 1434 usually finds nothing, and on a server with remote DAC turned on it can add an extra port-only result.

How the two approaches differ

Aspect The script in this article Find-DbaInstance
What it looks for Windows services whose program is sqlservr.exe (Database Engine only) SQL Server instances, using several signals combined
Where service data comes from Win32_Service, the generic Windows service list dbatools' Get-DbaService, which queries SQL Server's own WMI provider (the SqlService class under root\Microsoft\SqlServer\ComputerManagementNN)
Connection method CIM over DCOM/RPC by default, or WinRM if you choose CIM over WinRM first, then falls back to DCOM and WMI
PowerShell version 5.1, or 7.x on Windows 5.1 and 7. The service scan needs Windows, because it uses CIM/WMI
Needs a server list? Yes No. It can build one from AD, SPNs or IP ranges
Output One row per Database Engine service, with state, start mode, PID and path One row per instance, with confidence, availability, port and the evidence behind it
Unreachable servers Listed separately when the query raises an error Skipped. You only see them in verbose output
Network footprint One WMI query per server, plus an optional ping With the defaults: Browser (UDP 1434), TCP port probes, AD SPN lookups, DNS, ping and WMI. dbatools warns that this can show up in audit logs and security monitoring
Dependencies None beyond PowerShell itself The dbatools module

A few of these rows deserve a closer look.

Unreachable servers are the biggest practical difference. In dbatools 2.9.1 (released October 15, 2026), the service scan runs with errors suppressed, and a computer that can't be contacted only gets a verbose message. So if the service scan fails on a server, its instance can drop to Medium or Low confidence, or vanish from the output entirely if nothing else answers. That's a reasonable default for a tool built to explore a network. It's less useful when the question is "did we check every server on the list?" If that's your question, compare the output against the input list yourself, or run with -Verbose. This comes from reading the 2.9.1 source, not from the documentation, so check it again if you're on a different version.

The service evidence comes from a different place. My script reads the generic Windows service list. Find-DbaInstance reads SQL Server's own WMI provider, which is installed with SQL Server and knows things Windows doesn't, such as which instance a service belongs to and whether it's clustered. The flip side is a dependency. In 2.9.1, Get-DbaService first looks for SQL Server's ComputerManagementNN namespaces. If that lookup or the SqlService query fails, it writes a verbose message and returns no services. Win32_Service doesn't rely on that provider, so it would still list the engine service. I traced this through the source code but haven't reproduced it in a lab.

High confidence doesn't mean the engine is running. For a named instance, High means dbatools found SQL services tied to that instance name, and that can include Agent or Full-Text, not only the engine. Whether the engine is actually running shows up separately in Availability (Available, Unavailable or Unknown). So filter on both columns, not just Confidence.

Browser and port scans have blind spots of their own. A Browser scan only sees instances the SQL Server Browser service reports, so it won't see an instance that's hidden, or one where the Browser service is stopped. A port scan only sees ports you ask about, and anything listening on 1433 looks like a "possible instance," whether or not it's SQL Server.

Which one should you use?

Use Find-DbaInstance when you don't have a reliable server list yet: a new environment, an acquisition, or an audit of what's actually on the network. Its discovery phase and multiple signals are built for that. Talk to your security team before running wide scans.

Use the service-based approach when you already have a list and want a repeatable, quiet check that records failures, stores the results in a table, and doesn't need any extra modules. That's the job this series is doing.

If you already use dbatools and want a closer equivalent of this script, look at Get-DbaService -ComputerName $servers -Type Engine. It returns service-level rows like the script does, but from SQL Server's WMI provider. Its documentation says it needs local admin on the target computers.

One permissions note: the non-admin setup described earlier applies to this script, not to dbatools. The dbatools service scan reads SQL Server's WMI namespace (root\Microsoft\SqlServer\ComputerManagementNN), not root\cimv2. So an account that only has Remote Enable on root\cimv2 could run this script fine while the dbatools service scan fails with no error. And the Get-DbaService documentation says it needs local admin anyway.

Common misconceptions

  • "It's not in the table, so there's no SQL Server there." It might have failed the check. Look at the failed-servers list first. If you run under a least-privilege account, check that it sees the same services an admin account does.
  • "The service is stopped, so something's wrong." On a failover cluster instance node that doesn't own the role, that's expected.
  • "I can see the service, so I can connect to the instance." Reading the service list and logging in to SQL Server use different permissions, and often different accounts.
  • "A service name starting with MSSQL means Database Engine." Not always. Full-Text and Analysis Services use the same prefix.
  • "Find-DbaInstance High confidence means the instance is up." Check Availability for that.

The takeaway

Ask each server which services run sqlservr.exe, and keep "answered with nothing" separate from "didn't answer." That gives you a reliable, low-noise list of Database Engine services, along with a list of the servers that raised an error. Find-DbaInstance is the better tool when you don't know where to look yet. The service-based script is the better fit for checking the same list on a schedule and getting the same kind of answer each time.

References

  1. PowerShell 101: Working with WMI (Microsoft Learn)
  2. Get-CimInstance (Microsoft Learn)
  3. Securing a remote WMI connection (Microsoft Learn)
  4. Service security and access rights (Microsoft Learn)
  5. Find-DbaInstance (dbatools documentation)








Thursday, June 27, 2019

Find clustered index on non primary key columns

Find clustered index on non primary key columns
By default when a primary key constrained is created on a table/view,  SQL Server automatically creates a unique clustered in order to enforce it.  And since a table can only have one clustered index, all the subsequent or any previous indexes created before that are created as a non-clustered index.

That works best in most cases and is the recommended best practice.

And decision to have clustered index on what columns affects everything about everyday working of an application. And also as a general best practice every table should have a clustered index, but its not required and there are cases where its best not to.

Scenario:

So now imagine a scenario where a table has the PK but the clustered index is on non PK columns. I am going to assume that there must be well thought-out index strategy for that particular table at the design time.

But over time the usage patterns may evolve and/or through endless enhancements, bug fixes etc. now that index may not be optimal. Of course that could be true for any index but the consequences are more severe if that's the case for a clustered index.

Now you are supporting that database in production mode. Users report that the query performance has gotten extremely slow and you also notice that the index optimization job is taking much longer to complete.

As part of your research and troubleshooting this issue, one of things you decide to check is index strategy already in place and you check 1) Are there any missing indexes 2) are the indexes of correct type (unique, clustered, non-clustered etc.), fill factor etc. 3) whether the clustered index is created on right columns etc....

The query that I have below is to find out if clustered index is on non-pk columns. I have consciously decided to exclude tables that have either no clustered index, no primary key or there is clustered as well as non-clustered index created on primary key columns.


-- CREATE A TEST TABLE
IF OBJECT_ID('dbo.tbl_test_ci_on_non_pk', 'U') IS NOT NULL
   DROP TABLE tbl_test_ci_on_non_pk
GO

-- ADD A CLUSTERED INDEX ON A NON-PK COLUMN
CREATE TABLE [dbo].[tbl_test_ci_on_non_pk](
	[id] [int] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
	[name] [varchar](50) NULL)
GO
CREATE CLUSTERED INDEX [idx_ci_tbl_test_ci_on_non_pk_name] 
ON [dbo].[tbl_test_ci_on_non_pk]
([name] ASC)
GO


;WITH cte_indexes
     AS (SELECT db_name()                         db_name, 
                schema_name(o.schema_id)          schema_name, 
                object_name(i.object_id)          object_name, 
                o.type_desc                       object_type, 
                i.NAME                            index_name, 
                i.type_desc                       index_type, 
                i.is_primary_key, 
                o.object_id                       object_id, 

                pk_index_id   = (SELECT index_id FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                pk_index_name = (SELECT name FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                clustered_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ic.index_id = i.index_id) 
        FOR xml path ('')), 1, 1, '') ), ''),

               pk_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ii.is_primary_key = 1) 
        FOR xml path ('')), 1, 1, '') ), '')

         FROM   sys.objects o 
                INNER JOIN sys.indexes i ON o.object_id = i.object_id 
         WHERE  Objectproperty(o.object_id, 'ismsshipped') = 0) 

 
SELECT db_name, 
       schema_name, 
       object_type, 
       object_name,
       index_name  non_pk_clustered_index_name,
       pk_index_name,
       clustered_index_columns,
       pk_index_columns

FROM   cte_indexes 
WHERE  1 = 1 
       AND index_type = 'CLUSTERED' 
       AND pk_index_id ! = 1 
       AND clustered_index_columns != pk_index_columns
ORDER  BY object_name, 
          index_name 









Caveat:  I only considered the traditional index types (clustered, non-clustered, unique, non-unique etc.).

I have tested this on SQL Server versions 2008 R2 and above.

Wednesday, June 26, 2019

What about orphaned windows users?

SQL Server 2012+ only/: If you're on older versions, this won't work.

Almost every DBA and some developers know that a database user becomes orphaned when its SID doesn't match anything in sys.server_principals. No big deal if you're using contained databases with database-level authentication. But for traditional server logins, those users are locked out of the instance entirely, even though they still "exist" in the database with permissions.

This usually happens when:
  • You restore a database to a new server, but the instance-level LOGINS don't match
  • SIDs get out of sync between database users and server logins
  • Someone deletes a login from sys.server_principals (or Active Directory cleans house)

Microsoft's been dealing with this forever and gave us sp_change_users_login to find/fix it:

How To Troubleshoot Orphaned Users in SQL Server

But sp_change_users_login only works for SQL Server logins, it completely ignores Windows users.

Windows users get even trickier because they often authenticate through Windows group membership. A simple SID comparison between sys.database_principals and sys.server_principals won't catch those. That's where the extended stored procedure xp_logininfo saves the day, it validates actual Windows account existence.

That's why I wrote the T-SQL script below. It finds orphaned Windows users and optionally fixes them by leveraging xp_logininfo to bridge the gap that traditional methods miss.

-- See blog post: https://sqlpal.blogspot.com/2019/06/what-about-orphaned-windows-users.html
/*==========================================================================================
  Script Name : Find-Fix-Orphaned-Windows-Users.sql

  Purpose:
      Identifies and optionally recreates orphaned Windows users in the current database.
      Unlike sp_change_users_login (SQL logins only), this handles Windows users/groups
      using xp_logininfo to validate Windows account existence.

  How it works:
      1. Finds database users (WINDOWS_USER type) with no matching server login SID
      2. Uses xp_logininfo @option='all' to verify if Windows account still exists
      3. Reports orphaned users OR auto-creates missing server logins

  Configuration:
      @fix_orphaned_user (BIT) 
          0 = Report-only mode (default, safe)
          1 = Auto-fix by creating missing server logins

  Prerequisites:
      - Run in target database (orphaned users are DB-scoped)
      - sysadmin or equivalent to create logins (when fixing)
      - Windows Authentication environment

  Safety Notes:
      - xp_logininfo queries Active Directory/local SAM - network dependent
      - Only creates logins, doesn't modify existing database users/permissions
      - Test in non-prod first when @fix_orphaned_user=1
      - Won't fix OS-level account issues (disabled/locked/deleted accounts)

  Typical Scenarios:
      - Database restore to new server (login SIDs don't match)
      - AD cleanup removed accounts referenced by databases
      - Failover cluster with domain trust issues
==========================================================================================*/

DECLARE @username NVARCHAR(500),
        @privilege NVARCHAR(500), 
        @sql NVARCHAR(4000),
        @fix_orphaned_user BIT,
        @cnt INT = 0;

SET @fix_orphaned_user = 0;  -- 0 = Report only (SAFE), 1 = Auto-fix logins

DECLARE c1 CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
    SELECT dp.NAME 
    FROM sys.database_principals dp 
    LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid 
    WHERE dp.type_desc = 'WINDOWS_USER' 
      AND dp.authentication_type_desc = 'WINDOWS'
      AND dp.principal_id != 1  -- Exclude dbo
      AND sp.sid IS NULL;       -- No matching server login

OPEN c1;
FETCH c1 INTO @username;

WHILE @@FETCH_STATUS = 0 
BEGIN
    -- Count potential orphans before validation
    SET @cnt = @cnt + 1;

    /*
        xp_logininfo validates Windows account existence in AD/local SAM.
        @option='all' returns privilege level (user/group details).
        NULL result = account doesn't exist = TRUE orphan.
    */
    EXEC xp_logininfo 
        @acctname = @username, 
        @option = 'all', 
        @privilege = @privilege OUTPUT;

    -- Orphan confirmed (no Windows account found)
    IF @privilege IS NULL 
    BEGIN
        RAISERROR('Orphaned Windows user: %s', 10, 1, @username) WITH NOWAIT;
        
        -- AUTO-FIX: Create missing server login
        IF @fix_orphaned_user = 1 
        BEGIN 
            SET @sql = 'CREATE LOGIN [' + @username + 
                       '] FROM WINDOWS WITH DEFAULT_DATABASE = [' + DB_NAME() + ']';
            
            PRINT 'Creating login: ' + @sql;
            EXEC(@sql);
        END
    END;

    FETCH c1 INTO @username;
END;

CLOSE c1;
DEALLOCATE c1;

-- Final status message
IF @cnt = 0 
    RAISERROR('No potential orphaned Windows users found.', 10, 1) WITH NOWAIT;
ELSE IF @cnt > 0 AND @fix_orphaned_user = 0
    RAISERROR('%d potential orphaned Windows users (run with @fix_orphaned_user=1 to auto-fix).', 
              10, 1, @cnt) WITH NOWAIT;

Report only:

Report and fix:




Download Script: Find-Orphan-Windows-Users-In-SQLServer

Caveat: The script won't fix OS-level account issues (deleted/disabled/locked Windows users).

Linux: Untested but should work, let me know!