dbatools: Simplifying SQL Server Configuration with PowerShell

SQL Server configuration used to mean a lot of clicking around in SSMS, memorizing registry tweaks, or juggling half a dozen scripts. These days, though, there's a cleaner, faster way (one that scales better and makes mistakes a lot less likely).

Enter dbatools: a free, open-source PowerShell module designed to help DBAs automate and standardize nearly every aspect of managing SQL Server.

It’s community-built, which means new features and bug fixes are constantly being added and if there's a command you think should exist, you can suggest it or even help build it yourself. You’re not stuck waiting for the next official release or unlock something basic.

In this post, I’ll walk through how to get started with dbatools, including some of the configuration commands I use regularly in real environments.

What is dbatools?

At its core, dbatools is a massive PowerShell module that gives DBAs the tools to automate SQL Server tasks (backups, migrations, log shipping, AGs, security, and yes, even all those tiny configuration steps you tend to forget during a fresh install)

It's completely free, regularly updated, and backed by a strong community of SQL Server professionals. The documentation is solid, the examples are realistic, and there’s a Slack workspace where you can ask questions or get involved.

Installing dbatools

You'll need PowerShell v3+ or PowerShell Core v7.3+, and administrator rights for the install:

Install-Module dbatools

Update it later with:

Update-Module dbatools

To browse what it can do:

Find-DbaCommand -Tag Configuration

Need help with something specific?

Get-Help Set-DbaSpConfigure -Detailed

Commands I Actually Use for Configuration

Here’s a list of dbatools commands I personally rely on when prepping a new server or standardizing existing ones.

These are just the basics, enough to cover a lot of common configuration scenarios. But they’re also flexible. You can easily customize or chain them together to make something more powerful and tailored to your setup. Add error handling, parameter splatting, log results to a table (whatever fits your process).

Set-DbaPrivilege

Configures key Windows rights like Lock Pages in Memory (LPIM) and Instant File Initialization (IFI), so SQL Server performs better from day one.

Set-DbaPrivilege -ComputerName localhost -Type LPIM,IFI

Memory Settings

These two work together: one tests the best max memory setting based on your system, the other applies it.

Test-DbaMaxMemory -SqlInstance localhost | Set-DbaMaxMemory

MaxDOP Settings

Helps assess and configure parallelism settings based on the server's cores and workload.

Test-DbaMaxDop -SqlInstance localhost | Set-DbaMaxDop

Cost Threshold for Parallelism

Most setups work better with a higher threshold than the default of 5.

Set-DbaSpConfigure -SqlInstance localhost -Name CostThresholdForParallelism -Value 50

Disable Risky or Unused Features

Turning off what you don’t need is just as important.

Get-DbaSpConfigure -SqlInstance localhost -Name XPCmdShellEnabled,IsSqlClrEnabled | Set-DbaSpConfigure -Value $false

Configure TempDB

The Test command helps you decide how many data files to use, and Set applies it.

Test-DbaTempDbConfig -SqlInstance localhost | Out-GridView Set-DbaTempDbConfig -SqlInstance localhost -DataFileCount 4 -DataFileSize 512

Network Protocols

Some environments still enable Named Pipes by default—these fix that.

Set-DbaNetworkConfiguration -SqlInstance localhost -EnableProtocol TcpIp Set-DbaNetworkConfiguration -SqlInstance localhost -DisableProtocol NamedPipes

Network Encryption

Enables forced encryption without diving into the registry or Group Policy.

Enable-DbaForceNetworkEncryption

Default Backup Compression

If you always want backup compression on, this is the way to set it once and forget it.

Set-DbaSpConfigure -SqlInstance localhost -Name DefaultBackupCompression -Value 1

Default Trace

Still useful for troubleshooting and auditing changes.

Set-DbaSpConfigure -SqlInstance localhost -Name DefaultTraceEnabled -Value $true

Ola Hallengren’s Maintenance Solution

Installs the gold standard of SQL Server maintenance scripts.

Install-DbaMaintenanceSolution -SqlInstance localhost -Database DBA -InstallJobs -CleanupTime 120 -ReplaceExisting

Enable Database Mail

Turns on Database Mail so you can receive alerts.

Set-DbaSpConfigure -SqlInstance localhost -Name DatabaseMailEnabled -Value 1

Enabling HADR

Preps your SQL instances to support Always On Availability Groups.

Enable-DbaAgHadr -SqlInstance $SqlInstances -Force

Configure Endpoints for AG

Set up encrypted communication endpoints and permissions for HADR.

New-DbaEndpoint -SqlInstance $SqlInstances -Name hadr_endpoint -Port 5022 -EncryptionAlgorithm Aes -EndpointEncryption Required | Start-DbaEndpoint New-DbaLogin -SqlInstance $SqlInstances -Login $SQLServerServiceAccount Add-DbaServerRoleMember -SqlInstance $SqlInstances -ServerRole sysadmin -Login $SQLServerServiceAccount Invoke-DbaQuery -SqlInstance $SqlInstances -Query "GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [$SQLServerServiceAccount]"

Build an Availability Group

Sets up a full AG with synchronous commit, automatic failover, and listener creation.

$AvailabilityGroup = New-DbaAvailabilityGroup -Name $AGName -ClusterType Wsfc -Primary $SqlInstances[0] -Secondary $SqlInstances[1] -SeedingMode Automatic -AutomatedBackupPreference Secondary -AvailabilityMode SynchronousCommit -FailoverMode Automatic -ConnectionModeInSecondaryRole AllowReadIntentConnectionsOnly -Confirm:$false -Verbose 

Add-DbaAgListener -SqlInstance $SqlInstances[0] -AvailabilityGroup $AGName -Name $AGListenerName -IPAddress $AGListenerIPs -SubnetMask $AGListenerSubnet

Then fine-tune replica settings:

Get-DbaAgReplica -SqlInstance $SqlInstances[0] | Set-DbaAgReplica -AvailabilityMode SynchronousCommit -FailoverMode Automatic -ConnectionModeInPrimaryRole AllowAllConnections -ConnectionModeInSecondaryRole AllowReadIntentConnectionsOnly -SessionTimeout 30

Final Notes

The commands above are just a starting point. You can tweak the settings, add logging, build full deployment scripts, or integrate these into broader CI/CD pipelines. That’s part of what makes dbatools so useful, its commands are simple enough to use right away, but flexible enough to grow with your needs.

And again, it’s free. If there’s a feature you wish it had, odds are someone else has too or you could be the one to build it.