Azure-docs: Whenever I iniate the job at publisher the Log and SQL Agent Failed to start.

[Anmolgan81]

I have Azure Managed Instance as my publisher, I have followed the above steps mentioned bu whenver I try to create the publisher the SQL Agents failed to start, I have opened in the inbound and outbound port 445 for managed instance, still I am getting agents failed to start, can anyone tell me whats wrong here.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: d7cd1942-8ac7-eae9-4ac8-eb716ecd7b7b
• Version Independent ID: 559730f0-71fd-b16c-ecac-56954d5f2f64
• Content: Configure replication in Azure SQL Database Managed Instance
• Content Source: articles/sql-database/replication-with-sql-database-managed-instance.md
• Service: sql-database
• GitHub Login: @allenwux
• Microsoft Alias: xiwu

[Mike-Ubezzi-MSFT]

@Anmolgan81 Thank you for the detailed feedback. We are actively investigating and will get back to you soon.

[Mike-Ubezzi-MSFT]

@Anmolgan81 Do you have any log details to share? At this point, this is more of a forum question or support issue than a documentation issue. We need more information. Given the number of issues filed today with regard to replication, I highly suggest you open an incident ticket with support, as this has become a product support issue and not documentation related at this point. Even asking this question in the forums will provide greater visibility and engagement from the community. Thank you!

[cubikca]

I can confirm that the above steps do not work

Date        1/31/2019 12:15:57 AM
Log     Job History (***)

Step ID     2
Server      ***
Job Name        ***
Step Name       Run agent.
Duration        00:01:09
Sql Severity    0
Sql Message ID  0
Operator Emailed    
Operator Net sent   
Operator Paged  
Retries Attempted   0

Message
2019-01-31 00:15:57.212 Copyright (c) 2016 Microsoft Corporation
2019-01-31 00:15:57.212 Microsoft SQL Server Replication Agent: logread
2019-01-31 00:15:57.212 
2019-01-31 00:15:57.212 The timestamps prepended to the output lines are expressed in terms of UTC time.
2019-01-31 00:15:57.212 User-specified agent parameter values:
            -Publisher ***
            -PublisherDB RSPNetProd_2019-01-08T20-46Z
            -Distributor ***
            -DistributorSecurityMode 1
            -Continuous
            -XJOBID 0x31747AB31D13C1449692C42BC4680382
            -XJOBNAME ***
            -XSTEPID 2
            -XSUBSYSTEM LogReader
            -XSERVER ***
            -XCMDLINE 1
            -XDB
            -XHOST np:\\.\pipe\DB.1-E6B274EF23C9\sql\query
            -XCancelEventHandle 0000000000001908
            -XParentProcessHandle 00000000000017EC
2019-01-31 00:16:44.884 Parameter values obtained from agent profile:
            -outputverboselevel 2
            -pollinginterval 5000
            -historyverboselevel 1
            -logintimeout 15
            -querytimeout 1800
            -readbatchsize 500
            -logscanthreshold 500000
2019-01-31 00:17:06.025 Status: 0, code: 22037, text: 'Named Pipes Provider: Could not open a connection to SQL Server [53]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Query timeout expired, Failed Command: '.
2019-01-31 00:17:06.025 Named Pipes Provider: Could not open a connection to SQL Server [53]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Query timeout expired, Failed Command: 
2019-01-31 00:17:06.634 Disconnecting from OLE DB DistLog '***'
2019-01-31 00:17:06.634 Disconnecting from OLE DB Publisher '***'
2019-01-31 00:17:06.634 Disconnecting from OLE DB  ''

Why is the agent trying to connect via named pipes? Do named pipes even work on SQL Managed Instance?

[Mike-Ubezzi-MSFT]

@Anmolgan81 Can you change from Named Pipes to TCP.

2019-01-31 00:17:06.025 Status: 0, code: 22037, text: 'Named Pipes Provider: Could not open a connection to SQL Server [53]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Query timeout expired, Failed Command: '.
2019-01-31 00:17:06.025 Named Pipes Provider: Could not open a connection to SQL Server [53]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Query timeout expired, Failed Command:

[cubikca]

Thanks for your quick reply. I also agree that connecting via TCP would be a solution. However, the SQL Server Agent appends the following argument: -XHOST np:\.\pipe\DB.1-E6B274EF23C9\sql\query. I believe that this is why it's trying to connect via named pipes. I've looked all over, but I can't find any documentation about how to change this behavior. Please let us know how to change from Named Pipes provider to TCP.

[Anmolgan81]

How can we change the named pipes of azure sql managed instance??

[Anmolgan81]

@cubikca @Mike-Ubezzi-MSFT

SQL MI doesn't support connectivity through Named Pipes. So if you app connects to SQL Server using Named Pipes, you need to switch it to TCP/IP sockets.

I find the above in this document is this not supported?
https://blogs.technet.microsoft.com/hybridcloudbp/2018/11/02/migration-from-sql-server-to-azure-sql-database-managed-instance/

[Anmolgan81]

@Mike-Ubezzi-MSFT We have currently opened a ticket with Azure Managed Instance, beacuse we are not able to make Transactional Replication work in any of the cases which involves Azure Managed Instance. And this is the only option as we have presented it to the customer.

[Anmolgan81]

@Mike-Ubezzi-MSFT for the information I am using the Publisher/Distributor both as a Azure Managed Instance.

[cubikca]

For anyone else who encounters this issue, this is the recommended resolution by Microsoft:

"On managed instances, you may see initial connectivity issues immediately after configuring your publication and subscriptions."

We should increase login timeout period and force TCP protocol as follows:

-- Increase the default login timeouts for all jobs
update msdb..sysjobsteps set command = command + N' -LoginTimeout 150' 
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

-- Explicitly set the tcp protocol for the connections:
update msdb..sysjobsteps set command = replace(command,' -Distributor [',' -Distributor [tcp:') 
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '% -Distributor \[tcp:%' escape '\'
GO

This resolved the issue for me. Thanks.

[Anmolgan81]

@cubikca I am still struglling with it.

[CarlRabeler]

@MashaMSFT for awareness

[MashaMSFT]

Hi @Anmolgan81 , can you please confirm which version of SQL Server you're using? In another thread, I saw a mention of SQL 2008, and unfortunately that's not supported. Using an older version of SQL can cause connectivity issues. Publishing to objects in Azure (standalone and manged instance) is only supported from these versions of SQL:

• SQL Server 2019 (preview)
• SQL Server 2016 to SQL 2017
• SQL Server 2014 SP1 CU3 or greater (12.00.4427)
• SQL Server 2014 RTM CU10 (12.00.2556)
• SQL Server 2012 SP3 or greater (11.0.6020)
• SQL Server 2012 SP2 CU8 (11.0.5634.0)

If your SQL Server is older, please try utilizing the republishing method. If you're on a supported version of SQL Server, then your approach in opening a ticket with support is the best as we may need investigate your situation further. Thanks!

[Anmolgan81]

I am using sql server 2008 to azure sql managed instance migration but I think transactional replication is still not supported in that case, but in this scenario we are talking about azure managed instance to azure sql database migration.

[Mike-Ubezzi-MSFT]

That is correct @Anmolgan81, that is what I was trying to communicate with #23779.

[Anmolgan81]

@Mike-Ubezzi-MSFT I was confused, I though that those instructions are for Azure SQL Database and not SQL Managed Instance, the document should be accurate I suppose.

[CarlRabeler]

I am pushing a PR to add some clarity to this topic

[Mike-Ubezzi-MSFT]

@CarlRabeler Please let me know when the PR is completed.

[CarlRabeler]

done, please close @Mike-Ubezzi-MSFT

[Mike-Ubezzi-MSFT]

@Anmolgan81 We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.

[jdneilso]

For anyone else who encounters this issue, this is the recommended resolution by Microsoft:
"On managed instances, you may see initial connectivity issues immediately after configuring your publication and subscriptions."
We should increase login timeout period and force TCP protocol as follows:
-- Increase the default login timeouts for all jobs
update msdb..sysjobsteps set command = command + N' -LoginTimeout 150'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

-- Explicitly set the tcp protocol for the connections:
update msdb..sysjobsteps set command = replace(command,' -Distributor [',' -Distributor [tcp:')
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '% -Distributor [tcp:%' escape '\'
GO

This resolved the issue for me. Thanks.

Thank you so much for this!!! I was racking my brain for almost a whole work day! Microsoft should include this in the article. Managed instances don't support Named Pipes!!!

[jdneilso]

For anyone else who encounters this issue, this is the recommended resolution by Microsoft:
"On managed instances, you may see initial connectivity issues immediately after configuring your publication and subscriptions."
We should increase login timeout period and force TCP protocol as follows:
-- Increase the default login timeouts for all jobs
update msdb..sysjobsteps set command = command + N' -LoginTimeout 150'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'
-- Explicitly set the tcp protocol for the connections:
update msdb..sysjobsteps set command = replace(command,' -Distributor [',' -Distributor [tcp:')
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '% -Distributor [tcp:%' escape ''
GO
This resolved the issue for me. Thanks.

Thank you so much for this!!! I was racking my brain for almost a whole work day! Microsoft should include this in the article. Managed instances don't support Named Pipes!!!

Also, you will need to stop and start the log reader agent, snapshot agent, and reinitialize the subscribers.

[CarlRabeler]

@MashaMSFT please review for doc update