Art of the DBA Rotating Header Image

October 9th, 2015:

A Month of SQLPS: Starting and Stopping

Before you can learn how to drive a car, you have to know how to stop it and start it. This carries over to many things, including SQL Server. Most of us are used to controlling SQL Server through either SQL Server Management Studio or the SQL Server Configuration Manager, stopping and starting the service through the management UI. Others may go into the general Services control panel to manage these services. However we do it, knowing how to stop, start, and restart SQL Server is necessary to managing instances.

Since managing all Windows services is an important management task, Powershell has supported it from the beginning. To manage services involves Get-Service, Start-Service, and Stop-Service. Using these cmdlets is easy and they are my go-to way to manage my services:

#Powershell console must be run as Administrator
Stop-Service MSSQLSERVER -Force

Nothing complex, right? So let’s move to the SQLPS module. Using our Get-Command call, let’s look up the cmdlets that relate to SQL Instances:

Get-Command -Module SQLPS *SqlInstance


The resulting three cmdlets seem pretty straight forward with their names. Shouldn’t be too difficult, right? Unfortunately, this is where things start to break down. First, if we look at the help files for each of these cmdlets, we find them woefully lacking in any useful information. There are one or two examples for their use, but no detailed examples and no documentation on the parameters or their use (with an exception for Get-SqlInstance).

Time for some detective work, right? I started with using Get-SqlInstance and quickly discovered I needed something called the Cloud Adapter Service, an add on for SQL Server. This service is apparently something born out of Azure for working with SQL Server on Azure VMs. It needs to be running to support these commands and also needs permissions to affect the Windows firewall.

Next up we need a credential. No, it’s not a SQL credential. Instead, it’s a Windows credential that has administrator rights on the target machine. Once this is all in place, we can use it to retrieve our information. For good measure, we’ll pipe the output to Get-Member to investigate what kind of object we’re dealing with:

$cred = Get-Credential
$instance = Get-SqlInstance -MachineName PICARD -Credential $cred
$instance | Get-Member


While this doesn’t have any surprising information, what’s critical here is the object type. Note that it’s a IaaS.SqlInstanceInfo object, specifically designed to manage instances on Azure VMs. I believe the idea was to give users of SQLPS a consistent way to manage their instances, regardless of whether they were on-premises or in the Azure cloud.

Understanding Get-SqlInstance helps us make sense of the Start-SqlInstance and Stop-SqlInstance cmdlets as well. When I ran them they performed the same operation as Start- and Stop-Service, including the gotcha that when you start a SQL instance/service, it will not automatically start the corresponding Agent service. All we get is the ability to retrieve information and manipulate our services through a specialized communication service.

Honestly, I can’t see any benefit to the inclusion of these cmdlets. When working with SQL Instances, I would expect to get and manage SMO server objects, as that is what most of the other interfaces use. When it comes to managing services, WinRM access allows me to use the traditional service management cmdlets included in the core of Powershell. These -SqlInstance cmdlets add an additional level of overhead that simply isn’t needed, even in the case of Azure VMs.