Post SQL Server Installation steps with PowerShell
I bet, each DBA would change some settings after the SQL Server installation as per their company standard . I wrote this PowerShell script for one of my client who has below standards:
- Port should be 1433, static
- Disable SQL telemetry* & SQLWriter services
- Change recovery model to FUll for all databases except tempdb and master
- Assign sysadmin permissions to NT AUTHORITY\SYSTEM
- Change min and max memory (If the computer has less than 8GB of physical memory, allocate 80% of it to SQL Server and leave 20% for the OS and other applications
If the computer has more than 8GB of physical memory, assign 30% to OS and 70% to SQL Server)
- Create TempFiles equal to number of computers
- Remove ndf files
Note - DO NOT run this script in production environment as it will RESTART the SQL Services!
I would recommend to run this script ONLY on new build server and this script be very helpful for team who only do SQL Server Installation.
# Chaning ports
'Loading SQLPS environment'
Import-Module SQLPS -DisableNameChecking -Force
'Initializing WMI object and Connect to the instance using SMO'
($Wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $env:COMPUTERNAME)
($uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='$env:COMPUTERNAME']/ServerProtocol[@Name='Tcp']")
# Getting settings
($Tcp = $wmi.GetSmoObject($uri))
$Tcp.IsEnabled = $true
($Wmi.ClientProtocols)
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
'Setting IP Properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value="1433"
'Review properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
'Disable Dynamic Ports'
#($uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='$env:COMPUTERNAME']/ServerProtocol[@Name='TcpDynamicPorts'].value=""")
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties['TcpDynamicPorts'].value=""
'Save properties'
$Tcp.Alter()
'Review properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
#Restart SQL Services
#Restart-Service -Force mssql$*
# Disabling SQLWriter and SQLtelemetry* services
Get-Service SQLWriter, SQLtelemetry* | Stop-Service -PassThru -Force | Set-Service -StartupType disabled | write-output
# Changing Recovery Model to FULL for all databases except tempdb and Master
$Server="localhost"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Server
$SMOserver.Databases | where-object {$_.Name -ne "tempdb" -and $_.Name -ne "master"} | select Name, RecoveryModel | Format-Table
$SMOserver.Databases | where-object {$_.Name -ne "tempdb" -and $_.Name -ne "master"} | foreach {$_.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full; $_.Alter()}
$SMOserver.Databases | where-object {$_.Name -ne "tempdb" -and $_.Name -ne "master"} | select Name, RecoveryModel | Format-Table
# Assigning sysadminpermissions to NT AUTHORITYSYSTEM
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "localhost"
$svrole = $svr.Roles | where {$_.Name -eq 'sysadmin'}
$svrole.AddMember("NT AUTHORITYSYSTEM")
# If the computer has less than 8GB of physical memory, allocate 80% of it to SQL Server and leave 20% for the OS and other applications
# If the computer has more than 8GB of physical memory, assign 30% to OS and 70% to SQL Server
Function Get-ComputerMemory {
$mem = Get-WMIObject -class Win32_PhysicalMemory | Measure-Object -Property Capacity -Sum
return ($mem.Sum / 1MB);
}
Function Get-SQLMaxMemory {
$memtotal = Get-ComputerMemory
$min_os_mem = 2048 ;
if ($memtotal -le $min_os_mem) {
Return $null;
}
if ($memtotal -ge 8192) {
$sql_mem = $memtotal * 0.7
} else {
$sql_mem = $memtotal * 0.8 ;
}
return [int]$sql_mem ;
}
Function Set-SQLInstanceMemory {
param (
[string]$SQLInstanceName = "localhost",
[int]$maxMem = $null,
[int]$minMem = 0
)
if ($minMem -eq 0) {
$minMem = 1024
}
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object Microsoft.SQLServer.Management.Smo.Server($SQLInstanceName)
if ($srv.status) {
Write-Host "[Running] Setting Maximum Memory to: $($srv.Configuration.MaxServerMemory.RunValue)"
Write-Host "[Running] Setting Minimum Memory to: 1024"
Write-Host "[New] Setting Maximum Memory to: $maxmem"
Write-Host "[New] Setting Minimum Memory to: 1024"
$srv.Configuration.MaxServerMemory.ConfigValue = $maxMem
$srv.Configuration.MinServerMemory.ConfigValue = 1024
$srv.Configuration.Alter()
}
}
$MSSQLInstance = "localhost"
Set-SQLInstanceMemory $MSSQLInstance (Get-SQLMaxMemory)
# Create TempFiles
function Invoke-Sqlcmd2
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
[Parameter(Position=1, Mandatory=$false)] [string]$Database,
[Parameter(Position=2, Mandatory=$false)] [string]$Query,
[Parameter(Position=3, Mandatory=$false)] [string]$Username,
[Parameter(Position=4, Mandatory=$false)] [string]$Password,
[Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
[Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
[Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile,
[Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow"
)
if ($InputFile)
{
$filePath = $(resolve-path $InputFile).path
$Query = [System.IO.File]::ReadAllText("$filePath")
}
$conn=new-object System.Data.SqlClient.SQLConnection
if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
$conn.ConnectionString=$ConnectionString
#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
if ($PSBoundParameters.Verbose)
{
$conn.FireInfoMessageEventOnUserErrors=$true
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
$conn.add_InfoMessage($handler)
}
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
switch ($As)
{
'DataSet' { Write-Output ($ds) }
'DataTable' { Write-Output ($ds.Tables) }
'DataRow' { Write-Output ($ds.Tables[0]) }
}
} #Invoke-Sqlcmd2
function Set-TempDbSize
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$false)] [Int16]$maxFileCount = 16,
[Parameter(Position=1, Mandatory=$false)] [Int32]$maxFileInitialSizeMB = 1024,
[Parameter(Position=2, Mandatory=$false)] [Int32]$maxFileGrowthSizeMB = 10240,
[Parameter(Position=3, Mandatory=$false)] [Int32]$fileGrowthMB = 100,
[Parameter(Position=4, Mandatory=$false)] [float]$coreMultiplier = 1.0,
[Parameter(Position=5, Mandatory=$false)] [switch]$outClipboard
)
#get a collection of physical processors
[array] $procs = Get-WmiObject Win32_Processor
$totalProcs = $procs.Count
$totalCores = 0
#count the total number of cores across all processors
foreach ($proc in $procs)
{
$totalCores = $totalCores + $proc.NumberOfCores
}
#get the amount of total memory (MB)
$wmi = Get-WmiObject Win32_OperatingSystem
$totalMemory = ($wmi.TotalVisibleMemorySize / 1024)
#calculate the number of files needed (= number of procs)
$fileCount = $totalCores * $coreMultiplier
Write-Host "$fileCount"
if ($fileCount -gt $maxFileCount)
{
$fileCount = $maxFileCount
}
#calculate file size (total memory / number of files)
$fileSize = $totalMemory / $fileCount
if ($fileSize -gt $maxFileInitialSizeMB)
{
$fileSize = $maxFileInitialSizeMB
}
Write-Host "$fileSize","$fileCount"
#build the sql command
$command = @"
declare @data_path varchar(300);
select
@data_path = replace([filename], '.mdf','')
from
sysaltfiles s
where
name = 'tempdev';
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = {0}MB , MAXSIZE = {1}MB, FILEGROWTH = {2}MB );
"@ -f $fileSize, $maxFileGrowthSizeMB, $fileGrowthMB
for ($i = 2; $i -le $fileCount; $i++)
{
$command = $command + @"
declare @stmnt{3} nvarchar(500)
select @stmnt{3} = N'ALTER DATABASE [tempdb] ADD FILE ( NAME = N''tempdev{3}'', FILENAME = ''' + @data_path + '{3}.mdf'' , SIZE = {0}MB , MAXSIZE = {1}MB, FILEGROWTH = {2}MB )';
print @stmnt{3}
exec sp_executesql @stmnt{3};
"@ -f $fileSize, $maxFileGrowthSizeMB, $fileGrowthMB, $i
}
Write-Host "$fileSize","$maxFileGrowthSizeMB","$fileGrowthMB"
if ($outClipboard)
{
$command | clip
return "";
}
else
{
return $command
}
# remove ndf files
$command = "
DECLARE @ndfname nvarchar(100)
declare @queryNdf nvarchar(100)
use tempdb
DECLARE c1 CURSOR
FOR
select name from sysfiles
where filename like '%ndf%'
OPEN c1
FETCH NEXT FROM c1
INTO @ndfname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ndfname
use tempdb
--DBCC SHRINKFILE (@ndfname, EMPTYFILE)
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE ( 'ALL')
--DBCC SHRINKFILE (@ndfname, EMPTYFILE)
use master
SET @queryNdf = 'ALTER DATABASE tempdb REMOVE FILE [' + @ndfname + ']'
PRINT @queryNdf
Exec (@queryNdf)
FETCH NEXT FROM c1
INTO @ndfname
END
CLOSE c1
DEALLOCATE c1
return $command
}
#Set-TempDbSize -outClipboard
$query=Set-TempDbSize
Invoke-Sqlcmd2 -ServerInstance "localhost" -Query $query 
