There are some excellent Excel based ConfigMgr sizing tools out there. I recently had a design request for estimating SQL sizing via PowerShell. The function I created Returns disk capacity and file estimates for:
• SQL DB’s
• SQL Log’s
• SQL Temp
The Get-CMDBSizingEstimate function is pretty simple to use. You can pass the number of clients you’ll be managing and the number of cores (if above 8 cores). It’ll calculate the estimated capacity for the SQL DB, Log, Temp disks and the SQL MDF/LDF sizing.
PowerShell Function
Function Get-CMDBSizingEstimate
{
<#
.SYNOPSIS
Works out an estimate for Configuration Manager SQL disk sizing and database sizes based on the number of clients specified.
.DESCRIPTION
Returns disk capacity esitmates for SQL DB, SQL Log and SQL Temp.
Returns SQL Database, Log and Temp file estimates
.PARAMETER NumberofClients
Specifies the number of clients you expect in the Configuration Manager environment.
.PARAMETER DBSizePerClientMB
Specifies the database size per client in MB. "5" is the default.
.PARAMETER TotalCores
Specifies the total cores the Configuration Manager server will have. "8" is the default.
.PARAMETER DBMDFAutoGrowthPercent
Specifies the estimate database auto growth percent the Configuration Manager server. "20" (percent) is the default.
.PARAMETER DBLDFAutoGrowthPercent
Specifies the estimate database log auto growth percent the Configuration Manager server. "20" (percent) is the default.
.PARAMETER MinSizeGb
Specifies the default minimum database size. "5" is the default.
.INPUTS
None. You cannot pipe objects to Get-CMDBSizingEstimate.
.OUTPUTS
Get-CMDBSizingEstimate returns a hashtable for the capacity and CMDatabase. It also returns int for the DBSizePerClientMB, NumberofClients and TotalofCores.
.EXAMPLE
C:\PS: Get-CMDBSizingEstimate -NumberofClients 350
.EXAMPLE
C:\PS: Get-CMDBSizingEstimate -NumberofClients 350 -DBSizePerClientMB 5
.EXAMPLE
C:\PS: Get-CMDBSizingEstimate -NumberofClients 350 -DBSizePerClientMB 5 -TotalCores 8
.LINK
Home Page
#>
Param
(
[parameter(Mandatory=$true)]
[int]$NumberofClients,
[parameter(Mandatory=$false)]
[int]$DBSizePerClientMB = 5,
[parameter(Mandatory=$false)]
[alias("TotalThreads")]
[Int]$TotalCores = 8,
[parameter(Mandatory=$false)]
[int]$DBMDFAutoGrowthPercent = 20,
[parameter(Mandatory=$false)]
[int]$DBLDFAutoGrowthPercent = 20,
[parameter(Mandatory=$false)]
[int]$MinSizeGb = 5
)
[int]$SiteDBmdfNumberofFiles = $TotalCores
[int]$SiteDBldfNumberofFiles = 1
[int]$TempDBmdfNumberofFiles = 1
[int]$TempDBldfNumberofFiles = 1
IF(($NumberofClients*$DBSizePerClientMB)/1024 -le $MinSizeGb )
{
[decimal]$DBMDFSize = "{0:N2}" -f ((($NumberofClients*$DBSizePerClientMB)/1024) + $MinSizeGb)
}
Else
{
[decimal]$DBMDFSize = "{0:N2}" -f (($NumberofClients*$DBSizePerClientMB)/1024)
}
$DBMDFSizePerFileGB = [Math]::Round(($DBMDFSize/$SiteDBmdfNumberofFiles),2)
[int]$DBMDFAutoGrowth= "{0:N2}" -f ((($DBMDFSize/$SiteDBmdfNumberofFiles)*"0.$DBMDFAutoGrowthPercent")*1024)
$DBMDFDiskSize= [Math]::Round(($DBMDFSize * 1.2))
#LDF Database
[decimal]$SiteDBldfSizeGB = [Math]::Round(($DBMDFSize/3), 2)
$SiteDBldfSizePerFileGB = [Math]::Round(($SiteDBldfSizeGB /$SiteDBldfNumberofFiles ), 2)
[int]$SiteDBldfAutoGrowth = ([Math]::Round(((((($DBMDFSize/3) /$SiteDBldfNumberofFiles )*"0.$DBldfAutoGrowthPercent")*1024) ), 2))
$SiteDBldfDiskSize = [Math]::Round(($SiteDBldfSizeGB * 1.2))
#End Region Region Site Database
#Region TEMP DB
#Temp DB MDF
[decimal]$TempDBmdfSizeGB = $SiteDBldfSizeGB
[decimal]$TempDBmdfSizePerFileGB = $TempDBmdfSizeGB / $TempDBmdfNumberofFiles
[int]$TempDBmdfAutoGrowth = ([Math]::Round((((($TempDBmdfSizePerFileGB)*"0.$DBmdfAutoGrowthPercent")*1024) ), 2))
[int]$TempDBmdfDiskSize = $TempDBmdfSizeGB * 1.2
#Temp DB LDF
$TempDBldfSizeGB = $TempDBmdfSizeGB /3
[decimal]$TempDBldfSizePerFileGB = $TempDBldfSizeGB / $TempDBldfNumberofFiles
[int]$TempDBldfAutoGrowth = ([Math]::Round((((($TempDBldfSizePerFileGB)*"0.$DBldfAutoGrowthPercent")*1024) ), 2))
[int]$TempDBldfDiskSize = $TempDBldfSizeGB * 1.2
#End Region
$result = @()
$Object = New-Object -TypeName PSObject -Property @{
CMDatabase = @{
CMLogSizeMB = [Math]::Round(($SiteDBldfSizeGB * 1gb) /1mb, 0)#default uses 100 client sizes for testing on scaling script.
CMLogMaxSizeMB = [int](([Math]::Round(($DBMDFDiskSize * 1gb) /1mb, 0))/2*0.7)
CMLogGrowthMB = $SiteDBldfAutoGrowth
CMDBSizeMB = [Math]::Round(($DBMDFSizePerFileGB * 1gb) /1mb, 0)
CMDBMaxSize = 'Unlimited'
CMDBGrowthMB = $DBMDFAutoGrowth
NumberofCMDBFiles = $TotalCores
}
Capacity = @{
SQLDBSizeMB = [Math]::Round(($DBMDFDiskSize * 1gb) /1mb, 0)
SQLLogSizeMB = ([Math]::Round(($SiteDBldfDiskSize * 1gb) /1mb, 0))
SQLTempSizeMB = ([Math]::Round(($TempDBmdfDiskSize * 1gb) /1mb, 0)) + ([Math]::Round(($TempDBldfDiskSize * 1gb) /1mb, 0))
}
NumberofClients = $NumberofClients
DBSizePerClientMB = $DBSizePerClientMB
TotalofCores = $TotalCores
}
$result +=$Object
return $result
}
P.S. Thanks for all the feedback and help on this from the community. Every environments different so good to have second opinions 😄.