It seems like Windows PowerShell is finding its way into everything these days. I even wonder if someday I’ll be able to program my car with PowerShell to take me on my next trip. That’s silly, of course, but what Microsoft has been doing, continues to do, and seems intent upon doing in the future with PowerShell is nothing to sneeze at. Microsoft SQL Server database administrators and developers have been lagging in this area as PowerShell has gained traction in other Microsoft server products. Not being a database guru or wizard myself, however, I haven’t stayed too much abreast concerning this, so I decided to talk with an expert and find out what’s happening with PowerShell for SQL Server and what might be coming in the near future.
Chrissy LeMaire is a Cloud and Datacenter MVP and creator of dbatools, the popular community PowerShell module for SQL Server Pros. She is also lead for the Belgian PowerShell User Group and co-lead for the PASS PowerShell Virtual Chapter. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint, and network security, and if you’re interested in following her you can find her on Twitter at @cl.
TECHGENIX: Chrissy, thanks for taking some time to fill us in on what’s been happening with PowerShell for SQL Server. How about starting off by taking us briefly through the history of PowerShell capabilities for Microsoft SQL Server.
CHRISSY LEMAIRE: It’s a bit of a challenge keeping the sordid history of PowerShell and SQL Server brief, but I’ll try my best!
Microsoft first introduced SQL PowerShell with SQL Server 2008. Initially, it was a mini-shell and snap-in but eventually became a module in SQL Server 2012, called SQLPS. The module was unreliable and followed a few bad practices, which left both the SQL Server and PowerShell communities disappointed.
Up through SQL Server 2014, Microsoft did not designate a dedicated engineer, so the module had only 45 commands and it was buggy. The basics, such as Get-SqlLogin or Add-SqlJob, were missing.
But that’s all changed with SQL Server 2016!
TECHGENIX: What’s new and exciting with regard to PowerShell functionality in the latest version SQL Server 2016? What excites you, in particular?
CL: I absolutely love that, starting with SQL Server 2016, Microsoft now has a dedicated SQL PowerShell Engineer, Matteo Taveggia! Matteo and the SQL Tools team, led by Ken Van Hyning, are working very closely with the community to see which commands and bug fixes should get prioritized.
After we saw some forward movement with SQL PowerShell, a few of us in the community created a public Trello board to help design the SQL Server 2016 module, which is now called SqlServer. As a result, we’ve gotten a number of new commands, bug fixes, and features and it feels nothing short of amazing that Microsoft participates on the board and allows us to have a voice in the module’s direction.
The SQL Server team went from a Product Group that was lagging behind in the PowerShell arena to leading the pack. They are even going to add SqlServer to the PowerShell Gallery! We’re hoping they go open source on GitHub soon, too, much like the PowerShell team itself did.
The thing developers are most excited about are the database migration commands. PowerShell enables easy nightly refreshes of dev environments — Chrissy LeMaire
TECHGENIX: Why is PowerShell capability important for those of us who administer SQL Server? What sort of things can you do with it?
CL: To me, the most important capability is that PowerShell provides a legitimate way to loop through servers, databases, tables, indexes, etc. No more unnecessary tables, cursors, or sp_MSforeachdb.
I can search an entire SQL Server estate’s worth of stored procedures — quickly — to find the ones that contain specific strings or regex patterns. Once, I even used the dbatools module’s Find-DbaStoredProcedure command to search 37,545 stored procedures on nine servers and got the results in 8.6 seconds!
PowerShell also helps simplify things we all dread, like backup testing. I actually have a dedicated backup test server that easily tests all the backups for my entire SQL Server estate using Test-DbaLastBackup. Test-DbaLastBackup automates the process of performing restores and integrity checks.
Functionality like that saves us time to do other less tedious (and more fun) tasks.
TECHGENIX: That’s really cool! OK, so how can PowerShell be useful for developers who build solutions with SQL Server?
CL: The thing developers are most excited about are the database migration commands. PowerShell enables easy nightly refreshes of dev environments.
TECHGENIX: Yes, I agree that sounds like a big improvement. What do you feel is still lacking in PowerShell functionality for SQL Server? What’s your wish list for future SQL Server versions?
CL: Since PowerShell works wonderfully with SMO, the functionality Microsoft can build in is limitless — but commands have got to be built. So my answer is: more commands! PowerShell for SharePoint, Exchange and Skype for Business each have hundreds of commands; last time I checked, all of them had 700 or more. I’d like to see the same number or more for SQL Server. And I’m really itching to see the SqlServer module open sourced and placed on GitHub.
TECHGENIX: Great, thanks! How about ending by telling us how we can learn more about PowerShell in SQL Server 2016? Are there any good resources out there you can point to that we can learn from?
CL: Microsoft doesn’t really have official references yet that I’m aware of, but Aaron Nelson wrote a few articles on SSC that are great for getting started. My favorite one is this one: Backing Up SQL Server Databases is Easier in PowerShell than T-SQL. And here are the rest of Aaron’s articles.
TECHGENIX: Thank you! We’ll hopefully get you to show us firsthand some of the things you can do with PowerShell in SQL Server 2016 in the near future, as that’s one technology area we haven’t delved very deeply into yet in our TechGenix articles. But with the growing reliance upon Big Data and data mining in our cloud-driven technology world, it’s important that we know and understand Microsoft’s own contributions in this technology field, especially in the area of automation. Meanwhile, I need to dig out that manual so I can figure out how to program my new toaster using PowerShell…
Photo credit: Bing Images
