Thursday, 7 March 2019

SQLServer Module in PowerShell

SQLServer Module in PowerShell

If you are not already using this modal as a DBA it’s well worth a go.  In it you will find some great ways to administrate your database servers from your desk top.
To install it just open ISE and type:-

Install-Module –Name  SQLServer
Or to get the very latest (at the time of writing)
Install-Module -Name SqlServer -RequiredVersion 21.1.18080
You might find that you don’t have admin permissions to install it for all users, you can get round this by installing just for your user with:-
Install-Module -Name SqlServer -Scope CurrentUser
Also you may get an issue as parts of this modal may exist, so go for the below that will allow overwrite:-
Install-Module -Name SqlServer –AllowClobber
So the whole thing:-
Install-Module -Name SqlServer -RequiredVersion 21.1.18068  -Scope CurrentUser  -AllowClobber

So what can it do?
Well almost anything, have a look for yourself here:-
Get-Command -Module SqlServer -CommandType Cmdlet | Out-GridView

But here are a few of the top picks I think are worth an explore.
Use this to search the SQL error log for set words or strings, can be very useful for looking for stack dumps or log in failures.
Does what it says on the tin, there are a number of availability group modules that are worth a look.
Start-SQLInstance , Stop-SQLinstance
Great for controlling SQL from afar.
Again great if you need to remove a login on all servers quickly.

Have a look about, and tell me your favourite commands.