Sql Server Agent Required For Remote Connections?

Question

I'm connecting to a remote sql server instance with the following connection string

"Network Library=DBMSSOCN;Data Source=xx.xxx.x.xxx,1433;Initial Catalog=MyDatabase;User Id=MyUserId;Password=MyPassword;Connect Timeout=120;"

1. Sql Server Browser is running
2. Local and remote connections are allowed using TCP/IP only

The application randomly fails to connect citing the following SqlException

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

Code to connect as follows

using (SqlConnection connection = new SqlConnection(m_ConnectionString))
{
    connection.Open(); // falls over here
}

I thought about Firewall config on port 1433 but why would the Firewall sometimes permit connections and sometimes not?

Is Sql Server Agent required to be running? And if so why as the description of Sql Server Agent on MSDN is not obvious in this respect?

Thanks!

EDIT: Tried adding an explicit rule on the Firewall to open up 1433 and so far so good, but could be randomness again so hard to say if I fix or not yet

Answer 1

No, SQL Server Agent does not need to be running. SQL Server agent is only needed to execute scheduled tasks on the SQL server.

This is probably a network issue. Can you ping the server? If so, try to ping it continuously with /t and see if all packets arrive.

It could also be a problem with the SQL server load. Is the server idle or under load?

Could you test the application locally on the SQL server or on the same LAN to see if the problem persists?

Answer 2

Well you don't need SQL Server Agent running now, the error suggest you are getting a timeout from the scenario.

There are various reasons why you may be blocked. Perhaps the firewall is configured to allow only a certain number of connections at a port at one time. Or the database itself is limiting the number of connections. Your internet is slow and you are hitting the 120 seconds timeout. The list goes on. :)

Answer 3

Your SQL Server database is going to sleep. The first time you try to connect to it, it has to restart and your connection times out.

Make sure the option "Auto Close" is set to "False". This prevents it from shutting itself down after the last connection is closed.

http://itknowledgeexchange.techtarget.com/sql-server/auto-close-is-almost-as-bad-as-auto-grow/