Proving On-Demand

Question: What are the steps required to set up a proof-of-concept to implement 5 servers and 30 databases using the SQL Anywhere 16 On-Demand Edition? The databases will be created as numbered copies of a template: tenant1.db, tenant2.db, tenant3.db. It's OK to use a single computer as a host for everything for the time being.

Tip: Choosing a naming convention for databases is a big deal when you're dealing with hundreds or thousands of them. There's a lot to be said for a simple numbering system, something administrators of large MobiLink synchronization setups have discovered over the years: A store number makes a great MobiLink database identifier, better than a "store name" ...and anything's better than a GUID. Consider this: The On-Demand software itself forces a simple 1-2-3 numbering system on you for SQL Anywhere server names, so think twice before insisting on some kind of "meaningful" or "corporate standard" naming convention for the databases. Another tip: If you want to hide the fact that you're starting out small, don't use 1 for your first tenant database; for example, if your street address is 129 Main Street then use 129 as your first database number :)

Step 1: Create The ONDEMAND1 Environment Variable.

When writing command files to run On-Demand executables, it's easier to code the path as "%ONDEMAND1%" than "C:\Program Files\SQL Anywhere OnDemand 1.0\sa-win64-x64-16.0.0.1535-1.0.0.4613".

Here's how to set that up:

Control Panel - System - Advanced System Settings - Environment Variables... - System variables - New... Variable name: ONDEMAND1 Variable value: C:\Program Files\SQL Anywhere OnDemand 1.0\sa-win64-x64-16.0.0.1535-1.0.0.4613

Step 2: Stop The Cloud If Necessary.

This article assumes you're starting from scratch, and that means if you've got the On-Demand software already running you have to shut it down. The quickest, most thorough way to do that is to use the Cloud Console itself: Overview - Stop the cloud.

Step 3: (Re)Initialize The Cloud.

The command files in this article are numbered to match the steps, and they're shown in boxes like this:

03_dbcloudinit.bat

"%ONDEMAND1%\Bin64\dbcloudinit.exe" PAUSE

The dbcloudinit.exe utility will prompt you for permission to re-create everything from scratch, then it will prompt you to fill in values like this:

Cloud name: MyCloud Full name: Breck User ID: admin Password: sql TCP/IP: 2638 HTTP: 12345 HTTPS: 443 Encryption key: 6mFu3Cfi9ej Secured feature key: j6ogbIrueOgi9 Certificate password: yj487Gjr4uE

Part of the cloud initialization process is to contact the SAP Usage Server, but that part might not work. If it doesn't, and you see these error messages, try the workaround in the next step:

Proxy Settings Unable to contact Usage Server. If there are Internet Proxy settings, enter the host and port below: Proxy Host Proxy Port OK Error Unable to contact the SAP Sybase SQL Anywhere, on-demand Usage Server: https://saode.sybase.com Verify that you have internet connectivity and that your firewall is configured and working properly OK

Step 3b: (Re)Initialize The Cloud, Alternative Method

The following version of the dbcloudinit command doesn't bother contacting the SAP Usage Server; at some point you'll have to fix the problem but this is a proof-of-concept, not a roll-out:

03b_dbcloudinit-no-usage-server-check.bat

"%ONDEMAND1%\Bin64\dbcloudinit.exe" -no-usage-server-check PAUSE

Same as before, you will be prompted to fill in values like this:

Cloud name: MyCloud Full name: Breck User ID: admin Password: sql TCP/IP: 2638 HTTP: 12345 HTTPS: 443 Encryption key: 6mFu3Cfi9ej Secured feature key: j6ogbIrueOgi9 Certificate password: yj487Gjr4uE

Step 4: Create The Additional 4 Servers.

The first On-Demand server is created as part the cloud initialization in the previous step. On-Demand servers are all named after the hosts they're on; in the case of this article the host name is ENVY so the first server is Envy#1.

Here's a command file to create 4 additional servers:

04_dbcloudcmd_CreateServer_4_servers.bat

"%ONDEMAND1%\Bin64\dbcloudcmd.exe" -cc "HOST=ENVY; UID=admin; PWD=sql"^ run CreateServer host=Envy "%ONDEMAND1%\Bin64\dbcloudcmd.exe" -cc "HOST=ENVY; UID=admin; PWD=sql"^ run CreateServer host=Envy "%ONDEMAND1%\Bin64\dbcloudcmd.exe" -cc "HOST=ENVY; UID=admin; PWD=sql"^ run CreateServer host=Envy "%ONDEMAND1%\Bin64\dbcloudcmd.exe" -cc "HOST=ENVY; UID=admin; PWD=sql"^ run CreateServer host=Envy PAUSE

All four of the above commands are identical; each one creates "one more server" on host=Envy with the server names being generated automatically Envy#2 through Envy#5. It's probably a good thing you don't get to pick the server names; with hundreds and perhaps thousands of database names to pick you don't need the extra work.

The dbcloudcmd.exe utility is a general-purpose command processor, a kind of "ISQL-for-On-Demand":

• The -cc option specifies the connection string for the cloud itself, which is separate and different from any particular database connection string.
  

• The run keyword is one of several cloud command verbs; others include describe, download, poll, queue and wait.
  

• The CreateServer task is one of many cloud tasks that can be executed by the run verb. There are a veritable sackload of tasks you can run; think of them as SQL-for-On-Demand.
  

• The host=Envy is one of the parameters for the CreateServer task. Each task has its own set, so expect to spend some time in the Help reading about task syntax.
  

Step 5: Create and initialize a template tenant database.

In some On-Demand setups each tenant database will be a hand-crafted affair, constructed and loaded by separate custom processes. In other setups each new tenant database will be initialized from a common template and will assume an independent "data personality" after it goes live.

This article assumes the latter: each new tenant database starts life being the same; the files are separate and the database names are different, but the data's identical.

Here is a skeleton script for creating a template for new tenant databases; it's a skeleton because it doesn't contain any application-specific SQL statements, it just creates an empty SQL Anywhere 16 database:

05_create_template_database.bat

"%SQLANY16%\Bin64\dbinit.exe"^ tenant.db "%SQLANY16%\Bin64\dbspawn.exe"^ -f "%SQLANY16%\Bin64\dbsrv16.exe"^ -o dbsrv16_log_tenant.txt^ tenant.db "%SQLANY16%\Bin64\dbstop.exe"^ -c "HOST=Envy; DBN=tenant; UID=dba; PWD=sql"^ -y^ tenant PAUSE

The dbinit utility creates the database file, the dbsrv16 command starts the database to force a transaction log to be created, and the dbstop utility does a clean stop of the database to make tenant.db and tenant.log available for copying.

Note: The %SQLANY16% copy of SQL Anywhere 16 must be used for tasks outside the context of the On-Demand software. An attempt to use the %ONDEMAND1% copy (say, in running dbsrv16 command above) will result in the error "No cloud administration database is running":

Step 6: Create 30 tenant databases and add them to the cloud.

A new tenant database must be created outside the On-Demand environment and then "added" to the cloud using the dbcloudadd.exe utility.

The following Windows command file performs those tasks; it accepts a single parameter (1, 2, 3, ...) to identify the database to be created (tenant1, tenant2, tenant3, ...):

06b_dbcloudadd_one_empty_tenant_db.bat

COPY tenant.db tenant%1.db COPY tenant.log tenant%1.log "%SQLANY16%\Bin64\dblog.exe"^ -t tenant%1.log^ tenant%1.db "%ONDEMAND1%\Bin64\dbcloudadd.exe" -cc "HOST=ENVY; UID=admin; PWD=sql"^ -c "UID=dba; PWD=sql; DBN=tenant%1"^ tenant%1.db ERASE /F tenant%1.db ERASE /F tenant%1.log

Here's what a "CALL 06b_dbcloudadd_one_empty_tenant_db.bat 3" command does:

• The COPY commands create a local copy of the tenant.db and .log files as tenant3.db and tenant3.log.
  

• The dblog.exe utility modifies the tenant3.db file to refer to tenant3.log instead of tenant.log.
  

• The dbcloudadd.exe utility connects to the On-Demand software using the -cc connection string,
  

• then uses the -c connection string to connect to the tenant3 database
  

• and specifies database file as tenant3.db.
  

• The ERASE commands delete the local files tenant3.db and tenant3.log because they're no longer needed.
  

The Windows FOR command makes it possible to automate 30 (or 100, or 1000) calls to 06b_dbcloudadd_one_empty_tenant_db.bat with a single command:

06_dbcloudadd_30_empty_tenant_dbs.bat

REM Repeat %%a = 1 step 1 to 30 DO ... FOR /L %%a IN (1,1,30) DO CALL 06b_dbcloudadd_one_empty_tenant_db.bat %%a PAUSE

When that FOR loop is finished, there are 30 tenant databases up and running in the On-Demand cloud... being able to use the FOR command in this manner is a beneficial result of the decision to number the tenant databases 1, 2, 3.

Step 7: Restart the Cloud Agent and Cloud Console.

In theory, it shouldn't be necessary to restart the On-Demand setup after a reboot, it should happen automatically. In practice (and "practice" is what a proof-of-concept is all about) manual intervention may be required. Here's how to restart the Cloud Agent and the first On-Demand server Envy#1 and then launch the Cloud Console in a browser window:

07_start Cloud Agent and Console.bat

"%ONDEMAND1%\Bin64\dbsvc.exe" -x dbcloudagent1.0.0.4613 "%ONDEMAND1%\Bin64\dbsvc.exe" -u dbcloudagent1.0.0.4613 PAUSE Launch the Cloud Console (it may not respond right away)... START "" "https://localhost:443" PAUSE

Step 8: Restart the 4 additional servers.

Here's a script to restart the On-Demand servers Envy#2 through Envy#5 using the Windows FOR command to automate the process:

08_dbcloudcmd_StartServer_4_servers.bat

REM Repeat %%a = 2 step 1 to 5 DO ... FOR /L %%a IN (2,1,5) DO "%ONDEMAND1%\Bin64\dbcloudcmd.exe"^ -cc "HOST=ENVY; UID=admin; PWD=sql"^ run StartServer Envy#%%a PAUSE

Step 9: Restart the 30 tenant databases.

If the 30 tenant database don't start along with the servers, here's a FOR command to get them all going:

09_dbcloudcmd_StartDatabase_30_databases.bat

REM Repeat %%a = 1 step 1 to 30 DO ... FOR /L %%a IN (1,1,30) DO "%ONDEMAND1%\Bin64\dbcloudcmd.exe"^ -cc "HOST=ENVY; UID=admin; PWD=sql"^ run StartDatabase tenant%%a PAUSE

Step 10: Connect to the 30 tenant databases.

When it comes time for client applications to connect to the tenant databases, the trick with On-Demand is to specify the host, database and user id but not the server name.

The next version of the Foxhound Database Monitor is one such client application; you can start 30 separate monitor sessions by providing a text file containing 30 session names each separated by a tab from the corresponding connection string:

tenant1 HOST=Envy; DBN=tenant1; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant2 HOST=Envy; DBN=tenant2; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant3 HOST=Envy; DBN=tenant3; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant4 HOST=Envy; DBN=tenant4; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant5 HOST=Envy; DBN=tenant5; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant6 HOST=Envy; DBN=tenant6; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant7 HOST=Envy; DBN=tenant7; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant8 HOST=Envy; DBN=tenant8; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant9 HOST=Envy; DBN=tenant9; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant10 HOST=Envy; DBN=tenant10; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant11 HOST=Envy; DBN=tenant11; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant12 HOST=Envy; DBN=tenant12; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant13 HOST=Envy; DBN=tenant13; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant14 HOST=Envy; DBN=tenant14; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant15 HOST=Envy; DBN=tenant15; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant16 HOST=Envy; DBN=tenant16; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant17 HOST=Envy; DBN=tenant17; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant18 HOST=Envy; DBN=tenant18; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant19 HOST=Envy; DBN=tenant19; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant20 HOST=Envy; DBN=tenant20; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant21 HOST=Envy; DBN=tenant21; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant22 HOST=Envy; DBN=tenant22; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant23 HOST=Envy; DBN=tenant23; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant24 HOST=Envy; DBN=tenant24; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant25 HOST=Envy; DBN=tenant25; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant26 HOST=Envy; DBN=tenant26; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant27 HOST=Envy; DBN=tenant27; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant28 HOST=Envy; DBN=tenant28; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant29 HOST=Envy; DBN=tenant29; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; tenant30 HOST=Envy; DBN=tenant30; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16;