Service depends on SQL Server

  • Thread starter Thread starter Alan Pretre
  • Start date Start date
A

Alan Pretre

I have a Windows service that is having trouble starting up at boot time.
It requires SQL Server to start up. I have added MSSQLSERVER to the service
dependencies (as explained in http://tinyurl.com/5s7kx) but that doesn't
help. I changed the dependency from MSSQLSERVER to SQLSERVERAGENT which
made no difference. I log exceptions to the eventlog which tell me the
following error, abridged:

Login failed for user 'xxxxxx'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
<snip>

I'm thinking I need to catch this exception in a loop a few times at service
startup for a long enough time for SQL Server to completely spin up.
Anybody else have other ideas? The service is written in C#.

-- Alan
 
Try playing with the "Recovery" tab parameters.

You still want your service dependent on MSSQLSERVER. If I remember
correctly, the SQLSERVERAGENT service is not started Automatically.

Dave
 
Hi,

You can set the dependency in code. The code snippet below is from a
service that I wrote that assures that SQL Server is running before it
starts. After I run InstallUtil on my service MSSQLSERVER is listed in the
depended on tab in the services window. When I reboot or start from a
command line I have no problems. You should not have to code any retry loops
or take any special action in your code:

private void InitializeComponent()
{
....
//
// service
//
this.service.ServicesDependedOn = new string[] {"MSSQLSERVER"};
this.service.DisplayName = "Xxxxx";
this.service.ServiceName = "Xxxxx";
this.service.StartType = System.ServiceProcess.ServiceStartMode.Automatic;
....
}

--Richard
 
I would try Richard's solution before doing anything else.

This looks like it will work.

Nice Richard!


Richard said:
Hi,

You can set the dependency in code. The code snippet below is from a
service that I wrote that assures that SQL Server is running before it
starts. After I run InstallUtil on my service MSSQLSERVER is listed in
the
depended on tab in the services window. When I reboot or start from a
command line I have no problems. You should not have to code any retry
loops
or take any special action in your code:

private void InitializeComponent()
{
....
//
// service
//
this.service.ServicesDependedOn = new string[] {"MSSQLSERVER"};
this.service.DisplayName = "Xxxxx";
this.service.ServiceName = "Xxxxx";
this.service.StartType =
System.ServiceProcess.ServiceStartMode.Automatic;
....
}

--Richard

Alan Pretre said:
I have a Windows service that is having trouble starting up at boot time.
It requires SQL Server to start up. I have added MSSQLSERVER to the
service
dependencies (as explained in http://tinyurl.com/5s7kx) but that doesn't
help. I changed the dependency from MSSQLSERVER to SQLSERVERAGENT which
made no difference. I log exceptions to the eventlog which tell me the
following error, abridged:

Login failed for user 'xxxxxx'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
<snip>

I'm thinking I need to catch this exception in a loop a few times at
service
startup for a long enough time for SQL Server to completely spin up.
Anybody else have other ideas? The service is written in C#.

-- Alan
Click to expand...
Click to expand...
 
Using dependencies may work in a situation where SQL server and your service
are on the same machine. But what about a distributed environment where SQL
server is on a separate server?

Yoshi said:
I would try Richard's solution before doing anything else.

This looks like it will work.

Nice Richard!


Richard said:
Hi,

You can set the dependency in code. The code snippet below is from a
service that I wrote that assures that SQL Server is running before it
starts. After I run InstallUtil on my service MSSQLSERVER is listed in
the
depended on tab in the services window. When I reboot or start from a
command line I have no problems. You should not have to code any retry
loops
or take any special action in your code:

private void InitializeComponent()
{
....
//
// service
//
this.service.ServicesDependedOn = new string[] {"MSSQLSERVER"};
this.service.DisplayName = "Xxxxx";
this.service.ServiceName = "Xxxxx";
this.service.StartType =
System.ServiceProcess.ServiceStartMode.Automatic;
....
}

--Richard

Alan Pretre said:
I have a Windows service that is having trouble starting up at boot time.
It requires SQL Server to start up. I have added MSSQLSERVER to the
service
dependencies (as explained in http://tinyurl.com/5s7kx) but that doesn't
help. I changed the dependency from MSSQLSERVER to SQLSERVERAGENT which
made no difference. I log exceptions to the eventlog which tell me the
following error, abridged:

Login failed for user 'xxxxxx'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
<snip>

I'm thinking I need to catch this exception in a loop a few times at
service
startup for a long enough time for SQL Server to completely spin up.
Anybody else have other ideas? The service is written in C#.

-- Alan
Click to expand...
Click to expand...
Click to expand...
 
Yes in this case however I know that SQL Server is installed on this test
machine.

In the general case though this is why I was thinking of putting my startup
access in a retry loop. It seems like this would work for a local or remote
SQL Server. I was looking for more input from others....

-- Alan


Mike Mazar said:
Using dependencies may work in a situation where SQL server and your service
are on the same machine. But what about a distributed environment where SQL
server is on a separate server?

Yoshi said:
I would try Richard's solution before doing anything else.

This looks like it will work.

Nice Richard!


Richard said:
Hi,

You can set the dependency in code. The code snippet below is from a
service that I wrote that assures that SQL Server is running before it
starts. After I run InstallUtil on my service MSSQLSERVER is listed in
the
depended on tab in the services window. When I reboot or start from a
command line I have no problems. You should not have to code any retry
loops
or take any special action in your code:

private void InitializeComponent()
{
....
//
// service
//
this.service.ServicesDependedOn = new string[] {"MSSQLSERVER"};
this.service.DisplayName = "Xxxxx";
this.service.ServiceName = "Xxxxx";
this.service.StartType =
System.ServiceProcess.ServiceStartMode.Automatic;
....
}

--Richard

:

I have a Windows service that is having trouble starting up at boot time.
It requires SQL Server to start up. I have added MSSQLSERVER to the
service
dependencies (as explained in http://tinyurl.com/5s7kx) but that doesn't
help. I changed the dependency from MSSQLSERVER to SQLSERVERAGENT which
made no difference. I log exceptions to the eventlog which tell me the
following error, abridged:

Login failed for user 'xxxxxx'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
<snip>

I'm thinking I need to catch this exception in a loop a few times at
service
startup for a long enough time for SQL Server to completely spin up.
Anybody else have other ideas? The service is written in C#.

-- Alan
Click to expand...
Click to expand...
Click to expand...
 
Running a loop will consume too much CPU. If you want to go in that path, use
a timer.
What I would suggest is to use "Recovery" options in Service Properties
dialog box. It's designed for this purpose, but I have not been able to make
it work on my .net
service. Looks like the service has to return a specific error code so the
SCM can detect it as a failed service.

Alan Pretre said:
Yes in this case however I know that SQL Server is installed on this test
machine.

In the general case though this is why I was thinking of putting my startup
access in a retry loop. It seems like this would work for a local or remote
SQL Server. I was looking for more input from others....

-- Alan


Mike Mazar said:
Using dependencies may work in a situation where SQL server and your service
are on the same machine. But what about a distributed environment where SQL
server is on a separate server?

Yoshi said:
I would try Richard's solution before doing anything else.

This looks like it will work.

Nice Richard!


Hi,

You can set the dependency in code. The code snippet below is from a
service that I wrote that assures that SQL Server is running before it
starts. After I run InstallUtil on my service MSSQLSERVER is listed in
the
depended on tab in the services window. When I reboot or start from a
command line I have no problems. You should not have to code any retry
loops
or take any special action in your code:

private void InitializeComponent()
{
....
//
// service
//
this.service.ServicesDependedOn = new string[] {"MSSQLSERVER"};
this.service.DisplayName = "Xxxxx";
this.service.ServiceName = "Xxxxx";
this.service.StartType =
System.ServiceProcess.ServiceStartMode.Automatic;
....
}

--Richard

:

I have a Windows service that is having trouble starting up at boot time.
It requires SQL Server to start up. I have added MSSQLSERVER to the
service
dependencies (as explained in http://tinyurl.com/5s7kx) but that doesn't
help. I changed the dependency from MSSQLSERVER to SQLSERVERAGENT which
made no difference. I log exceptions to the eventlog which tell me the
following error, abridged:

Login failed for user 'xxxxxx'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
<snip>

I'm thinking I need to catch this exception in a loop a few times at
service
startup for a long enough time for SQL Server to completely spin up.
Anybody else have other ideas? The service is written in C#.

-- Alan
Click to expand...
Click to expand...
Click to expand...
 
I talked to Microsoft technical support and apparently, it's not a good idea
to use "Service Recovery" feature of Windows 2000/XP. Here is what I did
after getting ideas from them:
- In OnStart event, create a new thread to initialize the service. This way
the "Service Start" will be successful.
- If it fails (for example, because SQL server is not ready yet), use
Thread.Sleep to wait for some time and then try to initialize the service
again.
- You can choose the number of retries and delay between each.
- You have the option to stop your service after certain number of retries.
- You may want to log each retry in the event log, along with how many times
you will retry, and delay between each retry.

Mike Mazar said:
Running a loop will consume too much CPU. If you want to go in that path, use
a timer.
What I would suggest is to use "Recovery" options in Service Properties
dialog box. It's designed for this purpose, but I have not been able to make
it work on my .net
service. Looks like the service has to return a specific error code so the
SCM can detect it as a failed service.

Alan Pretre said:
Yes in this case however I know that SQL Server is installed on this test
machine.

In the general case though this is why I was thinking of putting my startup
access in a retry loop. It seems like this would work for a local or remote
SQL Server. I was looking for more input from others....

-- Alan


Mike Mazar said:
Using dependencies may work in a situation where SQL server and your service
are on the same machine. But what about a distributed environment where SQL
server is on a separate server?

:

I would try Richard's solution before doing anything else.

This looks like it will work.

Nice Richard!


Hi,

You can set the dependency in code. The code snippet below is from a
service that I wrote that assures that SQL Server is running before it
starts. After I run InstallUtil on my service MSSQLSERVER is listed in
the
depended on tab in the services window. When I reboot or start from a
command line I have no problems. You should not have to code any retry
loops
or take any special action in your code:

private void InitializeComponent()
{
....
//
// service
//
this.service.ServicesDependedOn = new string[] {"MSSQLSERVER"};
this.service.DisplayName = "Xxxxx";
this.service.ServiceName = "Xxxxx";
this.service.StartType =
System.ServiceProcess.ServiceStartMode.Automatic;
....
}

--Richard

:

I have a Windows service that is having trouble starting up at boot time.
It requires SQL Server to start up. I have added MSSQLSERVER to the
service
dependencies (as explained in http://tinyurl.com/5s7kx) but that doesn't
help. I changed the dependency from MSSQLSERVER to SQLSERVERAGENT which
made no difference. I log exceptions to the eventlog which tell me the
following error, abridged:

Login failed for user 'xxxxxx'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
<snip>

I'm thinking I need to catch this exception in a loop a few times at
service
startup for a long enough time for SQL Server to completely spin up.
Anybody else have other ideas? The service is written in C#.

-- Alan
Click to expand...
Click to expand...
Click to expand...
 
Mike Mazar said:
I talked to Microsoft technical support and apparently, it's not a good idea
to use "Service Recovery" feature of Windows 2000/XP. Here is what I did
after getting ideas from them:
- In OnStart event, create a new thread to initialize the service. This way
the "Service Start" will be successful.
- If it fails (for example, because SQL server is not ready yet), use
Thread.Sleep to wait for some time and then try to initialize the service
again.
- You can choose the number of retries and delay between each.
- You have the option to stop your service after certain number of retries.
- You may want to log each retry in the event log, along with how many times
you will retry, and delay between each retry.

Mike Mazar said:
Running a loop will consume too much CPU. If you want to go in that path, use
a timer.
What I would suggest is to use "Recovery" options in Service Properties
dialog box. It's designed for this purpose, but I have not been able to make
it work on my .net
service. Looks like the service has to return a specific error code so the
SCM can detect it as a failed service.

Alan Pretre said:
Yes in this case however I know that SQL Server is installed on this test
machine.

In the general case though this is why I was thinking of putting my startup
access in a retry loop. It seems like this would work for a local or remote
SQL Server. I was looking for more input from others....

-- Alan


Using dependencies may work in a situation where SQL server and your
service
are on the same machine. But what about a distributed environment where
SQL
server is on a separate server?

:

I would try Richard's solution before doing anything else.

This looks like it will work.

Nice Richard!


Hi,

You can set the dependency in code. The code snippet below is from a
service that I wrote that assures that SQL Server is running before it
starts. After I run InstallUtil on my service MSSQLSERVER is listed
in
the
depended on tab in the services window. When I reboot or start from a
command line I have no problems. You should not have to code any
retry
loops
or take any special action in your code:

private void InitializeComponent()
{
....
//
// service
//
this.service.ServicesDependedOn = new string[] {"MSSQLSERVER"};
this.service.DisplayName = "Xxxxx";
this.service.ServiceName = "Xxxxx";
this.service.StartType =
System.ServiceProcess.ServiceStartMode.Automatic;
....
}

--Richard

:

I have a Windows service that is having trouble starting up at boot
time.
It requires SQL Server to start up. I have added MSSQLSERVER to the
service
dependencies (as explained in http://tinyurl.com/5s7kx) but that
doesn't
help. I changed the dependency from MSSQLSERVER to SQLSERVERAGENT
which
made no difference. I log exceptions to the eventlog which tell me
the
following error, abridged:

Login failed for user 'xxxxxx'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at

System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
<snip>

I'm thinking I need to catch this exception in a loop a few times at
service
startup for a long enough time for SQL Server to completely spin up.
Anybody else have other ideas? The service is written in C#.

-- Alan









Thanks for the follow up.
Click to expand...
Click to expand...
Click to expand...

-- Alan
 
Mike Mazar said:
I talked to Microsoft technical support and apparently, it's not a good idea
to use "Service Recovery" feature of Windows 2000/XP. Here is what I did
after getting ideas from them:
- In OnStart event, create a new thread to initialize the service. This way
the "Service Start" will be successful.
- If it fails (for example, because SQL server is not ready yet), use
Thread.Sleep to wait for some time and then try to initialize the service
again.
- You can choose the number of retries and delay between each.
- You have the option to stop your service after certain number of retries.
- You may want to log each retry in the event log, along with how many times
you will retry, and delay between each retry.
Click to expand...

Thanks for the followup.

-- Alan
 
Back
Top