DBATools is the best thing since sliced bread, i'm sure alot of us gluten tolerant people can all agree on that. Powershell makes automation and multiserver management a lot faster and easier, and DBATools just makes that easier still as the community has written many, many wonderful functions for us.
Refer to download – dbatools for installation instructions, its remarkably simple.
I often build solutions around the dbatools functions, the below is just some of my Operational favourites. With some I've included the output pipe that I use most frequently, but obviously, you can view and use the output however you choose to. Clearly, DBATools has many functions to add/remove/update SQL as well, but i'm just folking on the 'gets' here.
Naturally, as mentioned...its powershell, you can programmatically use this for any of your automation needs. Marvellous.
The ever growing list of commands can be found here: command index – dbatools . This can prove daunting to new users of the toolset, so here's a starter for 10.
This little gem will test server connectivity, SQL connectivity, PSRemoting connectivity, and even return the Authentication method used, ie.. KERBEROS or NTLM.
get-dbalastbackup servername | ft
This one shouldn't need explaining. Make sure backups are occurring (and test restore them elsewhere, which DBATools can also help with).
Disk and Database Capacity
See how your server space is looking.
get-dbadbspace servername | out-gridview
Also how large your databases are currently.
Really Useful: I've included the out-gridview (ogw) pipe in this one. What this pipe output gives is a sortable and filterable grid for simple viewing.
Need to quickly see what DB Log file has suddenly used the space? No problem. Simply type LOG in the filter bar, and all rows with LOG are shown, then sort the rows by File Size. You can see in 2 seconds flat.
get-dbainstanceproperty servername| ft
Get all of the properties you could possibly be interested in at the server instance level.
get-dbafeature servername | ft
You need to check what SQL features are installed, which can be a bit trickier that you'd like. This allows a fast check, no fuss, lovely. (Yes, I need to patch, but this is my 'throwaway' instance.)
Need to check the server spec, no problem. Usefully, this also gives an indication if the server has a reboot pending. Very useful to avoid those failed attempted installations as the server needs a bounce.
get-dbatempdbusage servername | ft
My tempdb is vast, who's causing it?
Get-DbaMemoryUsage servername | ft
Quick breakdown of the main memory brokers.
get-dbaagentjob servername | ft
Essentially this gives information similar to activity monitor.
And a subset of that:
Get-DbaRunningJob servername | ft
What jobs are running right now.
get-dbalastgoodcheckdb servername | ft
Days since the last good checkdb by database, and a status column, and if DataPurity is enabled or not.
Returns the IP and TCP port for the SQL Engine.
This runs a basic command against the target, and returns a breakdown of the execution and round trip times. As ever, i'll put my usual disclaimer on this one, don't take just this information and start swearing at your local network techie.
get-dbaservice servername | ft
Useful to see what services are installed, their status and service accounts in use.
Quick look into how SQL server is configured to start up, or just the running traceflags. Saves a trip to SQL Configuration Manager.
Returns useful information about the OS, including timezone for troubleshooting those strange date formats.
Central Management Server
get-DbaRegServer -SqlInstance cmsserver -Group 'Live\UK'
This will return all servers within a specific CMS group.
get-dbaregserver -sqlinstance cmsserver -servername servername
This will return details of which folders a server is within your CMS. Really useful if you break your CMS down into Application name folders and have a servername that you don't know what it relates to.
Ever have a worry that DBs have been detached and the files left behind, or a DB offlined and then deleted, also leaving files behind? The above checks for exactly that and reports any files that aren't currently attached to a database, wasting your precious disk resource. Combine that with the CMS command above, you can sweep your estate in a single command. Nice!
Quick run down of what SQL updates were installed when.
This one isn't actually DBATools, but I love it, and really useful when you need to test connectivity between 2 remote servers, you can pass in a TCP port to test against too. Useful for debugging those pesky firewalls.
Test-Netconnection -computername servername2 -port 1433
The above is just a quick rundown of what I seem to use most in Dbatools. Its a magnificent resource, if you don't use it, I strongly urge you to take a look.
Thanks for reading
Rod the Tool.