PowerShell: the best SQL Server management tool

We have spent years championing PowerShell adoption for SQL database admins.  It is getting better out there in the SQL-verse, but some DBAs and managers still don’t understand why they should use PowerShell to manage their environments.  PowerShell is, very simply, the most important management tool DBAs have. (The second most important, of course, is our own Minion Enterprise, but I won’t go into that tangent right now.)

What’s the bottom line? The key idea, the lede? It’s this: With PowerShell, you can manage hundreds of objects at once with just a few lines of code.

With PowerShell, you can manage hundreds of objects at once with just a few lines of code.

-Me

SQL Server shops need PowerShell

No team can effectively manage a decent-sized environment by hand. There is far too much to cope with, oversee, audit, and be alerted to…and to do all this without making mistakes! Quite a lot of our training, videos – not to mention our entire software company – has been dedicated to solving this problem.

A “decent-sized environment” doesn’t necessarily mean hundreds of servers.  It could be a shop with just:

• dozens of databases on a single server,
• dozens of schemas in a single database,
• dozens of tables, stored procedures, views, etc., or even
• dozens of jobs or logins.

Whatever it is you’re cycling through, PowerShell is more efficient at managing these objects than T-SQL will ever be. 

POWERshell, not wussyshell

Here’s one quick example: let’s say we need to grant UserA and UserB “db_datareader” rights on 16 different databases on Server1. We can’t easily use the “sp_MSforeachdb” for this, because we just need to run against 16 of the 38 databases.

With PowerShell, we can create variables for server name ($Server), database list ($DBs), and query ($query), and run this command:

$DBs | %{ Invoke-Sqlcmd -ServerInstance $Server -Database $_ -Query $query; };

That’s not bad at all. Without PowerShell, we’d have to run through each of the 16 databases, and run the script individually.

The full script with the variables – and be careful with this script, as it’s very powerful! – looks like this:

$Server = 'Server1';
$DBs = 'DB8','DB9','DB10','DB11','DB12','DB13','DB14','DB15','DB16','DB17','DB18','DB19','DB20','DB21','DB22','DB23','DB24';
$query = "EXEC sp_addrolemember 'db_datareader', 'UserA'; EXEC sp_addrolemember 'db_datareader', 'UserB';"

$DBs | %{ Invoke-Sqlcmd -ServerInstance $Server -Database $_ -Query $query; };    #run the $query for each DB in $DBs, on $Server 

What’s even better is, we can save this script and parameterize it. Then in future, we could use a even fewer characters:

$Server = 'Server1';
$DBs = 'DB8','DB9','DB10','DB11','DB12','DB13','DB14','DB15','DB16','DB17','DB18','DB19','DB20','DB21','DB22','DB23','DB24';
$query = "EXEC sp_addrolemember 'db_datareader', 'UserA'; EXEC sp_addrolemember 'db_datareader', 'UserB';"

.\for-manyDB $Server #DBs $query; 

This script is flexible – PowerShell is flexible! It’s easy to alter the script to run against multiple servers, or against a list of tables (or other objects), and so on. The possibilities are nearly limitless, and THAT’S THE POWER OF POWERSHELL. 

A real-life application of PowerShell

When I was working in big shops, contractors would frequently come in to do specific work for us. Of course, we would have to grant those contractors rights on all of the environments for a given application. 

At Company X, we 7 prod servers, 3 QA servers, and 5 development instances, each with 500 or more databases. Math tells me that’s 15 servers, and over 7,500 databases I had to add these users to.  (As a side note, Windows groups will only take you so far. The next contractor may need different rights, so you’ll still end up modifying the permissions.)

I went through this exercise about once every two months. PowerShell made it a trivial matter, and that’s just ONE example.

Get empowered with PowerShell

Database administrators should run to PowerShell, if you haven’t already. We’re asked to do more and more with less time and fewer resources.  See?

Using mainly PowerShell*, I’ve managed 900+ servers by myself. PowerShell is vital to the management of any SQL Server shop.

There are so many courses available, so many SQLSaturday sessions, so many videos (FREE on MidnightDBA.com). There’s no reason to remain in the dark. Learn PowerShell, and provide true value to your company.

Want to work with us? Contact us here!

*This was the job where I started coding – using the principles of automation and whole-environment reach – and Minion Enterprise was born!