Introduction To PowerShell For The DBA Part 3
Hello dear reader! This is the third post in a series to help you get started automating things with PowerShell. You will be looking at how to start and stop SQL Server using PowerShell as well as setting the Max Server Memory setting, measuring free disk space and more.
This series is just an introduction to PowerShell and basic functionality you might be interested in as a SQL Server Database Administrator or as a System Administrator responsible for SQL Server. Because it is introductory I don’t have examples of long scripts that do a series of complex things as part of these posts. What the series does have are one or two line scripts that are still powerful and help you explore what is available to you in PowerShell.
With that in mind, let’s delve into a few more PowerShell commands that can help you with common tasks.
How To Start And Stop SQL Server Using PowerShell
Open PowerShell as an Administrator and run these commands. These are simple ways to search for commands related to the SQL Server services. Since we’re looking for information related to services, the first command searches for cmdlets containing the word “service”. Since we know we want to start or stop the service, we’re looking for commands containing “start” or “stop” in those second and third commands.
GCM *service* -Module DBATools, SQLServer GCM *start* -Module DBATools, SQLServer GCM *stop* -Module DBATools, SQLServer
I’m showing you how to find interesting commands related to what you’re trying to accomplish, but I also want you to see how much else you can learn about the capabilities of the DBATools and SQLServer PowerShell modules. As you can see from the output, DBATools has Stop-DBAService, Start-DBAService, Restart-DBAService. The SQLServer module has Stop-SqlInstance and Start-SqlInstance.
I encourage you to look through the output of the screenshots, or in your own PowerShell session, to see what else you can do. Particularly inside the DBATools these Get-Command outputs show many things are available. For example, you can start a SQL Agent job, start a trace, start an extended events session, you can stop an endpoint or process as well as do several other things. By the way, there is no Stop-DbaAgentJob because you can actually do that from the Start-DbaAgentJob cmdlet in DBATools using a switch.
So, now that you know the types of commands available how are you going to find out what they do? Remember your friend Get-Help or its alias Help?
Help Get-DBAService -Full Help Restart-DbaService -Full
The output of these commands is too much to put into the post. So, I’ll just point out a few things about each one.
For Get-DBAService notice that you can pass it a computer name value to get the services for all SQL Server instances installed on a given computer. This will be useful if you know your environment tends to do what’s called “instance stacking.” This is were 2 or more SQL instances are installed on the same computer. If this is done in your environment, you will want to know that information before you start sending commands to restart SQL Server services or you could end up restarting more SQL Server instances than you planned on. That sounds like unplanned down time and you want to avoid that!
<#Returns all SQL Server instances on a given computer. Default columns show things like the computerhname, instancename, service name and whether the current status of the service. #> Get-DBAService -ComputerName MyComputerNameHere
Setting Max Server Memory With PowerShell
We’re going to look at Set-DBAMaxMemory from the DBATools module. If you would like more information about SQL Server memory settings. I have a blog post that describes the topic in more detail.
Here is what the DBATools website has to say about this cmdlet in their documentation.
“Inspired by Jonathan Kehayias’s post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this uses a formula to determine the default optimum RAM to use, then sets the SQL max value to that number.”
Here are some examples from the documentation from DBATools and from the Help commands available in PowerShell.
<#
If you have a Central Management Server for you SQL environment, consider using this command to loop through all the SQL Servers and set the Max Server Memory where it is set to something larger than the total amount of RAM assigned to the server.
#>
Get-DbaRegServer -SqlInstance sqlserver | Test-DbaMaxMemory | Where-Object { $_.MaxValue -gt $_.Total } | Set-DbaMaxMemory
<#
If you have a Central Management Server for you SQL environment, consider using this command to loop through all the SQL Servers and set the Max Server Memory to this accepted formula created by a SQL Server expert.
#>
Get-DbaRegServer -SqlInstance sqlserver | Test-DbaMaxMemory | Set-DbaMaxMemory
<#
If you don't have a registered server then just use the below
#>
Test-DbaMaxMemory -SQLinstance SQLServerInstanceNameHere | Set-DbaMaxMemory
Measuring Free Space with PowerShell
DBAs should have some way of knowing and tracking free space in the database files and for the drives of the SQL Server machines. The DBATools module has two commands for doing just that. Check out the below examples and be sure to use the Help cmdlet on these to look at other examples.
<#Gets just certain parts of the output of get-dbaDbspace. Can add | Out-File C:\DBATools\SpaceOutput.txt to the end of this to output this information to a file for review. #> Get-DbaDbSpace -SqlInstance SQLinstanceNameHere | SELECT Database, FileName, UsedSpace, FreeSpace, PercentUsed, AutoGrowth #Resturns drive letters, total space and free space on drives on a computer. Get-dbadiskspace -ComputerName MyComputerNameHere
Detecting IO Latency With PowerShell
One of the things that DBAs want to check for is IO performance of the storage as seen by SQL Server. This is often done using sys.dm_io_virtual_file_stats. You can view this information in the DBATools cmdlet Get-DbaIoLatency.
The DBATools output from the Help command says that the output of this commands is based on two articles by Paul Randal of SQLSkills.com. Those articles are listed below. If you don’t know who Paul Randal is, you need to find out. Your career with SQL Server will be greatly enhanced by reading his stuff.
How to examine IO subsystem latencies from within SQL Server
I also have a post on finding queries experiencing waits related to SQL Server IO latency.
So here are a couple of examples of how to run this PowerShell cmdlet.
#Outputs the IO latency information of two different SQL Servers Get-DbaIoLatency -SqlInstance SQLServerInstance1, SQLServerInstance2 #Outputs the IO latency to a GridView UI for visual examination $output = Get-DbaIoLatency SQLServerInstance1 | Select-Object * | Out-GridView -PassThru #Writes the output to a file. Get-DbaIoLatency SQLServerInstance1 | Select-Object * | Out-File C:\DBATools\IOLatency.txt
Next Steps To Take
- On a Dev environment, practice using the DBATools commands related to stopping and starting SQL Server. This gives you another option when things go south and you have to restart the SQL instance. If the machine suddenly has issues showing the MMC snap-ins then SQL Server Configuration Manager may not be available. And yes, this has happened to me!
- Use the Test-DBAMaxMemory command to see if your SQL Servers are misconfigured in terms of the RAM allocated to them. Once you’ve reviewed the output of the command and are comfortable with it, then use the PowerShell examples in here to make the changes.
- Consider how you can leverage the cmdlets about diskspace, database space and IO latency to examine your environment. Write some test scripts and try them out.
- If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.