# 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 | 
