In an interview, how do you contrast the current Microsoft story for SQL automation in PowerShell with legacy options?
The SqlServer module from the PowerShell Gallery (Install-Module SqlServer) is the default path for new work. Older installs used SQLPS / SQL Server PowerShell snap-ins — treat those as legacy unless the org still mandates them. Keep the module updated relative to your SQL Server major version.
What does Invoke-Sqlcmd do, and what are typical parameters you’d name?
It runs T-SQL or .sql files with a sqlcmd-style surface. Common switches: -Query, -InputFile, -ServerInstance, -Database, -Credential (or integrated auth). It’s the familiar entry point for scripted queries from PowerShell.
What is SMO, and when is raw SMO vs Invoke-Sqlcmd the right mental model?
SMO (SQL Server Management Objects) is the .NET library tools use for programmatic DDL/DML, scripting objects, and migrations. Invoke-Sqlcmd is lighter for “run this batch”; SMO is heavier but flexible. The SqlServer module cmdlets often wrap SMO; advanced scripts may use [Microsoft.SqlServer.Management.Smo.Server] directly.
Name example SqlServer module cmdlets for backup/restore, AGs, and discovery — and what caveat applies?
Examples: Backup-SqlDatabase, Restore-SqlDatabase; Join-SqlAvailabilityGroup, Switch-SqlAvailabilityGroup (failover), Resume-SqlAvailabilityDatabase / Suspend-SqlAvailabilityDatabase; Get-SqlInstance, Get-SqlDatabase, Get-SqlErrorLog. Names and parameters vary by module/SQL version — verify Learn docs for your version.
Where do WSFC / cluster operations live in PowerShell relative to the SqlServer module?
Not in SqlServer. Use the FailoverClusters module: Get-Cluster, Get-ClusterNode, Add-ClusterNode, etc. That’s the Windows Server Failover Clustering surface that sits next to SQL AG/FCI work.
Give four reasons teams use PowerShell with SQL Server (not just “scripting”).
Repeatable automation across instances; remote and scheduled execution (Task Scheduler, Azure Automation, CI/CD); glue between OS tasks (disks, services, WSFC) and SQL tasks; object pipeline output to CSV, JSON, logging, and monitoring hooks.
Map one example each for runbooks, provisioning, migrations, and monitoring from a DBA/DevOps angle.
Runbooks: patch drain, failover, AG health, post-deploy backup checks. Provisioning: databases, logins, jobs from templates; restore to lower envs. Migrations: drive DACPAC/BACPAC or sqlpackage; schema drift (often SMO or third-party). Monitoring: DMVs, Invoke-Sqlcmd with JSON, feed observability.
What is dbatools, and how do you position it vs SqlServer in an interview?
dbatools is a community PowerShell module for SQL (migration, backup, AG, sp_configure, etc.) built on SMO. Interview line: teams standardize on SqlServer from Microsoft or dbatools for fleet work — both sit above SMO; dbatools is opinionated and feature-rich for DBAs.
How should you handle authentication and secrets in PowerShell scripts for SQL?
Prefer Windows integrated auth with a service account and least privilege for runbooks. For SQL authentication, use PSCredential, SecretManagement, or vault patterns — never plaintext passwords in scripts in git. Azure SQL / Entra often use Invoke-Sqlcmd with token/AAD options — confirm current docs for your module version.
When is T-SQL the better tool vs PowerShell vs SSMS?
T-SQL — set-based work, DMVs, one-off diagnostics inside the engine. PowerShell — multi-instance loops, OS + SQL glue, CI/CD, scheduled runbooks. SSMS — interactive triage and GUI wizards — not the automation layer.
Finish this sentence for a panel: “PowerShell doesn’t replace T-SQL — …”
“…it orchestrates it and ties SQL to Windows and pipelines.”
Give a compact “stack” answer: SqlServer, SMO, FailoverClusters, and optional dbatools.
“I use the SqlServer module for Invoke-Sqlcmd, backup/restore, and AG cmdlets where they save time; WSFC is FailoverClusters. Under the hood that’s often SMO. For heavy fleet work, teams add dbatools on top of SMO.”
What policy/compliance-style tasks does PowerShell excel at across a fleet?
Enumerating server settings, permissions, and backup history across many instances — repeatable reporting for audits and standards checks.
Why pair PowerShell with sqlpackage / DACPAC workflows in migrations?
PowerShell orchestrates packaging and deployment (drive sqlpackage, DACPAC/BACPAC), integrates with pipelines, and can combine deployment with pre/post checks — while the schema work is still expressed through those artifacts and/or SMO tooling.
