In the previous article I mentioned the need for setting certain dependencies for SQL Services startup. As promised, this article is a follow-up to that article to help you easily set the service dependencies for SQL Server.
Setting service startup dependencies is an essential step to take to help ensure a seamless startup experience and to reduce the chance for failure. Some of the possible failures that could occur were explained in the previous article as well as in this article about MSAs by Wayne Sheffield. Our goal as data professionals is to minimize the chance for surprises and unnecessary time spent troubleshooting problems that shouldn’t have happened in the first place.
Set Service Dependencies
What is it that a service dependency does for the system? Well, a service dependency is much like any sort of dependency. A service dependency simply means that in order for a service to function properly another service needs to be functioning properly.
This is very much like having children. The children are called dependents because children require somebody else to be around to take care of and support them to a certain point. A service that has a dependency means that it basically is a child service that needs a parent service to be properly functioning so the child service can go on about its duties and do what is expected / desired of it.
So what are the service dependencies that we should be setting? The services that should be running in order to ensure SQL Server will work properly are Netlogon, W32Time, and KEYISO. For the SQL Agent service, the same services can be set as dependencies but you really only need to ensure that the SQL Server service is listed as a service dependency. Here is an example of what that would look like from the service properties pages in the services control panel.
Now, you can either laboriously enter each of those dependencies while editing the registry (ok, so it isn’t really that laborious to do it by hand via regedit but that does more easily permit unwanted errors to occur) or you can take advantage of something that is repeatable and easier to run. A script comes to mind as an appropriate method for that latter option.
Script it once!
Scripts are awesome resources to make our lives easier. This script is one that I use time and again to quickly set all of these service dependencies. In addition, it also can set the properties for your MSA account. One thing it does not do is set the service to “Automatic (Delayed Start)” instead of the default “Automatic” start type. That sounds like a fantastic opportunity for you to provide feedback on how you would that into the script.
Without further ado, here is the script to help save time and set your service dependencies easily.
#Todo - modify so can be run against group of servers
# modify so can be run remotely
$servicein = '' #'MSSQL$DIXNEUFLATIN1' #use single quotes in event service name has a $ like sql named instances
$svcaccntname = '' #'svcmg_saecrm01$' #to set managed service account properties
#$RequiredServices = @("W32Time","Netlogon","KEYISO");
$RequiredServices = @('W32Time','Netlogon','KEYISO');
#$CurrentServices;
IF($servicein){
$ServiceList = [ordered]@{
Name = $servicein}
}
IF($svcaccntname) {
$ServiceList = Get-WmiObject Win32_Service | Select Name, StartName, DisplayName | Where-Object {($_.Name -match 'MSSQL' -or $_.Name -match 'Agent' -or $_.Name -match 'ReportServer') `
-and $_.DisplayName -match 'SQL SERVER' `
-or $_.StartName -like "*$svcaccntname*"
}
}
ELSE{
$ServiceList = Get-WmiObject Win32_Service | Select Name, StartName, DisplayName | Where-Object {($_.Name -match 'MSSQL' -or $_.Name -match 'Agent' -or $_.Name -match 'ReportServer') `
-and $_.DisplayName -match 'SQL SERVER' `
}
}
foreach ($service in $ServiceList)
{
$servicename = $service.Name
#$RequiredServices = @("W32Time","Netlogon","KEYISO"); #init at top
$CurrentReqServices = @(Get-Service -Name $servicename -RequiredServices | Select Name );
#<#
if ($CurrentReqServices) {
$CurrentReqServices | get-member -MemberType NoteProperty | ForEach-Object {
$ReqName = $_.Name;
$ReqValue = $CurrentReqServices."$($_.Name)"
}
"Current Dependencies = $($ReqValue)";
#>
} ELSE {
"Current Dependencies Do NOT exist!";
$ReqValue = $RequiredServices
}
$CurrentServices = $RequiredServices + $ReqValue | SELECT -Unique;
#"Processing Service: $servicename"
#"Combined Dependencies = $($CurrentServices)";
#<#
$dependencies = get-itemproperty -path "HKLM:SYSTEMCurrentControlSetServices$servicename" -Name DependOnService -ErrorAction SilentlyContinue
if ($servicename -match 'MSSQL'){
if ($dependencies) {
#$dependencies.DependOnService
Set-ItemProperty -Path "HKLM:SYSTEMCurrentControlSetServices$servicename" -Name DependOnService -Value $CurrentServices
}
ELSE {
New-ItemProperty -Path "HKLM:SYSTEMCurrentControlSetServices$servicename" -Name DependOnService -PropertyType MultiString -Value $CurrentServices
}
}
IF($svcaccntname) {
$mgdservice = get-itemproperty -path "HKLM:SYSTEMCurrentControlSetServices$servicename" -Name ServiceAccountManaged -ErrorAction SilentlyContinue
if ($mgdservice) {
Set-ItemProperty -Path "HKLM:SYSTEMCurrentControlSetServices$servicename" -Name ServiceAccountManaged -Value @("01","00","00","00")
}
ELSE {
New-ItemProperty -Path "HKLM:SYSTEMCurrentControlSetServices$servicename" -Name ServiceAccountManaged -PropertyType BINARY -Value @("01","00","00","00")
}
}
#>
}Mandatory disclaimer: Do not run code you find on the internet in your production environment without testing it first. Do not use this code if your vision becomes blurred. Seek medical attention if this code runs longer than four hours. Common side effects include but not limited to: Diarrhea, Infertility, Dizziness, Shortness of breath, Impotence, Drowsiness, Fatigue, Heart issues (palpitations, irregular heartbeats), Hives, Nausea and vomiting, Rash, Imposter Syndrome, FOMO, and seasonal Depression. Script creator and site owner take no responsibility or liability for scripts executed.
Put a bow on it
DBAs frequently have tasks that must be done in a repeatable fashion. One of those repeatable tasks should be the task to ensure the Service Dependencies are properly set. This article shares a script that achieves the goal of creating a routine that is repeatable and easy to take some of that weight off the shoulders of the DBA.
The script provided in this article is an easy means to help ensure consistency and repeatability in tasks that may have to be repeated many times. Doing these tasks with a script is mundane and monotonous enough. Imagine doing that by hand, manually, on hundreds of servers – or even just two servers. Then to try to do it again in 6 months on another server – after you have forgotten what you did manually the first two times.
Interested in little more about security? Check these out!
Want to learn more about your indexes? Try this index maintenance article or this index size article.
This is the fourth article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post Time to Set Service Dependencies for SQL Server, it’s Easy first appeared on SQL RNNR.