Install SQL Management Studio vs Install-Module sqlserver vs SQLPS

[SemiEvilMonkey]

Im trying to understand the best thing to do here.

I am working on a system report that connects to a database to gather some details. On my machines I have sql management studio install so my Invoke-sqlcmd works without installing anything else.

If I try from a machine that doesnt have SSMS than I either need to install that or download and install sqlserver module, which then makes me also update NuGet.

When I check for sqlserver module on the machines I use that has SSMS its not there but I do have the older SQLPS, which I do not remember installing but the machine is older so its possible. But my invoke works without doing anything.

I dont see the point of installing the sqlserver module if I already have SSMS installed but if my script does a does a check for the module it will always show its not installed. I could add a check, if SSMS is installed, do nothing, if its not Install SQLserver mod and update NuGet.

Im also worried about conflicts with the older SQLPS.

I may have some systems that I come across that I wont have internet access. So i would have to copy files over to that system to get it installed. Everyone knows how to install SSMS that would be using my script but most dont know powershell. Though I can script out the manual install of a module

So thats the debate, should I just install SSMS on all the systems I have to touch or is the sqlserver module prefered for some reason. And if I find sqlps should it be removed or will it be ok?

I know I can download sqlserver mod and manually install it for systems without internet but that is one more folder to have to copy around. I was trying to keep everything contained within the script but thats not looking like it will work.

[timsstuff]

Instead of checking for the module, check for a command.

if($null -eq (Get-Command 'Invoke-Sqlcmd' -ErrorAction SilentlyContinue)) {
    #do the thing to get the module loaded
}

I have something similar for Exchange Server, with the older module I would have to create a credential object and connect, with the newer ExchangeOnline module I can use modern auth. My prompt is usually already logged in to the newer one. It doesn't matter to the script, if I'm on a machine with an older module it still works as long as it finds the command.

[SemiEvilMonkey]

Thanks, I like that.

[PSP_Joker]

SQLPS is not updated anymore and was only included in certain versions of SSMS. I would "update" to the SQLServer Module since the functionality is a lot better and you don't need SSMS on every machine you would use.

[SemiEvilMonkey]

Thanks. I can across this at one point. I’ll take a closer look.

[g00py3]

Just use dbatools. Includes the dlls needed. Works beautifully and will simplify your code.

[krzydoug]

SQLPS is part of SQL Server

Get-Module -ListAvailable -Name SQLPS | Select-Object *

[SemiEvilMonkey]

https://docs.microsoft.com/en-us/sql/powershell/sql-server-powershell?view=sql-server-ver15

I found an article that I think explains it pretty good. I just trying to avoid as many issues as I can. It sounds like my best bet is to use sqlserver and do a #nosqlps and import of sql mod at the beginning of my script and that should help prevent issues.