Using HTTP, SQL Remote sends messages using the hypertext transfer protocol (HTTP) over the Internet. The messages are encoded in a text format and sent via HTTP to the target database. The messages are sent and received using a SQL Anywhere database acting as an HTTP server.
For a list of operating systems for which HTTP is supported, see Supported platforms.
Setting up the HTTP message serverYou use the SQL Anywhere database server to act as the HTTP server that transfers SQL Remote messages to and from remote databases. By default, a newly initialized SQL Anywhere database does not have the web services defined to allow it to act as a message server. Three system stored procedures, sr_add_message_server, sr_drop_message_server and sr_update_message_server are defined in newly created SQL Anywhere databases to allow you to define the required database objects so that the database can act as an HTTP Server to transfer SQL Remote messages.
The database must have been initialized with SQL Anywhere 12.0.1 and initialized with a database server with a build number of 3336 or greater. Query the SYS.SYSHISTORY system table to determine which version and build of the database server were used to initialize the database. If the database was initialized with 12.0.1 and a build number less than 3336, update the database by executing "ALTER DATABASE UPGRADE PROCEDURE ON".
You need to decide whether you want to run a separate database server, or whether you want to use the existing consolidated database as your message server. Consider the following when making this decision:
When a remote database authenticates with the message server, it uses the publisher of the remote database and the provided password to authenticate. While the user exists in the consolidated database, it may not have a defined password (the remote user may not have connect permissions), which is a requirement of the HTTP message system. If granting CONNECT permissions to the remote users in the consolidated database is a security concern, set up a separate database to act as your message server.
If the consolidated database is heavily loaded, adding message server functionality to it may overwhelm the resources on the computer when the consolidated database runs.
To set up the required database objects for the database to act as a message server, call the sr_add_message_server stored procedure, which queries the SQL Remote definitions in the database. See sr_add_message_server system procedure.
If you are creating the message server as a separate database, you need to define a second database with SQL Remote definitions matching those of the consolidated database. Use the dbunload utility to create a copy of the consolidated database and specify the -n switch to unload only the schema of the consolidated database, not the data:
dbunload -n -an -c "ENG=cons.DBN=cons;UID=DBA;PWD=sql" |
If you are using a separate database as the message server, when changes are made to the SQL Remote definitions in the consolidated database, corresponding changes must also be made in the message server database.
To set up the message server, the directory where SQL Remote messages are stored must be accessible to the database server. To define the directory where messages are stored, use the SET REMOTE OPTION command and set the root_directory HTTP message parameter to the directory under which SQL Remote messages are stored. Next, choose the database user that will own the new objects that will be created, and ensure that the user is a group. Finally, execute the sr_add_message_server stored procedure, and pass in the name of the user that will own the objects. See SET REMOTE OPTION statement [SQL Remote] and sr_add_message_server system procedure.
Whenever changes are made to the SQL Remote definition of the message server (such as adding or removing remote users), run the sr_update_message_system stored procedure to update the definition of the objects required to support the message server. The message server will be unavailable for replication for a short period of time while the stored procedure runs and objects are dropped and recreated. See sr_update_message_server system procedure.
If you are no longer using the database as a message server, you can run the sr_drop_message_system stored procedure to remove the objects that were created to support the message server. See sr_drop_message_server system procedure.
Starting the message server database serverAfter the objects required to support the message server are created, when you start the message server database server you need to enable HTTP (and/or HTTPS) support to the database server using the -xs switch. For more information on using -xs, see -xs dbeng12/dbsrv12 server option. The HTTP server-side protocol options of interest to those who have defined the objects needed for the message server:
ServerPort | PORT Specifies the port number that the database server uses to listen for HTTP or HTTPS requests in case the default ports of 80 and 443 are already being used on the computer. See ServerPort (PORT) protocol option.
MaxRequestSize | MAXSIZE Specifies the maximum size of a single HTTP request. The default value is 100 KB. If you have defined your SQL Remote messages size (-l option on the dbremote command line) to be greater than 100 KB, you also need to increase the size of the largest HTTP request that the database server can accept. The default SQL Remote message size is 50 KB. See MaxRequestSize (MAXSIZE) protocol option.
Identity (HTTPS only) When you use HTTPS, the identity file contains the public certificate and its private key, and for certificates that are not self-signed, the identity file also contains all of the signing certificates, which include, among other things, the encryption certificate. The password for this certificate must be specified with the Identity_Password parameter. See Identity protocol option.
Identity_Password (HTTPS only) When you use transport-layer security, this option specifies the password that matches the password for the encryption certificate specified by the Identity protocol option. See Identity_Password protocol option.
HTTP addresses and user IDsTo use SQL Remote and HTTP, each database participating in the system requires an HTTP address, a user ID and a password. These are distinct identifiers: the HTTP address is the destination of each message, and the user ID and password are the name and password entered by a user when they authenticate against the server.
HTTP message control parametersBefore the SQL Remote Message Agent (dbremote) connects to the message system to send or receive messages, the user must have a set of control parameters already set on their computer, or the user is prompted to specify needed information. This information is needed only on the first connection. It is saved and used as the default for subsequent connections.
The HTTP message system uses the following control parameters that are set using the SET REMOTE OPTION statement:
certificate To make a secure (HTTPS) request, a client must have access to the certificate used by the HTTPS server. The necessary information is specified in a string of semicolon-separated key/value pairs. You can use the file key to specify the file name of the certificate. You cannot specify a file and certificate key together. The following keys are available:
| Key | Abbreviation | Description |
|---|---|---|
| file | The file name of the certificate | |
| certificate | cert | The certificate itself |
| company | co | The company specified in the certificate |
| unit | The company unit specified in the certificate | |
| name | The common name specified in the certificate |
Certificates are required only for requests that are either directed to an HTTPS server, or can be redirected from a non-secure to a secure server. Only PEM formatted certificates are supported. certificate='file=filename'
client_port Identifies the port number on which SQL Remote communicates using HTTP. It is provided for, and recommended only for, connections through firewalls that filter "outgoing" TCP/IP connections. You can specify a single port number, ranges of port numbers, or a combination of the two. Specifying a low number of client ports could result in SQL Remote being unable to send and receive messages if the operating system has not released the ports in a timely manner after SQL Remote closes the port on a previous run. client_port=nnnnn[-mmmmm]'
debug When set to YES, all HTTP commands and responses are displayed in the output log. This information can be used for troubleshooting HTTP support problems. The default is NO.
https Specify whether to use HTTPS (https=yes) or HTTP (https=no).
password The message server database password. Authenticates to third-party HTTP servers and gateways using RFC 2617 Basic authentication. password='password'
proxy_host Specifies the URI of a proxy server. For use when SQL Remote must access the network through a proxy server. Indicates that SQL Remote is to connect to the proxy server and send the request to the message server through it.proxy_host=' http://proxy-server[:port-number]'
reconnect_retries The number of times the link should try to open a socket with the server before failing. The default value is 4. When you set this parameter, only reconnections are affected. The initial connection made by the FTP link is not affected.
reconnect_pause The time in seconds to pause between each connection attempt. The default setting is 30 seconds. When you set this parameter, only reconnections are affected. The initial connection made by the FTP link is not affected.
root_directory This HTTP control parameter is ignored when specified at the client side. You define this control parameter in the message server prior to calling the sr_add_message_server or sr_update_message_server stored procedure. Specify the directory accessible by the message server under which the SQL Remote messages are stored. Note that when using the HTTP message system, the address specified for a remote user or publisher can only contain a single subdirectory, and not multiple subdirectories. root_directory='c:\msgs'
url Specify the server name or IP address and, optionally, the port number of the HTTP server being used, separated by a semicolon. If requests are being passed through the Relay Server, you can optionally add a URL extension to indicate which server farm the request should be passed to. url ='server-name[:port-number][url-extension]'
user The message server database user ID. Authenticates to third-party HTTP servers and gateways using RFC 2617 Basic authentication. user='userid'
See also |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |