MSSQL Agent Not Working on Windows-MSSQL-Docker Image

[shubhamgoel-tomhorn]

Hello,

I was trying the mssql-docker image for windows and everything seems to work fine but the SQL Server Agent doesn't start at all. It is always in Agent XPs disabled mode.

I have tried: Enabling the Agent XPs with advanced options. After enabling the Agent XPs the warning goes away but the Sql Server Agent Service doesn't start. If I start the Sql Server Agent Service, it fails with error

Start-Service : Failed to start service 'SQL Server Agent (MSSQLSERVER) (SQLSERVERAGENT)'.
At line:1 char:1
+ Start-Service SQLSERVERAGENT
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ServiceCommandException
    + FullyQualifiedErrorId : StartServiceFailed,Microsoft.PowerShell.Commands.StartServiceCommand

Also the log file C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT gives the following information:

2020-08-14 12:23:49 - ? [297] SQLServer Message: 15457, Configuration option 'Agent XPs' changed from 1 to 0. Run the RCONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2020-08-14 12:23:49 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)                                                                                 
2020-08-14 12:23:49 - ? [098]  SQLServerAgent terminated (normally)

I am unable to get rid of this problem and this is a necessary requirement for my POC to complete.

Please help.

Also I cannot find any new MSSQL-Docker images for Windows. Has Microsoft discontinued the plan to put MSSQL in docker to production?

[tracker1]

I'm guessing that ongoing MSSQL-Docker efforts will center around the use of the Linux version for Docker and MSSQL in Docker. Given, as you mention there isn't a recent version.

[shubhamgoel-tomhorn]

Is it ok, if we use this image in production. Are there any plans to decommission this image?

[tracker1]

@shubhamgoel-tomhorn I'm not an MS employee, so cannot speak from any official stance. To be honest/blunt, I wouldn't touch/use docker with windows containers in production as a general rule. This is simply because the number of issues I've experienced regarding what is needed in legacy windows environments is difficult in containers, running both linux and windows containers on a system are problematic, and for anything new, it's easy enough to cover windows (local/dev) and linux containers for production deploys in current/modern langauges.

Again, this is just my own opinion on this, that the effort just hasn't been worth it. I'll use a full windows server or vm when needed and target linux containers as a preference.

[pedro-bonilla]

Hi @shubhamgoel-tomhorn, I had a similar issue testing docker and the sql-server image for windows, the sqlagent did not start. I fixed it by doing next steps:

1. Adding to the docker run command this option: -e "MSSQL_AGENT_ENABLED=true"
2. After container becomes up & running, execute this PS command from your host: docker container exec YourContainerName powershell.exe /C "Start-Service sqlserveragent"

If you open your SSMS client you will see the Agent is enabled, so you can create jobs, schedules, etc.

Hope that helps!

[pedro-bonilla]

BTW, as @tracker1 mentioned the MSSQL container for Windows is not supported for Production, you can see this by checking:

• https://hub.docker.com/search?q=microsoft%2Fmssq&type=image (the only windows containers for mssql are the development and the sqlserverexpress, rest is all linux)
• https://hub.docker.com/r/microsoft/mssql-server-windows-developer (where it says: Intended Use: Development and Testing only. Not supported in production environments.)

Cheers!

[soroshsabz]

@shubhamgoel-tomhorn Did you check PR #486 and PR #485 ? I think this PR maybe can resolve your issue.

[PaulineXiong]

-e "MSSQL_AGENT_ENABLED=true" did not work in my case. (did not give error either). I connected to SQL Server instance using SSMS, then use EXEC SP_CONFIGURE to set 'Agent XPs' to 1. start the agent service: docker container exec cmd.exe /C "net start sqlserveragent"

[shubhamgoel-tomhorn]

If this is not working for anyone yet, try to change the Service Account of SQLServerAgent Service. What I remember is that I changed it from default assigned account to some other account, not remember exactly and it worked for me.

[PaulineXiong]

I meant, in my case, by changing 'Agent XPs' to enable agent, I can start the agent service with no problem. by default the agent is disabled.

[bmeschesi]

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/agent-xps-server-configuration-option?view=sql-server-ver16

[thesqlsith]

Thank you @shubhamgoel-tomhorn for letting us know about the comments the SQL Server Agent issue that you have encountered. Unfortunately, we had to suspended the SQL Server on Windows container beta program.

https://techcommunity.microsoft.com/t5/sql-server-blog/update-beta-program-for-sql-server-on-windows-container-is/ba-p/2516639

SQL Server on Linux containers, however, is supported and is not in beta and has been available for production use for some time.

https://learn.microsoft.com/sql/linux/sql-server-linux-overview

Please feel free to pull one of our existing container images for Linux.

[soroshsabz]

Why Microsoft force users to use foreign operating system, instead of Windows Server? (Did you want to retired windows server?)

Why Windows Container does not support by Microsoft products?

[tracker1]

@soroshsabz Not a MS employee... but imo containerization in Windows is problematic because of tethers to incompatible base images that change with each major windows release. It's simply difficult to maintain an ever expanding cross-section of windows and application versions.

SQL Server is not being retired on Windows. If you want SQL Server on Windows, you can install SQL Server on Windows.

The Docker community really didn't embrace any containerization efforts for non-Linux images. If you're wanting to use Docker, you're effectively using/embracing Linux. I'm really unaware of anyone actually using Windows Containers on Windows at all. I know of a few places that toyed with it and found it problematic (as mentioned above).