Could you point what else is needed to be configured at SSMS to enable SQLServer Agent after its service is enabled?

Eduardo Fuerte
Eduardo Fuerte used Ask the Experts™
on
Hi Experts

Could you point what else is needed to be configured at SSMS to enable SQLServer Agent after its service is enabled?

MSSQLServer 2017

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
You mean to start SQL Agent via SSMS or somethinge else your asking about?
Here https://docs.microsoft.com/en-us/sql/ssms/agent/autostart-sql-server-agent-sql-server-management-studio?view=sql-server-ver15 you can find how you can set "Auto restart SQL Server Agent if it stops unexpectedly."
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hi

I had started the service but after that returning to SSMS the SQL Agent doesn't appear to be used.
lcohanDatabase Analyst

Commented:
Hi Eduardo, sorry I don't quite understand what do you mean by "SQL Agent doesn't appear to be used." ?
Does it looks looks like an db icon with blue question mark in SSMS? You can use the query below against that SQL Server to see what all the services statuses are:

select * from sys.dm_server_services

Open in new window


or like this to just see SQL Agent state

SELECT dss.[status], dss.[status_desc]
FROM   sys.dm_server_services dss
WHERE  dss.[servicename] LIKE N'SQL Server Agent (%';

Open in new window


Alternatively you could Enable these Windows Firewall rules and maybe also you'll need to open port 135
•      Windows Management Instrumentation (WMI-In)
•      Windows Management Instrumentation (DCOM-In)
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Eduardo FuerteDeveloper and Analyst

Author

Commented:
Running the commands

img001
img002
Em execução => Running

I'm going to enable port 135

Could you better explain this ?

•      Windows Management Instrumentation (WMI-In)
•      Windows Management Instrumentation (DCOM-In)
lcohanDatabase Analyst

Commented:
The two mentioned rules are predefined and you can enable them by right clicking and enabling these rules in Windows Firewall advanced settings.
But your problem is that SQL Agent is not included in on your SQL Express and in order tu use this SQL Feature you would need to run a different Edition - please have a look here https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15 under Management Tools and you'll see that SQL Agent is not part of the edition you're currently running.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Enlightened.

Could you suggest a MSSQLServer version (free) that enables SQL Agent, even it's not recent?
Database Analyst
Commented:
If I am not mistaken you should be able to still download and use the Developer Edition that has the full features like enterprise does however...I don't think you can use that Edition for any production system as Microsoft will come after you for licensing cost
https://cloudblogs.microsoft.com/sqlserver/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/
lcohanDatabase Analyst

Commented:
Also you can upgrade your current SQL Express edition to Developer:

Download the appropriate edition (x86 or x64)
Mount the .iso and run the installer package
Select "Maintenance"
Select "Edition Upgrade"
Follow the prompts and select the Express instance that you wish to upgrade.
To enable SQL Agent:

sp_configure 'show advanced options', 1
RECONFIGURE
GO

sp_configure 'agent xps', 1
RECONFIGURE
GO

sp_configure
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Thank you for assistance until now.

Is it necessary to complete install:

https://cloudblogs.microsoft.com/sqlserver/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

Or just SSMS ?

If completely install is needed I'm planning to uninstall the actual SQLEXPRESS instalation and then reinstall.
lcohanDatabase Analyst

Commented:
It is not enough to install only SSMS - this is just the management tool - you must either upgrade your SQL Express following steps from link above or to uninstall and reinstall but save any DB you may already have in SQL Express + everything else as they will get lost if you uninstall.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Icohan

Thank you very much for the guidance!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial