new best practices commands now available

So many SQL Server and PowerShell pros have joined the dbatools team and we’re producing well designed PowerShell commands like mad!

dbatools is not only intended to be a great migration tool, but also a toolset to help DBAs follow best practices. We like to think of these commands as fully automated Wizards that are executed from the command line instead of the GUI.

Best Practices Commands

Ever read a really great article about how to do something properly but then kinda forgot and you have to revisit each time? Well, we’ve codified some those practices for you, making them easy to both follow and remember.

  1. Performs a DBCC CHECKDB
  2. Backs up the database WITH CHECKSUM
  3. Restores with VERIFY ONLY on the source
  4. Creates and Agent job to easily restore from that backup
  5. Drops the database
  6. The Agent Job restores the database
  7. Performs a DBCC CHECKDB and drops the database for a final time

     Simply amazing! You can read more about Remove-SqlDatabaseSafely on Rob’s blog.

  • Set-SqlTempDbConfiguration
    These commands, created by Mike Fal, sets tempdb data and log files according to best practice calcluations. Mike writes more about this function on his blog.

  • Find-SqlUnusedIndex
    This command, created by Aaron Nelson, will help you to find unused indexes on a database or a list of databases. It also tells how much space you can save by dropping the index. For now only supported for CLUSTERED and NONCLUSTERED indexes.

Commands that make your life easier

  • Show-SqlWhoIsActive
    We wrote a PowerShell command to output results of Adam Machanic’s beloved sp_WhoIsActive to a GridView (default) or DataTable. If sp_WhoIsActive is not installed in the system, it will be downloaded and installed to a database you specify with either -Database or a database you select from Show-SqlDatabaseList.

    This is v0.1 of Show-SqlWhoIsActive. We’ll be working on formatting options and auto-population soon.

  • Repair-SqlOrphanUser
    This command, created by Cláudio Silva, helps DBAs find and repairs orphaned users in one, multiple or all databases.

  • Remove-SqlOrphanUser
    Another homerun written by Cláudio Silva, which removes orphaned database users with no matching logins.

  • Get-DbaDiskSpace
    Displays Disk information for all local drives on one or more servers. Returns server name, name of disk, label of disk, total size, free size and percent free. Considering a new column in the future that designates if any SQL data/log files are stored on the returned disks. What do you think?

  • Get-SqlRegisteredServerName
    Gets list of SQL Server names stored in SQL Server Central Management Server. Filtering by one or more server groups supported.

  • Show-SqlServerFileSystem
    Similar to the remote file system popup you see when browsing a remote SQL Server in SQL Server Management Studio, this command allows you to traverse the remote SQL Server’s file structure.

    Show-SqlServerFileSystem uses SQL Management Objects to browse the directories and what you see is limited to the permissions of the account running the command. This will complement the upcoming Move-SqlDatabaseFile command.

wpf

  • Show-SqlDatabaseList
    Shows a list of databases in a GUI. Returns a simple string. Hitting cancel returns null.

dblist

Test Commands

  • Test-SqlTempDbConfiguration
    Test your TempDb Configuration

  • Test-SqlNetworkLatency
    This function is intended to help measure SQL Server network latency by establishing a connection and making a simple query. This is a better alternative than ping because it actually creates the connection to the SQL Server, and times not only the entire routine, but also how long the actual queries take vs how long it takes to get the results.

  • Test-SqlPath
    Use this command to determine whether the SQL Server service account can “see” a directory. This command uses master.dbo.xp_fileexist under the hood and returns $true or $false.

  • Test-SqlMigrationConstraint
    Shows if you can migrate the database(s) between the servers. When you want to migrate from a higher edition to a lower one there are some features that can’t be used. This function will validate if you have any of this features in use and will report to you. The validation will be made ONLY on on SQL Server 2008 or higher using the ‘sys.dm_db_persisted_sku_features’ DMV.

Commands that are coming soon

And we’ve got more planned!

  • Move-SqlDatabaseFile
    Move database files from disk to disk

  • Get-SqlDatabaseSpace
    Gets information about space avaialable inside the database

  • Test-SqlBackup
    Routine to test your backups

  • Restore-SqlBackupFromDirectory
    Rotuine to restore databases from directories (think the way that Ola Hallengren’s outputs his by default)

  • Write-SqlSpWhoIsActive
    Write the results of Show-SqlSpWhoIsActive to table!

  • Install-SpWhoIsActive
    Auto-installer for sp_WhoIsActive

  • Disable-SqlLogonTrigger
    Like Reset-SqlAdmin, this is a command that won’t be used often, but when it is, its a lifesaver!

  • Get-SqlClusterActiveNode
    Returns the network name of the active node in a cluster

Join us!

Some of these commands are in their infancy. Want to help make them better? Come join the coding party! We’re all hanging out on the SQL Server Community Slack in the #dbatools channel.

Leave a Reply

Your email address will not be published. Required fields are marked *