Book Details

ISBN 139781785283321

Paperback760 pages

Book Description

PowerShell can be leveraged when automating and streamlining SQL Server tasks. PowerShell comes with a rich set of cmdlets, and integrates tightly with the .NET framework. Its scripting capabilities are robust and flexible, allowing you to simplify automation and integration across different Microsoft applications and components.

The book starts with an introduction to the new features in SQL Server 2014 and PowerShell v5 and the installation of SQL Server. You will learn about basic SQL Server administration tasks and then get to know about some security-related topics such as the authentication mode and assigning permissions. Moving on, you will explore different methods to back up and restore your databases and perform advanced administration tasks such as working with Policies, Filetables, and SQL audits. The next part of the book covers more advanced HADR tasks such as log shipping and data mirroring, and then shows you how to develop your server to work with BLOB, XML, and JSON.

Following on from that, you will learn about SQL Server's BI stack, which includes SSRS reports, the SSIS package, and the SSAS cmdlet and database. Snippets not specific to SQL Server will help you perform tasks quickly on SQL servers. Towards the end of the book, you will find some useful information, which includes a PowerShell tutorial for novice users, some commonly-used PowerShell and SQL Server syntax, and a few online resources. Finally, you will create your own SQL Server Sandbox VMs. All these concepts will help you to efficiently manage your administration tasks.

Table of Contents

Chapter 1: Getting Started with SQL Server and PowerShell

Introduction

Working with the sample code

Installing SQL Server using PowerShell

Installing SQL Server Management Objects

Loading SMO assemblies

Exploring the SQL Server PowerShell hierarchy

Discovering SQL-related cmdlets and modules

Creating a SQL Server Instance Object

Exploring SMO Server Objects

Chapter 2: SQL Server and PowerShell Basic Tasks

Introduction

Listing SQL Server instances

Discovering SQL Server services

Starting/stopping SQL Server services

Listing SQL Server configuration settings

Changing SQL Server Instance configurations

Searching for database objects

Scripting SQL Server Stored Procedures

Creating a database

Altering database properties

Dropping a database

Changing database owner

Creating a table

Creating a view

Creating a stored procedure

Creating a trigger

Creating an index

Executing a query/SQL script

Performing bulk export using Invoke-SqlCmd

Performing bulk export using the bcp command-line utility

Performing bulk import using BULK INSERT

Performing bulk import using the bcp command-line utility

Connecting to an Azure SQL database

Creating a table in an Azure SQL database

Chapter 3: Basic Administration

Introduction

Creating a SQL Server instance inventory

Creating a SQL Server database inventory

Listing installed hotfixes and Service Packs

Listing running/blocking processes

Killing a blocking process

Checking disk space usage

Setting up WMI server event alerts

Detaching a database

Attaching a database

Copying a database

Executing SQL query to multiple servers

Creating a filegroup

Adding a secondary data file to a filegroup

Increase data file size

Moving an index to a different filegroup

Checking index fragmentation

Reorganizing/rebuilding an index

Running DBCC commands

Setting up Database Mail

Listing SQL Server Jobs

Adding a SQL Server operator

Creating a SQL Server Job

Adding a SQL Server event alert

Running an SQL Server Job

Scheduling a SQL Server Job

Chapter 4: Security

Introduction

Listing SQL Server service accounts

Changing SQL Server service account

Listing authentication mode

Changing authentication mode

Listing SQL Server log errors

Listing failed login attempts

Enabling Common Criteria compliance

Listing logins, users, and database mappings

Listing login/user roles and permissions

Creating a user-defined server role

Creating a login

Assigning permissions and roles to a login

Creating a database user

Assigning permissions to a database user

Creating a database role

Fixing orphaned users

Creating a credential

Creating a proxy

Chapter 5: Backup and Restore

Introduction

Changing database recovery model

Checking last backup date

Creating a backup device

Listing backup header and FileList information

Creating a full backup

Creating a backup on Mirrored Media Sets

Creating a differential backup

Creating a transaction log backup

Creating a filegroup backup

Restoring a database to a point-in-time

Performing an online piecemeal restore

Backing up database to Azure Blob storage

Restoring database from Azure Blob storage

Chapter 6: Advanced Administration

Introduction

Connecting to LocalDB

Creating a new LocalDB instance

Listing database snapshots

Creating a database snapshot

Dropping a database snapshot

Enabling FileStream

Setting up a FileStream filegroup

Adding a FileTable

Adding full-text catalog

Adding full-text index

Creating a memory-optimized table

Creating a database master key

Creating a certificate

Creating symmetric and asymmetric keys

Setting up Transparent Data Encryption

Chapter 7: Audit and Policies

Introduction

Enabling/disabling change tracking

Configuring SQL Server Audit

Listing facets and their properties

Listing policies

Exporting a policy

Importing a policy

Creating a condition

Creating a policy

Evaluating a policy

Running and saving a profiler trace event

Extracting the contents of a trace file

Chapter 8: High Availability with AlwaysOn

Introduction

Installing the Failover Cluster feature on Windows

Enabling TCP and named pipes in SQL Server

Enabling AlwaysOn in SQL Server

Creating and enabling the HADR endpoint

Granting the CONNECT permission to the HADR endpoint

Creating an AlwaysOn Availability Group

Joining the secondary replicas to Availability Group

Adding an availability database to an Availability Group

Creating an Availability Group listener

Testing the Availability Group failover

Monitoring the health of an Availability Group

Chapter 9: SQL Server Development

Introduction

Importing data from a text file

Exporting records to a text file

Adding files to a FileTable

Inserting XML into SQL Server

Extracting XML from SQL Server

Creating an RSS feed from SQL Server content

Applying XSL to an RSS feed

Creating a JSON file from SQL Server

Storing binary data in SQL Server

Extracting binary data from SQL Server

Creating a new assembly

Listing user-defined assemblies

Extracting user-defined assemblies

Chapter 10: Business Intelligence

Introduction

Listing items in your SSRS Report Server

Listing SSRS report properties

Using ReportViewer to view your SSRS report

Downloading an SSRS report in Excel and as a PDF

Creating an SSRS folder

Creating an SSRS data source

Changing an SSRS report's data source reference

Uploading an SSRS report to Report Manager

Downloading all SSRS report RDL files

Adding a user with a role to SSRS report

Creating folders in an SSIS package store and MSDB

Deploying an SSIS package to the package store

Executing an SSIS package stored in a package store or filesystem

Downloading an SSIS package to a file

Creating an SSISDB catalog

Creating an SSISDB folder

Deploying an ISPAC file to SSISDB

Executing an SSIS package stored in SSISDB

Listing SSAS cmdlets

Listing SSAS instance properties

Backing up an SSAS database

Restoring an SSAS database

Processing an SSAS cube

Chapter 11: Helpful PowerShell Snippets

Introduction

Documenting PowerShell script for Get-Help

Getting history

Getting a timestamp

Getting more error messages

Listing processes

Getting aliases

Exporting to CSV and XML

Using Invoke-Expression

Testing regular expressions

Managing folders

Manipulating files

Compressing files

Searching for files

Reading an event log

Sending an e-mail

Embedding C# code

Creating an HTML report

Parsing XML

Extracting data from a web service

Using PowerShell remoting

What You Will Learn

Explore database objects and execute queries on multiple servers

Manage and monitor the running of SQL Server services and accounts

Back up and restore databases

Create an inventory of database properties and server configuration settings

Maintain permissions and security for users

Work with CLR assemblies, XML, and BLOB objects in SQL

Manage and deploy SSIS packages and SSRS reports

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 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. Her blog is located at www.sqlbelle.com and her Twitter handle is @sqlbelle.

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.