MC415 Adaptive Server Anywhere:

1
MC415 Adaptive Server Anywhere Remote
Administration Without Remote Access
Eric Murchie-Beyma Stanley Associates,
Inc. Technical Director, Army Systems emb_at_stanleya
ssociates.com
2
Presenter

• Eric Murchie-Beyma
• Technical Director, Army systems, Stanley
  Associates
• ASA developer since 1995
• Database developer since 1989
• Stanley Associates, Inc.
• Information technology company, Alexandria VA,
  serving federal and commercial clients, including
  Dept. of State, Dept. of Defense, Dept. of
  Transportation, Dept. of Justice, Treasury
  Department, Dept. of Energy

3
Purpose

• Remote Administration techniques SQL Anywhere
  as PC Anywhere
• Distributed database platform that does not
  presuppose a direct, continuously open connection
  
• Administer remote systems even when your only
  access to the machine is SQL Anywhere replication
• Techniques for automated and broadcast
  administration
• Build a virtual dbisql window to the remote
  database

4
Adaptive Server Anywhere as a Distributed System
Platform

• Not just for mobile embedded
• Full-featured client-server RDBMS
• Server to server replication, geographically
  separate

5
Small-scale Distributed Systems

• Small per-site user base
• Aggregated Data in Gigabytes, not Terabytes
• DBA Resources
• Few DBAs, many databases
• Limited DBA skills in the field
• Communications Infrastructure
• Some form of Internet access
• No guaranteed bandwidth
• Less than reliable service

6
Remote Administration Capabilities

• Data Maintenance
• Remote queries
• Operating System Administration
• Database structure upgrades
• All examples NT, though concepts will translate
  to other platforms
• If remote control software available and
  applicable, use it
• Techniques still useful for broadcast and fully
  automated operations

7
Remote Administration Tools

• Passthrough Mode
• XP_CMDSHELL()
• DBISQL command line
• DBISQL output statement
• Command line FTP
• Batch files
• DOS Echo command
• Update Publication statements

8
5 Remote Administration Techniques

• 1. Passthrough Mode
• 2. Command Result Code Tables
• 3. Result Set Table
• 4. ISQL Result Files
• 5. Stored Procedure Result Files

9
Technique 1 Passthrough

• Like having dbisql with only the command window
• Send SQL statements to remote database
  independent of normal replication stream
• DDL and DML
• Note changes received in passthrough will not
  replicate further

10
Technique 1 Passthrough

• PASSTHROUGH ONLY FOR rem_user1, rem_user2
• UPDATE tbl1 SET col1 'x' WHERE pk 'y'
• CREATE PROCEDURE my_proc ...
• ALTER TABLE tbl1 ...
• PASSTHROUGH STOP / lt-- DO NOT OMIT!! /

11
Technique 1 Passthrough

• Advantages
• Can alter remote data, structure from
  consolidated
• Disadvantages
• No visibility of results
• Cannot see data or structure

12
Technique 2 Command Result Code Tables

• Like having dbisql with command and (limited)
  message windows
• Mimic passthrough with a replicating table
• Issue commands by inserting records into Command
  table on the consolidated
• Record replicates to the remote
• Insert trigger executes statement, records
  SQLCODE in Result Code Table, pushes result back
  to consolidated

13
Technique 2 Command Result Code Tables

• Note 2 reasons Result Code Table insert will not
  replicate to consolidated it is a trigger
  action, and it is part of a replicated
  transaction.
• Must use UPDATE PUBLICATION statement to push the
  record back to consolidated.
• May also want to delete Command Table record
  through the same technique

14
Technique 2 Command Result Code Tables

• CREATE TABLE commands(
• cmd_id INT DEFAULT AUTOINCREMENT,
• cmd_string LONG VARCHAR,
• remote_name VARCHAR(12),
• PRIMARY KEY (cmd_id)
• )

15
Technique 2 Command Result Code Tables

• CREATE TABLE result_codes(
• remote_name VARCHAR(12) DEFAULT
• CURRENT PUBLISHER,
• cmd_id INT,
• result_code INT,
• result_string LONG VARCHAR,
• PRIMARY KEY(remote_name, cmd_id)
• )

16
Technique 2 Command Result Code Tables

• On remote database
• CREATE TRIGGER i_cmd AFTER INSERT ON commands
• REFERENCING NEW AS newrow
• FOR EACH ROW
• BEGIN
• / Run the command /
• EXECUTE IMMEDIATE newrow.cmd_string
• / Record the result code /
• INSERT INTO result_codes(cmd_id,result_code,
  result_string) VALUES(newrow.cmd_id, SQLCODE,
  newrow.cmd_string)

17
Technique 2 Command Result Code Tables

• Remote trigger continued
• / Send result code to consolidated /
• UPDATE result_codes PUBLICATION ResultCode_Pub
• OLD SUBSCRIBE BY('NoOne')
• NEW SUBSCRIBE BY('cons')
• WHERE result_id(select "max"(result_id) FROM
  result_codes)

18
Update Publication sidebar

• Syntax
• UPDATE tablename
• PUBLICATION pubname
• OLD SUBSCRIBE BY ( rep_key1 )
• NEW SUBSCRIBE BY ( rep_key2 )
• WHERE record_key key_val

19
Update Publication sidebar

• Dont try to read like a sentence
• 1. Nothing is getting updated
• 2. The publication is not being affected
• 3. Subscribe by values are not being replaced
• 4. The where clause doesnt make sense until you
  understand 1 through 3
• A record set is being moved from one subscriber
  (or group of subscribers) to another

20
Update Publication sidebar

• Pseudo-syntax
• (UPDATE) CREATE FAKE TRANSACTION LOG ENTRIES
  FOR tablename
• (PUBLICATION) USING THE RULES OF pubname
• (OLD SUBSCRIBE BY) HERES WHO CURRENTLY HAS
  THE DATA ( rep_key1 )
• (NEW SUBSCRIBE BY) HERES WHO SHOULD END UP
  WITH THE DATA ( rep_key2 )
• (WHERE) HERE ARE THE RECORDS TO MOVE
  cust_key key_val

21
Technique 2 Command Result Code Tables

• Remote trigger continued
• / Remove the command from the consolidated /
• UPDATE commands PUBLICATION Command_Pub
• OLD SUBSCRIBE BY('cons')
• NEW SUBSCRIBE BY ('NoOne')
• WHERE cmd_id newrow.cmd_id AND
• remote_name newrow.remote_name

22
Technique 2 Command Result Code Tables

• Remote trigger completed
• / Remove the command from the remote /
• DELETE FROM commands
• WHERE cmd_id newrow. cmd_id
• AND remote_name newrow.remote_name
• END

23
Technique 2 Command Result Code Tables

• Advantages
• Can do everything passthrough can
• Visibility of results
• Disadvantages
• No visibility of data

24
Technique 3 Result Set Table

• Add data window to the virtual dbisql screen
• Create generic Result Set table
• Whole result row stored in single long varchar
• Formulate queries to force result sets into
  result table format (e.g. convert data types,
  concatenate columns)
• Populate Result Set table though SQL statements
  issued via above methods
• Use Update Publication to push results to
  consolidated

25
Technique 3 Result Set Table

• CREATE TABLE result_sets(
• cmd_id INT,
• remote_name VARCHAR(12) DEFAULT CURRENT
  PUBLISHER,
• result_row INT,
• result_string LONG VARCHAR,
• PRIMARY KEY(cmd_id, remote_name,
• result_row)
• )

26
Technique 3 Result Set Table

• Final query needs to be of the form
• SELECT cmd_id, number(), col1 '' col2 FROM
  table1
• Consolidated will send this portion
• col1 '' col2 FROM table1
• Trigger on remote database will complete the
  front part of the query.

27
Technique 3 Result Set Table

• Command table trigger on remote database
• EXECUTE IMMEDIATE
• 'INSERT INTO result_sets(cmd_id,
  result_row, result_string)
• (SELECT ' newrow.cmd_id
• ',number(),' newrow.cmd_string ')'
• UPDATE result_sets PUBLICATION RsultSet_Pub
• OLD SUBSCRIBE BY ('NoOne')
• NEW SUBSCRIBE BY('cons')
• WHERE cmd_id newrow.cmd_id

28
Technique 3 Result Set Table

• Advantages
• Can now issue SELECT statements
• Visibility of data and database structure
• Disadvantages
• Extra burden on consolidated (space, replication
  time)
• Need to force result set into single string value
• Need to parse result rows

29
Technique 4 ISQL Result Files

• Alternative data window for virtual dbisql screen
• Invoke dbisql command line SQL statement
  followed by Output statement, via xp_cmdshell()
• Create an FTP script that sends the output file
  to your consolidated FTP server, via
  xp_cmdshell(echo )
• Invoke command line FTP via xp_cmdshell().
• Can zip the file first, also using xp_cmdshell()
• Timing problem...

30
Technique 4 ISQL Result Files

• Timing is an issue output file must be complete
  before FTP is called.
• 1. Create a stored procedure that FTPs the file
• 2. Create an dbisql script that does the
  following
• Run query
• Output results
• Call FTP procedure
• 3. Run dbisql script

31
Technique 4 ISQL Result Files

• Create a stored procedure that FTPs the file
  (note sample is greatly simplified)
• CREATE PROCEDURE ftp_proc
• BEGIN
• XP_CMDSHELL('echo open ftp.me.com gt
  ftpscript.txt')
• XP_CMDSHELL('echo put file.txt gtgt
  ftpscript.txt')
• XP_CMDSHELL('ftp -s ftpscript.txt')
• END

32
Technique 4 ISQL Result Files

• Create and run dbisql script
• XP_CMDSHELL('echo select from tbl gt
  isqlscript.sql')
• XP_CMDSHELL('echo output to file.txtgtgt
  isqlscript.sql')
• XP_CMDSHELL('echo call ftp_proc() gtgt
  isqlscript.sql')
• XP_CMDSHELL('dbisql -c "uiddba
  pwdsqldbnmydb" isqlscript.sql')

33
Technique 4 ISQL Result Files

• Advantages
• Visibility of data, database structure
• Results can be sent to server other than
  consolidated
• Receive result sets in any supported DBISQL
  format
• Very flexible method (can also do batch files)
• Disadvantages
• Timing issues among scripts can get complex
• Multiple scripts can be difficult to maintain
• Reliance on OS search paths
• Every XP_CMDSHELL call opens a temporary DOS box

34
Technique 5 Stored Procedure Result Files

• Another alternative data window for virtual
  dbisql screen
• Create a stored procedure that opens a cursor,
  and steps through it, writing each row to a file
  via xp_cmdshell('echo 'row_string' gtgt
  file.txt')
• Create an FTP script via xp_cmdshell(echo )
• Call the FTP command line, using the script

35
Technique 5 Stored Procedure Result Files

• Advantages
• Easier control over timing than ISQL Result Files
• Fewer scripts running
• Disadvantages
• ISQL Output types not available
• Concatenating and parsing result rows
• XP_CMDSHELL() has 254 character limit
• Every XP_CMDSHELL call opens a temporary DOS box

36
Operating System Administration

• XP_CMDSHELL() can issue any OS command
• Can pipe results to files
• Can FTP them to yourself

37
Operating System Administration

• Examples
• Stop database engine, schedule a restart
• Check on and modify directory structures
• Check on file versions
• Send executables to the remote and run them
• FTP the EXE to remote machine, database engine
  can run EXE with xp_cmdshell call
• Embed EXEs in Blob field, on insert, extract and
  run
• Create a download-and-run table, listing FTP
  servers, file names, etc.

38
Database Structure Upgrades

• Upgrading a distributed application is
  problematic
• DB structure may change. Dbremote doesn't like
  this.
• All databases in the system must change
  simultaneously. Version 2 database will probably
  reject version 1 data.
• Hard to do when remote sites span time zones.
• Hard when all sites are not staffed with
  qualified DBAs
• May be possible to upgrade front end at your
  leisure if you can ensure backward compatibility.

39
Database Structure Upgrades

• Kick out all users / disable accounts
• Let DBRemote process all pending transactions
• Verify Quiescence
• Send stored procedures that make all structure
  changes
• Verify that stored procedures made it to all
  sites
• Execute stored procedures
• Verify all results at all sites
• Reenable user accounts

40
Security Concerns

• No longer in a sandbox
• Must be a DBA on the consolidated
• Remote will only accept messages from the
  consolidated, which contain the correct state
  information about the two databases.
• Checksums on message header and body
• If you want, intercept message traffic, add a
  digital signature layer to authenticate / check
  integrity

41
Conclusions

• 5 techniques for remote administration
• Maintain data, view result codes, view data, make
  operating system calls
• Fully administer database
• Automate and broadcast database administration
• Perform limited OS administration
• Perform version upgrades of your schema from the
  consolidated

42

• Questions?