SQL Server 2014 with PowerShell v5 Cookbook电子书

SQL Server 2014 with PowerShell v5 Cookbook

Table of Contents

SQL Server 2014 with PowerShell v5 Cookbook

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

Sections

Getting ready

How to do it…

How it works…

There's more…

See also

Conventions

Reader feedback

Customer support

Downloading the example code

Downloading the color images of this book

Errata

Piracy

Questions

1. Getting Started with SQL Server and PowerShell

Introduction

Working with SQL Server and PowerShell

Running as an administrator

Execution Policy

Running scripts

Running different PowerShell versions

Line continuation

PowerShell modules

Working with the sample code

How to do it...

See also

Installing SQL Server using PowerShell

Getting ready

How to do it...

How it works...

There's more...

Installing SQL Server Management Objects

Getting ready

How to do it...

There's more...

Loading SMO assemblies

Getting ready

How to do it...

How it works...

There's more...

See also

Exploring the SQL Server PowerShell hierarchy

Getting ready

How to do it...

How it works...

Discovering SQL-related cmdlets and modules

Getting ready

How to do it...

How it works...

There's more...

Creating a SQL Server Instance Object

Getting ready

How to do it...

How it works...

See also

Exploring SMO Server Objects

Getting ready

How to do it...

How it works...

See also

2. SQL Server and PowerShell Basic Tasks

Introduction

Listing SQL Server instances

Getting ready

How to do it...

How it works...

There's more...

Discovering SQL Server services

Getting ready

How to do it...

How it works...

There's more...

See also

Starting/stopping SQL Server services

Getting ready

How to do it...

How it works...

There's more...

See also

Listing SQL Server configuration settings

How to do it...

How it works...

There's more...

Changing SQL Server Instance configurations

Getting ready

How to do it...

How it works...

There's more...

See also

Searching for database objects

Getting ready

How to do it...

How it works...

There's more...

See also

Scripting SQL Server Stored Procedures

Getting ready

How to do it...

How it works...

There's more...

Creating a database

Getting ready

How to do it...

How it works...

Altering database properties

Getting ready

How to do it...

How it works...

See also

Dropping a database

Getting ready

How to do it...

How it works...

Changing database owner

Getting ready

How to do it...

How it works...

See also

Creating a table

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a view

Getting ready

How to do it...

How it works...

There's more...

Creating a stored procedure

Getting ready

How to do it...

How it works...

Creating a trigger

Getting ready

How to do it...

How it works...

Creating an index

Getting ready

How to do it...

How it works...

There's more...

See also

Executing a query/SQL script

Getting ready

How to do it...

How it works...

Performing bulk export using Invoke-SqlCmd

Getting ready

How to do it...

How it works...

See also

Performing bulk export using the bcp command-line utility

Getting ready

How to do it...

There's more...

See also

Performing bulk import using BULK INSERT

Getting ready

How to do it...

How it works...

See also

Performing bulk import using the bcp command-line utility

Getting ready

How to do it...

How it works...

See also

Connecting to an Azure SQL database

Getting ready

How to do it...

How it works...

There's more...

There's more...

Creating a table in an Azure SQL database

Getting ready

How to do it...

How it works...

3. Basic Administration

Introduction

Creating a SQL Server instance inventory

How to do it...

How it works...

There's more...

See also

Creating a SQL Server database inventory

Getting ready

How to do it...

How it works...

See also

Listing installed hotfixes and Service Packs

How to do it...

How it works...

There's more...

Listing running/blocking processes

Getting ready

How to do it...

How it works...

There's more...

See also

Killing a blocking process

Getting ready

How to do it...

How it works...

There's more...

See also

Checking disk space usage

How to do it...

How it works...

There's more...

Setting up WMI server event alerts

Getting ready

How to do it...

How it works...

There's more...

Detaching a database

Getting ready

How to do it...

How it works...

There's more...

See also

Attaching a database

Getting ready

How to do it...

How it works...

There's more...

See also

Copying a database

Getting ready

How to do it...

How it works...

There's more...

Executing SQL query to multiple servers

Getting ready

How to do it...

How it works...

See also

Creating a filegroup

Getting ready

How to do it...

How it works...

See also

Adding a secondary data file to a filegroup

Getting ready

How to do it...

How it works...

See also

Increase data file size

Getting ready

How to do it...

How it works...

See also

Moving an index to a different filegroup

Getting ready

How to do it...

How it works...

There's more...

See also

Checking index fragmentation

Getting ready

How to do it...

How it works...

There's more...

See also

Reorganizing/rebuilding an index

Getting ready

How to do it...

How it works...

See also

Running DBCC commands

How to do it...

How it works...

Setting up Database Mail

Getting ready

How to do it...

How it works...

Listing SQL Server Jobs

Getting ready

How to do it...

How it works...

There's more...

See also

Adding a SQL Server operator

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a SQL Server Job

Getting ready

How to do it...

How it works...

There's more...

See also

Adding a SQL Server event alert

How to do it...

How it works...

There's more...

See also

Running an SQL Server Job

Getting ready

How to do it...

How it works...

See also

Scheduling a SQL Server Job

Getting ready

How to do it...

How it works...

There's more...

See also

4. Security

Introduction

Listing SQL Server service accounts

How to do it...

How it works...

See also

Changing SQL Server service account

Getting ready

How to do it...

How it works...

There's more...

See also

Listing authentication mode

Getting ready

How to do it...

How it works...

See also

Changing authentication mode

Getting ready

How to do it...

How it works...

There's more...

More on legacy LoginMode values

See also

Listing SQL Server log errors

Getting ready

How to do it...

How it works...

See also

Listing failed login attempts

Getting ready

How to do it...

How it works...

See also

Enabling Common Criteria compliance

How to do it...

How it works...

There's more...

See also

Listing logins, users, and database mappings

Getting ready

How to do it...

How it works...

There's more...

See also

Listing login/user roles and permissions

How to do it...

How it works...

See also

Creating a user-defined server role

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a login

Getting ready

How to do it...

How it works...

See also

Assigning permissions and roles to a login

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a database user

Getting ready

How to do it...

How it works...

See also

Assigning permissions to a database user

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a database role

Getting ready

How to do it...

How it works...

See also

Fixing orphaned users

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a credential

Getting ready

How to do it...

How it works...

See also

Creating a proxy

Getting ready

How to do it...

How it works...

There's more...

See also

5. Backup and Restore

Introduction

Changing database recovery model

Getting ready

How to do it...

How it works...

There's more...

See also

Checking last backup date

Getting ready

How to do it...

How it works...

See also

Creating a backup device

Getting ready

How to do it...

How it works...

There's more...

See also

Listing backup header and FileList information

Getting ready

How to do it...

How it works...

There's more...

Creating a full backup

Getting ready

How to do it...

How it works...

There's more...

More about backup and PercentCompleteEventHandler

See also

Creating a backup on Mirrored Media Sets

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a differential backup

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a transaction log backup

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a filegroup backup

Getting ready

How to do it...

How it works...

There's more...

See also

Restoring a database to a point-in-time

Getting ready

How to do it...

How it works...

Gathering your backup files

Restoring the latest good full backup with NORECOVERY

Restoring the last good differential backup taken after the full backup you just restored with NORECOVERY

Restoring the transaction logs taken after your differential backup

There's more...

See also

Performing an online piecemeal restore

Getting ready

How to do it...

How it works...

There's more...

See also

Backing up database to Azure Blob storage

Getting ready

How to do it...

How it works...

There's more...

See also

Restoring database from Azure Blob storage

Getting ready

How to do it...

How it works...

There's more...

See also

6. Advanced Administration

Introduction

Connecting to LocalDB

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a new LocalDB instance

Getting ready

How to do it...

How it works...

There's more...

See also

Listing database snapshots

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a database snapshot

Getting ready

How to do it...

How it works...

See also

Dropping a database snapshot

How to do it...

How it works...

See also

Enabling FileStream

How to do it...

How it works...

There's more...

See also

Setting up a FileStream filegroup

Getting ready

How to do it...

How it works...

There's more...

See also

Adding a FileTable

Getting ready

How to do it...

How it works...

There's more...

See also

Adding full-text catalog

Getting ready

How to do it...

How it works...

There's more...

See also

Adding full-text index

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a memory-optimized table

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a database master key

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a certificate

Getting ready

How to do it...

How it works...

There's more...

See also

Creating symmetric and asymmetric keys

Getting ready

How to do it...

How it works...

There's more...

See also

Setting up Transparent Data Encryption

Getting ready

How to do it...

How it works...

There's more...

See also

7. Audit and Policies

Introduction

Enabling/disabling change tracking

Getting ready

How to do it...

How it works...

There's more...

See also

Configuring SQL Server Audit

How to do it...

How it works...

There's more...

See also

Listing facets and their properties

How to do it...

How it works...

There's more...

See also

Listing policies

Getting ready

How to do it...

How it works...

There's more...

See also

Exporting a policy

Getting ready

How to do it...

How it works...

There's more...

See also

Importing a policy

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a condition

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a policy

Getting ready

How to do it...

How it works...

There's more...

See also

Evaluating a policy

Getting ready

How to do it...

How it works...

There's more...

See also

Running and saving a profiler trace event

Getting ready

How to do it...

How it works...

There's more...

See also

Extracting the contents of a trace file

Getting ready

How to do it...

How it works...

See also

8. High Availability with AlwaysOn

Introduction

Installing the Failover Cluster feature on Windows

Getting ready

How to do it...

How it works...

There's more...

Enabling TCP and named pipes in SQL Server

Getting ready

How to do it...

How it works...

There's more...

Enabling AlwaysOn in SQL Server

Getting ready

How to do it...

How it works...

There's more...

Creating and enabling the HADR endpoint

Getting ready

How to do it...

How it works...

There's more...

See also

Granting the CONNECT permission to the HADR endpoint

Getting ready

How to do it...

How it works...

There's more...

See also

Creating an AlwaysOn Availability Group

Getting ready

How to do it...

How it works...

There's more...

Joining the secondary replicas to Availability Group

Getting ready

How to do it...

How it works...

There's more...

See also

Adding an availability database to an Availability Group

Getting ready

How to do it...

How it works...

There's more...

Creating an Availability Group listener

Getting ready

How to do it...

How it works...

There's more...

Testing the Availability Group failover

Getting ready

How to do it...

How it works...

There's more...

Monitoring the health of an Availability Group

Getting ready

How to do it...

How it works...

There's more...

9. SQL Server Development

Introduction

Importing data from a text file

Getting ready

How to do it...

How it works...

There's more...

Exporting records to a text file

Getting ready

How to do it...

How it works...

There's more...

Adding files to a FileTable

Getting ready

How to do it...

How it works...

There's more...

See also

Inserting XML into SQL Server

Getting ready

How to do it...

How it works...

There's more...

See also

Extracting XML from SQL Server

Getting ready

How to do it...

How it works...

See also

Creating an RSS feed from SQL Server content

Getting ready

How to do it...

How it works...

There's more...

See also

Applying XSL to an RSS feed

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a JSON file from SQL Server

Getting ready

How to do it...

How it works...

There's more...

Storing binary data in SQL Server

Getting ready

How to do it...

How it works...

There's more...

See also

Extracting binary data from SQL Server

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a new assembly

Getting ready

How to do it...

How it works...

There's more...

See also

Listing user-defined assemblies

Getting ready

How to do it...

How it works...

There's more...

See also

Extracting user-defined assemblies

Getting ready

How to do it...

How it works...

See also

10. Business Intelligence

Introduction

Listing items in your SSRS Report Server

Getting ready

How to do it...

How it works...

There's more...

See also

Listing SSRS report properties

Getting ready

How to do it...

How it works...

There's more...

See also

Using ReportViewer to view your SSRS report

Getting ready

How to do it...

How it works...

There's more...

See also

Downloading an SSRS report in Excel and as a PDF

Getting ready

How to do it...

How it works...

There's more...

See also

Creating an SSRS folder

Getting ready

How to do it...

How it works...

There's more...

See also

Creating an SSRS data source

Getting ready

How to do it...

How it works...

There's more...

See also

Changing an SSRS report's data source reference

Getting ready

How to do it...

How it works...

There's more...

See also

Uploading an SSRS report to Report Manager

Getting ready

How to do it...

How it works...

There's more...

See also

Downloading all SSRS report RDL files

Getting ready

How to do it...

How it works...

There's more...

See also

Adding a user with a role to SSRS report

Getting ready

How to do it...

How it works...

There's more...

Creating folders in an SSIS package store and MSDB

Getting ready

How to do it...

How it works...

There's more...

See also

Deploying an SSIS package to the package store

Getting ready

How to do it...

How it works...

There's more...

See also

Executing an SSIS package stored in a package store or filesystem

Getting ready

How to do it...

How it works...

There's more...

See also

Downloading an SSIS package to a file

Getting ready

How to do it...

How it works...

There's more...

See also

Creating an SSISDB catalog

Getting ready

How to do it...

How it works...

There's more...

See also

Creating an SSISDB folder

Getting ready

How to do it...

How it works...

There's more...

See also

Deploying an ISPAC file to SSISDB

Getting ready

How to do it...

How it works...

There's more...

See also

Executing an SSIS package stored in SSISDB

Getting ready

How to do it...

How it works...

There's more...

See also

Listing SSAS cmdlets

How to do it...

How it works...

There's more...

See also

Listing SSAS instance properties

How to do it...

How it works...

There's more...

See also

Backing up an SSAS database

Getting ready

How to do it...

How it works...

There's more...

See also

Restoring an SSAS database

Getting ready

How to do it...

How it works...

There's more...

See also

Processing an SSAS cube

Getting ready

How to do it...

How it works...

There's more...

See also

11. Helpful PowerShell Snippets

Introduction

Documenting PowerShell script for Get-Help

How to do it...

How it works...

There's more...

Getting history

How to do it...

How it works...

Getting a timestamp

How to do it...

How it works...

There's more...

Getting more error messages

How to do it...

How it works...

Listing processes

How to do it...

How it works...

There's more...

See also

Getting aliases

How to do it...

How it works...

There's more...

Exporting to CSV and XML

How to do it...

How it works...

There's more...

Using Invoke-Expression

Getting ready

How to do it...

How it works...

There's more...

Testing regular expressions

How to do it...

How it works...

There's more...

Managing folders

How to do it...

How it works...

There's more...

See also

Manipulating files

How to do it...

How it works...

There's more...

See also

Compressing files

How to do it...

How it works...

Searching for files

How to do it...

How it works...

There's more...

See also

Reading an event log

How to do it...

How it works...

There's more...

Sending an e-mail

Getting ready

How to do it...

How it works...

There's more...

Embedding C# code

How to do it...

How it works...

There's more...

Creating an HTML report

How to do it...

How it works...

There's more...

Parsing XML

Getting ready

How to do it...

How it works...

Extracting data from a web service

How to do it...

How it works...

There's more...

Using PowerShell remoting

Getting ready

How to do it...

How it works...

There's more...

A. PowerShell Primer

Introduction

Understanding the need for PowerShell

Setting up the environment

Running PowerShell scripts

Through shell or through the ISE

The execution policy

Learning PowerShell basics

Cmdlets

Learning PowerShell

Get-Command

Get-Help

Get-Member

Starter notes

PowerShell is object-oriented and works with .NET

Cmdlets may have aliases or you can create one

You can chain commands

Filter left, format right

Package and reuse

Common cmdlets

Scripting syntax

Statement terminators

Escape and line continuation

Variables

Here-string

String interpolation

Operators

Displaying messages

Comments

Special variables

Special characters

Conditions

Regular expressions

Arrays

Hash tables

Loops

Error handling

Converting scripts into functions

Listing notable PowerShell features

Exploring more PowerShell

B. Creating a SQL Server VM

Introduction

Terminologies

Downloading software

VM details and accounts

Creating an empty virtual machine

Installing Windows Server 2012 R2 as guest OS

Installing VMware tools

Making a snapshot as a baseline

Configuring a domain controller (optional)

Creating domain accounts

Installing SQL Server 2014 on a VM

Configuring Reporting Services in native mode

Installing sample databases

Installing PowerShell V5

Using SQL Server on a Windows Azure VM

Index