Pro PowerShell for Database Developers

Pro PowerShell for Database Developers

by Bryan Cafferky
Released October 2015
Publisher(s): Apress
ISBN: 9781484205419

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Pro PowerShell for Database Developers helps you master PowerShell application development by continuing where other books leave off. There are no ""Hello World"" functions here, just real-world examples that get down to business. Develop and deploy database and ETL applications in a reusable framework. Read from any data source and write to any destination. Integrate PowerShell with SQL Server.

Pro PowerShell for Database Developers shows how to dive into the PowerShell environment and customize it to your needs. Learn about CmdletBinding and its use in extending functions with powerful new features. Also learn to package functions into libraries called modules that are loaded automatically on demand.

PowerShell is about automation and getting work done when you’re not around to perform it. To that end you’ll see how to schedule PowerShell applications using SQL Agent and the scheduled job cmdlets. And for when you are around to run automated processes in person, you’ll learn to add a Windows GUI to your applications to give them that professional polish.

Other exciting topics include:

  • Creating objects with custom methods and properties and extending them with PowerShell-style inheritance.
  • Implementing function polymorphism using parameter sets.
  • Using PowerShell as an ETL tool surpassing even SSIS.
  • Creating distributed, interruptible, and multithreaded programs using PowerShell's workflow engine and the .Net Windows Foundation.

    • All these topics are explained using real-world examples encapsulated into modules you can immediately put to use in your organization. You’ll reap even more as you come to grips with all that PowerShell can do. Begin the journey toward deep expertise and amazing productivity. Buy and read Pro PowerShell for Database Developers today.

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Dedication
    5. Contents at a Glance
    6. Contents
    7. About the Author
    8. About the Technical Reviewer
    9. Acknowledgments
    10. Introduction
    11. Chapter 1: PowerShell Basics
      1. What Is PowerShell?
        1. Why Is PowerShell Important?
        2. History of PowerShell Versions
      2. Starting PowerShell
      3. The Command Line Interface (CLI)
      4. The Integrated Scripting Environment (ISE)
      5. Enabling Scripting
      6. Using the Integrated Scripting Environment
      7. Encountering Script Errors
      8. Summary
    12. Chapter 2: The PowerShell Language
      1. Challenges to Learning PowerShell
        1. Thinking Differently
        2. Just Got to Get the Job Done
        3. Getting Good Examples
        4. The Paradox of Power
      2. The PowerShell Language
        1. A Brief Introduction Using a Script
        2. Operators and Operands
        3. Cmdlets
        4. Variables
        5. Cmdlet Output
        6. Strings
        7. Files
        8. Control Flow
        9. Script Blocks
        10. Arrays
        11. Associative Arrays
      3. Summary
    13. Chapter 3: Advanced Programming
      1. Passing Parameters
      2. Functions
      3. It’s All about Objects
      4. A Real-World Use of PowerShell Objects for ETL
      5. Remember the Pipeline
      6. Pipelining Example: SQL Server Deployment
      7. Formatting Output
      8. Creating and Using COM Objects
      9. Creating and Using .Net Objects
      10. Waiting for a File
      11. Summary
    14. Chapter 4: Writing Scripts
      1. Strict Mode
      2. Error Handling
        1. Using the Try/Catch Block
        2. The Trap Statement
        3. Using Try/Catch and the Trap Statement Together
        4. Setting the $ErrorActionPreference Preference Variable
        5. Using the $Error Variable and the ErrorAction Parameter
        6. More on the $Error Variable
      3. Common cmdlet Parameters
      4. Debugging
      5. Events and Script Blocks: The Perfect Marriage
        1. Using Windows Events
        2. Using .NET Object Events
      6. Using PowerShell Transactions
        1. Conceptual Overview
        2. An Example of How Transactions Should Work
        3. Why Doesn’t It Work?
        4. Setting the Integrated Script Editor (ISE) Options
      7. Summary
    15. Chapter 5: Writing Reusable Code
      1. CmdletBinding Arguments
        1. SupportsShouldProcess
        2. HelpURI
        3. SupportsPaging
      2. Validating Parameters
        1. Validating a String
        2. Validating a Number
        3. Validating an Array
        4. Using the ValidateScript Attribute
      3. Default Parameter Values
        1. Specifying a Default Parameter Value
        2. Using $PSDefaultParameterValues
        3. Default Parameter Values When No Default Is Specified
        4. Distinguishing Parameter Default Values from Intended Values
        5. Validating Parameters the Old-Fashioned Way
      4. Using Parameter Sets
        1. Using Switch Parameters with Parameter Sets
      5. ValueFromPipelineand ValueFromPipelineByPropertyName Attributes
      6. Internationalization with the Data Section
      7. Summary
    16. Chapter 6: Extending PowerShell
      1. The Four Types of Modules
        1. Script Modules
        2. Flexibility in Modules
        3. Module Manifest
        4. Dynamic Modules
      2. Popular Free PowerShell Modules
        1. OData PowerShell Explorer
        2. PowerShell Community Extensions (PSCX)
        3. ShowUI
        4. SQL Server PowerShell Extensions
        5. SQLISE
        6. Windows Automation Snap-In (WASP)
      3. Summary
    17. Chapter 7: Accessing SQL Server
      1. About the Module
        1. Using umd_database
        2. How umd_database Works
        3. Supporting Functions
        4. Calling Stored Procedures
      2. Summary
    18. Chapter 8: Customizing the PowerShell Environment
      1. PowerShell Variables
        1. Automatic Variables
        2. Preference Variables
        3. Environment Variables
        4. Using PS Drives
        5. Aliases
        6. Customizing the Console
        7. Profiles
      2. Summary
    19. Chapter 9: Augmenting ETL Processes
      1. The Functions
        1. Wait for a File
        2. Copy File
        3. Unzip the File
        4. Add the File Name to a Flat File
        5. Move the Files
        6. Send Email
        7. Load the Files
      2. Let’s Process Some Files
      3. More Useful ETL Functions
        1. Data Validation
        2. Combine Files
        3. Add Column Headings
        4. Securing Credentials
        5. Send Secure Email
      4. Summary
    20. Chapter 10: Configurations, Best Practices, and Application Deployment
      1. Configuring PowerShell Applications
        1. Using Environment Variables
        2. PowerShell Variables Scope
        3. A Simple Configuration Approach
        4. A More Advanced Configuration Approach
        5. Taking Configuration a Step Further
        6. Storing Configuration Values in a SQL Server Table
        7. Using a Script Module to Support Configuration
      2. PowerShell Development Best Practices
        1. Function Naming
        2. File Naming
        3. Make Code Reusable
        4. Use Source Code Control
        5. Build In Configurations from the Start
      3. PowerShell Application Deployment
        1. Deploying Script Modules
        2. Deploying Script Files
        3. Deploying Jobs
      4. Summary
    21. Chapter 11: PowerShell Versus SSIS
      1. Introduction
      2. Advantages of PowerShell as an ETL Tool
        1. Pre-Installed and Free
        2. Supports Interaction
        3. PowerShell Goes Where SSIS Can’t
        4. Reusability and Extensibility
        5. Dynamic Code
        6. Upgrades and Code Dependencies
      3. Advantages of SSIS
        1. Performance
        2. Visual Development Interface
        3. SQL Server Integration and Configuration
        4. Built-In Components
      4. The PowerShell ETL Framework
        1. Mapping
        2. The Northwind Company Use Case
        3. The Products Table Load
        4. The Order Table Load
        5. The Customers Table
        6. Packaging Up the ETL Job
      5. Summary
    22. Chapter 12: PowerShell Jobs
      1. PowerShell Jobs
        1. Running Background Jobs
        2. Scheduled Jobs
        3. Scripting with the Job Cmdlets
      2. Using PowerShell with SQL Server Agent
      3. Accessing SQL Server Agent Jobs from PowerShell
      4. Summary
    23. Chapter 13: PowerShell Workflows
      1. PowerShell Remote Execution
        1. Configuring Remote Execution
        2. Using Remote Execution with Invoke-Command
      2. Workflows
        1. Parallel Execution
        2. Sequence
        3. Foreach –Parallel
      3. ETL Workflow
        1. The Workflow Code
        2. Calling the Workflow
        3. Register the Events
        4. Sending Mail
        5. Workflow Creation Steps
      4. A Poor Man’s Hadoop
        1. Setting Up the Poor Man’s Hadoop Workflow
        2. Calling the Poor Man’s Hadoop Workflow
      5. Summary
    24. Index

    Product information

    • Title: Pro PowerShell for Database Developers
    • Author(s): Bryan Cafferky
    • Release date: October 2015
    • Publisher(s): Apress
    • ISBN: 9781484205419