SqlServer PowerShell module (CLI)

Status: Reviewed Stack: PowerShell → SQL / stored procs → Blazor Depends on: SQL-CLI-01 helpful (when to prefer sqlcmd vs Invoke-Sqlcmd); SQL-CLI-02 for bulk file loads Goal: Install and use the SqlServer module from the command line for queries, backups, and ops that feed PS → SQL → Blazor — and know when Invoke-Sqlcmd wins over sqlcmd/bcp.

1. When to use this module vs sqlcmd / bcp / SMO

Rick’s loop: AD/Windows/PowerShell → SQL stored procs → Blazor Server. Pick the client by object shape, volume, and whether you need an exit code.

Rule of thumb

• Objects for Blazor / JSON / in-memory shaping → Invoke-Sqlcmd (this doc).
• Ops batch, Agent, exit codes, :setvar files → SQL-CLI-01.
• Bulk files → SQL-CLI-02; use Write-SqlTableData only for modest PS-held tables.
• App already holding IDataReader/DataTable → SqlBulkCopy in the Blazor worker, not PowerShell.

2. Install

Use SqlServer from the PowerShell Gallery — not legacy SQLPS. SSMS (17+) does not ship the module; Agent still auto-loads SQLPS unless you opt out.

# Current user (no elevation) — fine for jump-box / laptop
Install-Module -Name SqlServer -Scope CurrentUser -AllowClobber

# All users (elevated) — needed for SQL Agent jobs under service account
Install-Module -Name SqlServer -Scope AllUsers -AllowClobber

Get-Module SqlServer -ListAvailable | Select-Object Name, Version, Path
Import-Module SqlServer   # pin in scripts; don't rely on auto-load with SQLPS present

# Sanity
Get-Command Invoke-Sqlcmd | Format-List Source, Version
# Source must be SqlServer, not SQLPS

3. Auth & connection

# Windows Integrated (lab often needs TrustServerCertificate under v22+ driver defaults)
Invoke-Sqlcmd -ServerInstance 'SQL01' -Database master `
  -Query 'SELECT @@VERSION AS v' -TrustServerCertificate

# SQL auth via PSCredential (never -Password on cmdline in shared sessions)
$cred = Get-Credential -UserName 'deploy_login' -Message 'SQL auth'
Invoke-Sqlcmd -ServerInstance 'SQL01' -Database AppDb -Credential $cred `
  -Query 'SELECT SUSER_SNAME() AS me' -TrustServerCertificate

# ConnectionString
Invoke-Sqlcmd -ConnectionString 'Data Source=SQL01;Initial Catalog=AppDb;Integrated Security=True;Encrypt=Mandatory;TrustServerCertificate=True' `
  -Query 'SELECT DB_NAME() AS db'

# Azure SQL + Entra user token (Az.Accounts)
Import-Module Az.Accounts
Connect-AzAccount
$token = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net').Token  # string or SecureString — both OK on modern SqlServer
Invoke-Sqlcmd -ServerInstance 'myserver.database.windows.net' -Database mydb `
  -AccessToken $token -Query 'SELECT SYSTEM_USER AS me'

Azure / Entra notes

• Service principal / managed identity: obtain token the same way (client credentials or Connect-AzAccount -Identity), then -AccessToken.
• Newer Get-AzAccessToken may return SecureString for .Token; Invoke-Sqlcmd accepts string or SecureString.
• Prefer FQDN for Azure SQL; encryption is expected.

4. Core cmdlets Rick will actually use

Skip the AS/cube/Always Encrypted marketing surface. These are the CLI workhorses (all still in Gallery 22.x):

Import-Module SqlServer

Get-SqlInstance -ServerInstance 'SQL01' -TrustServerCertificate |
  Select-Object InstanceName, VersionString, Edition

Get-SqlDatabase -ServerInstance 'SQL01' -TrustServerCertificate |
  Where-Object Status -eq 'Normal' |
  Select-Object Name, Size, RecoveryModel

Backup-SqlDatabase -ServerInstance 'SQL01' -Database 'AppDb' `
  -BackupFile '\\backup\SQL01\AppDb_$(Get-Date -Format yyyyMMdd_HHmm).bak' `
  -TrustServerCertificate

# Read / write table data (still current cmdlets)
Read-SqlTableData -ServerInstance 'SQL01' -DatabaseName AppDb -SchemaName dbo `
  -TableName Customers -TopN 100 -TrustServerCertificate

$rows = @(
  [pscustomobject]@{ Sku = 'A1'; Qty = 3 }
  [pscustomobject]@{ Sku = 'B2'; Qty = 1 }
)
Write-SqlTableData -ServerInstance 'SQL01' -DatabaseName AppDb -SchemaName dbo `
  -TableName StagingSku -InputData $rows -TrustServerCertificate

Backup-SqlDatabase defaults to full backup; set -BackupAction Database|Log|Files as needed. Prefer UNC/share paths the SQL service account can write.

5. Invoke-Sqlcmd deep patterns

-Query vs -InputFile

# Inline
Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb -Query 'EXEC dbo.usp_RefreshInventory' -TrustServerCertificate

# File (UTF-8). Supports GO batches + many sqlcmd scripting bits — NOT full sqlcmd.exe
Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb -InputFile 'C:\deploy\AppDb_procs.sql' `
  -TrustServerCertificate -AbortOnError

Not supported (unlike sqlcmd.exe — see SQL-CLI-01): :!!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, :serverlist. Do not expect :r include chains to behave like classic sqlcmd for complex deploy packs — prefer pre-merged scripts or sqlcmd/go-sqlcmd for those.

-Variable

# Array form (v21+; values trimmed — don't rely on leading/trailing spaces)
$vars = "DbName='AppDb'", "Env='PROD'"
Invoke-Sqlcmd -Query "SELECT `$(DbName) AS db, `$(Env) AS env" -Variable $vars

# Hashtable form — v22+ only (preferred)
Invoke-Sqlcmd -Query "SELECT `$(DbName) AS db" -Variable @{ DbName = 'AppDb' }

Escape $ in double-quoted PS strings with ` so PowerShell doesn’t expand them before sqlcmd variables.

-OutputAs / -As

$rows = Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb `
  -Query 'EXEC dbo.usp_ListOpenOrders @Top = 50' -OutputAs DataRows -TrustServerCertificate

$tables = Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb `
  -Query 'SELECT 1 AS a; SELECT GETDATE() AS t' -As DataTables -TrustServerCertificate

Timeouts, length, errors

try {
  $rows = Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb `
    -Query 'EXEC dbo.usp_BuildBlazorSnapshot' `
    -QueryTimeout 180 -MaxCharLength 1000000 `
    -OutputAs DataRows -OutputSqlErrors $true -AbortOnError `
    -TrustServerCertificate -ErrorAction Stop
} catch {
  Write-Error "SQL failed: $($_.Exception.Message)"
  throw
}

Feed Blazor (JSON)

$rows = Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb `
  -Query 'EXEC dbo.usp_GetDashboardFeed' -OutputAs DataRows `
  -MaxCharLength 1000000 -TrustServerCertificate

# DataRow → note: ConvertTo-Json on raw DataRow can be noisy; project first
$payload = $rows | Select-Object * | ConvertTo-Json -Depth 5 -Compress
[System.IO.File]::WriteAllText('C:\share\blazor\dashboard.json', $payload)

For Blazor Server, prefer stored procs that return the exact columns the UI needs — keep PowerShell as orchestration, not business logic.

6. Scripting recipes (PS → SQL → Blazor)

1) Quick query → JSON file for Blazor

Import-Module SqlServer
$out = 'C:\data\blazor\open-orders.json'
$rows = Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb `
  -Query 'EXEC dbo.usp_OpenOrders' -OutputAs DataRows `
  -MaxCharLength 500000 -TrustServerCertificate
($rows | Select-Object *) | ConvertTo-Json -Depth 4 |
  Set-Content -Path $out -Encoding utf8

2) Run a .sql deploy pack

Import-Module SqlServer
Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb `
  -InputFile 'C:\deploy\2026-09-12_procs.sql' `
  -AbortOnError -OutputSqlErrors $true -TrustServerCertificate -ErrorAction Stop
# Complex :r trees → use sqlcmd/go-sqlcmd (SQL-CLI-01) instead

3) Backup one database

Import-Module SqlServer
$db = 'AppDb'
$file = "\\backup\SQL01\${db}_$(Get-Date -Format 'yyyyMMdd_HHmmss').bak"
Backup-SqlDatabase -ServerInstance SQL01 -Database $db -BackupFile $file `
  -CompressionOption On -TrustServerCertificate
Write-Host "Backed up to $file"

4) Loop servers from a list → collect health JSON

Import-Module SqlServer
$servers = Get-Content 'C:\config\sql-servers.txt'   # one host\instance per line
$report = foreach ($s in $servers) {
  try {
    Invoke-Sqlcmd -ServerInstance $s -Database master -TrustServerCertificate -ErrorAction Stop `
      -Query @"
SELECT @@SERVERNAME AS ServerName, 
       (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process=1) AS UserSessions,
       GETDATE() AS CapturedUtc;
"@
  } catch {
    [pscustomobject]@{ ServerName = $s; UserSessions = $null; CapturedUtc = Get-Date; Error = $_.Exception.Message }
  }
}
$report | Select-Object * | ConvertTo-Json -Depth 3 |
  Set-Content C:\data\blazor\sql-health.json -Encoding utf8

5) Entra access token → Azure SQL

Import-Module SqlServer, Az.Accounts
Connect-AzAccount   # or -Identity / SP login in automation
$token = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net').Token
Invoke-Sqlcmd -ServerInstance 'contoso.database.windows.net' -Database AppDb `
  -AccessToken $token -Query 'EXEC dbo.usp_RefreshCache' -QueryTimeout 120

6) Write-SqlTableData vs bcp — when each wins

# WIN: Write-SqlTableData — small/medium objects already in PS (config, AD sample, API pull)
Import-Module SqlServer
$users = Get-ADUser -Filter * -SearchBase 'OU=App,DC=contoso,DC=com' |
  Select-Object @{n='Sam';e={$_.SamAccountName}}, @{n='Enabled';e={$_.Enabled}}
Write-SqlTableData -ServerInstance SQL01 -DatabaseName AppDb -SchemaName dbo `
  -TableName StagingAdUsers -InputData $users -Force -TrustServerCertificate
Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb `
  -Query 'EXEC dbo.usp_MergeAdUsers' -TrustServerCertificate

# WIN: bcp — multi-million-row CSV / native file (see SQL-CLI-02)
# bcp AppDb.dbo.StagingInventory in C:\drop\inv.csv -c -t, -S SQL01 -T -b 10000
# Then: Invoke-Sqlcmd ... 'EXEC dbo.usp_LoadInventory'

7) Restore (smoke / lower env)

Import-Module SqlServer
Restore-SqlDatabase -ServerInstance SQLDEV -Database AppDb_Copy `
  -BackupFile '\\backup\SQL01\AppDb_20260912.bak' `
  -ReplaceDatabase -TrustServerCertificate

8) Parameterized proc via sqlcmd variables + JSON out

Import-Module SqlServer
$top = 25
$rows = Invoke-Sqlcmd -ServerInstance SQL01 -Database AppDb -TrustServerCertificate `
  -Variable @{ TopN = "$top" } `
  -Query 'EXEC dbo.usp_TopCustomers @Take = $(TopN)' `
  -OutputAs DataRows
$rows | Select-Object * | ConvertTo-Json -Depth 3

7. Gotchas

Cross-links

• SQL-CLI-01 — sqlcmd & go-sqlcmd — exit codes, :r / :setvar, when text CLI beats objects.
• SQL-CLI-02 — bcp — bulk file in/out; use after staging, before EXEC dbo.usp_*.

References (Learn / Gallery, 2026)

• SqlServer module
• Install SqlServer module
• Invoke-Sqlcmd
• Backup-SqlDatabase / Restore-SqlDatabase
• Read-SqlTableData / Write-SqlTableData
• PowerShell Gallery: SqlServer