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.
Created by Chrissy LeMaire originally (http://www.linkedin.com/in/chrissylemaire) , the main contributors page can be found at: team – dbatools . All credit to these folks.
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.
Connectivity
test-dbaconnection servername
This little gem will test server connectivity, SQL connectivity, PSRemoting connectivity, and even return the Authentication method used, ie.. KERBEROS or NTLM.
Backups
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
get-dbadiskspace servername
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.
Configuration
get-dbainstanceproperty servername| ft
Get all of the properties you could possibly be interested in at the server instance level.
Installed Features
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.)
Server Details
get-dbacomputersystem servername
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.
Tempdb
get-dbatempdbusage servername | ft
My tempdb is vast, who's causing it?
Memory
Get-DbaMemoryUsage servername | ft
Quick breakdown of the main memory brokers.
Jobs
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.
CheckDB
get-dbalastgoodcheckdb servername | ft
Days since the last good checkdb by database, and a status column, and if DataPurity is enabled or not.
Ports
get-dbatcpport servername
Returns the IP and TCP port for the SQL Engine.
Network Latency
test-dbanetworklatency servername
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.
Services
get-dbaservice servername | ft
Useful to see what services are installed, their status and service accounts in use.
Startup Parameters
get-dbaStartupParameter servername
get-dbatraceflag servername
Quick look into how SQL server is configured to start up, or just the running traceflags. Saves a trip to SQL Configuration Manager.
Operating System
get-dbaoperatingsystem servername
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.
Files
Find-DbaOrphanedFile servername
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!
Patches
get-dbainstalledpatch servername
Quick run down of what SQL updates were installed when.
Bonus Connectivity.
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.
Enter-PSSession servername
Test-Netconnection -computername servername2 -port 1433
Exit-PSSession
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.
Kommentare