For T-SQL Tuesday #94 the topic is databases and PowerShell. Rob Sewell asks us: What are you going to automate today?
I’m going to automate some thing in SSRS & SSAS, but that’s not what I’m going to write about today. Instead, I’m just going to post some pointers to help you install/update to the latest version of the SqlServer module more easily;
I put together an article for MSSQLTips on Best Practices for working with the SqlServer module that is available in the PowerShell Gallery. The article has some handy details in it but here are the highlights:
You need to run this command from an elevated PowerShell session to install the latest version:
Install-Module SqlServer
I didn’t mention in the article that you will probably need to add two additional parameters to upgrade your SqlServer module when the team releases new versions:
Install-Module SqlServer -AllowClobber -Force
If you want to download the module from the PowerShell Gallery so that you can copy it to a server that isn’t connected to the internet, use:
Save-Module SqlServer -Path c:\temp\
And then copy the folder to the C:\Program Files\WindowsPowerShell\Modules\ directory on the server you need it on.
Whatever you do, try to avoid using the -Scope CurrentUser option. You may need to use it if you don’t have high enough permissions, in that situation go ahead and use -Scope CurrentUser. However, if you can avoid using it, do so. I have lost a lot of time to having used this option.
Install-Module SqlServer -Scope CurrentUser
Finally, the SSAS cmdlets and my improvements to the Provider have now been incorporated into the main SqlServer module. I’m working on an article to walk people the benefits of using that. I hope to share the draft next week or so.
If you run into any trouble with the SqlServer module, please don’t hesitate to reach out to me, I’m happy to help! 🙂
One Response
I have been trying to download this since reading a blog from Rob Sewell last week. And I tried your options and still get access denied. When I just to Install-Module from a PS Console as Administrator running as a user with permissions to Program Files I get Access Denied. So I tried you option of Save-Module to paths I have access to, but it looks to try and install to Program Files after it downloads and never leaves me a zip file or folder I can manually copy. I am just trying to get SqlServer module where I can do some learning. I am newer to PS, so modules and stuff are new to me and I am new to Win10.