SQL Server Developer edition is free and feature-complete with the capabilities supported by Enterprise edition. Its decisive restriction is licensing: it is intended for development and testing, not production environments. section 8.5 section 8.8
Use Developer edition when:
- A development machine needs Enterprise-level features without production deployment. section 8.5
- Installation standards must select Developer for development and retain Standard or Enterprise as production options. section 8.8
- Do not treat database size alone as proof that Enterprise is necessary; capacity planning may show that Standard is sufficient. section 8.5
The 2 TB database mistake
MagicChoc chose Enterprise for a 2 TB Marketing database because it needed an availability group with a synchronous replica and SSIS. That choice was premature: Standard supports databases up to 524 PB, so processor and memory requirements should be validated through capacity planning before paying for Enterprise. Developer would be appropriate for development and testing, but not for the production system. section 8.5
Listing 8.6 Creating a scripted installation of SQL Server
[CmdletBinding()]
param(
[string] $SQLServiceAccount,
[string] $SQLServiceAccountPassword,
[string] $AgentServiceAccount,
[string] $AgentServiceAccountPassword,
[string] $SaName,
[string] $SaPassword,
[string] $edition,
[string] $InstanceName = 'MSSQLSERVER'
)
if ($edition -eq 'Developer') {
$sqlPid = '22222-00000-00000-00000-00000'
} elseif ($edition -eq 'Standard') {
$sqlPid = '00000-00000-00000-00000-00000'
} elseif ($edition -eq 'Enterprise') {
$sqlPid = '00000-00000-00000-00000-00000'
}
If(-not(Get-Module NuGet -ErrorAction silentlycontinue)){
Install-Module NuGet -Confirm:$False -Force
} #1
If(-not(Get-Module SQLServer -ErrorAction silentlycontinue)){
Install-Module SQLServer -Confirm:$False -Force -AllowClobber
} #2
./setup.exe /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=$InstanceName /SQLSVCACCOUNT=$SQLServiceAccount /SQLSVCPASSWORD=$SQLServiceAccountPassword /AGTSVCACCOUNT=$AgentServiceAccount /AGTSVCPASSWORD=$AgentServiceAccountPassword /PID=$sqlPid /IACCEPTSQLSERVERLICENSETERMS /Q /SECURITYMODE=SQL /SAPWD=$SaPassword
/SQLSYSADMINACCOUNTS=Administrator #3
if ($InstanceName -ne 'MSSQLSERVER') { #4
$ServiceName = 'MSSQL$' + $InstanceName
$ServerInstance = 'localhost\' + $InstanceName
} else {
$ServiceName = $InstanceName
$ServerInstance = 'localhost'
}
$ServiceStatus = Get-Service -servicename $ServiceName
if ($ServiceStatus.Status -eq 'Running') {
$output = "The service for SQL Server instance: {0} is Running" -f
$InstanceName
Write-Output $output
} else {
$output = "The service for SQL Server instance: {0} is NOT Running.
Script terminating" -f $InstanceName
Write-Output $output
exit
}
$ServerName = Invoke-Sqlcmd -ServerInstance $ServerInstance -query 'SELECT
@@SERVERNAME ;' -Username 'sa' -Password $SaPassword
if ($ServerName -ne $null) {
$output = "The SQL Server instance: {0} is accessible" -f $InstanceName
Write-Output $output
} else {
$output = "The SQL Server instance: {0} is NOT accessible. Script
terminating" -f $InstanceName
Write-Output $output
exit
} #5
Invoke-Sqlcmd -ServerInstance $ServerInstance -Query "EXEC sp_configure 'show advanced options', 1 ; RECONFIGURE ; EXEC sp_configure 'remote
access', 0 ; RECONFIGURE ; EXEC sp_configure 'show advanced options', 0 ;
RECONFIGURE ;" -Username 'sa' -Password $SaPassword #6
$Query = "ALTER LOGIN sa WITH NAME = {0}" -f $SaName
Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $Query -Username 'sa'
-Password $SaPassword #7
The scripted installation accepts an edition parameter, maps Developer to an installation product identifier, and can be invoked explicitly for a Developer instance. section 8.8
