How to Synchronize Windows Service Access to SQL Server Database on Startup?

Question

I have a WCF service that is running as a windows service. As soon as the WCF service starts it will try to connect to the DB and do some initialization processing.

I'm having an issue when I restart my machine (the wcf service is set to start automatically), the service would get an exception as too sql is busy. So it seems like the sql is trying to start and at the same time my service is trying to communicate with it.

For now, I did something like try/catch and I sleep for like 10 sec in my catch and I retry again until my initialization is successful.

This works pretty well. But I'm looking for a more elegant solution.

I don't want to set a dependency on sql on my windows service as the service can be installed on a different machine that the DB.

Answer 1

You might try configuring the "Recovery" options of your service when you deploy it to have it restart itself when it fails to load. You can do this via the SC.EXE command-line tool, WMI, or manually through the service properties (services.msc MMC).

Trying [n] times, with some timespan between retries, before ultimately failing if still unable to connect is not a bad solution if you consider monitoring and alerting.

The service actually failing to start will write warning/error events to the Windows event log, potentially triggering monitoring and alerting behavior that you might not want if its just waiting on a dependency to be available, and it will eventually recover.

HTH,

Z