PowerShell Interview Questions for SQL Server DBAs

 

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

2. Start / stop a SQL Server service

Stop-Service -Name MSSQLSERVER -Force; #note -name parameter value is case-sensitive.
Start-Service -name MSSQLSERVER;

3. Find the SQL Server version / edition including the service pack level

$svr | select version, edition, productlevel;

4. Find the SQL Server operating system information such as the OS version, processor number, physical memory, etc.

$svr | select OSVersion, Processors, PhysicalMemory;

5. Find the largest SQL Server database by size

$svr.databases | select name, size | Sort-Object -Property size -Descending | Select-Object -First 1;

6. 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

7. 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;

8. 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;

9. 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 }

10. 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;

11. Find all SQL Server Agent Jobs that failed during the last execution

$svr.JobServer.jobs | where lastRunOutcome -eq 'failed' | select name, lastrundate, lastrunoutcome

12. 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.