SQL PowerShell 2017 IS HERE!
Cue Princess Jasmine.
What do I mean “Cue Princess Jasmine”?
Well because starting today, it’s a “Whole New World for You & Me”
What on Earth am I talking about??
The SqlServer PowerShell is now available for download in the PowerShell Gallery! http://sqlps.io/sqlserver
What Does It Mean!?
Why Would You Want to Do That?
Scheduling a SQL PowerShell script in SQL Agent is one reason. On a server, you wouldn’t want to install all of SSMS just to get the SqlServer PowerShell module. With SSMS 16.3+ that installing SSMS on the server was your only option, but now with SSMS 17 released today, the SqlServer module is now able to be downloaded and deployed just with PowerShell itself!
So basically: Double Rainbow is what it means!!
How Do I Install the New SqlServer PowerShell Module?
Installing items from the Gallery requires the latest version of the PowerShellGet module, which is available in Windows 10, in Windows Management Framework (WMF) 5.0, or in the MSI-based installer (for PowerShell 3 and 4).
As long as you have that, it’s pretty Straight Forward actually.
First, Start PowerShell as Administrator.
Second, run Install-Module SqlServer
Note: You will need to use -Force if you have the previous version of the SqlServer module installed, the one that came with SSMS 16.3+
Then just wait for the progress bar to complete.
But What If My SQL Server Isn’t Connected to the Internet?
No problem, just run Save-Module SqlServer from a machine that is connected to the internet and copy the folder over to the C:\Program Files\WindowsPowerShell directory of the machine that you want the module on.
What If I Don’t Have Administrator Rights?
For those of you without Administrator access there is a way in install the SqlServer module but it comes with two important issues that we have found. First, no other users on the machine will be able to see the module, not even SQL Server Agent (unless it’s running under your account). If multiple people use the machine, and they aren’t administrators, they will have to follow the same install process, leading to multiple copies of the SqlServer on the machine, possibly at different version levels, depending on when they downloaded it. Second, and much more problematic, if an administrator does come around later and install the SqlServer module using the first method we mentioned (the better one) your user account will not load the central copy of the SqlServer module, it will instead load the copy that you had downloaded yourself. This is due to the default behavior of the PSModulePath, you can read more about how to change that here: https://msdn.microsoft.com/en-us/library/dd878326(v=vs.85).aspx. If you’re OK with both these drawback, then just run Install-Module SqlServer -Scope CurrentUser for a local user install.
Is That All?
That’s it for now! I have more information to share, especially about that PSModulePath issue, but I’m still testing everything and gathering comments. I will have more to share soon so make sure to check back.