SQL Server and PowerShell Basic Tasks

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.

(For more resources related to this topic, see here.)

Listing SQL Server instances

In this recipe, we will list all SQL Server instances in the local network.

Getting ready

Log in to the server that has your SQL Server development instance, as an administrator.

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. Let's use the Start-Service cmdlet to start SQLBrowser:

    Import-Module SQLPS -DisableNameChecking #sql browser must be installed and running Start-Service "SQLBrowser"

  3. Next, you need to create a ManagedComputer object to get access to instances. Type the following script and run it:

    $instanceName = "KERRIGAN" $managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo. Wmi.ManagedComputer' $instanceName #list server instances $managedComputer.ServerInstances

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

    Note that $managedComputer.ServerInstances gives you not only instance names, but also additional properties such as ServerProtocols, Urn , State, and so on.

  4. Confirm that these are the same instances you see in Management Studio . Open up Management Studio .

  5. Go to Connect | Database Engine .

  6. In the Server Name drop-down, click on Browse for More.

  7. Select the Network Servers tab, and check the instances listed. Your screen should look similar to this:

     

How it works...

All services in a Windows operating system are exposed and accessible using Windows Management Instrumentation (WMI). WMI is Microsoft's framework for listing, setting, and configuring any Microsoft-related resource. This framework follows Web-based Enterprise Management (WBEM). Distributed Management Task Force, Inc. defines WBEM as follows (http://www.dmtf.org/standards/wbem):

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:

$managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi. ManagedComputer' $instanceName

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:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Select ServerName, InstanceName, Version | Format-Table -AutoSize

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:

$hostname = "KERRIGAN" $namespace = Get-WMIObject -ComputerName $hostName -NameSpace root\ Microsoft\SQLServer -Class "__NAMESPACE" | Where Name -Like "ComputerManagement*"

If you are using PowerShell V2, you will have to change the Where cmdlet usage to use the curly braces ({}) and the $_ variable, thus: Where {$_.Name -Like "ComputerManagement*" }

For SQL Server 2012, this value is:

ROOT\Microsoft\SQLServer\ComputerManagement11

Once you have the namespace, you can use this value with Get-WmiObject to retrieve the instances. One property we can use to filter is SqlServiceType.

According to MSDN (http://msdn.microsoft.com/en-us/library/ms179591.aspx)), the following are the values of SqlServiceType:

SqlServiceType

 

Description

 

1

SQL Server service

 

2

SQL Server Agent service

 

3

Full-text Search Engine service

 

4

Integration Services service

 

5

Analysis Services service

 

6

Reporting Services service

 

7

SQL Server Browser service

 

Thus, to retrieve the SQL Server instances, you need to filter for SQL Server service, or  SQLServiceType = 1.

Get-WmiObject -ComputerName $hostname ` -Namespace "$($namespace.__NAMESPACE)\$($namespace.Name)" ` -Class SqlService | Where SQLServiceType -eq 1 | Select ServiceName, DisplayName, SQLServiceType | Format-Table -AutoSize

If you are using PowerShell V2, you will have to change the Where cmdlet usage to use the curly braces ({}) and the $_ variable: Where {$_.SQLServiceType -Like –eq 1 }

Your result should look similar to the following screenshot:

Discovering SQL Server services

In this recipe, we enumerate all SQL Server services and list their status.

Getting ready

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

How to do it...

Let's assume you are running this script on the server box.

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. Add the following code and execute it:

    Import-Module SQLPS #replace KERRIGAN with your instance name $instanceName = "KERRIGAN" $managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo. Wmi.ManagedComputer' $instanceName #list services $managedComputer.Services | Select Name, Type, Status, DisplayName | Format-Table -AutoSize

    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.

  3. 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.

$hostName = "KERRIGAN" $namespace = Get-WMIObject -ComputerName $hostName -NameSpace root\ Microsoft\SQLServer -Class "__NAMESPACE" | Where Name -Like "ComputerManagement*" Get-WmiObject -ComputerName $hostname -Namespace "$($namespace.__ NAMESPACE)\$($namespace.Name)" -Class SqlService | Select ServiceName

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:

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. Add the following code. Note that this code will work in both PowerShell V2 and V3:

    $Verbosepreference = "Continue" $services = @("SQLBrowser", "ReportServer") $hostName = "KERRIGAN" $services | ForEach-Object { $service = Get-Service -Name $_ if($service.Status -eq "Stopped") { Write-Verbose "Starting $($service.Name) ...." Start-Service -Name $service.Name } else { Write-Verbose "Stopping $($service.Name) ...." Stop-Service -Name $service.Name } } $VerbosePreference = "SilentlyContinue"

  3. 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:

$services | ForEach-Object { $service = Get-Service -Name $_ if($service.Status -eq "Stopped") { Write-Verbose "Starting $($service.Name) ...." Start-Service -Name $service.Name } else { Write-Verbose "Stopping $($service.Name) ...." Stop-Service -Name $service.Name } }

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:

$services | ForEach-Object { $service = Get-Service -Name $_ Write-Verbose "Services Dependent on $($service.Name)" $service.DependentServices | Select Name }

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:

Import-Module SQLPS -DisableNameChecking #list services you want to start/stop here $services = @("SQLBrowser", "ReportServer") $instanceName = "KERRIGAN" $managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi. ManagedComputer' $instanceName #go through each service and toggle the state $services | ForEach-Object { $service = $managedComputer.Services[$_] switch($service.ServiceState) { "Running" { Write-Verbose "Stopping $($service.Name)" $service.Stop() } "Stopped" { Write-Verbose "Starting $($service.Name)" $service.Start() } } }

When using the Smo.Wmi.ManagedComputer object, you can simply use the Stop method provided with the class and the Start method to stop and start the service respectively.

The following list shows the properties and methods available with the Smo.Wmi.ManagedComputer class:

There's more...

To explore available cmdlets that can help manage and maintain services, use the following command:

Get-Command -Name *Service* -CommandType Cmdlet -ModuleName *PowerShell*

This will enumerate all cmdlets that have "Service" in the name:

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.

Resources for Article :


Further resources on this subject:


Books to Consider

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
$ 26.99
Microsoft SQL Server Analysis Services Demystified [Video]
$ 72.25
Creating Reports with SQL Server 2012 Reporting Services [Video]
$ 72.25
comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free