SQL Server and PowerShell Basic Tasks

Packt Publishing

This versatile book is a great work companion if you’re a SQL Server database professional who wants to exploit the potential of PowerShell. Dip into the recipes or treat it like a training course – the choice is yours.

a set of management and internet standard technologies developed to unify the management of distributed computing environments. WBEM provides the ability for the industry to deliver a well-integrated set of standard-based management tools, facilitating the exchange of data across otherwise disparate technologies and platforms.

In order to access SQL Server WMI-related objects, you can create a WMI ManagedComputer instance:

The ManagedComputer object has access to a ServerInstance property, which in turn lists all available instances in the local network. These instances, however, are only identifiable if the SQL Server Browser service is running.

SQL Server Browser is a Windows service that can provide information on installed instances in a box. You need to start this service if you want to list the SQL Server-related services.

There's more...

An alternative to using the ManagedComputer object is using the System.Data.Sql. SQLSourceEnumerator class to list all the SQL Server instances in the local network, thus:

When you execute this, your result should look similar to the following screenshot:

Yet another way to get a handle to the SQL Server WMI object is by using the Get-WmiObject cmdlet. This will not, however, expose exactly the same properties exposed by the Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer object.

To do this, you will need to discover first what namespace is available in your environment, thus:

Your result will look similar to the one shown in the following screenshot:

Items listed on your screen will vary depending on the features installed and running in your instance.

Confirm that these are the services that exist in your server. Check your services window.

How it works...

Services that are installed on a system can be queried using WMI. Specific services for SQL Server are exposed through SMO's WMI ManagedComputer object. Some of the exposed properties include:

ClientProtocols

ConnectionSettings

ServerAliases

ServerInstances

Services

There's more...

An alternative way to get SQL Server-related services is by using Get-WMIObject. We will need to pass in the hostname, as well as SQL Server WMI provider for the Computer Management namespace. For SQL Server 2012, this value is:

ROOT\Microsoft\SQLServer\ComputerManagement11

The script to retrieve the services is provided in the following code. Note that we are dynamically composing the WMI namespace here.

Yet another alternative but less accurate way of listing possible SQL Server-related services is the following snippet of code:

#alterative - but less accurate
Get-Service *SQL*

It uses the Get-Service cmdlet and filters based on the service name. It is less accurate because this cmdlet grabs all processes that have SQL in the name but may not necessarily be SQL Server-related. For example, if you have MySQL installed, that will get picked up as a process. Conversely, this cmdlet will not pick up SQL Server-related services that do not have SQL in the name, such as ReportServer.

Starting/stopping SQL Server services

This recipe describes how to start and/or stop SQL Server services.

There's more...

Check which SQL services are installed in your machine. Go to Start | Run and type Services.msc. You should see a screen similar to this:

How to do it...

Let's look at the steps to toggle states for your SQL Server services:

Execute and confirm the service status changed accordingly. Go to Start | Run and type Services.msc.

For example, in our previous sample, both SQLBrowser and ReportServer were initially running. Once the script was executed, both services stopped.

How it works...

In this recipe, we picked two services—SQLBrowser and ReportServer—that we want to manipulate and saved them into an array:

$services = @("SQLBrowser","ReportServer")

We then pipe the array contents to a Foreach-Object cmdlet, so we can determine what action to perform for each service. For our purposes, if the service is stopped, we want to start it. Otherwise, we stop it. Note that this code will work in both PowerShell V2 and V3:

You may also want to determine dependent services, or services that rely on a particular service. You may want to consider synchronizing the starting/stopping of these services with the main service they depend on.

To identify dependent services, you can use the DependentServices property of the System.ServiceProcess.ServiceController class:

The following list shows the properties and methods of the System.ServiceProcess.ServiceController class, which is generated from the Get-Service cmdlet:

An alternative way of working with SQL Server services is by using the Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer class. Note that the following code will work in both PowerShell V2 and V3:

All of these cmdlets relate to Windows services, with the exception of New- WebServiceProxy, which is described in MSDN as a cmdlet that creates a Web service proxy object that lets you use and manage the Web service in Windows PowerShell.

Here is a brief comparison between these service-oriented cmdlets and the methods available for the object of Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputerservice, as discussed in the recipe:

Service Methods

Service-related cmdlets

Start()

Start-Service

Stop()

Stop-Service

Continue()

Resume-Service

Pause()

Suspend-Service

Refresh()

Restart-Service

Note that there isn't necessarily a one-to-one mapping between the methods of the Service class and the service cmdlets. For example, there is a Restart-Service cmdlet, but there isn't a Restart method.

This should not raise alarm bells, though. Although it may seem that some methods or cmdlets may be missing, it is important to note that PowerShell is a rich scripting platform and language. In addition to its own cmdlets, it leverages the whole .NET platform. Whatever you can do in the .NET platform, you most likely can do using PowerShell. Even if you think something is not doable when you look at a specific class or object, there is most likely a cmdlet somewhere that can perform that same task, or vice versa. If you still cannot find your ideal solution, you can create your own—be it a class, a module, a cmdlet, or a function.

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.