PowerShell for SQL Server Essentials
PowerShell for SQL Server Essentials

Author: Donabel Santos
Publisher: Packt Publishing
Pages: 183
ISBN: 978-1784391492
Print: 1784391492
Kindle: B00U1D9WTQ
Audience: Administrators and developers
Rating: 4.0
Reviewer: Ian Stirk

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:


  • Check server resources (CPU, memory, disk space, and network settings)

  • Check hotfixes and service packs versions

  • Check current SQL Server instances

  • Check services and service accounts

  • Check SQL Server logs (with appropriate filtering)

  • List current instance configurations (lots of output)

  • Change configurations

  • Start or stop services

  • Change service accounts

  • Change instance settings (e.g. enable xp_cmdshell)

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: 

  • Listing databases and tables (together with various properties)

  • Adding files and filegroups

  • Scripting database objects

  • Backing up and restoring databases

  • Reorganizing or rebuilding indexes

  • Managing logins, users, and permissions

  • Managing jobs 

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: 

  • SQL Server Management Objects (SMO)

  • Invoke-Sqlcmd


  • Invoke-Expression

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:  

  • Filter the SQL Server Error Log and email details to the DBA

  • Monitor for failed joins and alert the DBA

  • Alert the DBA on disk space

  • Log blocked processes details to the Windows Event Log

  • Create Data Collector Sets in Performance Monitor 

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.


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. 



Soft Skills

Author: John Sonmez
Publisher: Manning Publications
Pages: 504
ISBN: 978-1617292392
Print: 1617292397
Audience: Everyone!
Rating: 4.5
Reviewer: Ian Stirk

This book aims to make you the most successful software developer you can be – by enhancing your non-technical soft skills, how does it fare?

The Art of Unit Testing

Author: Roy Osherove
Publisher: Manning
Pages: 296
ISBN: 9781617290893
Print: 1617290890

Audience: C# users and others using statically typed languages
Rating: 5
Reviewer: Alex Armstrong

If you don't already use unit testing, this book is a complete and clear guide to the idea and how to implemen [ ... ]

More Reviews


Last Updated ( Tuesday, 03 May 2016 )

RSS feed of book reviews only
I Programmer Book Reviews
RSS feed of all content
I Programmer Book Reviews
Copyright © 2017 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.