Autostarting SQL Anywhere Databases and Servers

Purpose

The purpose of this wiki is to explain how auto-starting databases and database servers works within SQL Anywhere.

Overview

When connecting to many database systems, a common connection paradigm is that you must first start a database server and a database before a client is able to connect to the database. This is a typical connection scenario for SQL Anywhere as well. However, SQL Anywhere also supports a concept called autostarting, which means that if enough information is given by the client application, the database and/or database server can be started dynamically by the application if they cannot be found. This allows client applications to start up and run even if the database server has not been started previously. In addition, once the application that autostarted the database and/or server has disconnected the database and/or database server can automatically shut down. In the cases where the database server also shuts down all times (thereby using up system resources), this can ease administration, yet applications can use the database as they need to.

The autostart feature is only available to applications that use any of the following client application programming interfaces (APIs) to connect to SQL Anywhere database servers:

• ESQL
• ODBC
• ADO.NET
• OLE DB
• JDBC (using the SQL Anywhere JDBC driver)

Connections to SQL Anywhere servers that use Sybase Open Client or jConnect use a different protocol (Tabular Data Stream (TDS)) and connection mechanism that does not allow servers to be autostarted. This wiki does not apply to those types of connections.

Connecting to Databases

Connecting to a SQL Anywhere database is a two-step process. The SQL Anywhere client must first make a connection to a database server. In SQL Anywhere 12 and lower, the client uses the CommLinks (LINKS) and EngineName (ENG) parameters to determine which server to search for and how to search for it. In SQL Anywhere 16, the ServerName (SERVER) parameter should be used instead of EngineName. Once the server-level connection is made, the client tells the server which database it needs (typically by using the DatabaseName (DBN) connection parameter), which user ID to use, and the password for the requested user ID. If the database is available and the authentication succeeds, the server completes the database connection. The client application is then free to start executing SQL statements on the database. If either the requested database server or database is not running, the client may attempt to autostart them.

There are two types of autostarting corresponding to the two steps of the connection process: autostarting a database server, and autostarting a database. One significant difference between these two is that in the case of autostarting a database server, the client software does the work, while in the case of autostarting a database, the server software does the work and is a transparent process to the client. Each type of autostarting is described below.

Note that when a database server is autostarted, the appropriate database is also autostarted on that server. For simplicity, “autostarting a server” in this paper will actually mean autostarting a server and then a database.

It is sometimes useful to be able to connect to a server and execute SQL statements without connecting to a running database. SQL Anywhere servers can run a utility database, which is a virtual database that is always available when the database server starts. The utility database is usually autostarted; however, the rules and permissions are slightly different than for real databases.

For information about using the utility database, see “The Utility Database” in the documentation.

Autostarting Servers

If a connection attempt fails because the server could not be found, the client examines the supplied connection parameters to determine whether it is possible to autostart a server; that is, to start the server executable (referred to as spawning a server) on the appropriate database, and then connect to it. To autostart a server, the database file must be located on the same physical computer as the client application. See “Using Remote Files”. In addition, a number of assumptions are made by the client library:

• If you are trying to connect to a server that may exist on a remote computer (for example, you have specified LINKS with a protocol other than SharedMemory) and no such server is found, it is assumed that you do not want to autostart a server.
• If you do not supply the filename of the database file (using the DBF parameter), it is assumed that you do not want to autostart a server.

These assumptions imply the following conditions for autostarting a server. The client library will only attempt to autostart a server if all of the following are true:

1. The Autostart (ASTART) parameter is not set to NO (the default is YES).

2. The LINKS parameter is either set to SharedMemory, or is not set at all.

3. The StartLine (START) and/or DatabaseFile (DBF) connection parameter is set.

When the client library attempts to autostart a server, it creates a server start command. If the START parameter is specified, the library starts with that; otherwise, the command is the name of the personal server executable. For example, on Windows platforms, a 16.0 client chooses dbeng16.exe as the default start line. Other parameters are added to the start line depending on the other connection parameters (Autostop, DatabaseKey, DatabaseFile, and so on). See “Appendix 1” for further descriptions of the connection parameters.

The mapping from connection parameter to server command line is done according to the following chart:

For example, suppose the client library is given the connection string fragment:

...;ENG=sales;DBF=Sales.db;DBN=q1;DBKEY=EncKey;DBS=-r;...

If no server named sales is found, the client library attempts to spawn the following command:

dbeng16 -ga -n sales Sales.db -ek EncKey -r -n q1

Once the server is started, the client library waits until the server is ready to accept connections, and then attempts to connect again.

The START parameter can be used to specify a different executable name, or add additional server options. For example, say your application makes numerous connections to the local server, and the 10-connection limit of the personal server does not allow this. Since the network server has no such limit, you may want to spawn a network server rather than a personal server. However, suppose you also don’t want clients on remote computers to be able to connect to this server once you have spawned it. To do this, you can tell the client library to autostart a network server and specify the -x none option (to disable TCP/IP) using the following connection string fragment (note that the entire connection string must be in quotes because of the spaces):

“...;START=dbsrv16 -x none;...”

Autostarting Databases

If the client finds a server, but the database it is looking for is not running, the server checks to see if the client has provided (through the connection string) information on how to start the database (see “Connection Parameters”). If so, and if autostarting is allowed, the server attempts to start the database. Once the database is running, the server completes the connection request just as if the database were running all along.

By default, a database that is autostarted is put into autostop mode, which means that once the last connection to that database is closed, the database automatically shuts itself down. Autostop mode can be disabled by specifying ASTOP=NO as part of your connection string.

Connection Parameters

To autostart a database, the server needs to know at least one piece of information: the database file name. This is given to the server through the DBF connection parameter. It is important to note that the filename must be relative to the current directory of the server. This has two implications:

1. If your application’s current directory contains a file called sales.db, but the server that is running has a different current directory, you cannot simply specify DBF=sales.db as part of your connection string. If you do, the server will likely report a “Specified database not found” error, or even worse, attempt to start the wrong database.
2. If the server you are connected to is running on a different computer, it will not be able to start databases that are local to the client application. See “Using Remote Files”.

There are four other connection parameters that are useful when autostarting databases:

1. DBKEY – If the database you want to start is strongly encrypted, then you also need to specify the encryption key. This is done by using the DBKEY connection parameter.
2. DBN – By default, the database alias is the same as the database filename without the extension. For example, if the database file is sales.db, then the database name will be sales. To use a different database name, specify the DBN connection parameter.
3. DBS – If you need to specify additional database-specific options (other than -ek and -n — use DBKEY and DBN for those), you can use the DBS connection parameter.
4. ASTOP – If this parameter is set to NO, the database continues running after the last connection closes.

It is important to note that the DBF, DBKEY, DBS, and ASTOP connection parameters are only used if they are needed.
If a database that matches the database name is already running, then the client connects to that database, and no database is autostarted.

Permissions

By default, the personal server (dbeng16) allows autostarting databases, while the network server (dbsrv16) does not. This means that if you connect to a network server and the database you are looking for is not running, the server will refuse to autostart a database and the client application will be given an error. This can be changed using the -gd option; the personal server defaults to -gd all, while the network server defaults to -gd dba. Note, however, that the -gd option controls the starting of databases, not just autostarting. The server’s default of -gd dba means that a user is only allowed to start a new database (with the START DATABASE statement) if that user is already connected to a database as a user with DBA authority. This does not apply to autostarting, since the user is not connected to a database when it sends the connection string. In a nutshell, -gd all allows autostarting of databases, while any other setting disallows it.

EXAMPLES

In all of the examples below, you want to connect to a server called waterloo and start a database called sales.db. For each example, a connection string fragment is shown that contains the relevant connection parameters. In all of these cases, if a database with a matching database name is already running on the waterloo server, the client will connect to that database and no database will be started.
Example 1: sales.db is located in the current directory of the server.

...;ENG=waterloo;DBF=sales.db;...

Example 2: sales.db is located in the c:\databases subdirectory of the server computer, and is strongly encrypted. The encryption key is abcd.

...;ENG=waterloo;DBF=C:\databases\sales.db;DBKEY=abcd;...

Example 3: sales.db should be started in read-only mode (using the database -r option), and the database name should be set to sales_ro.

...;ENG=waterloo;DBF=sales.db;DBN=sales_ro;DBS=-r;...

The Utility Database

The utility database is a virtual database that is available on all SQL Anywhere servers. This database cannot contain data (since there is no database file in which to store it), but it allows you to execute certain SQL statements on a server if you cannot connect to a real database. To connect to the utility database, you must specify utility_db as the DBN in a connection string.

The utility database cannot be started through use of the START DATABASE SQL statement, nor can it be started on the server start line; the only way to start it is to autostart it. The utility database can be autostarted by specifying DBN=utility_db in your connection string—if the utility database is already running, you will be connected to it; if not, it will be autostarted. You can specify AUTOSTOP=NO if you don’t want the utility database to shut down once there are no more connections. The utility database can then be shut down using dbstop with the -d option or through use of the STOP DATABASE SQL statement, or it will be shut down automatically when the server shuts down.

If there is no server running, you can autostart a server running the utility_db as you would any other database; simply specify DBN=utility_db, and do not specify a DBF. For example:

...;ENG=waterloo;DBN=utility_db;UID=dba;PWD=sql;...

This is the only time that autostarting a server will succeed if neither START nor DBF are specified. If you do not specify the START parameter, the client will spawn a personal server. By default, there is no password for the utility_db on a personal server, so any value for the PWD parameter will work. To set the utility_db password on a personal or network server you can use the server -su option.

You can autostart a network server and specify the utility database password by using the START parameter. For example:

“...;ENG=waterloo;DBN=utility_db;START=dbsrv16 -su secret;UID=DBA;PWD=secret...”

Make sure you use the same password in the PWD field as you gave in the server’s -su option. Also note that the entire connection string must be enclosed in quotes because of the spaces in the START parameter value.

Using Remote Files

Starting a database file located on a remote computer is neither supported nor recommended. However, it is possible for a server to start a database on a remote computer. For example, on Windows, you can use a UNC-style filename, and on Unix you can use a file located on an NFS-mounted volume. Running a remote database file can lead to poor performance, data corruption, and server instability. Database files must be located on the same physical computer as the server, or accessed through a SAN configuration.

Summary

When a SQL Anywhere client attempts to connect to a database, the client software can automatically start a server and database if the desired server is not found. If the server is found, but the database is not running, the server can automatically start the correct database and complete the connection. In both cases, by default the server or database that is automatically started will also shut down automatically once the last connection to it is closed.