PowerShell Interview Questions for SQL Server DBAs

By:   |   Updated: 2016-06-08   |   Comments (10)   |   Related: More > Professional Development Interview Questions DBA


Problem

With PowerShell playing a more important role in administration of various types of servers, more SQL Server DBA candidates should have PowerShell knowledge and skills. But I still do not see many good PowerShell interview questions targeting DBA candidates, can you provide some questions?

Solution

PowerShell (PS) is rich and deep in content itself, and it is almost impossible to come up with a comprehensive question list to check a candidate's knowledge on every aspect of PS. But in the context of DBA work, we may come up with some good questions to test a DBA's PowerShell knowledge.

I personally do not like to ask questions where the answer can be memorized, such as "what is a PowerShell cmdlet?", "What is an advanced PowerShell function?", etc. Instead, I'd prefer to ask "hands-on" questions such as "Write me a function to display disk size and its free space", "write me a script to read the SQL Server error log and report any entries with words like 'Error xxx occurred'"?, etc.

The questions listed below are designed with two simple rules:

  • Questions should be related to common SQL Server DBA tasks and are simple to complete in a few lines of PS code
  • No Books Online (BOL) is needed as you are expected to find the necessary information via PS self-exploring capability, i.e. get-member

There is one requirement though, the answers should not use T-SQL, such as in this way, invoke-sqlcmd -query "t-sql-code".

Assume SQLPS module (in SQL Server 2012+) is installed and also assume all operations are on a local server. This way, we can prepare a stand-alone test environment on a laptop for the candidate. No network, internet or Googling needed.

  1. Determine if the SQL Server service or SQL Server Agent service is running
  2. Start / stop a SQL Server service
  3. Find the SQL Server version / edition including the service pack level
  4. Find the SQL Server operating system information such as the OS version, processor number, physical memory, etc.
  5. Find the largest SQL Server database by size
  6. Find any SQL Server databases in full recovery without a transaction log backup in the past 3 hours
  7. Find the largest (by size or rows) 3 tables in the AdventureWorks2012 database
  8. Delete the SQL Server backup files in the D:\Backup folder if the file is older than 7 days
  9. Find any orphaned SQL Server database users (i.e. db users without logins)
  10. Script out a SQL Server Agent Job, based on a specific category
  11. Find all SQL Server Agent Jobs that failed during the last execution
  12. Kill all sessions connected to a SQL Server database

Answers to PowerShell Interview Questions for SQL Server DBA

Note: each answer is just one of many possible ways to address the question, and you may come up with better answers than the provided ones.

We need to load PowerShell 3.0 with the SQLPS module.  Here is the code:

#require -version 3.0
#we first need to import SQLPS module
Push-Location
import-module SQLPS -DisableNameChecking;
Pop-Location
$svr=get-item SQLServer:\SQL\<your server>\default # <your server>\default means the default sql instance on <your server>, 
                                              # replace <default> with <instance name> if you are interested in a named instance.

 

  1. Determine if the SQL Server service or SQL Server Agent service is running 
get-wmiobject -class win32_service -filter "displayname like 'sql server%'" | select name, displayname, state
# or
get-service -name "*sql*" | select name, displayname, status
  1. Start / stop a SQL Server service
Stop-Service -Name MSSQLSERVER -Force; #note -name parameter value is case-sensitive.
Start-Service -name MSSQLSERVER;
  1. Find the SQL Server version / edition including the service pack level
$svr | select version, edition, productlevel;
  1. Find the SQL Server operating system information such as the OS version, processor number, physical memory, etc.
$svr | select OSVersion, Processors, PhysicalMemory;
  1. Find the largest SQL Server database by size
$svr.databases | select name, size | Sort-Object -Property size -Descending | Select-Object -First 1;
  1. Find any SQL Server databases in full recovery without a transaction log backup in the past 3 hours
$svr.databases | ?{ $_.RecoveryModel -eq 'Full' -and $_.lastlogbackupdate -lt (get-date).AddHours(-2)} | select name, lastbackupdate, lastlogbackupdate
  1. Find the largest (by rows or size) 3 tables in the AdventureWorks2012 database
# Size by row count
$svr.databases['AdventureWorks2012'].tables | select name, rowcount | Sort-Object -Property RowCount -desc | select -First 3



# Size by table storage
$svr.databases['AdventureWorks2012'].tables | select schema, name, @{name='size'; e={$_.dataspaceused+$_.indexspaceused }} | 
Sort-Object -Property size -desc | select -First 3;
  1. Delete the SQL Server backup files in the D:\Backup folder if the file is older than 7 days
dir d:\backup\*.bak -Recurse | where creationtime -lt (get-date).AddDays(-7) | Remove-Item -Force;
  1. Find any orphaned SQL Server database users (i.e. db users without logins)
$svr.databases | % {$_.users | ? {$_.issystemObject -eq $false -and $_.login -eq ''} | select parent, name, login }
  1. Script out a SQL Server Agent Job, based on a specific category
$svr.JobServer.jobs | where Category -eq 'DBA Admin' | % {$_.script()} | Out-File -path c:\temp\DBA_Admin_Jobs.sql -force;
  1. Find all SQL Server Agent Jobs that failed during the last execution
$svr.JobServer.jobs | where lastRunOutcome -eq 'failed' | select name, lastrundate, lastrunoutcome
  1. Kill all sessions connected to a SQL Server database
$svr.EnumProcesses() | ? { $_.IsSystem -eq $false -and $_.database -eq 'AdventureWorks2012'} | % {$svr.KillProcess($_.spid)}

Summary

In this tip, I listed some common SQL Server DBA tasks that may be completed easier with PS than T-SQL. Most answers rely on SMO. Using these questions as an example, you can come up with your own PS+SMO questions, such as doing backup/restore processes, checking user/object permissions, index fragmentation or more complex ones like Replication, High Availability management, etc.

One thing we need to bear in mind based on my experience is that in an unprepared "hands-on" test, the majority of candidates will perform probably only at 70% (or lower) of their normal capacity. So it is unfair to design some complex questions (like you need to solve with 50+ lines of code) and expect a sound solution within a short time.

I consider PS as an administration automation tool/platform, and I believe in the near future, with PS, we DBAs will be able to create a software-defined database administration framework, in which we will customize our administration according to business requirements, but with our personal styles and flavors. With Microsoft starting to switch their product management from GUI based to a Unix-like command line interface (CLI), the line between traditional DBAs and developers will be blurred, and programming skills will be more important to SQL Server DBAs.

Next Steps

You may look the following articles to learn more about database administration in PowerShell way.

   


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-06-08

Comments For This Article




Tuesday, July 18, 2017 - 12:15:48 PM - jeff_yao Back To Top (59584)

@Steffan, thanks for reading my post. 

In Q3, $svr is populated in the first block of the script in this tip. It is not a default variable.

Friday, July 14, 2017 - 2:31:03 PM - Steffan Rhoads Back To Top (59358)

Thank you Jeffery.  Gives me some exercises to do.

In question 1 my answer would be 'get-service *sql*' as your answer, while quite correct, is more than I'd go to the trouble to perform ad-hoc.

In question 3 you use the variable $svr: I am not aware of this being present by default when using SQLPS?

 

Friday, August 12, 2016 - 4:04:54 PM - James Back To Top (43115)

Thanks for the tip. I personally do not memorize PS syntax and do not think it is appropriate for an interview question. We have a wealth of reference materials that we use to write scripts and memorizing such syntax is counter-productive. Instead one should be asked about the strategic scenarios and implementations. From that answer one can reasonable be able to find syntax.  

 

Thursday, June 30, 2016 - 2:26:18 PM - GP Van Eron Back To Top (41800)

Good Article Jeff.  PS has become essential to plaform management (alhough I am quite guilty of abusing Invoke-Sqlcmd)     Makes sense to start including some of these questions in the interview process

 

Thursday, June 30, 2016 - 12:39:46 PM - jeff_yao Back To Top (41798)

Thanks @Riken for your comments.

Yes, PowerShell can do lots of things, but if a candidate is expected to use PowerShell to manage SQL Server, s/he is expected to be familiar with SMO, and as soon as you are at the SMO [Serve]r object level, you are expected to find all rudimentary information based on the DBA's knowledge. For example, login accounts are under [Server], same with database, and [database] has properties, such as size, filegroups etc. So with all these knowledge, a candidate is fairly expected to figure out all these things even if s/he may not touch one or two properties before.

 

As a pure self-taught PowerShell SQL DBA, I'd say the one standing-out beauty of PS is its self discovering capcity. These questions are not "composed" out of nowhere, they are actually a result of logical exploration from my own learning experience due to my DBA real work requirements.

But nevertheless, good discussions will help readers to expand their opinions.

Thanks again for your comments.

  

Wednesday, June 29, 2016 - 11:03:22 AM - Riken Back To Top (41785)

I like to do as much as I can in powershell and have written some pretty complicated scripts. However, if i was to get these questions in an interview I would probably struggle with most of them just because I don't use it all the time. I write a script, schedule it and forget about it because I use it mainly for automation. These questions would be more suited to someone who uses powershell for absolutely everything, which probably wouldn't relate to most DBA's.

I think it would be better to allow someone to use the internet but make the question more in depth so they can show that they know what they are doing. 

Thursday, June 9, 2016 - 12:59:02 PM - jeff_yao Back To Top (41653)

 Thanks Rob Sewell, that is an excellent tip about $srv.killallprocesses($DBName).

 

 

 

Thursday, June 9, 2016 - 4:45:52 AM - Rob Sewell SQLDBAWithABeard Back To Top (41649)

Excellent questions. I am really pleased to see more people using SQL and PowerShell together

 

I would use

$srv.killallprocesses($DBName) to kill all processes

 

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.killallprocesses.aspx 

 

Wednesday, June 8, 2016 - 12:36:17 PM - Greg Robidoux Back To Top (41641)

Jeffrey, congrats on your 25th tip!

-Greg

Wednesday, June 8, 2016 - 5:18:03 AM - Matt Back To Top (41638)

Good stuff!

Personally, the difficulty I'd have doing a test like this is that I've scripted much of this up. For 5 out of the 12 questions here, I've got a function, with aliases. So, for example, to get the service details, I'd type 'sserv servername'.

 

You've reminded me that if I go for an interview, I need to revise what my scripts actually do!!

 














get free sql tips
agree to terms