SQL Server and PowerShell Basic Tasks

Exclusive offer: get 50% off this eBook here
SQL Server 2012 with PowerShell V3 Cookbook

SQL Server 2012 with PowerShell V3 Cookbook — Save 50%

Increase your productivity as a DBA, developer, or IT Pro, by using PowerShell with SQL Server to simplify database management and automate repetitive, mundane tasks with this book and ebook.

$35.99    $18.00
by Donabel Santos | January 2013 | Enterprise Articles Microsoft

This article by Donabel Santos author of SQL Server 2012 with PowerShell V3 Cookbook, demonstrates scripts and snippets of code that accomplish some basic SQL Server tasks, using PowerShell. We will start with simple tasks, such as listing SQL Server instances and creating objects such as tables, indexes, stored procedures, and functions, to get you comfortable with working with SQL Server programmatically.

You will find that many of the recipes can be accomplished using PowerShell and SQL Management Objects ( SMO). SMO is a library that exposes SQL Server classes, which allows for programmatic manipulation and automation of many database tasks. For some recipes, we will also explore alternative ways of accomplishing the same tasks, using different native PowerShell cmdlets.

Even though we are exploring how to create some common database objects using PowerShell, I would like to note that PowerShell is not always the best tool for the task. There will be tasks that are best left accomplished using T-SQL. Even so, it is still good to know what is possible with PowerShell and how to do it, so that you know you have alternatives depending on your requirements or situation.

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

SQL Server 2012 with PowerShell V3 Cookbook Increase your productivity as a DBA, developer, or IT Pro, by using PowerShell with SQL Server to simplify database management and automate repetitive, mundane tasks with this book and ebook.
Published: October 2012
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

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:


SQL Server 2012 with PowerShell V3 Cookbook Increase your productivity as a DBA, developer, or IT Pro, by using PowerShell with SQL Server to simplify database management and automate repetitive, mundane tasks with this book and ebook.
Published: October 2012
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Donabel Santos

Donabel Santos is a SQL Server MVP and is the senior SQL Server Developer/DBA/Trainer at QueryWorks Solutions, a consulting and training company in Vancouver, BC. She has worked with SQL Server since version 2000 in numerous development, tuning, reporting, and integration projects with ERPs, CRMs, SharePoint, and other custom applications. She holds MCITP certifications for SQL Server 2005/2008, and an MCTS for SharePoint. She is a Microsoft Certified Trainer (MCT), and is also the lead instructor for SQL Server Administration, Development, and SSIS courses at British Columbia Institute of Technology (BCIT).

Donabel is a proud member of PASS (Professional Association of SQL Server), and a proud BCIT alumna (CST diploma and degree). She blogs (www.sqlmusings.com), tweets (@sqlbelle), speaks and presents (SQLSaturday, VANPASS, Vancouver TechFest, and so on), trains (BCIT, QueryWorks Solutions), and writes (Packt, Idera, SSWUG, and so on).

Books From Packt


Microsoft Windows PowerShell 3.0 First Look
Microsoft Windows PowerShell 3.0 First Look

Microsoft Exchange 2010 PowerShell Cookbook
Microsoft Exchange 2010 PowerShell Cookbook

Microsoft SharePoint 2010 and Windows PowerShell 2.0: Expert   Cookbook
Microsoft SharePoint 2010 and Windows PowerShell 2.0: Expert Cookbook

Microsoft SQL Server 2008 R2 Master Data Services
Microsoft SQL Server 2008 R2 Master Data Services

Microsoft SQL Server 2012 Security Cookbook
Microsoft SQL Server 2012 Security Cookbook

Microsoft SQL Server 2012 Performance Tuning Cookbook
Microsoft SQL Server 2012 Performance Tuning Cookbook

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software