Getting Started #

This page walks through a process to build a good defaults.hcl file.

Baseline #

The bare minimum change I make to every SQL Server are for security and fixing up the model database.

The sa login should be disabled by default
xp_cmdshell is disabled by default and should stay that way. It provides a mechanism to get from SQL Server out to the operating system.
The model database should be in SIMPLE recovery. For those databases that needs FULL recovery, we can set that later.

# disable the "sa" login
resource "login" "sa" {
	name = "sa"
	disabled = true
	ensure = "alter"
}

# disable xp_cmdshell
resource "configuration" "xp_cmdshell" {
	name = "xp_cmdshell"
	value = 0
	ensure = "alter"
}

# set the model database to simple recovery 
resource "database" "model" {
	name = "model"
	owner = "sa"
	recovery_model = "simple"
	ensure = "alter"
}

Basic Configuration #

# 50 is a reasonable default these days
resource "configuration" "cost_threshold" {
	name = "cost threshold for parallelism"
	value = 50
	ensure = "alter"
}

resource "configuration" "backup_compression" {
	name = "backup compression default"
	value = 1
	ensure = "alter"
}

# -1 sets a value based on each server's core count
# Please see the documentation
resource "configuration" "maxdop" {
	name = "max degree of parallelism"
	value = -1
	ensure = "alter"
}

# -1 sets a value based on each server's memory
# Please see the documentation
resource "configuration" "max_memory" {
	name = "max server memory (MB)"
	value = -1
	ensure = "alter"
}