| PowerShell for SQL Server Essentials |
|
Author: Donabel Santos This book aims to introduce you to PowerShell and how it can be used with SQL Server. How well does it do it?
PowerShell is Microsoft’s preferred scripting tool, useful for task automation and integration across products. It’s often specified as a tool which DBAs are eager to learn about. With growing numbers of servers, a tool that helps with admin is to be welcomed. The book is aimed at SQL Server administrators, and might be useful for developers too. To get the most out of this book, some basic awareness of scripting is needed. Below is a chapter-by-chapter exploration of the topics covered. 
Chapter 1 Getting Started with PowerShell The book opens with a brief history of PowerShell, being a replacement for a diverse collection of tools (e.g. VB Script). PowerShell eventually became the tool of choice for automation and integration between Microsoft products. The chapter continues with a look at the PowerShell environment, both the console and the GUI (Integrated Scripting Environment – ISE) are discussed. The importance of execution policies and how they impact running local or remote scripts is explained. The various versions of PowerShell are listed together with their salient features. Next, cmdlets are discussed, these form the core of PowerShell, performing specific tasks. The cmdlet Verb-Noun naming convention is described (e.g. Get-Help), which makes it easier to guess a command. PowerShell providers, which allow access to a data store, are briefly discussed. Extending PowerShell with snap-ins or (preferred) modules is examined. The ability to chain together cmdlets using pipes is noted (similar to Unix). There’s a useful overview of PowerShell scripting basics, including a table showing the scripting components and their meaning. Saving and running a script is shown. The chapter ends with a helpful section on where and how to get help, this is of paramount importance to the beginner. The use of partial command-name search is noted. The chapter provides a very helpful introduction to PowerShell, its environments and usage. The chapter is useful irrespective of SQL Server. Some awareness of scripting/PowerShell is assumed, so it’s not a book for the complete beginner. Some terms are used before being defined (e.g. cmdlets) The chapter is well written, with useful website links, tables, screenshots, and summary. The chapter has lots of useful tips (e.g. downgrade session version). These traits apply to the whole book. Chapter 2 Using PowerShell with SQL Server After looking at PowerShell in general, here we look at PowerShell’s usage with SQL Server. The chapter opens with a look at the ‘mini-shell’ sqlps utility, originally a limited PowerShell console. It’s now recommended to use the more functional SQLPS module instead of the sqlps utility. From SQL Server 2012 onwards, starting the mini-shell from SQL Server Management Studio (SSMS) loads the complete PowerShell console and the SQLPS module – this also loads the SqlServer provider, allowing access to SQL Server. Next, PowerShell snap-ins for SQL Server 2008 and 2008 R2 are discussed. The chapter then moves on to SQL Server specifics, with a helpful table showing the SQL Server 2014 cmdlets included with the SQLPS (database engine) and SQLASCMDLETS (analysis services) modules. The chapter ends with a look at SQL Server Management Objects (SMO), which allows programmatic access to SQL Server via PowerShell, providing much greater interaction with SQL Server than the cmdlets allow. Some example code is provided to create SMO objects. A useful SMO object model diagram shows the underlying components. This chapter provides a useful overview of the means of interaction between PowerShell and SQL Server, it would have been helpful to include some example usage of SQLPS cmdlets. Chapter 3 Profiling and Configuring SQL Server Examining the set-up of SQL Server instances and environment is a common task, especially for DBAs taking on new systems. This chapter provides details of how to:
For each task, snippets of PowerShell code are provided, together with a brief explanation. This chapter includes some useful template code for discovering both static and dynamic details about your database servers. You’re sure to want to re-use and extend these in your own systems.
Chapter 4 Basic SQL Server Administration Here PowerShell code snippets are provided to perform everyday (and often time consuming) database admin tasks. Tasks covered include:
This chapter includes some very useful template code for executing various time-consuming maintenance tasks, and it’s sure to give you ideas for your own needs. Chapter 5 Querying SQL Server with PowerShell There’s a short discussion on when to use PowerShell for querying, which is a bit reminiscent of when to use .NET code in SQL Server (i.e. SQL is best for set-based processing). The chapter explores various ways of submitting queries to SQL Server using PowerShell, the methods discussed are:
In each case, example code is provided, together with a short discussion of when its usage might be appropriate. The chapter ends by re-examining some earlier tasks, and how they could be performed more efficiently or elegantly, using the Invoke methods. This chapter provides a helpful overview on querying SQL Server using PowerShell. As well as providing useful code, its wider observation on when to use PowerShell should prove ‘interesting’. Chapter 6 Monitoring and Automating SQL Server Using PowerShell to examine SQL Server is very useful, however the ability to automate and extend this functionality shows the tool’s real value. The chapter opens with a brief section on some helpful cmdlets (e.g. Send-MailMessage), before PowerShell code is provided to:
This chapter provides a useful overview of what you might monitor. For completeness, it would have been useful to have included examples of scheduling PowerShell scripts using SQL Server Agent and the Windows Task Scheduler. There’s a helpful final reminder that PowerShell is a just another tool in your toolbox, and should be used appropriately. The book ends with a section on how to create functions and script modules in PowerShell for reuse. Conclusion This book aims to introduce you to PowerShell and how it can be used with SQL Server, and succeeds. The book is well written, easy to read, with useful website links, and screenshots. The book is filled with useful incidental tips, and contains plenty of template code that could form the basis of your own code. Some understanding of scripting in general is assumed. With all the different versions of the various software mentioned (e.g. SQL Server, PowerShell, Windows, .NET), I found the topic (but not the book) a bit messy. It would have been useful to have included examples of scheduling PowerShell scripts using SQL Server Agent and Windows Task Scheduler. The book’s title should reflect the main versions of software used (i.e. SQL Server 2014, and PowerShell v4). PowerShell is undoubtedly a useful tool for task automation and product integration, and is a valuable addition to your toolbox. There is much more to learn of course, but this book is a great starting place. Overall, this is a useful introduction to PowerShell, and its usage for SQL Server. Recommended.
|
|||
| Last Updated ( Tuesday, 03 May 2016 ) |