The issue: every day, in a different time, there is no possibility to connect to SQL Server.
2 days ago we've migrated to new server (from win server 2008 r2 to win server 2012 r2)
on the old win server 2008 everything worked fine and we have the same SQL Server congfiguration setting for the new one:
- In SQL Server settings using shared memory only -- connections to SQL Server from a client running on the same computer.
- Named Pipes and TCP/IP are disabled.
- Server agent and server browser stopped
- Firewall is disabled.
In most of the time everything works without problems on the new server. The problem was identified by the applications which is trying to connect to the server and falls.
Log looks like this:
Event viewer application log:
2017-01-05 02:40:09.72 Server Error: 26050, Severity: 17, State: 1. 2017-01-05 02:40:09.72 Server Server local connection provider has stopped listening on [ \.\pipe\SQLLocal\SQLEXPRESS ] due to a failure. Error: 0xe8, state: 4. The server will automatically attempt to re-establish listening. 2017-01-05 02:40:54.10 spid24s Server local connection provider has successfully re-established listening on [ \.\pipe\SQLLocal\SQLEXPRESS ].
asp.net sites error log:
2017-01-06 00:10:23,545 [32] ERROR: System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
What could be the problem? How can I solve it?
New server details:
- Windos Server 2012 r2 Standard
- SQL Server 2014 Express
Old server details:
- Windows Server 2008 R2 Standard
- SQL Server 2008 Express
If you need any information, tell me please.
forgot to mention, that after win server reboot the issue dissapear on some time
ADDITIANAL INFO
1) Disabling named pipes as a client protocol does not seem to have any effect.
2) SELECT net_transport FROM sys.dm_exec_connections
3) I only see local connection information in my SQL Server error log:
2017-01-08 17:02:52.49 spid15s Server local connection provider is ready to accept connection on [ \.\pipe\SQLLocal\SQLEXPRESS ].
2017-01-08 17:02:52.49 spid15s Server local connection provider is ready to accept connection on [ \.\pipe\MSSQL$SQLEXPRESS\sql\query ].
as far as i know with named pipes enabled, I should see messages like 'Server named pipe provider is ready...'
also i've found the info "Shared memory is an implementation of a local named pipe. So messages from shared memory can have pipe information."