Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

Released June 2009
Publisher(s): Wrox
ISBN: 9780470477281

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The definitive guide to automating SQL Server admnistration with Powershell scripts

If you're a SQL Server database administrator, this book will make your life easier. Windows PowerShell is an administrative scripting tool that allows you to automate many tasks you're probably currently doing by hand. This nuts-and-bolts guide shows you how to create Windows PowerShell scripts to administer almost every aspect of SQL Server.

You'll get a thorough introduction to PowerShell basics and all the PowerShell components that have been integrated into SQL Server 2008, as well as actual administration tasks. The book provides complete scripts that have been tested and proven, saving you hours of effort.

  • The new release of Microsoft SQL Server 2008 now includes Windows PowerShell, allowing administrators of large-scale enterprise databases to automate many tasks

  • SQL Server 2008 Administration with Windows PowerShell introduces you to PowerShell, covering its components in detail as well as providing basic instruction in using scripts to administer the database

  • The book includes numerous examples of SQL tasks that have been automated and also provides completed scripts that you can put to use immediately

  • The Companion Web site includes complete code scripts

If you're a database administrator, the scripts and techniques in SQL Server 2008 Administration with Windows PowerShell will save you hours of effort.

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
    2. What This Book Covers
    3. How This Book Is Structured
    4. What You Need to Use This Book
    5. Conventions
    6. Source Code
    7. Errata
    8. p2p.wrox.com
  6. 1. What Is Windows PowerShell?
    1. 1.1. Command-Line Interfaces versus Graphical User Interfaces
    2. 1.2. Prerequisites for Installing Windows PowerShell 2.0
      1. 1.2.1. Installing the Microsoft .NET Framework
      2. 1.2.2. Installing Windows Remote Management
    3. 1.3. Installing Windows PowerShell
    4. 1.4. Launching Windows PowerShell
      1. 1.4.1. Using the Command console
      2. 1.4.2. Using All Programs
      3. 1.4.3. Using Start Run
    5. 1.5. Summary
  7. 2. Windows PowerShell Command Types, Snap-ins, and cmdlets
    1. 2.1. PowerShell Command Types
      1. 2.1.1. Native Commands
      2. 2.1.2. PowerShell cmdlets
      3. 2.1.3. Shell Function Commands
      4. 2.1.4. Script Commands
      5. 2.1.5. Calling PowerShell cmdlets or Scripts from the Command Shell
    2. 2.2. PowerShell Snap-ins
      1. 2.2.1. Core
      2. 2.2.2. Host
      3. 2.2.3. PowerShell.Management
      4. 2.2.4. Security
      5. 2.2.5. Utility
      6. 2.2.6. Diagnostics
      7. 2.2.7. WsMan.Management
    3. 2.3. PowerShell cmdlet Syntax
      1. 2.3.1. Named Parameter
      2. 2.3.2. Positional Parameter
      3. 2.3.3. Command Chaining
    4. 2.4. Getting Help
      1. 2.4.1. Filtering
    5. 2.5. Sorting
    6. 2.6. Formatting
    7. 2.7. Summary
  8. 3. Windows PowerShell Programming, Scripting, Error Handling, and Debugging
    1. 3.1. PowerShell Scripts
    2. 3.2. PowerShell Variables
    3. 3.3. Arrays
    4. 3.4. Expressions
      1. 3.4.1. Operators
    5. 3.5. Conditional Expressions
      1. 3.5.1. IF Statement
    6. 3.6. Loop Construct
      1. 3.6.1. For Loop
      2. 3.6.2. While Loop
      3. 3.6.3. DoWhile Loop
      4. 3.6.4. DoUntil Loop
      5. 3.6.5. Foreach Statement
    7. 3.7. Input
    8. 3.8. PowerShell Scripting
    9. 3.9. Text File As Input
    10. 3.10. Output
      1. 3.10.1. Writing to the Console
      2. 3.10.2. Writing to a File
    11. 3.11. Error Handling
    12. 3.12. Debugging
    13. 3.13. Summary
  9. 4. Windows PowerShell Functions, Parameters, Sourcing, Scopes, and User Profiles
    1. 4.1. Functions
      1. 4.1.1. Arguments in Functions
      2. 4.1.2. Returning Values
    2. 4.2. Script Parameters
    3. 4.3. Sourcing
    4. 4.4. Variable Scope
      1. 4.4.1. Function Scope
      2. 4.4.2. User Profiles
    5. 4.5. Transcripts
    6. 4.6. Summary
  10. 5. Working with the File System, Registry, and Variables
    1. 5.1. Using Get-PSDrive and Get-PSProvider
    2. 5.2. Working with File System
      1. 5.2.1. Navigating the File System
      2. 5.2.2. Managing Files and Directories
      3. 5.2.3. Managing File Contents
    3. 5.3. Working with the Registry
    4. 5.4. Working with Variables
      1. 5.4.1. Automatic Variables
      2. 5.4.2. Preference Variables
      3. 5.4.3. User-Created Variables
        1. 5.4.3.1. New-Variable: Creates a New Variable
        2. 5.4.3.2. Get-Variable: Gets the Variables
        3. 5.4.3.3. Set-Variable: Sets the Value of Variables or Changes the Properties of the Variables
        4. 5.4.3.4. Remove-Variable: Removes Variables
        5. 5.4.3.5. Clear-Variable: Deletes the Value of Variables and Makes the Variables Null
    5. 5.5. Working with Environment Variables
    6. 5.6. Summary
  11. 6. Working with Event Logs
    1. 6.1. Event Log Service
    2. 6.2. Event Viewer
    3. 6.3. Event Logs
    4. 6.4. Log Entry Types
    5. 6.5. Exporting the event logs
      1. 6.5.1. PowerShell cmdlets Related to Event Log
    6. 6.6. Summary
  12. 7. Working with Windows Services and Processes
    1. 7.1. What Is a Windows Service?
    2. 7.2. Windows PowerShell and Windows Services
      1. 7.2.1. Get-Service
      2. 7.2.2. Stop-Service
      3. 7.2.3. Start-Service
      4. 7.2.4. Set-Service
    3. 7.3. Working with Windows Processes
    4. 7.4. Get-Process
    5. 7.5. Stop-Process
    6. 7.6. Summary
  13. 8. Working with WMI
    1. 8.1. Permission Issues and WMI
    2. 8.2. The WMI Model
    3. 8.3. Working with Event Log
    4. 8.4. Working with Services
    5. 8.5. Working with Processes
    6. 8.6. Working with Environment Variables
    7. 8.7. Working with the Registry
    8. 8.8. Summary
  14. 9. WMI Provider for Configuration Management
    1. 9.1. Managing SQL Server Services
    2. 9.2. Managing Client Network Protocols
    3. 9.3. Managing SQL Server Client Aliases
    4. 9.4. Managing Server Network Protocols
    5. 9.5. Changing FILESTREAM Settings
    6. 9.6. Changing SQL Server Advanced Properties
    7. 9.7. Summary
  15. 10. WMI Provider for Server Events
    1. 10.1. WMI Provider for Server Events
    2. 10.2. WMI Query Language (WQL)
    3. 10.3. Event Handling with Windows PowerShell 2.0
    4. 10.4. Monitoring Errors from the SQL Server Error Log
    5. 10.5. Monitoring Deadlocks
    6. 10.6. Monitoring Blockings
    7. 10.7. Monitoring Login Changes and Failed Login Attempts
    8. 10.8. Monitoring Databases
    9. 10.9. Monitoring Database Objects
    10. 10.10. Summary
  16. 11. Windows PowerShell in SQL Server 2008 Environment, SQL Server PowerShell Provider
    1. 11.1. sqlps Utility
    2. 11.2. SQLSERVER: Drive and Invoke-Sqlcmd cmdlet
    3. 11.3. SQL Snap-ins
    4. 11.4. Encoding and Decoding Uniform Resource Name (URN)
    5. 11.5. Summary
  17. 12. Managing Policies through SQLSERVER:\SQLPolicy
    1. 12.1. SQLSERVER:\SQLPolicy Folder
    2. 12.2. Conditions
    3. 12.3. Policies
    4. 12.4. Summary
  18. 13. Windows PowerShell and SMO
    1. 13.1. PowerShell and the SQLConnection .NET Class
    2. 13.2. Working with SQL Server using SMO
      1. 13.2.1. Methods and Properties
      2. 13.2.2. Getting Version Information
      3. 13.2.3. SQL Server SPID Information
      4. 13.2.4. SQL Server Server-Related Information
      5. 13.2.5. SQL Server Database-Related Information
      6. 13.2.6. Changing the Login Mode
      7. 13.2.7. Host Information
      8. 13.2.8. Performance Counters
    3. 13.3. Working with Databases using SMO
    4. 13.4. Working with Tables using SMO
      1. 13.4.1. Creating Tables
      2. 13.4.2. Listing Columns
      3. 13.4.3. Removing Columns
      4. 13.4.4. Adding Columns
      5. 13.4.5. Dropping a Table
    5. 13.5. Backup and Restore with SMO
      1. 13.5.1. Database Backup
      2. 13.5.2. Transaction Log Backup
      3. 13.5.3. Differential Backup
      4. 13.5.4. Restoring Full Backup
      5. 13.5.5. Restoring a Full Backup and Transaction Log Backup
    6. 13.6. Summary
  19. 14. Building SQL Server Standards and PowerShell Coding Standards
    1. 14.1. SQL Server Standards
    2. 14.2. SQL Server Development Standards
      1. 14.2.1. Naming Conventions
        1. 14.2.1.1. Databases
        2. 14.2.1.2. Tables
        3. 14.2.1.3. Views
        4. 14.2.1.4. Stored Procedures
        5. 14.2.1.5. User-Defined Functions
        6. 14.2.1.6. Triggers
        7. 14.2.1.7. Indexes
        8. 14.2.1.8. Columns
        9. 14.2.1.9. User-Defined Data Types
        10. 14.2.1.10. Primary Keys
        11. 14.2.1.11. Foreign Keys
        12. 14.2.1.12. Default and Check Constraints
        13. 14.2.1.13. Variables
        14. 14.2.1.14. Roles
      2. 14.2.2. General Rules
    3. 14.3. Stored Procedure Standards
      1. 14.3.1. Keep Them Small
      2. 14.3.2. "DBO" As Object Owner
      3. 14.3.3. Use Comments Generously
      4. 14.3.4. Select *
      5. 14.3.5. Cursors
      6. 14.3.6. Temporary Tables
      7. 14.3.7. Things to Avoid
      8. 14.3.8. Things to Use
      9. 14.3.9. Check @@ERROR
      10. 14.3.10. Use SQL Server Date Data Types
      11. 14.3.11. DML Statements
      12. 14.3.12. ANSI-Standard Join Clauses
      13. 14.3.13. Deprecated Features
    4. 14.4. Database Design Standards and Best Practices
      1. 14.4.1. User-Defined Tables
      2. 14.4.2. Logs
      3. 14.4.3. Split tempdb
      4. 14.4.4. Databases
      5. 14.4.5. Security and Roles
      6. 14.4.6. Auto Create and Auto Update
      7. 14.4.7. Size
      8. 14.4.8. Auto Shrink and Auto Close
      9. 14.4.9. Design and Performance
      10. 14.4.10. Store Unstructured Data
      11. 14.4.11. More Performance Guidelines
    5. 14.5. Data Protection Standards and Best Practices
      1. 14.5.1. Backup Policy
    6. 14.6. SQL Server Production Standards
      1. 14.6.1. High Availability and Disaster Recovery
      2. 14.6.2. The Administration Database
      3. 14.6.3. The Scratch Database
      4. 14.6.4. Centralized Inventory Server
      5. 14.6.5. Database File Location and RAID
      6. 14.6.6. Segregation
      7. 14.6.7. Features
    7. 14.7. PowerShell Coding Standard
      1. 14.7.1. Default Parameters
      2. 14.7.2. Log File
      3. 14.7.3. Log Format
      4. 14.7.4. Comments
      5. 14.7.5. Display
      6. 14.7.6. Variable Naming Convention
      7. 14.7.7. Exception Handling
    8. 14.8. Summary
  20. 15. Building SQL Server Inventory
    1. 15.1. SQL Server Inventory
    2. 15.2. Hosts
    3. 15.3. Clusters
    4. 15.4. ClusterNodes
    5. 15.5. Servers
    6. 15.6. Databases
    7. 15.7. Supplementary Tables
    8. 15.8. Summary
  21. 16. SQL Server Installation
    1. 16.1. Installation Procedure and Template
      1. 16.1.1. SQL Server Installation Template
    2. 16.2. Standalone Installation Example
    3. 16.3. Cluster Installation Example
    4. 16.4. Summary
  22. 17. Collecting SQL Server Performance and Host Performance Data
    1. 17.1. SQL Server Performance Data Collection
    2. 17.2. SQL Server Host Performance Data Collection
    3. 17.3. Summary
  23. 18. Monitoring SQL Server
    1. 18.1. Pinging SQL Server Hosts
    2. 18.2. Checking SQL Server–related Services on SQL Server Hosts
    3. 18.3. Checking Uptime of SQL Server Hosts
    4. 18.4. Monitoring Windows Event Logs
    5. 18.5. Monitoring SQL Server Error Log
    6. 18.6. Monitoring Blockings
    7. 18.7. Monitoring Deadlocks
    8. 18.8. Summary
  24. 19. Monitoring Disk Space Usage, Database Files, and Backups
    1. 19.1. Monitoring Disk Space Usage
    2. 19.2. Monitoring Database Files
    3. 19.3. Monitoring Backups
    4. 19.4. Summary
  25. 20. Defining Policies
    1. 20.1. Stored Procedure Naming Convention Policy
    2. 20.2. Auto_Close and Auto_Shrink Off Policy
    3. 20.3. Summary
  26. 21. Generating Database Scripts
    1. 21.1. Scripting Databases
    2. 21.2. Scripting Schemas
    3. 21.3. Scripting User-Defined Data Types
    4. 21.4. Scripting Tables
    5. 21.5. Scripting User Views
    6. 21.6. Scripting Stored Procedures
      1. 21.6.1. Scripting Functions
    7. 21.7. Scripting XML Schemas
    8. 21.8. Scripting Users
    9. 21.9. Summary
  27. A. cmdlets
    1. A.1. cmdlets Related to Core Snap-ins
    2. A.2. cmdlets Related to the PowerShell Management Snap-in
    3. A.3. cmdlets Related to the Security Snap-in
    4. A.4. cmdlets Related to the Utility Snap-in

Product information

  • Title: Microsoft® SQL Server® 2008 Administration with Windows PowerShell™
  • Author(s):
  • Release date: June 2009
  • Publisher(s): Wrox
  • ISBN: 9780470477281