I have been scratching my head for a while now on how to fix this issue, SQL Server Polybase Data Movement Service starts successfully however after a few seconds it just stops. The OS on our server went corrupt last week and force us to reload the OS(Windows Server 2016 Data Center), which meant I had to reinstall and reconfigure Microsoft SQL Server 2017 Enterprise. Since this I cannot get the data movement engine to start and stay running as I have a few process that relies on this I am really in need of some advice.
I have found an Microsoft article below which if I look at error:3409 (Which I cannot seem to find in the logs) is referring to registry access. I have always installed and configured Microsoft Sql Server to only use the service accounts for the Engine and the Agent and leave the other services to the defaults.
See logs
MSSQLSERVER_DET1_Dms_movement
2020/04/03 3:14:28 PM [Thread:114900] [CommandManager:ErrorEvent] (Error, High):
Shared memory segment has not been created or attached.
Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemoryException: Shared memory segment has not been created or attached.
at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.ThrowIfInvalid()
at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.SetDistributionCount(Int32 distrCount)
at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.UpdateSharedMemoryDistributionCount()
at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.AddDataChannelReceiver(String nodeName, DataChannelReceiver dataChannelReceiver)
at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.StartDataChannel(Int32 senderQueueSize, Int32 tcpBufferSize, HashSet`1 nodesToConnect)
at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessStartDataChannel(IMessage message)
at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessBaseMessage(IBaseMessage message)
2020/04/03 3:14:28 PM [Thread:114900] [CommandManager:InformationEvent] (Info, Normal): Shutting down.
2020/04/03 3:14:28 PM [Thread:12864] [DataChannelReceiver:InformationEvent] (Info, Normal): DataChannelReceiver DataChannelReceiver.Run–DET1:1433: Localhost DET1:1433 connected to RemoteHost DET1:1433: Initiating Run loop.
2020/04/03 3:14:28 PM [Thread:114900] [GeneralInstrumentation:SynchronousLogEvent] (Info, Normal): DMS Exit() called: Forceful shutdown initiated…
2020/04/03 3:14:28 PM [Thread:93816] [DMSDataChannelSenderClient:InformationEvent] (Info, Normal): DMSDataChannelClient created
2020/04/03 3:14:28 PM [Thread:93816] [DataChannelSender:InformationEvent] (Info, Normal): Starting CreditFeedbackReceiver thread: CreditFeedbackReceiver.Run–DET1
2020/04/03 3:14:28 PM [Thread:93816] [CommandManager:InformationEvent] (Info, Normal): Started data channel, node: DET1, type: Sender
2020/04/03 3:14:28 PM [Thread:84568] [CommandManager:InformationEvent] (Info, Normal): Started data channel, node: DET1, type: CreditFeedback
UPDATE
When stopping the SQL Sever Polybase Engine and then Trying to start the Polybase Data Movement Service, then the service remain started until I start the Polybase Engine Service then back to square 1 again.
Best Answer
It turned out to be a permissions issue with the DWConfiguration/ DWDiagnostics and DWQueue databases that I just reattached after the reinstall of the OS, for some reason the [NT AUTHORITY\NETWORK SERVICE] user did not have the correct permissions on these databases and thus the service kept on failing.
I managed to debug this with an extended events session trying to capture all queries that would touch these databases.
Thank you for all the help and support