PowerShell for SQL Server Essentials

By Santos Donabel

About This Book

• Create scripts using PowerShell to manage and monitor server administration and application deployment
• Automate creation of SQL Database objects through PowerShell with the help of SQL Server module (SQLPS) and SQL Server snapins
• A fast paced guide, packed with hands-on examples on profiling and configuring SQL Server

Who This Book Is For

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

• Language: English
• Publisher: Packt Publishing
• Release date: Feb 25, 2015
• ISBN: 9781784397104

Book preview

Table of Contents

PowerShell for SQL Server Essentials

Credits

About the Author

Acknowledgments

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Instant updates on new Packt books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Getting Started with PowerShell

A brief history of PowerShell

The PowerShell environment

The PowerShell console

The PowerShell ISE

Running PowerShell as an administrator

The execution policy

PowerShell versions

PowerShell cmdlets

Cmdlet naming convention

Cmdlet parameters

Cmdlet aliases

PowerShell providers

Snap-ins and modules

PowerShell Pipeline

Scripting basics

Running PowerShell scripts

Getting help

Getting help from other cmdlets

Summary

2. Using PowerShell with SQL Server

SQL Server via PowerShell

Mini-shell (or the sqlps utility)

The SQLPS module

SQL Server snap-ins

SQL Server assemblies

SQL Server-specific cmdlets

SQL Server Management Objects

Creating SMO objects

Summary

3. Profiling and Configuring SQL Server

Current server resources

Getting processor (CPU) information

Checking server memory

Checking disk space

Checking network settings

Hotfixes and service packs

Current SQL Server instances

Services and service accounts

SQL Server error logs

Current instance configuration

Changing configurations

Start or stop services

Changing a service account

Changing instance settings

Summary

4. Basic SQL Server Administration

Listing databases and tables

Listing database files and filegroups

Adding files and filegroups

Listing the processes

Checking enabled features

Scripting database objects

Attaching and detaching databases

Detaching databases

Backing up and restoring databases

Backing up

Restoring

Reorganizing or rebuilding indexes

Managing logins, users, and permissions

Permissions

Adding a login

Adding database users

Policies

Managing jobs

Summary

5. Querying SQL Server with PowerShell

To PowerShell or not to PowerShell

Sending queries to SQL Server

SQL Server Management Objects

The Invoke-Sqlcmd cmdlet

ADO.NET

The Invoke-Expression cmdlet

Sending simple queries to SQL Server – different variations

Fixing orphaned users

Getting fragmentation data

Backing up and restoring databases

Exporting data using bcp

Summary

6. Monitoring and Automating SQL Server

Getting to know helpful cmdlets

The Send-MailMessage cmdlet

The ConvertTo-Html cmdlet

The Export-Csv cmdlet

The Write-EventLog cmdlet

Additional cmdlets

Scheduling PowerShell scripts

Checking logs

Monitoring failed jobs

Alerting on disk space usage

Logging blocked processes

Getting performance metrics

Summary

A. Implementing Reusability with Functions and Modules

Functions

Simple functions

Advanced functions

Best practices

Modules

Script modules

Summary

Index

PowerShell for SQL Server Essentials

---

PowerShell for SQL Server Essentials

Copyright © 2015 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: February 2015

Production reference: 1200215

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78439-149-2

www.packtpub.com

Credits

Author

Donabel Santos

Reviewers

Mark Andrews

Peter Johnson

Rahul Singla

Acquisition Editors

Rebecca Pedley

Meeta Rajani

Content Development Editor

Akshay Nair

Technical Editors

Pragnesh Bilimoria

Taabish Khan

Copy Editors

Gladson Monteiro

Veena Mukundan

Alfida Paiva

Project Coordinator

Mary Alex

Proofreaders

Ting Baker

Simran Bhogal

Paul Hindle

Indexer

Monica Ajmera Mehta

Graphics

Valentina D'silva

Production Coordinator

Nilesh R. Mohite

Cover Work

Nilesh R. Mohite

About the Author

Donabel Santos (SQL Server MVP) is a business intelligence architect, trainer/instructor, consultant, author, and principal at QueryWorks Solutions, based in Vancouver, Canada. She works primarily with SQL Server for database/data warehouse, reporting, and ETL solutions. She scripts and automates tasks with PowerShell and creates dashboards and visualizations with Tableau and Power BI.

She is a Microsoft Certified Trainer (MCT). She provides consulting and corporate training to clients. She is also the lead instructor for SQL Server and Tableau (Visual Analytics) courses at British Columbia Institute of Technology (BCIT).

Donabel is an MCITP DBA and a developer for SQL Server and MCTS for SharePoint. She is also a Tableau Desktop 7 Core Certified and a Tableau Desktop 8 Certified Professional. She is currently working on her SQL Server 2012 (and upcoming 2014) certifications.

She is a self-confessed data geek. She loves working with data and thinks SQL Server is a lot of fun and Tableau is just amazing at delivering insights. She authored SQL Server 2012 with PowerShell V3 Cookbook, Packt Publishing, and contributed to PowerShell Deep Dives, Manning Publications. She blogs at www.sqlbelle.com and tweets at @sqlbelle.

Acknowledgments

I didn't think I had it in me to write another book. However, my niece came along after the first book was published and she wasn't in my acknowledgements. So, I wanted to have an opportunity to mention her in another book.

To my dearest Chiyo: I hope you always remember that Tita loves you very much. Tita will always be there for you whenever you need her.

In my first book, I apologized for the lengthy acknowledgements. In this second book, I will do the same.

To Eric: thank you for still being here with me through the ups and downs, the happy times, and the crazy times. I am looking forward to many more adventures, side by side, hand in hand. I love you.

To Papa and Mama: you always give me strength and inspiration. I keep on going because of you. Thank you for everything that you've done for us, and I am so happy that your granddaughter gives you a lot of joy. I love you both very much.

To JR and RR: you will always be my baby brothers, and I am so proud to be your elder sister.

To Lisa: you're my sister, and I wouldn't have it any other way. I'm there for you and will be there to support you as best as I can.

To my in laws: Mom Lisa, Dad Richard, Ama, Aunt Rose, Catherine, David, Jayden, and Kristina; thank you for being my family. Thank you for all the fun times and all the support all these years. Thank you for being there whenever I needed you; words cannot express my gratitude. Jayden and Kristina, Agim and Agu love you two very much, and we'll be there for you to play with you, teach you, and support you. We just want hugs and kisses in return.

To my BCIT family: Kevin Cudihee, Joanne Atha, Elsie Au, Cynthia van Ginkel, Steve Eccles, Dean Hildebrand, and to all my students, past and present; thank you. BCIT is my second home. It has paved the way for many good things in my life and I will always be grateful.

To my UBC family: my super wonderful boss extraordinaire, Pradeep Nair, and my superb teammates Joe Xing, Min Zhu, George Firican, Mary Mootatamby, Jason Metcalfe, Tom Yerex, and Suzanne Landry. I love going to work everyday. You are all awesome; we have a great team and it is a privilege to work with all of you.

To the Packt team: Meeta Rajani for contacting me to author this book and Akshay Nair, who has helped me throughout the process; thank you.

I didn't do this alone. I have learned so much from so many other people, all the SQL Server and PowerShell MVPs, and each technology's communities and bloggers. The Tableau community is also quite inspiring, from Zen masters (Joe Mako, Jonathan Drummey, Kelly Martin, and Dan Murray) to all the bloggers and vizzers. Special thank you to Dan Murray, Tim Costello, Jason Schumacher, John Pain, and Liz Feller. Thank you all for making learning fun again.

There are so many other people who inspired and helped me along the way, including friends, students, and acquaintances. Thank you.

Most importantly, thank you Lord for all the miracles and blessings in my life.

About the Reviewers

Mark Andrews has had a varied career in technology. Over the last 18 years, he has held several different positions, ranging from customer service to quality assurance. Throughout all these positions, the responsibility of configuration management and build management has always fallen either on Mark personally or on one of the groups that he managed. Because of his keeping a hand in management style, he has been involved closely with the scripting and automation framework for these areas. Creating scripted frameworks that intercommunicate across machine / operating system / domain boundaries is a passion for him.

He has worked on PowerShell 3.0 Advanced Administration Handbook, Packt Publishing, and Windows PowerShell 4.0 for .NET Developers, Packt Publishing.

Peter Johnson has over 34 years of enterprise computing experience. He started working with PowerShell when it first surfaced from Microsoft as Monad. He has been working with Java for 17 years, and for the last 12 years, he has been heavily involved in Java performance tuning. He is a frequent speaker on Java performance topics at various conferences, including the Computer Measurement Group annual conference, JBoss World, and Linux