PowerShell for SQL Server Administration
2h 1mIntermediate2019-06-21
Authors
Denny Cherry
Owner and principal consultant of Denny Cherry & Associates Consulting
Course details
Since SQL Server database administrators are typically charged with managing multiple machines, PowerShell—which enables administrators to manage large numbers of servers—is an especially valuable tool to master. In this course, instructor Denny Cherry shows SQL Server administrators how to use PowerShell to automate server management. Discover how to work with PowerShell to create objects, populate and delete data from tables, and remote to other servers. Plus, see how to loop constructs in PowerShell and make data available for transfer to third-party platforms.
Learning objectives
When PowerShell can be particularly useful
Connecting to SQL Server from PowerShell
Closing connections
Creating objects in PowerShell
Creating tables, views, and procedures
Querying data in PowerShell
Using loops in PowerShell
Exporting CSV files and XML data
Learning objectives
When PowerShell can be particularly useful
Connecting to SQL Server from PowerShell
Closing connections
Creating objects in PowerShell
Creating tables, views, and procedures
Querying data in PowerShell
Using loops in PowerShell
Exporting CSV files and XML data
Skills covered
PowerShellIT AutomationDatabase AdministrationDatabase ManagementPersonaNetwork and System AdministrationMicrosoft
Concepts
Introduction
- Manage SQL servers with PowerShell
- What you should know
- Download tools
PowerShell in SQL Server
- PowerShell won't solve all problems
- When PowerShell is useful
- Differences between the two modules
- Open-source solutions
Connecting to SQL Server from PowerShell
- Declaring objects
- Connecting to SQL Server
- Parameters in PowerShell
- Closing connections
- Disposing connections
- Object reuse
Creating Objects in PowerShell
- Creating databases
- Creating tables
- Creating views
- Creating procedures
Querying Data in PowerShell
- Inserting data into tables
- Returning data from SQL Server
- Running stored procedures in PowerShell
Looping Through Servers in PowerShell
- Loops in PowerShell
- Storing servers for PowerShell
- Concepts of threading
- PowerShell threading commandlets
- Authentication and threading
- Piping between objects
- Exporting CSV files
- Importing CSV files
- Exporting XML data
- Importing XML data
PowerShell Remoting
- What is remoting
- Removing over HTTP ports
- Enabling remoting via PowerShell
- Enabling remoting via GPO
- Starting a remote session
- Ending a remote session
- Using alternate credentials for remoting
- Invoking a remote command
Conclusion
- Next steps