Getting SQL Services, Starting, Stopping and Restarting them with dbatools

There was a question in the #dbatools slack channel 

dbatools question

Getting dbatools

dbatools enables you to administer SQL Server with PowerShell. To get it simply open PowerShell run

Install-Module dbatools

You can find more details on the web-site

Finding the Command

To find a command you can use the dbatools command Find-DbaCommand
For commands for service run

Find-DbaCommand Service

There are a whole bundle returned

find services.png

This is how you can find any dbatools command. There is also a -Tag parameter on Find-DbaCommand.

Find-DbaCommand -Tag Service

This returns

find services tag.png

How to use any PowerShell command

Always always start with Get-Help

Get-Help Get-DbaService -Detailed

get help.png

This will show you all the information about the command including examples 🙂

help examples.png

All of these commands below require that the account running the PowerShell is a Local Admin on the host.

One Host Many Hosts

Now I have used just one host for all of the examples on this page. Do not be fooled, you can always use an array of hosts wherever I have $ComputerName you can set it to as many hosts as you like

$ComputerName = 'SQL0','SQL1'

You can even get those names form a database, Excel sheet, CMS.

Getting the Services

So to get the services on a machine run

$ComputerName = 'Name of Computer'
Get-DbaService -ComputerName $ComputerName

getting servies 1.png

You can output into a table format.

Get-DbaService -ComputerName $ComputerName | Format-Table

I will use the alias ft for this in some of the examples, that is fine for the command line but use the full command name in any code that you write that other people use

services table.png
You have an object returned so you can output to anything if you want – CSV, JSON, text file, email, azure storage, database, the world is your oyster.

Getting the Services for one instance

The Get-DbaService command has a number of parameters. There is an InstanceName parameter enabling you to get only the services for one instance. If we just want the default instance services

Get-DbaService -ComputerName $ComputerName -InstanceName MSSQLSERVER| Format-Table

default instances.png

Just the MIRROR instance services

Get-DbaService -ComputerName $ComputerName -InstanceName MIRROR| Format-Table

mirror instances.png

Getting just the Engine or Agent services

You can also use the -Type parameter to get only services of a particular type. You can get one of the following: “Agent”,”Browser”,”Engine”,”FullText”,”SSAS”,”SSIS”,”SSRS”, “PolyBase”

So to get only the Agent Services

Get-DbaService -ComputerName $ComputerName -Type Agent
agent services.png
You can combine the InstanceName and the Type parameters to get say only the default instance engine service
Get-DbaService -ComputerName $ComputerName -InstanceName MSSQLSERVER -Type Engine
default engine service.png

Starting and stopping and restarting services

You can use Start-DbaService and Stop-DbaService to start and stop the services. They each have ComputerName, InstanceName and Type parameters like Get-DbaService.

So if after running

Get-DbaService -ComputerName $ComputerName | Format-Table

you find that all services are stopped

all stopped.png

Start All the Services

You can run

Start-DbaService -ComputerName $ComputerName | Format-Table

and start them all

start them all.png

The full text service was started with the engine service which is why it gave a warning. You can see this if you have all of the services stopped and just want to start the engine services with the type parameter.

Get-DbaService -ComputerName $ComputerName | Format-Table
Start-DbaService -ComputerName $ComputerName -Type Engine
Get-DbaService -ComputerName $ComputerName | Format-Table
all stopped - start engine.png

If you just want to start the Agent services, you can use

Start-DbaService -ComputerName $ComputerName -Type Agent

start agent.png

You can start just the services for one instance

Start-DbaService -ComputerName $ComputerName -InstanceName MIRROR

start instance services.png

Stopping the services

Stopping the services works in the same way. Lets stop the MIRROR instance services we have just started. This will stop the services for an instance

Stop-DbaService -ComputerName $ComputerName -InstanceName MIRROR

stopping instance services.png

We can stop them by type as well, although this will show an extra requirement. If we start our MIRROR instance services again and then try to stop just the engine type.

Start-DbaService -ComputerName $ComputerName -InstanceName MIRROR | ft
Stop-DbaService -ComputerName $ComputerName -Type Engine

cant stop.png

You will get a warning due to the dependant services

WARNING: [10:31:02][Update-ServiceStatus] (MSSQL$MIRROR on SQL0) The attempt to stop the service returned the following error: The service cannot be stopped because other services that are running are dependent on it.
WARNING: [10:31:02][Update-ServiceStatus] (MSSQL$MIRROR on SQL0) Run the command with ‘-Force’ switch to force the restart of a dependent SQL Agent

So all you have to do is use the force Luke (or whatever your name is!)

Stop-DbaService -ComputerName $ComputerName -Type Engine -Force

Use the force.png

You can also stop the services for an entire host, again you will need the Force parameter.

Start-DbaService -ComputerName $ComputerName |ft
Stop-DbaService -ComputerName $ComputerName -Force | ft

stop all of them.png

Restarting Services

It will come as no surprise by now to learn that Restart-DbaService follows the same pattern. It also has ComputerName, InstanceName and Type parameters like Get-DbaService, Start-DbaService and Stop-DbaService (Consistency is great, It’s one of the things that is being worked on towards 1.0 as you can see in the Bill of Health)

Again you will need the -Force for dependant services, you can restart all of the services on a host with

Restart-DbaService -ComputerName $ComputerName -Force

restart tehm all.png

or just the services for an instance

Restart-DbaService -ComputerName $ComputerName -InstanceName MIRROR -Force

restart instance.png

or just the Agent Services

Restart-DbaService -ComputerName $ComputerName -Type Agent

restart agent.png

Doing a bit of coding

Now none of that answers @g-kannan’s question. Restarting only services with a certain service account.

With PowerShell you can pipe commands together so that the results of the first command are piped into the second. So we can get all of the engine services on a host for an instance with Get-DbaService and start them with Start-DbaService like this

Get-DbaService -ComputerName $ComputerName -Type Engine | Start-DbaService

start.png

or get all of the engine services for an instance on a host and stop them

Get-DbaService -ComputerName $ComputerName -Type Engine  -InstanceName Mirror| Stop-DbaService

stop one isntance.png

or maybe you want to get all of the service that have stopped

(Get-DbaService -ComputerName $ComputerName -Type Engine).Where{$_.State -eq 'Stopped'}

stopped services.png

You can do the same thing with syntax that may make more sense to you if you are used to T-SQL as follows

(Get-DbaService -ComputerName $ComputerName -Type Engine) | Where State -eq 'Stopped'

T SQL syntax powershell.png

and then start only those services you could do

(Get-DbaService -ComputerName $ComputerName -Type Engine) | Where State -eq 'Stopped' | Start-DbaService

start the stopped ones.png

(note – you would just use Start-DbaService in this case as it wont start services that are already started!)

# Stop just one of the engine services
Stop-DbaService -ComputerName $ComputerName -InstanceName MIRROR -Type Engine
# Get the engine services
Get-DbaService -ComputerName $ComputerName -Type Engine
# This will only start the one engine service that is stopped
Start-DbaService -ComputerName $ComputerName -Type Engine

only one service.png

Come On Rob! Answer the question!

So now that you know a lot more about these commands, you can restart only the services using a particular service account by using Get-DbaService to get the services

Get-DbaService -ComputerName $ComputerName -Type Engine | Where StartName -eq 'thebeard\sqlsvc'

services by start name.png

and then once you know that you have the right ‘query’ you can pipe that to Restart-DbaService (Like making sure your SELECT query returns the correct rows for your WHERE clause before running the DELETE or UPDATE)

Get-DbaService -ComputerName $ComputerName -Type Engine | Where StartName -eq 'thebeard\sqlsvc' | Restart-DbaService

restarting only one.png

Happy Automating !

Documenting SQL Server the easy way with Power Doc

You know how it is. Question questions questions. As a DBA you are the fount of all knowledge. You are the protector of the data after all so obviously you know every little thing that is needed to be known.

Frequently, I am asked

How many processors does that server have?
How much RAM is on that server? What type?
What OS? Which Patches were installed

or more SQL based questions about configuration

Which SQL Product? Which version? Which Service Pack?
What are the linked servers on that server?
Or you want to know which login have which roles on the server or the autogrowth settings or any number of other ‘little things’

As the DBA as they are asking about my servers I should know and whilst I have a lot of info in my head, there’s not enough room for it all!! So I have to break from what I am doing and dive into Powershell or SSMS and get them the info that they need. When this happens I often thought I wish I could have this information to hand but I have never had time to organise it myself.

Worse still, imagine your boss walks through the door and says we have to provide information for an audit. Can you give me full details of all the SQL Servers and their configurations both windows and SQL and I need it by the end of play tomorrow.

It happens.

Its Personal Development Review time. I should have asked my boss to give me an objective of thoroughly documenting the SQL Server estate this year. I could have done it in a few hours. You only think of these things when its too late.

How can I do this? I hear you cry. Head over to https://sqlpowerdoc.codeplex.com and you will see.

SQL PowerDoc was written by Kendal VanDyke who is a practiced IT professional with over a decade of experience in SQL Server development and administration. Kendal is currently a principal consultant with UpSearch SQL, where he helps companies keep their SQL Servers running in high gear. Kendal is also a Microsoft MVP for SQL Server and president of the PASS chapter MagicPASS in Orlando, FL. You can find his blog at http://www.kendalvandyke.com/ and on Twitter at @SQLDBA

I found out about Power Doc a few weeks ago. It looked so cool, I tested it at home and then on my dev server and then on the whole estate. It is CPU heavy on the box it is running on if you have a load of servers. I don’t know how long it took to run as it ran overnight but the information you get back is staggering, enough to satisfy even the most inquisitive of auditors or questioners. You can pass it to your server team too and they will love it as it can do a Windows based inventory.

What does it document? What DOESNT it document? If you head over to https://sqlpowerdoc.codeplex.com/wikipage?title=What%27s%20Documented the list you see doesn’t reflect the sheer amount of data you can get back. Run it against your own machine and you will see what I mean.

As well as documenting everything it also runs around 100 checks to diagnose potential issues and problems. You can see where autogrowth is set to percentage, databases that haven’t been backed up, auto shrink, Max Memory set too high, the list goes on. Even the links to the MSDN articles are in there for the things it finds.

The documentation is thorough and even if you haven’t use Powershell before everything you need is on the website to run PowerDoc.

So much thought and effort has been put into this it’s difficult to see how it could be improved.

What I have done then is added the Excel file to our dba SharePoint team site and enabled the right people access to it. Equally they tell others and I get bothered slightly less.