Book Image

SQL Server 2014 with Powershell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with Powershell v5 Cookbook

By: Donabel Santos

Overview of this book

PowerShell can be leveraged when automating and streamlining SQL Server tasks. PowerShell comes with a rich set of cmdlets, and integrates tightly with the .NET framework. Its scripting capabilities are robust and flexible, allowing you to simplify automation and integration across different Microsoft applications and components. The book starts with an introduction to the new features in SQL Server 2014 and PowerShell v5 and the installation of SQL Server. You will learn about basic SQL Server administration tasks and then get to know about some security-related topics such as the authentication mode and assigning permissions. Moving on, you will explore different methods to back up and restore your databases and perform advanced administration tasks such as working with Policies, Filetables, and SQL audits. The next part of the book covers more advanced HADR tasks such as log shipping and data mirroring, and then shows you how to develop your server to work with BLOB, XML, and JSON. Following on from that, you will learn about SQL Server's BI stack, which includes SSRS reports, the SSIS package, and the SSAS cmdlet and database. Snippets not specific to SQL Server will help you perform tasks quickly on SQL servers. Towards the end of the book, you will find some useful information, which includes a PowerShell tutorial for novice users, some commonly-used PowerShell and SQL Server syntax, and a few online resources. Finally, you will create your own SQL Server Sandbox VMs. All these concepts will help you to efficiently manage your administration tasks.
Table of Contents (15 chapters)
14
Index

Exploring the SQL Server PowerShell hierarchy

SQL Server started shipping with the SQLPS module in SQL Server 2012. The SQLPS module allows PowerShell to access SQL Server-specific cmdlets and functions, and also loads commonly used assemblies when working with SQL Server. This continues to be the case in SQL Server 2014.

Launching PowerShell from SQL Server Management Studio (SSMS) launches a Windows PowerShell session which imports the SQLPS module automatically, and sets the current context to the item the PowerShell session was launched from. Database administrators and developers can then start navigating the object hierarchy from there.

Getting ready

To follow this recipe, you should log in to SQL Server 2014 Management Studio.

How to do it...

In this recipe, we will navigate the SQL Server PowerShell hierarchy by launching a PowerShell session from SQL Server Management Studio:

  1. Right-click on your instance node.
  2. Click on Start PowerShell.
    How to do it...

    Note that this will launch a PowerShell session and load the SQLPS module. This window looks similar to a command prompt, with a prompt set to the SQL Server object you launched this window from. In the following screenshot, ROGUE refers to the name of my local machine:

    How to do it...

    Note the starting path in this window. The screen now shows how you could get to the default instance if you were to navigate using the PowerShell console or ISE:

    PS SQLSERVER:\SQL\<SQL instance name>\DEFAULT>
  3. Type dir. This should give you a list of all objects directly accessible from the current server instance; in our case, from the default SQL Server instance ROGUE. Note that dir is an alias for the cmdlet Get-ChildItem.
    How to do it...

    Tip

    Downloading the example code

    You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

    This is similar to the objects you can find under the instance node in Object Explorer in SQL Server Management Studio.

    How to do it...
  4. While our PowerShell window is open, let's explore the SQL Server PSDrive or the SQL Server data store which PowerShell treats as a series of items. Type cd \. This will change the path to the root of the current drive, which is our SQL Server PSDrive.
  5. Type dir. This will list all items accessible from the root SQL Server PSDrive. You should see something similar to the following screen:
    How to do it...
  6. Close this window.
  7. Go back to Management Studio and right-click on one of your user databases.
  8. Click on Start PowerShell. Note that this will launch another PowerShell session with a path that points to the database you right-clicked from:
    How to do it...

    Note the starting path of this window is different from the starting path where you first launched PowerShell in the earlier steps. If you type dir from this location, you will see all items that are under the AdventureWorks2014 database.

    How to do it...

    You can see some of the items enumerated in this screenshot in SQL Server Management Studio Object Explorer, if you expand the AdventureWorks2014 database node:

    How to do it...

How it works...

When PowerShell is launched through SSMS, a context-sensitive PowerShell session is created and it automatically loads the SQLPS module. This will be evident in the prompt, which by default shows the current path of the object from which the Start PowerShell menu item was clicked from.

How it works...

The SQLPS module was not always loaded when PowerShell was launched from SSMS. With SQL Server 2008/2008 R2 it was shipped with a SQLPS utility, which is also referred to as a mini shell. When you started PowerShell from SSMS, it was not a full PowerShell console that was launched. It was a constrained, closed shell preloaded with SQL Server extensions was loaded. This shell was meant to be used for SQL Server only, which proved to be quite limiting because DBAs and developers often need to load additional snapins and modules in order to integrate SQL Server with other systems through PowerShell. At that time, the alternative way was to launch a full-fledged PowerShell session and depending on your PowerShell version, either load snapins or load the SQLPS module.

Since SQL Server 2012, the original constrained mini shell has been deprecated. When you launch a PowerShell session from SSMS in SQL Server 2012 onwards, what is launched is the full-fledged PowerShell console, with the updated SQLPS module loaded by default.

Once the SQLPS module is loaded, SQL Server becomes exposed as a PowerShell Drive (PSDrive), which allows traversing of objects as if they are folders and files. Familiar commands for traversing directories are supported in this provider, such as dir or ls. Note that these familiar commands are often just aliases to the real cmdlet name, in this case, Get-ChildItem.

When you launch PowerShell from SSMS, you can immediately start navigating the SQL Server PowerShell hierarchy.