THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

T-SQL Tuesday #39: Managing your SQL Server Services with PowerShell

T-SQL TuesdayThis T-SQL Tuesday is about using PowerShell to do something with SQL Server. Now, if you've read any of my blog posts you probably know I've been using PowerShell to do things with SQL Server for a while now, but I'm glad Wayne decided on this topic for his T-SQL Tuesday topic, because everyone has different ways to use PowerShell, and you can learn from all of them, as I do.

(When I started to write this post I'd intended to share how I convert a PerfMon binary log file into SQL Server data for baseline analysis, but found I'd already done that here. Then, I thought I'd share how I save SQL Agent jobs and move them to another server, but did that one, too!)

One of the interesting aspects of SMO (Server Management Objects) is the Managed Computer object. It doesn't get a lot of attention because, well, that goes to the SQL Server instance and the various database objects. Administrators, though, need to pay attention to managing the instance itself. SQL Server 2008 introduced the Configuration Manager, a GUI application that allows administrators to view the SQL Server services installed, including their current state, the service account they use, etc. It also allows them to manage the external access to the instances via the network protocols supported, the TCP/IP ports, etc.

Here's a diagram of the Managed Computer object:

Managed Computer Object

Now, if you're familiar with the Configuration Manager you should see some parallels there, and that makes sense, because these objects are the ones Configuration Manager is working with.

Let's say it's time for you to change the service account and password for your SQL Server instance. Using this model, we have our guide. We need to create a new ManagedComputer object, connect to the server, connect to the service, use the SetServiceAccount() method to set the new values, then restart the service. In this example I'll also restart the Agent service since I'm resetting the SQL Server instance service account.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
$sqlinst = $mc.Services['MSSQLSERVER']
$sqlagnt = $mc.Services['SQLSERVERAGENT']
start-sleep -s 10

Let's say I just want to see the services on my local instance, like I do in Configuration Manager. Again, that's pretty easy.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
$mc.Services | select Name, ServiceState, DisplayName, ServiceAccount | format-table

There are times you'll need to work with the network protocols, and I've actually used the following code to change the IP port for an instance after an install, because it HAD to match a particular value.

$mc = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') localhost
$ip.IPAddressProperties['TcpDynamicPorts'].Value = '1099'

Now, why is this important, if I can do all this in Configuration Manager? Well, SQL Server 2012 supports installation on Windows Server Core, and there's no GUI on a Core server, so this becomes the best way to access the objects you normally manage with Configuration Manager. More importantly, it allows you to build a set of scripts to "just handle" any number of problems, without trying to remember what submenu gives you access to what property to change to solve your problem.

Good luck, and thanks, Wayne, for hosting this month's T-SQL Tuesday!


Published Tuesday, February 12, 2013 2:09 PM by AllenMWhite



Raj said:

Through SQL Config manager, it's not required to restart the service only if the password is changed, right? Is the behavior same with SetServiceAccount too?


January 3, 2014 4:26 PM

Raj said:

Also, could you please tell me what powershell version need to be there to run this? I was getting the error it couldn't find method SetServiceAccount, which I confirmed by looking at this - New-Object Microsoft.SqlServer.Management.Smo.Wmi.Manag

edComputer | Get-Member.

Thanks Again.

January 3, 2014 6:33 PM

Good articale said:


July 4, 2015 6:13 AM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement