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 FilesWindowsPowerShellModules 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! 
