Wednesday, October 16, 2013

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; 



5 comments:

Eric Farrar said...

Step 3b (work-around for failed usage server check) should now be a thing of the past!

We have refreshed the software available on the download site so that it not longer performs this check. If you are running into the problem Breck describes, you can either use his work-around, or download the updated software (recommended).

Karim Khamis said...

Step 4b: Create The Additional 4 Servers using the Cloud Console
- Connect to the cloud using the cloud console
- Click on Servers
- Click on Create
- Choose the host and set the count to 4
- Click on finish to have all 4 servers created for you

Karim Khamis said...

Step 6:
It should be noted that in this example, we let the cloud decide which server the database should be added to. Additional parameters on the dbcloudadd executable allow the user to use a search query or explicit set of servers to inform the cloud exactly where a database should be added. This is important for large clouds that span across the world. In such a case you may have geographical or other restrictions that require the database to be placed on one of a specific set of servers.

Karim Khamis said...

Step 10:
It should be noted that the host is simply a host in the cloud and not necessarily the host where the database currently resides. The connection request simply needs to make it to one of the servers running in the cloud. After that, the cloud software will automatically redirect the connection request to a server running the database. This is important in the case of large clouds where the cloud administrator may decide to move databases around due to size, usage patterns, etc. The client need never know that the database is no longer on the same server or host it was the last time a connection was established.

Karim Khamis said...

Step 10:
One final note, one of the key features of the most recent on-demand release is the addition of autostartable and autostoppable databases. You can use dbcloudadd or the cloud console to indicate that a particular database is used infrequently and should be marked as autostartable. When you do so, that database is left stopped in the cloud and the database will automatically be started when a connection for that database comes in. The database will then be autostopped after the last connection to the database has dropped. You can even configure how long the cloud waits after the last connection before stopping the database. This autostart feature works even when the cloud needs to redirect the connection.