PowerShell Learning for the SQL DBA — Part 1 of 45 min read

In years past I have attempted to learn PowerShell numerous times.  Usually I just hack together a solution to a current problem I am trying to solve.  I never really sat down and learned the fundamentals.  As my SQL environment grows and interesting requests come in from management and other internal partners I see the big picture of how PowerShell can help complete some of these requests faster than putting together a TSQL equivalent.  And not to mention an ongoing project of DR and having certain objects scripted out and handy in the event of a disaster situation.  When I ran across a tool in the middle of 2016 that makes it easy for the non-PowerShell DBA to start easily taking advantage of PowerShell I was hooked.  I have been obsessed with this tool ever since.  I have been able to provide some assistance on small tasks relating to the tool.  So with all of this said I have seen the light and am actively learning and working with PowerShell daily, whether for a work situation or by helping with the DBATools (t)project.

DBATools

The magic that is DBATools should interest more SQL DBA’s in to the world of PowerShell.  If not to contribute to the community, but to make your work more fun and improve your efficiency.  There are some functions in DBATools that you cannot even do with TSQL. (That I have found anyway)  Others are you can just drop it in and connect it to all of your instances in one command and find things like are you using the right memory values.  Are your instances using Instant File Initialization or Lock Pages in Memory.  Within seconds you can run a command against all of your instances and know the answer.  DBATools started as a SQL Migration tool and has exploded in to so much more.  I have recently started working on a migration project from SQL 2012 servers to SQL 2016 and DBATools will play a huge roll in this migration.

 

PowerShell Book

Part of my 2017 goals is to learn PowerShell and contribute to DBATools.  On January 16, 2017 I started reading Learn Windows PowerShell in a Month of Lunches.  This book is great.  It takes the fundamentals of PowerShell and breaks it in to 30 understandable chunks.  I started strong with 7 days in a row and then life and work got in the way.  I have made it to chapter 14 in a little less than a month and a half.  I have read a little further in to the chapters, but have not had time to re-read and complete the labs.  So maybe for me it will be Learn Windows PowerShell in 3 Months of Lunches…

 

Below are three quick examples of what I have been able to complete recently where I was able to utilize what I have learned about PowerShell and DBATools. (Some took help from some very smart people)

 

Create an Audit table for Quarterly Access Review

We currently have a process that one of our security team members runs quarterly to review access to our primary SQL systems.  This is an SSRS report that is executed and then manually reviewed by a department manager.  This is tedious and time consuming for all involved.  Our company recently purchases an access management platform that allows user accounts be put in certain groups based on their department and job function and then the proper permissions will be granted to their account.  So this system needs to consume the data in a row/column format and not report format that the current process builds. So I began investigating what DBATools could do for me.  After searching the commands using Get-Command I found Get-DbaRoleMember.  This function allows me to get Server Level and User Level permissions with one command.

Add in the Out-DbaDataTable.

Then finally the Write-DbaDataTable combined with Out-DbaDataTable I was able to output the results to a SQL Sever table.  Now I have all my permissions stored in a table and with the correct TSQL supplied to the third party system I can pull the correct permission information.  TaDa!!!

 

Check if Instant File Initialization and Lock Pages In Memory are enabled

We recently did some troubleshooting around a memory issue on one of our product servers.  One of my colleagues read an article that referenced Lock Pages in Memory.  He said do we have this set?  I said give me a minute.  I put together a PowerShell cmdlet that will search all of our servers using CMS and tell me what the Lock Pages In Memory and Instant File Initialization settings are configured to.  TaDa!!!

Find Orphaned Data Files on File System

I saw this command when reading the help and website when I first ran across the tool.  Thinking wow, that is an interesting idea.  So I quickly ran Find-DbaOrphanedFile against one server.  And low and behold I had a file on the File System that was not attached to a SQL Database.  So again I expand that to all SQL Instances and found about 300GB of data and log files that were still on the File System that had no corresponding SQL Database. Bam!!!

 

Conclusion

I am well on my way to getting the fundamental skills of PowerShell under my belt as well as incorporating what I have learned in to my day to day SQL DBA job.

What I have shown of the DBATools is barely touching the surface.  There are more advanced techniques of pipelines and stringing multiple functions together to make things happen.

I am so excited about what I am doing now and the future enhancements that I might be able to introduce into my environment.