PowerShell for SQL Server Essentials

PowerShell for SQL Server Essentials

by Donabel Santos
Released February 2015
Publisher(s): Packt Publishing
ISBN: 9781784391492

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

Manage and monitor SQL Server administration and application deployment with PowerShell

In Detail

PowerShell for SQL Server Essentials helps us to manage and monitor server administration and application deployment. Use PowerShell along with SQL Server to perform common DBA tasks.

Starting with a basic introduction to PowerShell, the initial chapters will provide the SQL Server professional PowerShell fundamentals, covering topics such as PowerShell notations and syntax, cmdlets, pipeline, and getting help. Succeeding chapters build upon these fundamentals, and illustrate how to administer and automate SQL Server. Tasks covered throughout include profiling the SQL Server instance, performing backup and restores, invoking T-SQL scripts using PowerShell, and monitoring jobs, security, and permissions.

Packed with practical examples and numerous ready-to-use snippets, this book gets you to an intermediate level in using PowerShell for SQL Server.

What You Will Learn

  • Create scripts using PowerShell to manage and monitor server administration and application deployment
  • Automate the creation of SQL Database objects through PowerShell with the help of the SQL Server module (SQLPS) and SQL Server snap-ins
  • Use PowerShell to work with SQL Server specific providers and cmdlets
  • Identify and manage SQL Server services, instances, settings, and confi gurations
  • Profile your SQL Server instances and export current configurations to a file
  • Monitor SQL Server jobs and alerts
  • Manage logins, database users, and instance security and permissions
  • Invoke T-SQL queries from PowerShell and export results
  • Accomplish tasks from your DBA daily/weekly/monthly/yearly checklists with PowerShell

Table of contents

  1. PowerShell for SQL Server Essentials
    1. Table of Contents
    2. PowerShell for SQL Server Essentials
    3. Credits
    4. About the Author
    5. Acknowledgments
    6. About the Reviewers
    7. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
        2. Free access for Packt account holders
        3. Instant updates on new Packt books
    8. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    9. 1. Getting Started with PowerShell
      1. A brief history of PowerShell
      2. The PowerShell environment
        1. The PowerShell console
        2. The PowerShell ISE
        3. Running PowerShell as an administrator
        4. The execution policy
      3. PowerShell versions
      4. PowerShell cmdlets
        1. Cmdlet naming convention
        2. Cmdlet parameters
        3. Cmdlet aliases
      5. PowerShell providers
      6. Snap-ins and modules
      7. PowerShell Pipeline
      8. Scripting basics
      9. Running PowerShell scripts
      10. Getting help
        1. Getting help from other cmdlets
      11. Summary
    10. 2. Using PowerShell with SQL Server
      1. SQL Server via PowerShell
        1. Mini-shell (or the sqlps utility)
        2. The SQLPS module
        3. SQL Server snap-ins
        4. SQL Server assemblies
      2. SQL Server-specific cmdlets
      3. SQL Server Management Objects
        1. Creating SMO objects
      4. Summary
    11. 3. Profiling and Configuring SQL Server
      1. Current server resources
        1. Getting processor (CPU) information
        2. Checking server memory
        3. Checking disk space
        4. Checking network settings
      2. Hotfixes and service packs
      3. Current SQL Server instances
      4. Services and service accounts
      5. SQL Server error logs
      6. Current instance configuration
      7. Changing configurations
        1. Start or stop services
        2. Changing a service account
        3. Changing instance settings
      8. Summary
    12. 4. Basic SQL Server Administration
      1. Listing databases and tables
        1. Listing database files and filegroups
      2. Adding files and filegroups
        1. Listing the processes
      3. Checking enabled features
      4. Scripting database objects
      5. Attaching and detaching databases
        1. Detaching databases
      6. Backing up and restoring databases
        1. Backing up
        2. Restoring
      7. Reorganizing or rebuilding indexes
      8. Managing logins, users, and permissions
        1. Permissions
        2. Adding a login
        3. Adding database users
      9. Policies
      10. Managing jobs
      11. Summary
    13. 5. Querying SQL Server with PowerShell
      1. To PowerShell or not to PowerShell
      2. Sending queries to SQL Server
        1. SQL Server Management Objects
        2. The Invoke-Sqlcmd cmdlet
        3. ADO.NET
        4. The Invoke-Expression cmdlet
        5. Sending simple queries to SQL Server – different variations
      3. Fixing orphaned users
      4. Getting fragmentation data
      5. Backing up and restoring databases
      6. Exporting data using bcp
      7. Summary
    14. 6. Monitoring and Automating SQL Server
      1. Getting to know helpful cmdlets
        1. The Send-MailMessage cmdlet
        2. The ConvertTo-Html cmdlet
        3. The Export-Csv cmdlet
        4. The Write-EventLog cmdlet
        5. Additional cmdlets
      2. Scheduling PowerShell scripts
      3. Checking logs
      4. Monitoring failed jobs
      5. Alerting on disk space usage
      6. Logging blocked processes
      7. Getting performance metrics
      8. Summary
    15. A. Implementing Reusability with Functions and Modules
      1. Functions
        1. Simple functions
        2. Advanced functions
        3. Best practices
      2. Modules
        1. Script modules
      3. Summary
    16. Index

Product information

  • Title: PowerShell for SQL Server Essentials
  • Author(s): Donabel Santos
  • Release date: February 2015
  • Publisher(s): Packt Publishing
  • ISBN: 9781784391492