Training

PowerShell for DBAs training class

Register: Contact to Register
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

What you can expect to learn from this 3 day class.

  • How to Install/Configure PowerShell for use with DBA tasks
  • How to Install/Load PowerShell modules and in particular DBAtools and SqlServer
  • How to Create a database with PowerShell and SMO
  • How to Build reusable functions for your environment
  • How to make changes to SQL Server and SQL Databases
  • Quick Backup and Restore with PowerShell with the many different ways to do this

There are labs on each of the 3 days that will get you from Zero to Hero in the PowerShell for DBAs space.

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 2017, 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.

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
  • Loops to iterate over collections

Lab 1: Set up Environment and Test Commands

In this lab, we set up the VM to allow commands to be run later. Setup Execution Policy, get Help set up. Prepare the environment to accept installs of modules from the PowerShell Gallery. We will set up your PowerShell Profile to allow you to auto-load assemblies and modules we will use in the class.

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
  • Custom Objects and their uses

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 more 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

Lab 2: Set up and use PowerShell Modules and SMO

Installing Help, DBAtools and the SQL Server module. Testing functions and learning about the SQL Server Provider. This is about building a routine in using PowerShell daily, not just one time. We go through commands in each module and how to take advantage of all the power in the modules we have at our fingertips. We will build a function to create a database and talk about reusability. We will use this function to create many database and change their configuration using PowerShell and SMO.

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

Lab 3: Gathering Information using PowerShell and Scheduling

Gathering information for trending is one of the DBAs most important tools you can use. We set up a gathering script to ensure that data flows from source to destination. Using SQL Agent we schedule it to run and learn of the alternatives of SQL Agent to get things running behind the scenes.  We also go through PowerShell Remoting to show the reasons we use Remoting to get more done.  This is the concluding lab for the course.

Menu