SQL server agent node missing in SSMS

Hi experts.

SQL Server 2012 used to be backed up using tasks that were configured by SQL management studio. Lately, the underlying OS windows 8.1 was upgraded to Win10 v1803 and although the rest works normally, no SQL backups run anymore. Investigating, I found that in SQL management studio, there no longer is a node "SQL server agent" and that seems to be why we can't view or re-configure backups. The SQL server agent service is still present and can be started in services.msc, but immediately stops, saying: ""SQLSERVERAGENT service on Local Computer started and then stopped. Some services stop automatically if they have no work to do".
I had SQL management studio 2012 installed, I installed ssms 2017 (latest 17.8) as well, but no change.

Where to go from here?
LVL 61
McKnifeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I found that in SQL management studio, there no longer is a node "SQL server agent" an
<Knee-jerk response>  Maybe you no longer have privs to create/execute SQL Agent jobs, hence it does not appear in SSMS, and need a DBA to investigate this.
0
McKnifeAuthor Commented:
Hi Jim.

All possible DB privileges granted, don't worry. SSMS started elevated as well.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok.  Sorry, that's all I have, so I'll withdraw to encourage other experts to respond.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
It's almost as if the Windows upgrade was also coupled with a SQL Server edition change. Was the SQL Server installation touched in any way?
0
McKnifeAuthor Commented:
Hi. No, not changed in any way.
0
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
One more thing - please do not use the services.msc to administrate SQL Server services.
The SQL Server Configuration Manager also takes care of setting and maintaining appropriate registry permissions that are needed for SQL Server to run properly.

I would recommend upgrading to the latest Service Pack/CU to ensure permissions are set properly and then use SQL Server Configuration Manager to administrate the services.
0
McKnifeAuthor Commented:
Hi Nakul.

Look, we have some progress:
"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT" goes
--
2018-08-24 14:11:53 - ? [100] Microsoft SQLServerAgent, Version 11.0.7462.6 (Build X64 unicode Retail): Prozess-ID 8476
2018-08-24 14:11:53 - ? [495] Das Dienstkonto für den Start des SQL Server-Agents ist NT Service\SQLSERVERAGENT.
2018-08-24 14:11:53 - ? [393] SQL Server stellt die Datenbank 'msdb' wieder her. Bitte warten Sie...
2018-08-24 14:11:53 - ? [000]
2018-08-24 14:11:53 - ? [101] SQL Server AAZUSI, Version 11.00.7462 (Anzahl von möglichen Verbindungen: 0)
2018-08-24 14:11:53 - ? [102] SQL Server-ODBC-Treiber, Version 11.00.7462
2018-08-24 14:11:53 - ? [103] Der Treiber verwendet folgende NetLib: DBNETLIB. Der lokale Hostserver ist ''''.
2018-08-24 14:11:53 - ? [310] 2 Prozessor(en) und 6144 MB RAM erkannt.
2018-08-24 14:11:53 - ? [339] Der lokale Computer ''AAZUSI'' wird unter ''Windows NT 6.2 (9200) '' ausgeführt.
2018-08-24 14:11:53 - ! [000] Diese Installation des SQL Server-Agents ist deaktiviert. Die Edition von SQL Server, die diesen Dienst installiert hat, unterstützt den SQL Server-Agent nicht.
2018-08-24 14:11:54 - ? [000] Die Konfigurationsoption 'Agent XPs' wurde von 1 in 0 geändert. Führen Sie zum Installieren die RECONFIGURE-Anweisung aus. [SQLSTATE 01000] (Meldung 15457)
2018-08-24 14:11:54 - ? [098] ''SQLServerAgent'' wurde beendet (normal).
--
In case you don't have access to browser translation features, I will translate the important line: "This installation of sql server agent is deactivated. The edition of SQL Server that has installed the service does not support SQL Server Agent"
...which is utter non-sense, because it did support SQL Agent before upgrading to Win10. But anyway, since we only use SQL server Agent for backups and we can easily solve these using powershell, we will no longer try to repair sql server agent. Maybe it's as easy as reinstalling SQL server, but there is no need. I was just interested in the reason.
0
McKnifeAuthor Commented:
Not really solved, but found no need to continue. The error message in the log revealed, that the SQL edition suddenly does not recognize itself anymore (being a full edition with SQL agent). Never mind and thanks for the ideas. (See previous comment for more).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Thanks for the update. Based on the log, there is a very high probability that something went wrong with registry permissions during the Windows upgrade because of which SQL Server can't recognize the edition and the Agent correctly anymore.

Anyway, thanks again for the update and have a great week ahead!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.