PowerShell for SQL Server Essentials

Manage and monitor SQL Server administration and application deployment with PowerShell

PowerShell for SQL Server Essentials

Donabel Santos
February 2015

2 customer reviews

Manage and monitor SQL Server administration and application deployment with PowerShell

Mapt Subscription

FREE

€29.98/m after trial

eBook

€12.60

RRP €17.98

Save 29%

Print + eBook

€23.99

RRP €23.99

What do I get with a Mapt Pro subscription?

Unlimited access to all Packt’s 5,000+ eBooks and Videos
Early Access content, Progress Tracking, and Assessments
1 Free eBook or Video to download and keep every month after trial

What do I get with an eBook?

Download this book in EPUB, PDF, MOBI formats
DRM FREE - read and interact with your content when you want, where you want, and how you want
Access this title in the Mapt reader

What do I get with Print & eBook?

Get a paperback copy of the book delivered to you
Download this book in EPUB, PDF, MOBI formats
DRM FREE - read and interact with your content when you want, where you want, and how you want
Access this title in the Mapt reader

What do I get with a Video?

Download this Video course in MP4 format
DRM FREE - read and interact with your content when you want, where you want, and how you want
Access this title in the Mapt reader

€0.00

€12.60

€23.99

€29.98p/m after trial

RRP €17.98

RRP €23.99

Subscription

eBook

Print + eBook

Start 30 Day Trial

Code Files

Preview in Mapt

Book Details

ISBN 139781784391492

Paperback186 pages

Book Description

PowerShell for SQL Server Essentials helps us to manage and monitor server administration and application deployment. Use PowerShell along with SQL Server to perform common DBA tasks.

Starting with a basic introduction to PowerShell, the initial chapters will provide the SQL Server professional PowerShell fundamentals, covering topics such as PowerShell notations and syntax, cmdlets, pipeline, and getting help. Succeeding chapters build upon these fundamentals, and illustrate how to administer and automate SQL Server. Tasks covered throughout include profiling the SQL Server instance, performing backup and restores, invoking T-SQL scripts using PowerShell, and monitoring jobs, security, and permissions.

Packed with practical examples and numerous ready-to-use snippets, this book gets you to an intermediate level in using PowerShell for SQL Server.

Chapter 1: Getting Started with PowerShell

A brief history of PowerShell

The PowerShell environment

PowerShell versions

PowerShell cmdlets

PowerShell providers

Snap-ins and modules

PowerShell Pipeline

Scripting basics

Running PowerShell scripts

Getting help

Summary

Chapter 2: Using PowerShell with SQL Server

SQL Server via PowerShell

SQL Server-specific cmdlets

SQL Server Management Objects

Summary

Chapter 3: Profiling and Configuring SQL Server

Current server resources

Hotfixes and service packs

Current SQL Server instances

Services and service accounts

SQL Server error logs

Current instance configuration

Changing configurations

Summary

Chapter 4: Basic SQL Server Administration

Listing databases and tables

Adding files and filegroups

Checking enabled features

Scripting database objects

Attaching and detaching databases

Backing up and restoring databases

Reorganizing or rebuilding indexes

Managing logins, users, and permissions

Policies

Managing jobs

Summary

Chapter 5: Querying SQL Server with PowerShell

To PowerShell or not to PowerShell

Sending queries to SQL Server

Fixing orphaned users

Getting fragmentation data

Backing up and restoring databases

Exporting data using bcp

Summary

Chapter 6: Monitoring and Automating SQL Server

Getting to know helpful cmdlets

Scheduling PowerShell scripts

Checking logs

Monitoring failed jobs

Alerting on disk space usage

Logging blocked processes

Getting performance metrics

Summary

What You Will Learn

Create scripts using PowerShell to manage and monitor server administration and application deployment
Automate the creation of SQL Database objects through PowerShell with the help of the SQL Server module (SQLPS) and SQL Server snap-ins
Use PowerShell to work with SQL Server specific providers and cmdlets
Identify and manage SQL Server services, instances, settings, and confi gurations
Profile your SQL Server instances and export current configurations to a file
Monitor SQL Server jobs and alerts
Manage logins, database users, and instance security and permissions
Invoke T-SQL queries from PowerShell and export results
Accomplish tasks from your DBA daily/weekly/monthly/yearly checklists with PowerShell

Authors

Donabel Santos

Donabel Santos is a self-confessed data geek. She loves working with data, writing queries, and developing reports on her SQL Server databases, and exploring and visualizing data with Tableau.

She is the principal and senior business intelligence architect at QueryWorks Solutions, a Tableau Learning and Alliance partner in Vancouver, BC, Canada, providing consulting and training services. She has spent years in consulting and has developed a variety of solutions for clients in different verticals—finance, manufacturing, healthcare, legal, higher education, and local government.

Donabel is a multi-year Microsoft Data Platform MVP (previously known as SQL Server MVP) and has extensive experience in the SQL server in different areas, such as development, administration, data warehouse, reporting (SSRS), tuning, troubleshooting, XML, CLR, and integration with ERPs and CRMs using PowerShell, C#, SSIS, and Power BI.

One of Donabel's passions is teaching and sharing her love for data. She is a Tableau Certified Professional and a Tableau accredited trainer, delivering Tableau public and on-site client training. She is also the lead instructor for a number of courses at British Columbia Institute of Technology (BCIT), including Applied Database Administration and Design (ADAD) and Applied Data Analytics (ADA) programs. She teaches SQL server administration, development, integration (SSIS), data warehouse foundations, and visual analytics with Tableau.

Donabel has also authored three other books with Packt Publishing: SQL Server 2012 with PowerShell V3 Cookbook, PowerShell for SQL Server Essentials, and SQL Server 2014 with PowerShell V5 Cookbook. She also contributed a chapter to Manning Publications' PowerShell Deep Dives.

Chapter 1: Getting Started with PowerShell

A brief history of PowerShell

The PowerShell environment

PowerShell versions

PowerShell cmdlets

PowerShell providers

Snap-ins and modules

PowerShell Pipeline

Scripting basics

Running PowerShell scripts

Getting help

Summary