Book Image

SQL Server 2012 with PowerShell V3 Cookbook

By : Donabel Santos
Book Image

SQL Server 2012 with PowerShell V3 Cookbook

By: Donabel Santos

Overview of this book

PowerShell is Microsoft's new command-line shell and scripting language that promises to simplify automation and integration across different Microsoft applications and components. Database professionals can leverage PowerShell by utilizing its numerous built-in cmdlets, or using any of the readily available .NET classes, to automate database tasks, simplify integration, or just discover new ways to accomplish the job at hand."SQL Server 2012 with PowerShell V3 Cookbook" provides easy-to-follow, practical examples for the busy database professional. Whether you're auditing your servers, or exporting data, or deploying reports, there is a recipe that you can use right away!You start off with basic topics to get you going with SQL Server and PowerShell scripts and progress into more advanced topics to help you manage and administer your SQL Server databases.The first few chapters demonstrate how to work with SQL Server settings and objects, including exploring objects, creating databases, configuring server settings, and performing inventories. The book then deep dives into more administration topics like backup and restore, credentials, policies, jobs.Additional development and BI-specific topics are also explored, including deploying and downloading assemblies, BLOB data, SSIS packages, and SSRS reports. A short PowerShell primer is also provided as a supplement in the Appendix, which the database professional can use as a refresher or occasional reference material. Packed with more than 100 practical, ready-to-use scripts, "SQL Server 2012 with PowerShell V3 Cookbook" will be your go-to reference in automating and managing SQL Server.
Table of Contents (21 chapters)
SQL Server 2012 with PowerShell V3 Cookbook
Credits
About the Author
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface
Resources
Index

Introduction


PowerShell is an administrative tool that has both shell and scripting capabilities that can leverage Windows Management Instrumentation (WMI), COM components, and .NET libraries. PowerShell is becoming more prominent with each generation of Microsoft products. Support for it is being bundled, and improved, in a number of new and upcoming Microsoft product releases. Windows Server, Exchange, ActiveDirectory, SharePoint, and even SQL Server, have all shipped with added PowerShell support and cmdlets. Even vendors such as VMWare, Citrix, Cisco, and Quest, to name a few, have provided ways to allow their products to be accessible via PowerShell.

What makes PowerShell tick? Every systems administrator probably knows the pain of trying to integrate heterogeneous systems using some kind of scripting. Historically, the solution involved some kind of VBScript, some good old batch files, maybe some C# code, some Perl—you name it. Sysadmins either had to resort to duct taping different languages together to get things to work the way they intended, or just did not bother because of the complicated code.

This is where PowerShell comes in. One of the strongest points for PowerShell is that it simplifies automation and integration between different Microsoft ecosystems. As most products have support for PowerShell, getting one system to talk to another is just a matter of discovering what cmdlets, functions, or modules need to be pulled into the script. Even if the product does not have support yet for PowerShell, it most likely has .NET or COM support, which PowerShell can easily use.

Notable PowerShell V3 features

Some of the notable features in the latest PowerShell version are:

  • Workflows: PowerShell V3 introduces Windows PowerShell Workflow (PSWF), which as stated in MSDN (http://msdn.microsoft.com/en-us/library/jj134242.aspx):

    helps automate the distribution, orchestration, and completion of multi-computer tasks, freeing users and administrators to focus on higher-level tasks.

    PSWF leverages Windows Workflow Foundation 4.0 for the declarative framework, but using familiar PowerShell syntax and constructs.

  • Robust sessions: PowerShell V3 supports more robust sessions. Sessions can now be retained amid network interruptions. These sessions will remain open until they time out.

  • Scheduled jobs: There is an improved support for scheduled tasks. There are new cmdlets in the PSScheduledJob module that allow you to create, enable, and manage scheduled tasks.

  • Module AutoLoading: If you use a cmdlet that belongs to a module that hasn't been loaded yet, this will trigger PowerShell to search PSModulePath and load the first module that contains that cmdlet. This is something we can easily test:

    #check current modules in session
    Get-Module
    
    #use cmdlet from CimCmdlets module, which
    #is not loaded yet
    Get-CimInstance win32_bios 
    
    #note new module loaded CimCmdlets
    Get-Module 
    
    #use cmdlet from SQLPS module, which
    #is not loaded yet
    Invoke-Sqlcmd -Query "SELECT GETDATE()" -ServerInstance "KERRIGAN"
    
    #note new modules loaded SQLPS and SQLASCmdlets 
    Get-Module
  • Web service support: PowerShell V3 introduces the Invoke-WebRequest cmdlet, which sends HTTP or HTTPS requests to a web service and returns the object-based content that can easily be manipulated in PowerShell. You can think about downloading entire websites using PowerShell (and check out Lee Holmes' article on it: http://www.leeholmes.com/blog/2012/03/31/how-to-download-an-entire-wordpress-blog/).

  • Simplified language syntax: Writing your Where-Object and Foreach-Object has just become cleaner. Improvements in the language include supporting default parameter values, and simplified syntax.

    What you used to write in V1 and V2 with curly braces and $_ as follows:

    Get-Service | Where-Object { $_.Status -eq 'Running' }

    can now be rewritten in V3 as:

    Get-Service | Where-Object Status -eq 'Running'
  • Improved Integrated Scripting Environment (ISE): The new ISE comes with Intellisense, searchable commands in the sidebar, parameter forms, and live syntax checking.