Skip to main content
main-content

Über dieses Buch

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 Workflow 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.

Inhaltsverzeichnis

Frontmatter

Chapter 1. PowerShell Basics

Abstract
In this chapter we will discuss what PowerShell is and do a quick review of its history. We’ll describe the two PowerShell environments, which are the Command Line Interface (CLI) and the Integrated Script Editor (ISE). The PowerShell CLI is used for the immediate execution of commands entered at a prompt. The ISE is the PowerShell development environment and is intended for the creation and debugging of scripts. By default, PowerShell will not allow scripts to be executed. Therefore, we will discuss how to enable scripting. Then we will discuss using the ISE to write scripts. We’ll review how we can customize the ISE settings to fit our needs. Then we’ll run a script with a bug in it to see how PowerShell displays error messages.
Bryan Cafferky

Chapter 2. The PowerShell Language

Abstract
In this chapter, we will introduce the PowerShell language. We will start by considering some of the challenges faced when learning PowerShell, which can be discouraging. Then we will review a simple script so as to get a feel for the language. From there we will discuss comparison and mathematical operators and logical operands. PowerShell commands are called cmdlets, pronounced command-lets. We’ll discuss Microsoft’s cmdlet naming conventions, then review of some of the most prominent cmdlets. After this, we’ll discuss how to use variables and go over the supported data types. PowerShell has advanced support for string manipulation, and we will review some of these features, including regular expressions, which are used for string matching, and here strings, which allow long string values that include special characters. Then we’ll discuss PowerShell’s control flow statements that support conditional code execution and various looping constructs. We will close the chapter by reviewing PowerShell’s support for arrays and a special object called a hash table. Hash tables are arrays of key/value pairs ideal for code translations, such as a state abbreviation code for the state name. By the end of this chapter, you will have a basic grasp of the PowerShell language.
Bryan Cafferky

Chapter 3. Advanced Programming

Abstract
So far, we have just scratched the surface of what PowerShell can do—many books stop there. This can leave the reader with the impression that PowerShell is fairly limited as a development platform. This chapter will dispel that notion by discussing a wide variety of advanced programming techniques. We’ll start by discussing the use of parameters to support code reusability. This leads into a discussion of the CmdletBinding attribute, which causes PowerShell to add a number of powerful features to the code, including parameter validation, parameter sets, and support for PowerShell’s common parameters. We will then get into a discussion on creating functions so as to maximize reusability. From there we will move on to a discussion of creating custom objects in PowerShell complete with properties and methods. The value of custom objects will be demonstrated by creating a custom object to support ETL. To fully leverage PowerShell we need to understand how to use the pipeline. We will discuss how to write functions that do this with the special process blocks begin, process, and end. PowerShell’s built-in support for customizing output is limited, so we will discuss how we can create highly customized output by using a function that generates HTML. Windows has built-in support for application automation. We will review an example of leveraging this to load an Excel spreadsheet with data from SQL Server. Although we can use the SQLPS module to access SQL Server, this adds unnecessary overhead, so we will show how we can write code to directly query SQL Server tables using the .Net library. There are times when we need to know when something happens to files on the network. For example, when a file is created, we may need to run an ETL job to load it. We will discuss trapping such events in order to execute custom code using the .Net FileSystemWatcher object. Hopefully, by the time you finish this chapter you will be convinced of the wide-ranging capabilities of PowerShell.
Bryan Cafferky

Chapter 4. Writing Scripts

Abstract
This chapter focuses on PowerShell features critical to developing professional code. We will start by discussing the Set-StrictMode cmdlet that protects us from uninitialized variables, incorrect method calls, and invalid property references. Then we’ll discuss how to gracefully handle errors raised when your scripts execute. Error handling leads us into a discussion of common cmdlet parameters, so called, because all the built-in cmdlets support them. Then we introduce the PowerShell Integrated Script Environment’s debugging features. Since PowerShell has excellent support for event-driven programming, we proceed with a discussion of implementing Windows forms and events to provide a GUI for our scripts. Building on this, we show how events can be used with other .NET objects. Then we discuss PowerShell’s implementation of transaction support via a set of special cmdlets. This is an important section, as PowerShell transactions are not what we might expect. Finally, we will briefly discuss the PowerShell ISE options; what they do, and how to use them. It is easy to overlook these features, but they can make development easier.
Bryan Cafferky

Chapter 5. Writing Reusable Code

Abstract
This chapter will cover how to use PowerShell to write the most robust reusable code possible. To do this, we will discuss how to use CmdletBinding in our functions. There are two types of CmdletBinding attributes: general attributes and parameter-specific attributes. The general attributes control how the overall function behaves. The parameter-specific attributes give us control over each parameter, including value validation. When a parameter is not provided, a default value is given. PowerShell defaults may not be what you expect, so we’ll cover what they are and how to handle them. We’ll also review how to define default values for parameters. Object-orientated languages support function overloading, which allows the same function name to be used with different types of parameters. We’ll discuss how to simulate this using parameter sets, and we’ll cover how to combine the pipeline with passed parameters to maximize the power and flexibility of your functions. Finally, in this age of globalization, we often need to support multiple languages, so we’ll discuss how multiple language support can be implemented easily with special PowerShell features.
Bryan Cafferky

Chapter 6. Extending PowerShell

Abstract
To load a single function for use in a script, we use the dot-sourcing method discussed previously. However, what if we have many related functions that we want to make available to our script? Do we need to dot source each function individually? Fortunately, the answer is no. PowerShell provides an elegant way to group a set of functions together into a module so they can be loaded in just one statement. We’ve already used this feature in previous examples, and now we’re going to discuss how we can create our own modules. We will also cover how to merge multiple script files into a module by using a manifest. Modules can be written in PowerShell script or in .NET languages like C#. Modules are one of the great extensibility features of PowerShell, and you can find many free modules available for download. Some, such as the SQL Server Module, SQLPS, are written by Microsoft, and others by vendors or the Windows community. We’ll discuss some of the popular modules available and how to use them.
Bryan Cafferky

Chapter 7. Accessing SQL Server

Abstract
We have seen some examples of executing queries against SQL Server. There are many ways to access a database, and it can hinder development to custom code each variation, as there are a number of questions to be resolved. What type of access method will be used: ADO.Net, OleDB, ODBC, or the native SQL Server driver? What type of authentication will be used: Windows Integrated Security, Logon ID and password, or a credential object? What database platform: On Premise SQL Server, SQL Azure, or a third-party database like MySQL or PostgreSQL? Why not write some functions to make this task easier? In this chapter, we will be carefully walking through a custom module that provides easy access to databases using ADO.Net.
Bryan Cafferky

Chapter 8. Customizing the PowerShell Environment

Abstract
PowerShell provides an amazing level of customization and transparency. In this chapter, we will discuss how PowerShell supports configuration using three types of variables: automatic, preference, and environment. Automatic variables are created and used by PowerShell to support its operations and can be read by scripts to get context-specific information. Preference variables control PowerShell’s behavior and can be modified to suit our needs. Environment variables are not specific to PowerShell. They are variables used by Windows applications including PowerShell and can be created, read, and set by PowerShell scripts. We’ll review how to use aliases to create our own names for PowerShell cmdlets. We will use PowerShell drives to dynamically create short names for provider locations. Finally, we’ll discuss how to use all these features to customize PowerShell sessions on startup using a special script called a profile.
Bryan Cafferky

Chapter 9. Augmenting ETL Processes

Abstract
This chapter will cover how to use PowerShell to augment ETL development. We often need to load external files into a SQL Server database. However, these files usually need to go through some preparation before they can be loaded. Perhaps they arrive via FTP from an external source. Such files may be compressed. Perhaps they need to be scrubbed of bad values or modified to make them easier to load. After being loaded, the business may want the files archived and retained for a period of time. Before PowerShell, legacy-style batch files were often employed to do these tasks. However, batch files are cryptic, difficult to maintain, and lack support for reusability. In this chapter, we will see how PowerShell scripts can be used to accomplish these tasks. Rather than define a specific business scenario for these tasks, we consider this a common ETL pattern in which we can choose to employ the given tasks that apply. In this pattern, files arrive in a folder and copied to a local server, then are decompressed, loaded, and archived. Typically, when the job starts and ends, email notifications are sent out. Sometimes there are additional requirements. We will discuss functions that help with these tasks. Let’s consider the potential ETL steps already mentioned as a template from which we can pick what we need.
Bryan Cafferky

Chapter 10. Configurations, Best Practices, and Application Deployment

Abstract
In this chapter, we are going to discuss several related topics: configuring PowerShell applications, development best practices, and application deployment. First, we will discuss how to configure PowerShell applications to so as maximize flexibility and ease of maintenance. A nice feature of SQL Server Integration Services (SSIS) is that it has a number of configuration methods. In this chapter, we will discuss how to achieve comparable functionality in PowerShell using several approaches. Then, we will discuss the goals of best practices and how to apply them to PowerShell. Finally, we will consider deploying PowerShell applications and ways to simplify this process.
Bryan Cafferky

Chapter 11. PowerShell Versus SSIS

Abstract
In this chapter we will discuss using PowerShell as an extract, transform, and load (ETL) tool as an alternative to using SQL Server Integration Services (SSIS). We start by comparing PowerShell to SSIS. Then we define the general pattern of an ETL tool—extract from a source, transform the data, and write the data to a destination. In covering the application of these concepts, we employ a fictitious ETL use case that requires a number of files to be loaded into SQL Server tables.
Bryan Cafferky

Chapter 12. PowerShell Jobs

Abstract
In this chapter, we will discuss PowerShell’s batch-job execution features, which are implemented in the form of cmdlets. At its simplest, we have the ability to run a script as a background job. However, this requires that the PowerShell session that submitted the job remains active. To avoid this constraint, we can create a scheduled job. PowerShell has a number of cmdlets to support job scheduling. As we will see, these cmdlets are really an interface to the Windows Task Scheduler. We will discuss the cmdlets that support the three components of a scheduled job: the job definition, triggers, and options. The job definition defines what code will be executed. The triggers define when the job should be executed i.e., the schedule. The options define how the job should be executed. We will wrap up the section on job cmdlets by reviewing a script that provides a simple console with which to access our PowerShell jobs. Then, we will discuss why SQL Server Agent is a better solution for job scheduling. We will step through how to create a SQL Server Agent job that executes a PowerShell script. Then, we will discuss how we can easily manipulate SQL Server Agent jobs with PowerShell. Although PowerShell’s job scheduling may not be the best solution for production, there are two good reasons we need to understand it. First, PowerShell’s job-execution and scheduling features can be very useful in development. Second, PowerShell’s remote execution and workflows use batch-execution features, so we need to understand these in order to discuss remote execution and workflows, which will be covered in the next chapter.
Bryan Cafferky

Chapter 13. PowerShell Workflows

Abstract
In this chapter, we will discuss creating and executing workflows in PowerShell. Workflow Foundation is a .NET framework for defining complex processes that can span a long period of time, can be stopped and restarted where they left off, support the parallel execution of tasks, and can be packaged into reusable components. Until PowerShell 3.0, workflows could only be created in Visual Studio using C# or VB.Net. PowerShell 3.0 added support for the creation and execution of workflows. Since workflows can be executed either locally or on remote machines, we’ll start by discussing PowerShell remote execution. Workflows are suspended and restarted using PowerShell job cmdlets, which were covered in the previous chapter. Here, we’ll see how they apply to workflows. We will discuss using the commands parallel, foreach –parallel, sequence, and inlinescript to control workflow execution. Then, we will delve into using the cmdlets Checkpoint-Workflow, Suspend-Workflow, and Resume-Job to pause and resume workflow execution. Workflows can work on a single object at a time or on a collection. For example, John Doe applies for insurance coverage, and a series of steps occur until he is either insured or rejected. This pattern of usage is common to ASP.Net applications. A workflow could be used to load a series of external files into staging tables, validate the data, and, finally, load them into a data warehouse. We will cover two data-centric use cases for workflows. One is a typical data-warehouse load scenario. The other uses a workflow to speed up the extraction of data from flat files using parallel processing, similar to Hadoop.
Bryan Cafferky

Backmatter

Weitere Informationen

Premium Partner

    Bildnachweise