IEPS: Immersion Event on PowerShell for SQL Server DBAs

(Retired – no longer offered as a public class)

Overview

This 1-300-level, 3-day training class was created to teach database management using PowerShell. Developed by Ben Miller, the training is focused around instruction and demos. A hands-on approach is also possible if desired. Most technologists learn things by doing but this technology can be learned by seeing and then practicing with the tools provided that you can take with you. The scripts and tools will be immediately usable when you get back to the office, so bring your learning hat and get ready to PowerShell.

At the completion of this training you will have been exposed to the following:

  • Installation and configuration of PowerShell
  • Use of the ISE that comes with PowerShell
  • Shared Management Objects (SMO) for SQL Server
  • Programming concepts in PowerShell
  • Modules and scripts to manage SQL Server
  • Gathering data for analysis using PowerShell both with SMO and T-SQL
  • Repositories that contain scripts that you can leverage in your daily work

Prerequisites: No experience with PowerShell is required as we will cover the basics from beginning to becoming proficient in connecting to and managing a SQL Server and its databases. It is helpful (but not required) that students possess some knowledge of and experience with scripting languages or coding in general and T-SQL counts. SQL Server knowledge / experience will be more helpful than experience and knowledge of PowerShell.

Bring Your Own Laptop: You can bring your laptop but it is not required. You will be given a copy of the scripts used as well as all the information discussed in class. If you bring your laptop and want to participate, you should have SQL Server installed on your laptop. It can be any version from SQL Server 2012 to SQL Server 2016, and Express Edition will work for the scripts we will use. Note that there will not be a classroom computer provided for you.

Target audience: Database professionals or Dev Ops professionals that want a way to scale management of database servers and databases.

Instructor: Ben Miller (Click for bio)

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.


Curriculum

Module 1: PowerShell Environment

DBAs are very accustomed to the SQL Server environment but find that the PowerShell environment is a mystery. Understanding the PowerShell environment is important as a foundation to build on. Topics covered include:

  • PowerShell versions
  • Preparing your environment
  • Enabling the abilities of the Shell

Module 2: Commands, Cmdlets and Modules

PowerShell appears as a shell but has a vast set of commands that are built-in. There are a plethora more commands available from modules and scripts in the Community, Microsoft, and many 3rd-party vendors. Topics covered include:

  • Finding commands
  • Cmdlets explained
  • Modules explained
  • Functions explained
  • Repositories

Module 3: Environment Configuration

There is the Windows PowerShell environment that is provided by Windows and then there is the PowerShell environment of yours when you launch the shell. Knowing how to configure “your” environment will be a key in keeping the amount of work upon entering the PowerShell environment in your everyday work. Topics covered include:

  • Execution policy
  • Profiles
  • Which shell to use
  • Remoting configuration

Module 4: Assemblies in PowerShell

Windows PowerShell is built on top of the .NET Framework and assemblies are a key part of it. Understanding the role assemblies play in your environment is important to leveraging the full power of PowerShell. There are built-in assemblies that are there by default and then you have the option to use you own or others from the Community. Topics include:

  • What are assemblies and why do I care?
  • Where are assemblies?
  • Loading assemblies
  • Using objects in the assemblies

Module 5: PowerShell Fundamentals

Environment aside, PowerShell has its own syntax that you will use to get things done. Having this knowledge puts you in control of your destiny. Focus will be on learning the behavior of these elements to understand how you will use them. Tips and tricks about these items are also important to getting the most out of your scripts. Topics covered include:

  • Strings
  • Commands and parameters
  • Variables
  • Dot sourcing
  • Importing modules
  • Syntax fundamentals
  • The pipeline
  • Aliases

Module 6: SQL Server PowerShell

With the foundation understood, it is time to connect that knowledge to your DBA/SQL Server knowledge to complete the circle. Understanding how PowerShell interacts with SQL Server and the objects required will ensure your success in integrating PowerShell into your DBA roles and responsibilities. This is very important since PowerShell needs some help to connect and manage SQL Server objects as they are not built in to the shell. Topics covered include:

  • Installation and setup
  • SQL assemblies (SMO)
  • SQL Server connectivity
  • Objects, methods, and properties

Module 7: PowerShell for the DBA Starter

Combining knowing and doing are absolutely necessary to become a PowerShell DBA. Building blocks are essential to getting started with managing SQL Server with PowerShell. Managing SQL Server at scale means that the more you rely on clicking your way through solutions, the less you can do in less time. Management Studio is a great tool and it’s powerful, but sometimes you just need a little bit more help, with a tool that can run without your eyes constantly on it. Topics covered include:

  • Disk space on a server
  • Altering database properties
  • Modifying files
  • Altering configuration options
  • Finding information about your SQL Server environment

Module 8: Gathering Information

In the real world we are not fighting problems all the time. Sometimes it feels like we are, but there are times that you need to be proactive in managing SQL Server to stay ahead of disaster. Clicking your way through information is good for a quick glance, but getting information that you can use to avert problems is a great thing. There are a few ways to do this and PowerShell makes it easy to gather and store information from SQL Server for use in proactive DBA work. Topics covered include:

  • Database object information
  • Performance counters
  • Engine and environment information
  • Methods of gathering information

Module 9: Power Tools

Your learning is not complete until we unveil some Power Tools inside the PowerShell environment. Building tools is a skill that should be sought after, but leveraging the talents of others is a very good thing to do in the PowerShell space. There are many tools that others have built, have been tried and tested, and are ready to use. Using these tools gives you a head start into managing SQL Server with PowerShell. Topics covered include:

  • Community modules
  • SqlServer module and SQLPS
  • Build your own module
  • PS remoting

Module 10: Scheduling

Running scripts from a command line or inside an IDE is great, but having to manually run them yourself for each server or database that exists can get to be not only daunting but not very scalable. Automating these scripts to run with a scheduler is key. SQL Server Agent has a job step of PowerShell but it is not as easy as it sounds until you understand how it works. When you have leveraged the power of your scheduler and all the other tools available in PowerShell, you will complete your learning and move into automation and managing SQL Server through scheduled jobs and tasks. Topics covered include:

  • SQL Server Agent
  • Windows Scheduler
  • Other schedulers

Questions?

If you have any questions not answered by our F.A.Q., please contact us.