I realized my previous post still does things the “old way”, in that I used sqlcmd.exe rather than Invoke-SqlCmd via PowerShell.

Using the same approach, with the same SQL script, as that previous example, but with purely PowerShell as the medium, it might look like this…

$ScriptPath = Split-Path -Parent $MyInvocation.MyCommand.Definition

function Configure-SQLMemory {
  param (
    [parameter(Mandatory=$True)] [string] $SQLServerFQDN
  )
  $out = $True
  $TmpScript = "$ScriptPath\sql_mem_template.sql"
  $NewScript = "$ScriptPath\sql_memory.sql"
  $Temp = Get-Content "$TmpScript"
  $TotalMem = Get-WmiObject -Class Win32_ComputerSystem | 
    Select-Object -ExpandProperty TotalPhysicalMemory
  $Temp.Replace("%TOTALMEMORY%", $TotalMem) | Out-File $NewScript
  $SqlLog = "$ScriptPath\sql_memory_update.txt"
  Invoke-Sqlcmd -InputFile "$NewScript" | 
    Out-File -FilePath "$SqlLog"
}

Not drastically different, but different nonetheless.

Advertisements

2 thoughts on “PowerShell + SQL Server Memory Allocation, Pt. 2

  1. I hope you don’t mind some general tips and suggestions. As written, or at least as shown here, your function is a stand-alone command. That means it shouldn’t try to reference any variables defined outside of it if. In your sample you are defining $scriptpath outside of the function but calling it within the function. The recommended best practice is to avoid referencing variables defined outside of scope. You might make the path another function parameter or define it inside the function.

    You also have a mandatory parameter for the SQL Server, but I don’t see where you are using it. I’m going to take a wild guess that you are doing all of this in the PowerShell ISE and it works. But it works because everything is running in the same scope. I always recommend developing in the ISE but testing in a new console window.

    Oh, and what is the purpose of $Out?

    1. Yes. You are correct. It is part of a larger set of tasks. The $scriptpath variable is used throughout the other functions as well, so I define it once in the file. The $out return is just for getting a success/fail result to determine whether to continue with the next function call or stop. I do the build in ISE and test in the regular console actually. And also on two different machines. That way I test on a known incompatible environment and a known compatible. Just an old habit I have for verifying the condition tests work in both cases. The SQLServer parameters was a goof. Good catch. Thank you for reviewing this! I’m fortunate that you, of all people, take the time to read what I’ve posted.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s