Service depends on SQL Server

[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.Sql Client.Connecti onPool.GetConne ction(Boolean&
isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne c
tionString options, Boolean& isInTransaction )
at System.Data.Sql Client.SqlConne ction.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

[Yoshi]

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
"Alan Pretre" <al********@new sgroup.nospam> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

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.Sql Client.Connecti onPool.GetConne ction(Boolean&
isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne c
tionString options, Boolean& isInTransaction )
at System.Data.Sql Client.SqlConne ction.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

[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 InitializeCompo nent()
{
....
//
// service
//
this.service.Se rvicesDependedO n = new string[] {"MSSQLSERVER"} ;
this.service.Di splayName = "Xxxxx";
this.service.Se rviceName = "Xxxxx";
this.service.St artType = System.ServiceP rocess.ServiceS tartMode.Automa tic;
....
}

--Richard

"Alan Pretre" wrote:

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.Sql Client.Connecti onPool.GetConne ction(Boolean&
isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne c
tionString options, Boolean& isInTransaction )
at System.Data.Sql Client.SqlConne ction.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

[Yoshi]

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

This looks like it will work.

Nice Richard!
"Richard" <Ri*****@discus sions.microsoft .com> wrote in message
news:49******** *************** ***********@mic rosoft.com...

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 InitializeCompo nent()
{
....
//
// service
//
this.service.Se rvicesDependedO n = new string[] {"MSSQLSERVER"} ;
this.service.Di splayName = "Xxxxx";
this.service.Se rviceName = "Xxxxx";
this.service.St artType =
System.ServiceP rocess.ServiceS tartMode.Automa tic;
....
}

--Richard

"Alan Pretre" wrote:

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.Sql Client.Connecti onPool.GetConne ction(Boolean&
isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne c
tionString options, Boolean& isInTransaction )
at System.Data.Sql Client.SqlConne ction.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

[Mike Mazar]

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" wrote:

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

This looks like it will work.

Nice Richard!
"Richard" <Ri*****@discus sions.microsoft .com> wrote in message
news:49******** *************** ***********@mic rosoft.com...

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 InitializeCompo nent()
{
....
//
// service
//
this.service.Se rvicesDependedO n = new string[] {"MSSQLSERVER"} ;
this.service.Di splayName = "Xxxxx";
this.service.Se rviceName = "Xxxxx";
this.service.St artType =
System.ServiceP rocess.ServiceS tartMode.Automa tic;
....
}

--Richard

"Alan Pretre" wrote:

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.Sql Client.Connecti onPool.GetConne ction(Boolean&
isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne c
tionString options, Boolean& isInTransaction )
at System.Data.Sql Client.SqlConne ction.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

[Alan Pretre]

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" <Mi*******@disc ussions.microso ft.com> wrote in message
news:38******** *************** ***********@mic rosoft.com...

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" wrote:

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

This looks like it will work.

Nice Richard!
"Richard" <Ri*****@discus sions.microsoft .com> wrote in message
news:49******** *************** ***********@mic rosoft.com...

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 InitializeCompo nent()
{
....
//
// service
//
this.service.Se rvicesDependedO n = new string[] {"MSSQLSERVER"} ;
this.service.Di splayName = "Xxxxx";
this.service.Se rviceName = "Xxxxx";
this.service.St artType =
System.ServiceP rocess.ServiceS tartMode.Automa tic;
....
}

--Richard

"Alan Pretre" wrote:

> 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.Sql Client.Connecti onPool.GetConne ction(Boolean&
> isInTransaction )
> at
> System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne c> tionString options, Boolean& isInTransaction )
> at System.Data.Sql Client.SqlConne ction.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
>
>
>
>

[Mike Mazar]

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" wrote:

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" <Mi*******@disc ussions.microso ft.com> wrote in message
news:38******** *************** ***********@mic rosoft.com...

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" wrote:

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

This looks like it will work.

Nice Richard!
"Richard" <Ri*****@discus sions.microsoft .com> wrote in message
news:49******** *************** ***********@mic rosoft.com...
> 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 InitializeCompo nent()
> {
> ....
> //
> // service
> //
> this.service.Se rvicesDependedO n = new string[] {"MSSQLSERVER"} ;
> this.service.Di splayName = "Xxxxx";
> this.service.Se rviceName = "Xxxxx";
> this.service.St artType =
> System.ServiceP rocess.ServiceS tartMode.Automa tic;
> ....
> }
>
> --Richard
>
> "Alan Pretre" wrote:
>
>> 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.Sql Client.Connecti onPool.GetConne ction(Boolean&
>> isInTransaction )
>> at
>> System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne c >> tionString options, Boolean& isInTransaction )
>> at System.Data.Sql Client.SqlConne ction.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
>>
>>
>>
>>

[Mike Mazar]

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" wrote:

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" wrote:

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" <Mi*******@disc ussions.microso ft.com> wrote in message
news:38******** *************** ***********@mic rosoft.com...

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" wrote:

> I would try Richard's solution before doing anything else.
>
> This looks like it will work.
>
> Nice Richard!
>
>
> "Richard" <Ri*****@discus sions.microsoft .com> wrote in message
> news:49******** *************** ***********@mic rosoft.com...
> > 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 InitializeCompo nent()
> > {
> > ....
> > //
> > // service
> > //
> > this.service.Se rvicesDependedO n = new string[] {"MSSQLSERVER"} ;
> > this.service.Di splayName = "Xxxxx";
> > this.service.Se rviceName = "Xxxxx";
> > this.service.St artType =
> > System.ServiceP rocess.ServiceS tartMode.Automa tic;
> > ....
> > }
> >
> > --Richard
> >
> > "Alan Pretre" wrote:
> >
> >> 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.Sql Client.Connecti onPool.GetConne ction(Boolean&
> >> isInTransaction )
> >> at
> >>

System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne c

> >> tionString options, Boolean& isInTransaction )
> >> at System.Data.Sql Client.SqlConne ction.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
> >>
> >>
> >>
> >>
>
>
>

[Alan Pretre]

"Mike Mazar" <Mi*******@disc ussions.microso ft.com> wrote in message
news:AA******** *************** ***********@mic rosoft.com...

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" wrote:

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" wrote:

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" <Mi*******@disc ussions.microso ft.com> wrote in message
news:38******** *************** ***********@mic rosoft.com...
> 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" wrote:
>
> > I would try Richard's solution before doing anything else.
> >
> > This looks like it will work.
> >
> > Nice Richard!
> >
> >
> > "Richard" <Ri*****@discus sions.microsoft .com> wrote in message
> > news:49******** *************** ***********@mic rosoft.com...
> > > 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 InitializeCompo nent()
> > > {
> > > ....
> > > //
> > > // service
> > > //
> > > this.service.Se rvicesDependedO n = new string[] {"MSSQLSERVER"} ;
> > > this.service.Di splayName = "Xxxxx";
> > > this.service.Se rviceName = "Xxxxx";
> > > this.service.St artType =
> > > System.ServiceP rocess.ServiceS tartMode.Automa tic;
> > > ....
> > > }
> > >
> > > --Richard
> > >
> > > "Alan Pretre" wrote:
> > >
> > >> 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.Sql Client.Connecti onPool.GetConne ction(Boolean& > > >> isInTransaction )
> > >> at
> > >>
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne c > > >> tionString options, Boolean& isInTransaction )
> > >> at System.Data.Sql Client.SqlConne ction.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.

-- Alan

[Alan Pretre]

"Mike Mazar" <Mi*******@disc ussions.microso ft.com> wrote in message
news:AA******** *************** ***********@mic rosoft.com...

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.

Thanks for the followup.

-- Alan