Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Interview Questions for Hiring PowerShell Database Developers


By:   |   Last Updated: 2015-04-09   |   Comments (2)   |   Related Tips: More > Professional Development Interview Questions Developer

Problem

We're looking to hire a database developer with PowerShell skills. We want to know: what are some good questions to ask to identify good candidates?  Can you provide any suggestions?

Solution

PowerShell, like other scripting languages, offers a convenient approach to solving problems with less code than some alternatives, and can quickly access the .NET library to create enhanced solutions. In many cases, companies can use PowerShell as an alternative to other languages, or can leverage its ability to simplify automation. For instance, using PowerShell with TeamCity for builds, or using PowerShell with Azure or SMO for management, can enhance T-SQL scripts and save developers countless hours. From my experience working with startups and interviewing candidates, the below list provides good starting points to identify strong PowerShell candidates.

Who should do the interview?

This article, Why You Must Lie On Job Interviews And What You Must Lie About, provides one of the best reads when finding talent (I do not agree with the title, but I understand why he advocates the title's position). When I get asked about how to find good PowerShell candidates, one issue that arises is an excellent candidate will interview, yet later I'll hear they didn't get the offer and the reason is a person, who is not a developer, decided against it. While companies can make their own decisions, asking for help to locate a candidate, and then rejecting the candidate because [Department X] feels funny may be the reason companies can't find strong talent.

I think Mark Stevens, the author of this article, makes a excellent case that the people who know the current problem the company is facing should be the ones conducting the interview and making the decision.

Question 1 - Write a function

I use this to determine if the candidate will write code that is re-usable and readable. If I'm speaking to the person over the phone, I can get a sense if they've written a function. In some cases, procedural code will work, but I prefer candidates know how to write and use functions, as it will save both them and other developer's time. When I'm speaking on the phone, I don't expect them to list every semi-colon; the key is structure.

For instance, a candidate responding with "I'd write with Function, then its name, like Add-ToTwo with the function accepting an integer parameter, then within the code adding the parameter to two and returning the result" is an acceptable phone response.

Question 2 - Show me some of your code

In the world of open source, especially with sites like GitHub, Bitbucket, MSSQLTips, and other online repositories, candidates can show their code. I recognize that some candidates may be building tools on their own and want to keep their code private, however, I recommend that everyone keep some code public, as it demonstrates ability, as well as problems they have solved. This also saves candidates and interviewers a ton of time; I can pick some of the code that the candidate has written and ask about it, such as why he or she chose to write their function a certain way, or why they used one logical approach over another approach.

The key here is not that their code is perfect, but that they understand what they're doing. Three of the largest tech disasters I've seen, which involved PowerShell, originated from employees copying and pasting code from the internet without any understanding of what it did. When I asked two of them why they chose the function, they responded that it was the solution that was offered. The internet can be helpful, but understanding what the solution does is required.

Question 3 - Add-Type and using libraries from .NET

Another thing I have asked is whether they have used Add-Type and how. One of the key PowerShell functions, Add-Type, is a must for candidates. This can save a massive amount of time coding, in addition to allowing candidates to use the .NET library. For instance:

###  This is using the SqlConnection class from System.Data assembly$scon = New-Object System.Data.SqlClient.SqlConnection

###  This is using the SMO library
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

While PowerShell one-liners can be great tools, they can come with limitations, and in some cases, developers may need a one-line function that doesn't exist yet. With .NET, developers can create their own functions that save time and which can be re-used in other processes. Also, when we find ourselves using the same approach to a problem, such as a function that can connect to an API, we may find that compiling a dll and using it saves time, as well as allows us to easily make the changes we need. Like the SMO library, there is little need to build a dll that's well written and many helpful libraries exist on open source websites that we can use for a variety of problems. In addition, some code may not need to be re-written and in some cases, we can write PowerShell commandlets around the existing dlls.

Question 4 - Debugging

I will give a candidate a function, which intentionally is written wrong, and let them identify where there will be errors. They do not need to solve the problem depending on the function's simplicity, but they need to identify where the error started. One observation I've made about some PowerShell candidates is that many of them don't know where to start on errors, and knowing where to start is key to solving a problem. Debugging consumes the most time for developers, so understanding where to look can reduce the time it takes to solve a problem.

The below code is an example and the key is that they know what started the problems (PowerShell outputs 2 errors). Also, try-catch's can be useful tools, but if it prevents developers from identifying problems quickly while developing, so it's best to wrap code with them post verifying functionality.

Function Execute-SQL {    Param (
        [string]$server
        , [string]$database
        , [string]$commandtext
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        ###  Note that a connection string property isn't set, and PowerShell will throw an error when it tries to open a connection
        
  $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        $cmd.CommandText = $commandtext
        $cmd.CommandTimeout = 0
        
  $scon.Open()
        $cmd.ExecuteNonQuery() | Out-Null
        $scon.Dispose()
        $cmd.Dispose()
    }
}

Execute-SQL -server "OURSERVER\OURINSTANCE" -database "OurDatabase" -commandtext "CREATE TABLE tb_TestTable (TestID INT) INSERT INTO tb_TestTable VALUES (1)"

Using the above miss-written function as an example, the developer would be expected to identify where he or she would begin looking for the solution, and because of the function's simplicity, that there is no set connection string. The error, "The ConnectionString property has not been initialized" tells us why the current connection is closed. In addition, with this simple example it even tells us what we need to do - set our connection string property to our object $scon:

$scon.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=true;Connection Timeout=0"

Question 5 - Solve a real problem

If the candidate does well on the above questions, I give them a test that relates directly to the position, and will involve a portion of what I expect the person to do. As an example, if I'm hiring a DBA primarily for performance or maintenance, I offer a test that involved doing a piece of maintenance with PowerShell, or automated performance work. The two advantages here for both the candidate and the company are that the candidate develops a good feel for what's currently challenging the company, and the company can see the potential in the candidate. Asking the candidate to "build a website" when the position is for designing OLAP cubes makes very little sense, yet quite a few companies have challenges unrelated to the actual position.

Most companies hire candidates because they need their problems solved and the most effective way to measure a candidate is to test them on their ability, especially as it relates to the position. Asking candidates about a loop is less meaningful than seeing how they solve problems (and if they can). This returns us back to the top of this article, where the person familiar with the position needs should design the test questions.

MSSQLTips features many solutions to technical problems using PowerShell that I suggest looking through to get an idea for a test.

Next Steps
  • Identify what problems need to be solved in the current environment. What solutions will you need to those problems? Measure the candidates according to those needed solutions.
  • If looking for test ideas, look through these helpful tips.


Last Updated: 2015-04-09


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, April 13, 2015 - 9:27:55 AM - Tim Back To Top

Thanks Jeff; that's an excellent question to ask as well.


Thursday, April 09, 2015 - 5:34:30 PM - jeff_yao Back To Top

I like the first two links in your article.

As for interview questions, I will add "Do you follow any PowerShell MVPs either via twitter/linkedin or RSS?" 


Learn more about SQL Server tools