2016-05-18

PowerShell And SQL Server: An Interview With Chrissy LaMaire

SQL Server and PowerShell

PowerShell is a tool I’ve not taken full advantage of. I’ve used it to write a few scripts here and there. As a DBA, I’ve been able to do most tasks with T-SQL and that’s been my preference for a long time. My perspective may be shifting a bit, though. I’m still not writing PowerShell scripts, but I’ve been following a few PowerShell gurus and seeing how they use it and what SQL tasks they use it for.

After reading an article on SQLServerCentral.com, my interest was further piqued. It was an interview of Ken Van Hyning by Chrissy LaMaire and Aaron Nelson. I was more curious than ever about the tools, the tech, and the PowerShell community. I asked Chrissy if I could interview her, and she graciously agreed.

For those of you that don’t know her, Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.


[Dave]: How did you first get started with PowerShell? Were there specific problems you were trying to solve?

[Chrissy]: I got started back in 2005 when I was first introduced to it at Microsoft's Professional Developers' Conference in Los Angeles. I’m a huge fan of scripting and, at the time, mostly worked with VBScript and T-SQL. Learning PowerShell was challenging. I kept wanting to put it in a VBScript/T-SQL box.

It wasn't until 2011 when I started using PowerShell within SharePoint that it finally clicked (you can see the beginnings of my journey on my blog). Mostly, I was trying to use PowerShell as a workaround for a security requirement that prevented the domain I was using to enable Replicate Directory Changes, which was a new requirement for SharePoint. As for PowerShell and SQL Server, that came at a much later time. Initially, I was put off by PowerShell for SQL Server because it seemed terribly slow and as a DBA, I like things to perform quickly. I began by using PowerShell and SMO to help me with a migration project and discovered that PowerShell and SMO together are actually fast, fun and useful.

[Dave]: Is it fair to say your use of PowerShell with SharePoint was out of necessity? How did that make you feel--having to learn a new technology?

[Chrissy]: No, it wasn't a necessity. I was eager to work with PowerShell and SharePoint had nearly 500 cmdlets that I could explore. In PowerShell, cmdlets are well-developed commands that offer a lot of functionality. I know it's been a challenge for the SQL Server community to see the value of PowerShell cmdlets, one reason for that is that we have so few to work with. As of today, that number hovers around 49 in SQLPS, while Lync, SharePoint and Exchange have upwards of 700 cmdlets in their modules. Fortunately, Microsoft has now prioritized PowerShell support in SQL Server, so we should be seeing some cool things soon.

I love learning new things and was eager for that ah-ha moment with PowerShell when I woke up and could code with ease. That day came for me in September of 2011. Since then, I've used PowerShell to automate everything. I even used PowerShell to write a Twitter bot that checks for items on Microsoft Connect that have recently been marked Closed as Fixed. When it comes to SQL Server, I've automated some really cool things like regaining lost administrative access with Reset-SqlAdmin. Or performing entire SQL Server instance migrations with the Start-SqlMigration. Soon, I plan to make a cmdlet that helps DBAs easily regain access to SQL Servers with misconfigured Logon Triggers.

[Dave]: You and Aaron Nelson recently interviewed Ken Van Hyning from the SQL Tools team at Microsoft. One of your questions was about the possibility of a "Script Action to PowerShell" feature in SSMS. If that became a reality, and there was an expanded number of SQL Server cmdlets, do you think more SQL Server professionals would get on board with PowerShell?

[Chrissy]: Oh, absolutely. Ultimately, SQL Server Pros need to see what value PowerShell brings. Aaron and I are working on a number of demos that are full on solutions, and not just "Look! You can get a list of databases with PowerShell!" And? Now what? Well, you can use that list to visually select a few databases to migrate to a new server, as seen in this 20 second clip on YouTube. You can also do things like use PowerShell to find large indexes, and move them to empty filegroups. Or drop unused indexes. Right now, the PowerShell/SMO syntax for doing this in PowerShell wouldn't be worth it for most DBAs, but once Microsoft adds Alter-SqlTable to the SQLPS module, it will be a no-brainer. And I know this can be done in T-SQL, but then you have to have the stored procedure on every SQL Server that you want to run this on. With PowerShell, it's more like SSMS. You can install the module on just one workstation, and then use that to manage hundreds of servers

[Dave]: It's natural to have bias toward the tools and technology we know, which can lead to spirited debate. Most of the time, it’s friendly and thoughtful. I’ve been getting a sense of "us and them" regarding T-SQL vs PowerShell. Do you get that sense too?

[Chrissy]: Yes, which has been pretty surprising to me. As a PowerShell MVP, it sometimes feels like fellow DBAs may see me as an invader of SQL territory, when in fact, I've been a DBA for 17 of the 20 years that I've been in IT. I even updated my Twitter profile to make it clear that I’ve been a SQL Server DBA since 1999. I believe this issue will resolve itself as DBAs begin to see how PowerShell can make their jobs way easier. I'm also hoping that my SQL Server Migration script, which has no T-SQL (or even C#) equivalent, will be as persuasive as it is useful.

I remember reading an article in SQL Server Magazine back around 2002 that made the case for DBAs to learn T-SQL and other scripting languages. My first thought was "Wait, there are DBAs that don't know T-SQL?" I always thought T-SQL was part of the job description, and it's the same now with PowerShell. This belief was further enforced by the fact that when I was getting started with PowerShell and SQL, Simple Talk's Phil Factor and Laerte Junior already had a ton of stuff out there and a few books about SQL Server and PowerShell had already been written. I thought I was late to the party.

[Dave]: What are some deciding factors for you when choosing between T-SQL and PowerShell? Are there any tasks you refuse to do (or insist upon doing) with one vs the other?

[Chrissy]: When deciding between two of my favorite things, when embarking upon a new project, my decision tree goes like this:

  1. Do I use T-SQL or PowerShell?
  2. If PowerShell:
    • Do I use SQL Management Objects (SMO) which requires SSMS or a separate download?
    • Or do I use native .NET classes such as SqlConnection, SqlCommand and SqlBulkCopy?
I suppose if I'm doing an administrative task, I'll default to PowerShell since it's likely I'll want to perform that task on all of my servers and not just one. But then sometimes within PowerShell, I have to use T-SQL. This is true for Copy-SqlLogin, where SMO totally fails me by changing the password and disabling the login on the destination. There are some tasks that I refuse to use PowerShell for, at least at this time. When it comes to regular maintenance and backups, I use Ola Hallengren's T-SQL scripts across the board. In my opinion, that type of maintenance belongs in SQL Agent, and I don't like the way SQL Agent currently interacts with PowerShell. Even if they did work well together, I'd keep using T-SQL because Ola is constantly updating and tuning his scripts and T-SQL for this task just makes sense. Now, I do use PowerShell to deploy Ola's scripts to new servers and check to see if any of its jobs have failed. So really, many of my projects use both, and I imagine it will always be that way.

[Dave]: DBA's come from all walks of life. Some of us started out as developers. As for the others, do you think it requires a developer's mentality to use PowerShell or SQLPS?

[Chrissy]: I'm more of a scripter and systems person (Windows/OS X/Linux, you name it). In my experience, that's true of a lot of the DBA's that I've met. Ultimately, I think that a DBA who writes T-SQL is the same DBA who will eventually write PowerShell scripts if they haven't started already.

[Dave]: Speaking as a former developer, if there is a task that is a good candidate for PowerShell, I gravitate toward writing my own solution with C#. The Visual Studio IDE is comfortable and familiar, but you could make the argument that it's become a crutch. Are there lots of other former developers that fit this description?

[Chrissy]: I've never considered myself a developer. I've only written straight up C# code 2-3 times to test the performance of a console application vs. PowerShell and it was painful because I was so slow at it. I use Notepad++, which drives me crazy sometimes, but my brain refuses to use other IDEs and ISEs even though they'd make me even way more productive. I’m stuck in my ways with Notepad++ so I understand what you're saying. I used VBscript for years after I was introduced to PowerShell, but then I moved forward. I also upgraded my spinning disks to SSD and got rid of that old LCD in favor of a 27-inch Thunderbolt display so that I can Notepad++ really well. I'm totally conflicted ;)

[Dave]: Who is the target audience of PowerShell?

[Chrissy]: Anyone in IT. From Help Desk to Engineers to Developers and Data Center architects; I see it being used by such a wide audience. Every person in my team uses PowerShell in some capacity. From the Exchange guy, to the AD folks. The vSphere team. The storage admins use it extensively, and so do our SharePoint developers and administrators. That's one of the things I love about PowerShell, once you learn to use it, you can use it to improve your entire IT experience. Even as a hobbyist, I use PowerShell to post to Facebook and Twitter. I've even used to help manage my Spotify playlists and I've used it to ensure I always know my home's external IP. Playing with the CloudFlare API in PowerShell is always fun.

[Dave]: Any last thoughts?

[Chrissy]: Yes! I just want to reemphasize that while SQLPS used to be something that even I didn't want to use, the future of SQLPS is looking really bright. For those of you who didn't hear the news, 3 big issues with SQLPS were fixed in the March 2016 Refresh. Namely

  1. It now loads quickly, in less than 1 second
  2. It does not change your path to SQLSERVER:\
  3. It no longer complains about unapproved verbs, so the -DisableNameCheck switch isn't necessary.
In addition, not only has Microsoft assigned a dedicated engineer to SQL PowerShell, they've been extremely responsive on Connect, Twitter and now Trello. This is true for both SQLPS and SQL Server Management Studio. They are actively encouraging us to vote and give them suggestions, and we're already seeing some wins. It looks like June is going to be an extra exciting month, not only for SQL Server, but for SQL Server and PowerShell.

[End]

Thanks for reading, everyone. I hope you enjoyed the interview.


SHARE