Automating admin tasks with powershell - SQLBits

healthyapricotMechanics

Nov 5, 2013 (3 years and 7 months ago)

59 views

James Boother

Email: james@boother.co.uk


Twitter: @jimmyboo

Blog: http://www.boother.co.uk

AUTOMATING ADMIN TASKS WITH
POWERSHELL

AGENDA


A brief history lesson


What’s
new in 2.0
?


Quick look at Cmdlets


What it won’t do


Example admin tasks automated with PowerShell


SQL
Server
specific admin tasks automated with PowerShell


Scheduling
Scripts


Resources

IN THE BEGINNING…

AFTER YEARS OF RESEARCH POSH WAS BORN

CMDLETS


Main building block of PowerShell Scripts


Mini Commands that perform one action


Common naming convention: Verb then Noun


e.g.: Get
-
Help, Get
-
Object, Remove
-
Item

Destructive CmdLets have

WhatIf parameter


Actually .NET Classes that can easily be created if new functionality is required


The output of one Cmdlet can be piped into further cmdlets using the | operator


Equality test with expressions such as

eq

lt

le

WHAT’S NEW IN VERSION 2?


PowerShell ISE


pre
-
installed on every Windows 7 & Server 2008 R2 machine


Remoting


Background
Jobs


Transactions


Modules


Eventing


WHAT IT WON’T DO


It won’t make the tea…

DEMO
-

VISUAL SCRIPTING ENVIRONMENT

DEMO


WMI SCRIPT


Full access to WMI from within Powershell:

DELETING OLD BACKUP FILES

DEMO
-

FINDING FAILED SQL JOBS

SCHEDULING SCRIPTS


SQL Server Agent or windows Task Scheduler

SQL SERVER PROVIDER


Open PowerShell from within SQL Server Management Studio


Open SQLPS


Cmdlets and a Provider are provided:





Also instantiate the SQL Server SMO Objects:


DEMO


HELP SERVER DOWN!


SUMMARY


Powerful object based scripting engine


Backwards compatible so you can start from what you already know


Full access to File System; Registry; WMI; COM; .NET Objects and SQL Server


Simple to schedule using Windows Scheduled Tasks or SQL Jobs


Extensible through .NET

BOOKS

QUESTIONS


If you have any questions now’s your chance…

THANK YOU


Thank you for attending
Automating admin tasks with
PowerShell


if you have any further questions please see my contact details here:


Email
: james@boother.co.uk



Twitter: @jimmyboo


Blog: http://www.boother.co.uk


Enjoy the rest of the day, happy scripting!


Coming up…

P/X001

How to Get Full Access to a Database Backup in 3 Minutes or Less

Idera

P/L001

How to Monitor your SQL Servers

Red Gate

P/L002

SQL Server Expert Lunchtime Quiz : Major Myths About Microsoft SQL Server
Quest

P/L005

Expert Query Analysis with SQL Sentry

SQLSentry

P/T007

Attunity

Change Data Capture for SSIS

Attunity

#
SQLBITS