TLDR: To efficiently manage multiple SQL Server instances learn PowerShell and check out the dbatools. There is also the mssql-cli to look at too.
Since its introduction in 2005 the SQL Server Management Studio (SSMS) was the tool used for development and administration of SQL Server databases. Most of the common, day to day tasks one can perform by pointing and clicking without the need to remember (or at least type in) all those confusing commands. But I never liked mice much and the main reason I kept using SSMS was that the only real alternative was the sqlcmd command line utility which is a command line tool, but doesn’t make you any more productive than SSMS.
But the times are changing. I attended ~SQL~Data Relay earlier this week and it struck me that a lot of SQL Server demos where done without the SSMS. And as I started thinking about it I realised that in fact I don’t use it that frequently in my job either. With the power of PowerShell and the dbatools and dbachecks module I can no longer imagine managing SQL Servers with SSMS.
Examples
Imagine you’ve got 3 servers (just to make the example simple, but it could be 30 or 300, it doesn’t really matter). All we have to do is define a variable holding a list of those servers like so:
$s = server1,server2,server3
And now we can get to work. Here are a few example of tasks which in SSMS would involve too much clicking for my liking, and task I can perform with a single PowerShell command.
Find a Database
I’ve got hundreds of databases on each instance but I need to find the specific one. All I have to do is
Find-DbaDatabase -SqlInstance $s -Pattern "PartOfMyDatabaseName"
And it really can be just a part of the name, so I don’t have to remember the whole name, it doesn’t matter if I remember the beginning or the end of the name.
Check server logs
Let’s say I need to check all my servers error logs for a specific five minutes when our system had a wobble. With SSMS it’s hours of mouse abuse. With PowerShell it’s a single command again
Get-DbaSqlLog -SqlInstance $s -Afater '2018-10-10 10:00' -Before '2018-10-10 10:05`
It will collate all error logs for the time period between 10:00 and 10:05 on 10/10/2018. If the output is too long to read in the PowerShell console you can pipe it to Out-GridView. In fact you can do that with any powershell command returning data. Try adding | Out-GridView at the end of any command like so in the log example
Get-DbaSqlLog -SqlInstance $s -Afater '2018-10-10 10:00' -Before '2018-10-10 10:05` | Out-GridView
Find DB Growth Events
How to find when my databases' files have grown recently? It is possible with SSMS but why would you do it if you can do:
Find-DbaDbGrowthEvent -SqlInstance $s | Out-GridView
That’s it. That is how simple it is. The above command returns all the growth events from all of the servers previously defined in the $s variable.
Conclusions
I still use SSMS, especially when it comes to managing Availability Groups and the AG Dashboards. It is useful for report writing and some ad hoc querying although for those sort of tasks I started using ~SQL Server Operations Studio~ recently renamed to Azure Data Studio. It is based on VS Code and runs on Windows, Linux and macOS, and although it doesn’t have all the features of SQL Server Management Studio, it does have some gems of its own which are not available in SSMS. Another reason could be performance tuning and looking at execution plans, but for those reasons I have long switched to the free Sentry One product Plan Explorer.
For anything else I use PowerShell with dbatools. Looking at the content of SQL Relay demos I’m not alone on that trend.
Michał's Blog 