Friday, August 31, 2012 7:36 PM
Earlier in the week we upgraded our SQL Server from 2008 R2 to 2012 Standard Edition. Since performing the upgrade we have been getting the following exceptions generated from our website:
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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
For the most part the website appears to be functioning correctly. It is able to access data in the database without error, however intermittently our website will generate the exception shown above. It does not appear to be isolated to a particular page and is being generated by multiple websites which use the same database server.
After searching around for this error and potential solutions I have established the following:
The websites are hosted on a separate server to the databases and there is no firewall between them.
I have checked that the SQL browser service is running. The sql installation is a named instance.
The connection strings for the website are as follows:
<add name="Name" connectionString="Network Library=DBMSSOCN;Data Source=<Server IP Address>,1433\<Instance Name>;Initial Catalog=<Database Name>;User ID=<username>;Password=<password>" providerName="System.Data.SqlClient" />
I have checked that the SQL Server is configured to allow remote connections.
Up until we upgraded to 2012 we were not experiencing this exception and nothing has changed from our website in terms of how data is accessed. Our SQL Service is currently running as NETWORK SERVICE, which I have seen suggested as a possible cause of the problem. As far as I know this is what our SQL Server Service has always run as. Has anyone else changed this?
Has anyone else experienced this problem? Does anyone have any suggestions as to what the problem would be? All suggestions gratefully received.
Monday, September 03, 2012 12:41 AM
Data Source=<Server IP Address>,1433\<Instance Name>;
Have you specifically configured a named instance to port 1433? By default SQL Server will be using a dynamic port for a named instance. You can check this in SQL Server Configuration Manager, SQL Server Network Configuration, Protocols for [instance name], TCP/IP - Properties, IP Addresses tab, IPAll, TCP Dynamic Ports/TCP Port
Monday, September 03, 2012 7:09 AM
Thanks for the response. I wasn't aware that we had but TCP Port is set to 1433.
Should I change that setting?
Monday, September 03, 2012 7:27 AMWhile checking that I have noticed that IP7 also contains 1334. Not sure if that is important.
Monday, September 03, 2012 7:41 AMTypically port TCP 1433 is reserved for a default rather than named instance but it should be fine as long as you don't also have a default instance installed on that server.
Monday, September 03, 2012 7:52 AMI'm starting to question if it is a named instance (as you can tell I'm not a DBA). If I go to server properties it has a name, if I go into SQL Server Configuration Manager it is a nonnamed instance. How can I tell definitively?
Monday, September 03, 2012 9:16 AM
If you can connect to it using just the server name then it is a Default instance. If you need to use servername\instancename (like was suggested in the connection string), then it is a named instance.
Sounds like you have a Default instance.
Monday, September 03, 2012 9:24 AM
I had always thought we had a default instance rather than a named instance but obviously got confused on Friday.
It looks like the problem has started a few days after the upgrade so that could just be a coincidence.
Monday, September 03, 2012 11:42 AMModerator
When you upgrade SQL Server 2008 R2 to SQL Server 2012, please make sure that the hardware meets the requirements.
It may be caused by the low performance network or the busy SQL Server instance, and so on.
Refer to potential causes: http://support.microsoft.com/kb/328306.
Hope this helps,
Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
Saturday, September 08, 2012 1:01 PM
We have got to the bottom of the problem. It appears that the SQL upgrade was a red herring and that the problem was with one of the network components being used on our setup.
Thanks for the help.
- Marked As Answer by jonesri Saturday, September 08, 2012 1:02 PM