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. Most technologists learn things by doing so there are several labs included in the course. This technology can also be learned by seeing and then practicing with the tools provided that you 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:
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.
Instructor: Ben Miller (Click for bio)
Listed below are some verbatim quotes from recent attendees of this class:
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:
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:
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:
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:
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:
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.
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:
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:
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.
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:
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:
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:
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.
If you have any questions not answered by our F.A.Q., please contact us.