SQL Server Power Shell Marek Chmel MVP Data

SQL Server & Power. Shell Marek Chmel MVP: Data Platform | MCSE: Data Management and Analytics | MCT: Regional Lead | CEH v 8 marek. chmel@technet. ms @marekchmel

Motivation § Why Use Power. Shell to Manage SQL Server − Lightweight Access to Server Management − Repeatable Management through Scripting − Access both Windows and SQL Server Properties § “Power. Shell is a Core IT Skill – Learn it or be Left Behind” − Basics of using Power. Shell with SQL Server − Use of the SQL Server Provider − Using SMO (SQL Management Objects)

Environment & Security § Command Line − Tab completion auto completes commands, etc. − Get-History returns previously run commands − Up/Down arrows scrolls through previously run commands § Integrated Scripting Environment – ISE (PS 2. 0+) § Scripts allow you to batch commands together § You must include the path to the script to run it − By requiring the path, prevents scripts from “hijacking” operating system commands § By default you cannot run scripts − Set-Execution. Policy set by default to Restricted − Change to Remote. Signed to run local scripts − NOT the case for sqlps. exe, though

Power. Shell Language § Power. Shell is an Object-Based Language − Not Object-Oriented § Cmdlets are Command-Line Utilities built into Power. Shell § They use a Verb-Noun Naming Convention − Get-Process − Stop-Service § Three most important cmdlets − Get-Help − Get-Command − Get-Member

SQL Server & Power. Shell options § SMO − SMO: SQL Management Objects −. Net Libraries − Contain Objects for SQL Server [App. Domain]: : Current. Domain. Get. Assemblies() | Where-Object {$_. Full. Name -match "Sql. Server" } | Select-Object Full. Name

SQL Provider § § Power. Shell Module Cmd. Lets for SQL Server Navigable tree Types, Formats § Get-Command. Type Cmdlet –Module SQLPS, SQLASCMDLETS | Select-Object Name, Module | Sort -Object Module, Name | Format-Table -Auto. Size

DEMOS

WMI Service Types Sql. Service. Type Description 1 SQL Server Service 2 SQL Server Agent Service 3 Full-Text Search Engine Service 4 Integration Services Service 5 Analysis Service 6 Reporting Services Service 7 SQL Browser Service

Main SMO Objects Server Property Description Information This includes nonconfigurable instance settings, such as Build. Number, Edition, OSVersion, and Product. Level. This also includes settings specified during install, for example, Collation, Master. DBPath, and Master. DBLog. Path. Settings This lists some instance level configurable settings, such as Login. Mode and Backup. Directory. User. Options This has options that can be set for user connections, such as Ansi. Warnings, Ansi. Nulls, Ansi. Padding, and No. Count. Configuration This contains instance-specific settings such as Agent. XPs, remote access, clr enabled, and xp_cmdshell, which you will normally see and set when you use the sp_configure system stored procedure.

Configuration with Power. Shell § Depending on what server properties you need to change, you may need to determine which of the following classes you may need to access: Settings, User. Options, or Configuration. § Once you have determined which class and property you want to change, you can change the values and invoke the Alter() method:

Why Maintain a Server Inventory § Need an overview of your Domain § Allows you to identify outliers § You can see resource usage − How much are you using now? − What's the growth rate? § Reports help you justify new resources

What Data Do You Collect? § § § Computer Name, Domain, Make, Model Operating System Versions and Patch Levels Total Physical Memory Local Disk (SAN storage is local) SQL Server Instance Information − Version, Edition, Service Pack − Default Data, Log, Backup Location − Configuration Options − Databases with files and sizes − Logins and Users

A Little Bit about WMI § § WMI - Windows Management Instrumentation Provides Access to All Windows System Information Grouped into Classes Win 32_Computer. System − provides computer name and model, number of processors, etc. § Win 32_Operating. System − provides OS type, service pack installed, etc. § Win 32_Physical. Memory − provides physical memory device, and capacity, etc. § Win 32_Logical. Disk − provides local storage size, free space, etc.

Getting WMI Data § Power. Shell V 2 and V 1 – Get-WMIObject § Power. Shell V 3+ – Get-Cim. Instance § Get-WMIObject is Deprecated − Uses remote procedure calls (RPCs) − Can cause problems with access, firewall issues § Get-Cim. Instance uses Win. RM

Capture Performance Baseline § Baseline shows normal performance § Deviations from Baseline require investigation § Frequency of Data Capture is important − Too often impacts performance − Too seldom can miss important events − Balance comes with experience

Scripting the Data Capture § Capture the counter data # Initialize Perfcounters $ppt = new-object System. Diagnostics. Performance. Counter $ppt. Category. Name = 'Processor' $ppt. Counter. Name = '% Processor Time' $ppt. Instance. Name = '_Total' $pptv = $ppt. nextvalue() § Insert into Performance Database § Wait defined interval and do it again

Q&A Marek Chmel MVP: Data Platform | MCSE: Data Management and Analytics | MCT: Regional Lead | CEH v 8 marek. chmel@technet. ms @marekchmel