MC415 Adaptive Server Anywhere: Remote Administration Without Remote Access

1. MC415 Adaptive Server Anywhere: Remote Administration Without Remote Access Eric Murchie-Beyma Stanley Associates, Inc. Technical Director, Army Systems emb@stanleyassociates.com

2. 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 Presenter

3. 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 Purpose

4. Not just for mobile & embedded Full-featured client-server RDBMS Server to server replication, geographically separate Adaptive Server Anywhere as a Distributed System Platform

5. 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 Small-scale Distributed Systems

6. 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 Remote Administration Capabilities

7. Passthrough Mode XP_CMDSHELL() DBISQL command line DBISQL output statement Command line FTP Batch files DOS Echo command Update Publication statements Remote Administration Tools

8. 1. Passthrough Mode 2. Command & Result Code Tables 3. Result Set Table 4. ISQL Result Files 5. Stored Procedure Result Files 5 Remote Administration Techniques

9. 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 Technique #1: Passthrough

10. PASSTHROUGH ONLY FOR rem_user1, rem_user2; UPDATE tbl1 SET col1 = 'x' WHERE pk = 'y'; CREATE PROCEDURE my_proc ... ; ALTER TABLE tbl1 ... ; PASSTHROUGH STOP; /* <-- DO NOT OMIT!! */ Technique #1: Passthrough

11. Advantages Can alter remote data, structure from consolidated Disadvantages No visibility of results Cannot see data or structure Technique #1: Passthrough

12. 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 Technique #2: Command & Result Code Tables

13. 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 Technique #2: Command & Result Code Tables

14. CREATE TABLE commands( cmd_id INT DEFAULT AUTOINCREMENT, cmd_string LONG VARCHAR, remote_name VARCHAR(12), PRIMARY KEY (cmd_id) ); Technique #2: Command & Result Code Tables

15. 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) ); Technique #2: Command & Result Code Tables

16. 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); Technique #2: Command & Result Code Tables

17. 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); Technique #2: Command & Result Code Tables

18. Syntax: UPDATE tablename PUBLICATION pubname OLD SUBSCRIBE BY ( rep_key1 ) NEW SUBSCRIBE BY ( rep_key2 ) WHERE record_key = key_val; Update Publication sidebar

19. Don’t 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 doesn’t make sense until you understand 1 through 3 A record set is being moved from one subscriber (or group of subscribers) to another Update Publication sidebar

20. Pseudo-syntax: (UPDATE) CREATE FAKE TRANSACTION LOG ENTRIES FOR tablename (PUBLICATION) USING THE RULES OF pubname (OLD SUBSCRIBE BY) HERE’S WHO CURRENTLY HAS THE DATA ( rep_key1 ) (NEW SUBSCRIBE BY) HERE’S WHO SHOULD END UP WITH THE DATA ( rep_key2 ) (WHERE) HERE ARE THE RECORDS TO MOVE cust_key = key_val; Update Publication sidebar

21. 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; Technique #2: Command & Result Code Tables

22. 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; Technique #2: Command & Result Code Tables

23. Advantages Can do everything passthrough can Visibility of results Disadvantages No visibility of data Technique #2: Command & Result Code Tables

24. 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 Technique #3: Result Set Table

25. 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) ); Technique #3: Result Set Table

26. 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. Technique #3: Result Set Table

27. 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; Technique #3: Result Set Table

28. 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 Technique #3: Result Set Table

29. 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... Technique #4: ISQL Result Files

30. 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 Technique #4: ISQL Result Files

31. 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 > ftpscript.txt'); XP_CMDSHELL('echo put file.txt >> ftpscript.txt'); XP_CMDSHELL('ftp -s ftpscript.txt'); END; Technique #4: ISQL Result Files

32. Create and run dbisql script XP_CMDSHELL('echo select * from tbl; > isqlscript.sql'); XP_CMDSHELL('echo output to file.txt;>> isqlscript.sql'); XP_CMDSHELL('echo call ftp_proc(); >> isqlscript.sql'); XP_CMDSHELL('dbisql -c "uid=dba; pwd=sql;dbn=mydb" isqlscript.sql'); Technique #4: ISQL Result Files

33. 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 Technique #4: ISQL Result Files

34. 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||' >> file.txt'); Create an FTP script via xp_cmdshell(echo …) Call the FTP command line, using the script Technique #5: Stored Procedure Result Files

35. 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 Technique #5: Stored Procedure Result Files

36. XP_CMDSHELL() can issue any OS command Can pipe results to files Can FTP them to yourself Operating System Administration

37. 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. Operating System Administration

38. 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. Database Structure Upgrades

39. 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 Database Structure Upgrades

40. 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 Security Concerns

41. 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 Conclusions

42. Questions?