Over the month of November a fellow PowerShell enthusiast, Mike Fal (blog
|@Mike_Fal), did a series on the SQL Server PowerShell module (SQLPS). In reading his series curiosity struck on what the module does when it was loading. I also just answered a question on DBA.SE that I figured out by tweaking a particular file for this module, so thought I would share more details.
WARNING: You are modifying the files at your own risk. You have been warned.
If you are not familiar with the files involved with a module, you can read more on that here. The file I found most interesting is the “SqlPsPostScript.PS1” file, located in the SQLPS module folder for the given version of SQL Server:
C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\PowerShell\Modules\SQLPS\
This file is being called after the module is loaded as a nested module. You can find all of this by reading through the manifest file for SQLPS,
The post script file does a handful things:
- Sets the location of your session to SQLSERVER:\ (the provider)
- Has a function that does the same thing, if you just enter “SQLSERVER:” at the prompt instead of typing the Set-Location. Could be a waste of 47 characters to me.
- Loads the DAC assemblies for managing and creating DAC packages with PowerShell.
- Loads the SQLAS module, if not already loaded.
Now the most annoying thing I always have with the SQLPS module is that it puts me in the location of the provider after it loads. I stop that by simply commenting out that first line in the file. A
Measure-Command on loading the module before and after, shaves off a whole second (time is money).
The other thing I found was the warnings on the WMI service the poster on DBA.SE was getting, was strictly due to the SQLAS module being loaded. Which in the documentation I linked to in that question shows that this module utilizes WMI. Which that begs the question for Microsoft: Why you put WMI management into SQLAS, and not SQLPS?
Now the module itself when loading also loads numerous DLL files, which all those contain the magic that is SQLPS.