Chapter 1, Getting Started with SQL Server and PowerShell, provides an introduction on how to work with SQL Server and PowerShell, including an introduction to SQL Server Management Objects (SMO). This chapter provides a recipe to install SQL Server using PowerShell and helps you explore and discover SQL Server-related objects and cmdlets.

Chapter 2, SQL Server and PowerShell Basic Tasks, provides scripts and snippets of code that accomplish some basic SQL Server tasks using PowerShell. Tasks include listing SQL Server instances, discovering SQL Server services, configuring SQL Server, importing/exporting records in SQL Server, and creating objects such as tables, indexes, stored procedures, and functions. Some recipes also teach you how to work with Azure SQL Database.

Chapter 3, Basic Administration, explores how administrative tasks can be accomplished in PowerShell. Some recipes deal with how to create SQL Server instances and database inventories, how to check disk space, running processes, and SQL Server jobs. Other recipes show you how to attach/detach/copy databases, add files to databases, and execute a query to multiple SQL Server instances

Chapter 4, Security, focuses on how to work with SQL Server service accounts, manage logins/users/permissions, and monitor login attempts and also how to work with database roles, credentials, and proxies.

Chapter 5, Backup and Restore, teaches you what you already know about SQL Server backup and restore procedures and shows you how these tasks can be done using PowerShell. Many recipes use SQL Server-specific cmdlets, such as Backup-SqlDatabase and Restore-SqlDatabase wherever possible, but also utilize SQL Server Management Objects (SMO) to get more information on backup metadata. Some recipes also help you tackle backup and restore to Azure BLOB storage.

Chapter 6, Advanced Administration, discusses some of the most advanced features of SQL Server and how you can work with them in PowerShell. Recipes in this chapter include how to work with LocalDB, database snapshots, Filestream, FileTable, Full-Text Index, memory-optimized tables, security objects such as certificates, symmetric and asymmetric keys, and setting up Transparent Data Encryption (TDE).

Chapter 7, Audit and Policies, focuses on how to work with SQL Server tracking and auditing capabilities and SQL Server Policy Based Management (PBM). This chapter also explores how to work with SQL Server Profiler trace files and events programmatically.

Chapter 8, High Availability with AlwaysOn, covers specific recipes that can help you manage and automate SQL Server AlwaysOn, including how to install the failover cluster feature, enabling AlwaysOn, creating AlwaysOn availability groups and listeners, and testing the availability group failover.

Chapter 9, SQL Server Development, provides snippets and guidance on how you can work with XML, XSL, JSON, binary data, files in FileTable, and CLR assemblies with SQL Server and PowerShell.

Chapter 10, Business Intelligence, covers how PowerShell can help you automate and manage any BI-related tasks, including how to manage and execute SQL Server Integration Services (SSIS) packages, list and download SQL Server Reporting Services (SSRS) reports, and backup and restore SQL Server Analysis Services (SSAS) cubes.

Chapter 11, Helpful PowerShell Snippets, covers a variety of recipes that are not SQL Server-specific, but you may find them useful when working with SQL Server and PowerShell. Recipes include snippets for creating files that use timestamps, using Invoke-Expression, compressing files, reading event logs, embedding C# code, extracting data from a web service, and exporting a list of processes to CSV or XML.

Appendix A, PowerShell Primer, offers a brief primer on PowerShell fundamentals for the SQL Server professional. This chapter includes sections on how to run PowerShell scripts, understand PowerShell syntax, and convert scripts into functions to make them more reusable.

Appendix B, Creating a SQL Server VM, provides a step-by-step tutorial on how to create and configure the virtual machine that was used for this book.

For the purpose of this book, the requirements are as follows:

PowerShell V5 is bundled with Windows Management Framework (WMF) 5. WMF 5 is supported by the following operating systems:

WMF 5 requires .NET Framework 4.5.

This book is written for SQL Server administrators and developers who want to leverage PowerShell to work with SQL Server. A little bit of scripting background will be helpful but not necessary.

In this book, you will find several headings that appear frequently (Getting ready, How to do it, How it works, There's more, and See also).

To give clear instructions on how to complete a recipe, we use these sections as follows:

In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.

Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "We can include other contexts through the use of the include directive."

A block of code is set as follows:

#set connection to mixed mode
#note that this authentication will fail if mixed mode
#is not enabled in SQL Server
$server.ConnectionContext.set_LoginSecure($false)

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

$server.Databases["AdventureWorks2014"].Tables |
Get-Member -MemberType "Property" |
Where-Object Definition -Like "*Smo*"

Any command-line input or output is written as follows:

(Get-Command -Module "*SQL*" –CommandType Cmdlet).Count

New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "Open up your PowerShell console, PowerShell ISE, or your favorite PowerShell editor."

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.

To send us general feedback, simply e-mail <[email protected]>, and mention the book's title in the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.