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 .LINKHome 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 😄.