Sql-server – SQL Server Polybase Data Movement Service stopping after few seconds

polybasesql server

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.

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver15

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