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:

  1. Port should be 1433, static
  2. Disable SQL telemetry* & SQLWriter services
  3. Change recovery model to FUll for all databases except tempdb and master
  4. Assign sysadmin permissions to NT AUTHORITY\SYSTEM
  5. 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)

  6. Create TempFiles equal to number of computers
  7. 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

Rate

5 (2)

Share

Share

Rate

5 (2)