Skip to Content
SAP SQL Anywhere

SQL Anywhere, on-demand edition Making Client Connections

Tags:

Module 3 - Making Client Connections

In this module, you will walk through how to establish client-side connections using dbping with databases that are running in the Sap Sybase SQL Anywhere, on-demand edition Cloud.

Pre-requisites

It is expected that you have completed Modules 1 and 2 and have some knowledge of how to create a SQL Anywhere, on-demand edition cloud.

Refer to Module 1 for information on installing and starting a cloud, and Module 2 for information on adding databases to the cloud.

This module requires that you have the SQL Anywhere Developer Edition installed.

Background

A SQL Anywhere, on-demand edition cloud is made up of many separate cloud servers all working together. From the outside, the cloud is intended to look as though it is one server. Client applications and application servers must be able to connect to databases inside the cloud without any knowledge of its underlying topology. This is accomplished by automatic connection redirection.

Setting up the Cloud

  1. Using your knowledge from the previous modules, set up a cloud with at least two cloud servers on different hosts.
  2. Start one cloud server on each host. Refer to Module 1 for more information on starting cloud servers on hosts.
  3. Add two databases to the cloud. The cloud should add each database to a separate cloud server based on the load on each cloud server. On the Databases tab for each individual server, verify that a database has been added to each server.
  4. Click on the name of the cloud in the left hand navigation pane, and then click Databases.The two databases you added should be running.

For this particular demonstration, the two databases (demo and sales), running on three servers (MY-CLOUD-1#1, MY-CLOUD-2#1, MY-CLOUD-3#1).The demo database is running on MY-CLOUD-3#1, and the sales database is running on MY-CLOUD-2#1.

Connection Attempt Scenarios

This section contains six scenarios illustrating attempts to connect to databases running in a cloud. All of these examples use the dbping utility that is included as part of the cloud tools. Client applications can also use ODBC, JDBC, ADO.NET, among other APIs, to make connections to the databases using the same or similar connection strings.

By default, dbping will not attempt to connect to the specified database. In order to force dbping to attempt a connection, the –d switch must be used. The general format for dbping for all of these examples is:

dbping –d –c “<connection string>”

Note that SAP Sybase SQL Anywhere, on-demand edition defaults to port 2638 for client connections if no port is specified. Therefore, in the following examples, host=mycloud-1 is equivalent to host=mycloud-1:2638. Unless the default ports were changed during cloud installation, the first cloud server started on each host will default to port 2638.

Scenario 1: Attempting to Connect with Host Name Only

Example Connection Info

ParameterValue
hostmycloud-1

Example Connection String Syntax

host=mycloud-1

Result

Failure. Database name must be specified when connecting to a cloud server.

Explanation

This connection failed because no database was specified. Because there are potentially many databases running inside the cloud, the database name must always be specified (there is no default). Note that if the –d switch had not been specified, this command would have succeeded because dbping would have returned after locating a server, but before attempting to locate the database.

Scenario 2: Connecting to a Named Database on a Target Host

Example Connection Info

ParameterValue
hostmycloud-3
dbndemo
uiddba
pwdsql

Example Connection String Syntax

host=mycloud-3;dbn=demo;uid=dba;pwd=sql

Result

Success. Connected to demo on MYCLOUD-3#1


Explanation

This connection was successful because the target database, demo, was found running in the cloud. In this particular case the target database happened to running on a server on the same host, MYCLOUD-3, that was the target of the dbping.

Scenario 3: Connecting to a Named Database on a Non-Target Host

Example Connection Info

ParameterValue
hostmycloud-1
dbnsales
uiddba
pwdsql

Example Connection String Syntax

host=mycloud-1;dbn=sales;uid=dba;pwd=sql

Result

Success. Connected to sales on MYCLOUD-2#1

Explanation

This connection was successful because the specified database, sales, was found running in the cloud. In this particular case the specified database was running on a server on host MYCLOUD-2, which was different than the target host specified to dbping (MYCLOUD-1). This is the expected behavior in the cloud. Each server in the cloud has full knowledge of the location of all tenant databases. Connections can come in through any server and host in the cloud, and they will be automatically and transparently rerouted to the actual host that is running the specified database.

This automatic redirection is necessary to be able to achieve the black box nature of the cloud. It allows clients to connect without needing to know which host and server their database is running on.

Scenario 4: Attempting to Connect to a Named Database by Server Name

Example Connection Info

ParameterValue
hostmycloud-2
dbnsales
uiddba
pwdsql
servermycloud-2#1

Example Connection String Syntax

host=mycloud-2;dbn=sales;server=mycloud-2#1;uid=dba;pwd=sql”

Result

Failure. Server name can only be specified when connecting to a cloud server with NODETYPE=DIRECT.

Explanation

The NodeType parameter instructs the system on how it wants its connection redirected. If this parameter is not specified (as in the scenarios up to this point), it defaults to PRIMARY when connected to cloud servers. A NodeType of PRIMARY instructs the system to transparently redirect the connection to the cloud server that is running the primary copy of the specified database. This worked successfully in scenarios 2 and 3.

In this scenario, a target server has also been specified in the connection string. When both a server and a database name are specified, it becomes ambiguous as to where exactly the client is trying to connect. It is not clear if the client is trying to connect to the target database anywhere in the cloud, or to the database on that specific server. Because this case is ambiguous, the cloud will not let you connect to a database if you specify both a server name, and a NodeType of PRIMARY (the default).

The supported values for NodeType are PRIMARY, DIRECT, MIRROR, READONLY, and COPY. MIRROR redirects the connection to the tenant mirror database, COPY redirects the connection to a read-only copy node of the database, and READONLY redirects the connection to any read-only database copy. READONLY is equivalent to the MIRROR NodeType if there are no read-only copy nodes and is equivalent to the COPY NodeType if there are only read-only copy nodes.

Scenario 5: Connecting to a Named Database by Server Name and Node Type

Example Connection Info

ParameterValue
hostmycloud-2
dbnsales
uiddba
pwdsql
server

mycloud-2#1

nodetypedirect

Example Connection String Syntax

host=mycloud-2;dbn=sales;server=mycloud-2#1;uid=dba;pwd=sql;nodetype=direct

Result

Success. Connected to sales on MYCLOUD-2#1

Explanation

There are times when it is useful to connect to a database on a specific server without redirection. Setting the NodeType to DIRECT will disable the automatic connection redirection, and will only connect to the database if it is available on the specified server. While this capability is provided, it is recommended that you do not set the NodeType to DIRECT. Following this recommendation allows the database to be moved to a different cloud server or host without affecting the connection to your application.

In this case, because the sales database is running on the server MYCLOUD-2#1, the connection is successful.

Scenario 6: Attempting to Connect to a Named Database by Server Name and Node Type on Non-Target Machine

Example Connection Info

ParameterValue
hostmycloud-1
dbnsales
uiddba
pwdsql
server

mycloud-1#1

nodetypedirect

Example Connection String Syntax

host=mycloud-1;dbn=sales;server=mycloud-1#1;uid=dba;pwd=sql;nodetype=direct

Result

Failure. Specified database not found.


Explanation

In the last example, the client is attempting to make a direct connection to MYCLOUD-1#1 and connect to the sales database. In this case, the NodeType is DIRECT, which means there will be no automatic connection redirection. Because the sales database is not running on MYCLOUD-1#1, the connection fails (even though the database is available elsewhere in the cloud).

Using Multiple Hosts Addresses

For practical use, it is recommended that host addresses for the arbiter and both cloud partners be used in the connection string. This will allow the connection to be made even if one of the hosts is down.

See the documentation at http://dcx.sybase.com/index.html#cloud100/en/dbcloud/connecting-cloud-databases.html for more information. Below is an example connection string that specifies multiple host addresses.

host=cloud-arbiter-host,cloud-partner1-host,cloud-partner2-host;dbn=tenant2;uid=dba;pwd=sql

No comments