How does SQL Anywhere behave differently when running as a service?

[Former Member]

How does SQL Anywhere behave differently when running as a service?

I'm considering writing a service wrapper for dbsrv12.exe to make some changes to its startup and ASSERT behavior, and I would like to know what changes the server makes when running as a service, as opposed to simply being ran from the commandline (other than the obvious service control thread which I'm going to have to simulate, likely with a utility_db connection). Are there certain commandline parameters that should be forced to have it behave properly in a service environment?

[VolkerBarth]

Since you are relating to the ASSERT behaviour: Are you aware that v16 has introduced enhancements in this respect - for example, to distinguish between database-level and server-level assertions (via dbsrv16 -uf action and dbsrv16 -ufd action)? To cite from the docs:

New default behavior for database assertions

In previous releases, database assertion failures were treated as database server assertion failures, causing the database server to shut down or return an error to all client connections. Database assertion failures are now treated separately from database server assertion failures, and cause the database to shut down while the database server continues to run. See -ufd database server option.

[Former Member]

Thank you for highlighting these options, although it looks like neither -uf or -ufd are available to me on ASA12. Current behavior on an ASSERT appears to be "graceful shutdown", which both the Service Control Manager and our custom watchdog process interprets as "intentional administrator shutdown".

Our current ASSERT issue is something like "cross-linked database page" with a randomly-selected running database. Smells like an EBF, although I will be reloading one of our databases tonight.

[VolkerBarth]

Yes, these options are introduced with v16 (or made available on Windows, -uf has been available on Unix platforms since v10).

Just to add some links from Breck's great blog with some rants information on the assertion handling in SQL Anyhwere:

• Assertion Relief
• SQL Anywhere 16 Sneak Peek: Abort, Retry, Escalate

[Former Member]

None of this helps answer the question (which is starting to sound like "try it and see what falls out"), but as long as we're on wishlist items the other main focus this wrapper would do is change startup behavior. Specifically it would move the "Now accepting requests" line before it started attempting to launch any databases. We've had engines with eight databases on them that were unavailable for hours because one of the databases was taking a long time to recover.

Now if you tell me this is also in ASA13 then I might just try to be more patient and put off rewriting the service wrapper...

[Former Member]

As there does not exist a ASA13, it does not exist there. ASA16 is the next version after ASA12, and your logical upgrade path 😉

[jeff_albion]

When we run SQL anywhere as a service, there is a hidden switch (-hv) automatically added to the server's command line by Sybase Central / dbsvc. You can see this information directly if you go and view a service entry in the registry:

\\\\HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\services\\SQLANYe_<servicename>

ImagePath  REG_EXPAND_SZ  dbsrv16.exe -hvSQLANYe_<servicename>

That is how SQL Anywhere maintains service messaging functions to the Windows Service Manager.

---

I'm considering writing a service wrapper for dbsrv12.exe to make some changes to its startup and ASSERT behavior

I wouldn't expect that you would be able to successfully alter this behaviour yourself with just a wrapper script.

When the database server asserts, it doesn't necessarily stop the executable right away so your wrapper script may not get 'control' back in these situations. As others have mentioned, SQL Anywhere 16 has been enhanced to provide additional behaviour options upon assertions (dbsrv16 -uf, -ufd).

If you are seeing multiple assertions in the database server, writing a wrapper script is not the appropriate response to the issue - you should resolve the assertion condition, instead. (Contacting SAP technical support may also be appropriate here).

---

the other main focus this wrapper would do is change startup behavior. Specifically it would move the "Now accepting requests" line before it started attempting to launch any databases. We've had engines with eight databases on them that were unavailable for hours because one of the databases was taking a long time to recover.

By design, all databases started on the command line with a database server must be started before any requests may be accepted to other databases (which will include automatic recovery behaviour).

An alternative to this design would be to either break out the databases into separate database services, or start none of the databases at start-up with the database server and only start the databases as required by the client using the utility_db with the START DATABASE SQL statement or the database auto-start behaviour from a client connection string (i.e. DBF=filename.db and ASTART=YES).

[Former Member]

Yah, I see the (undocumented) -hv parameter, the wrapper script would need to handle the SCM commands itself and relay them to the engine. I'd like to know what cmdline switches I will need when the -hv parameter is dropped; at a minimum I will be adding a -qi.

I also recognize that standard behavior for ASSERTs is to leave the engine running in a nonresponsive state. I remember a really old newsgroup post that indicated a cmdline parameter such as "-qi" would change this behavior. Obviously it is shutting down while running as a service (with a "shutdown requested by user" message).

My approach to ASSERT behavior would be to treat it as an error condition similar to how Windows services handle the situation (action on 1st failure, action on 2nd failure, action on subsequent failures, how long until the clock is reset). The wrapper script will also be sending an email broadcast when an ASSERT occurs; I do not intend to treat it lightly, I do not accept it as fatal. Most of the ASSRTs we run into are engine bugs and we unload all our backups nightly.

As for altering startup behavior, yes the wrapper will need to launch the engine without any databases and then feed the start scripts as soon as it begins accepting connections; the wrapper will be censoring the engine's commandline to accomplish this.

[VolkerBarth]

In order to solve the "any database from the server command line is only available when any other database has been restored":

In case you need to start these databases before (i.e. you don't want to use the client-triggered AUTOSTART feature), a further approach might be to a create a small "admin database" that is the only one that is started on the server's command line, and then use a DatabaseStart event within that database to start all "real" databases. That way, the database server would be immediately available for requests (AFAIK, even before the DatabaseStart event is completed). (Note, I have not tried that myself.)