1 / 78

M405 Upgrading Your SQL Anywhere Applications

M405 Upgrading Your SQL Anywhere Applications. Robert Waywell Senior Product Support Consultant iAnywhere Solutions rwaywell@ianywhere.com. Objectives. Focus on behavior and architectural changes rather than new features.

season
Download Presentation

M405 Upgrading Your SQL Anywhere Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. M405 Upgrading Your SQL Anywhere Applications • Robert Waywell • Senior Product Support Consultant • iAnywhere Solutions • rwaywell@ianywhere.com

  2. Objectives • Focus on behavior and architectural changes rather than new features. • Develop an understanding of necessary changes to migrate an existing SQL Anywhere Studio application from SQL Anywhere 5.5.x, Adaptive Server Anywhere 6.x or 7.x software to Version 8.x • Determine when and how to upgrade the database file.

  3. Topics • Introduction • Upgrade Tools • Client-Server Applications • SQL Remote Applications • MobiLink Applications • UltraLite Applications • Question Period • Behavior Changes

  4. Introduction • Compatibility with existing software • 8.0 database servers can run older databases (eg. 5.5.x, 6.x, 7.x) that have not been upgraded • For 6.x or later, can mix client and server software from different versions • Benefits of many new features can be obtained without upgrading the database file • eg. multi-processor support, improved network communications,dynamic caching • Even if the database file is not upgraded, software upgrades can introduce new behaviors

  5. Introduction • Upgrading can involve multiple layers and components: • Front-end Application • Operating System • Hardware • SQL Anywhere Studio Software • Database File

  6. Reasons to Upgrade Software • Performance Improvements • Platform Support • New OS versions are only supported by current versions of released software • Improved Stability • Bug fixes are made in current versions, only back ported to active versions • Enhancement of utility programs eg. dbvalid • Move from inactive or archived versions • Support matrix located at: www.sybase.com/detail?id=1002288

  7. Reasons to Upgrade the Database File • Performance Improvements • Separate Primary Key/Foreign Key structures • New index structures for long indexes • Enhanced Statistics • Modify Page Size • If database has grown, a larger page size may be more appropriate • Functionality requires it • eg. SQL Remote required changes to system information between 6.x and 7.0 • Take advantage of new features that rely on new database options or changes to system tables • eg. Scheduling and event handling

  8. Upgrade Practices • Check behavior changes • Always make a full backup before starting an upgrade • Benchmark performance • Check the PLAN() function for key queries • Measure performance of your standard tests • If you will be using dbupgrade ensure that you run dbvalid. • Should be part of your regular backup strategy

  9. Upgrade Practices (cont) • Test application • Many intentional behavior changes • Fix bugs • Improve compliance to standards • Return Warnings that were previously missed • Could be unexpected side effects • Application may have been coded to a bug • Reliance on non-static features e.g. Error message text, assertion error • Test upgrade procedure • Development environment first before rolling out in production

  10. Upgrade Practices (cont) • Pay attention to system path when using command-line tools e.g. Which version of dbinit is being used? • Between 5.5.x and 6.0, moved from a proprietary installation program to using InstallShield • InstallShield has a silent install feature that lets you record a response file to replay. • Also provide template InstallShield scripts to install common components.

  11. Topics • Introduction • Upgrade Tools • DBUpgrade • DBUnload • Client-Server Applications • SQL Remote Applications • MobiLink Applications • UltraLite Applications • Question Period • Behavior Changes

  12. DBUpgrade • What it does • Updates the system tables, provides new database options, adds or modifies system procedures • This process is not recoverable • You must: • Perform a full backup before starting the upgrade • Perform a full backup after completing the upgrade • When to use it • When upgrading the database in-place • When changes to the physical file format are not important • Possibly when upgrading for platform support

  13. DBUpgrade (cont) • Limitations • Does not modify the physical file format • No benefit from: • Separate Primary Key – Foreign Key structure. • Enhanced Index Structures • Improved statistics recording • Does not validate the data • Only modifies the necessary system objects

  14. DBUnload • What it does • Unloads data and schema, generating a SQL script (reload.sql) and .dat files. • When to use it • When you want to take advantage of changes to the physical data store • Process of unloading and reloading the database both validates and defragments the data • Creating and loading the new database • Completed by reading the SQL script via ISQL

  15. DBUnload - Limitations • Reloading views that require qualified table names • View dependencies • Database initialization • Certain characteristics not covered by DBINFO (eg. jConnect support) • Default collation sequences changed • SQL Remote • Need to maintain log offset information • New system users added in V6.0

  16. Topics • Introduction • Upgrade Tools • Client-Server Applications • Architecture • Upgrading • SQL Remote Applications • MobiLink Applications • UltraLite Applications • Question Period • Behavior Changes

  17. Architecture • At this point, let’s look at how the file components have changed between versions and how this impacts both the files required for deployment and the way these components interact. • Version 5.5.x • Version 6.x • Version 7.x • Version 8.x

  18. ODBC Translator Wtr50?.dll Shared Memory Named Pipes DDE HLI Database Engine Dbeng50?.exe Language DLL Wl50??.dll Client Application ODBC Driver Wod50?.dll ESQL Library Dbl50?.dll Language DLL Wl50??.dll Language DLL Wl50??.dll Client Application Client-Server TCPIP IPX NetBios NetBeui Database Server Dbsrv50?.exe Dbclien?.exe Language DLL Wl50??.dll Language DLL Wl50??.dll Version 5.5.x Standalone Engine

  19. Version 5.5.x – Key Points • ODBC driver works through the ESQL library • In this context, ODBC was not a “native” interface to the database engine • DBClient is a separate process • This model was a good choice historically when inter-process communication was relatively inexpensive • The language dll is used by several different components and contains language specific resource strings • Maintaining these strings in a separate component facilitates internationalization

  20. ODBC Translator Dbodtr6.dll Dbsrv6.dll Client Application ODBC Driver DBODBC6.dll Database Engine Dbeng6.exe Shared Memory Named Pipes Language DLL Dbl??6.dll Language DLL Dbl??6.dll Client Application ESQL Library Dblib6.dll Language DLL Dbl??6.dll Client-Server TCPIP IPX NetBios NetBeui Database Server Dbsrv6.exe Dbsrv6.dll Dbport6.dll Language DLL Dbl??6.dll Version 6.x • Personal Server (Standalone Engine)

  21. Version 6.x – What Changed? • ODBC is now a native interface, that communicates directly with the database engine independently of the ESQL library • One layer of translation has been omitted • Network communications are now handled through a dll rather than through a separate process • No separate dbclient executable • More efficient than 5.5.x model

  22. Version 6.x – What Changed? (cont.) • The internal client-server communications protocol changed • 5.5.x client cannot communicate directly with the 6.x server • A client-side compatibility library is available that allows 5.5.x client applications to communicate with a 6.x server • This library replaces the original dbl50?.dll • DDE and HLI are no longer supported for local machine connections

  23. ODBC Translator Dbodtr7.dll Dbsrv7.dll Client Application ODBC Driver DBODBC7.dll Database Engine Dbeng7.exe Shared Memory Named Pipes Language DLL Dbl??7.dll Language DLL Dbl??7.dll Client Application ESQL Library Dblib7.dll Language DLL Dbl??7.dll Client-Server TCPIP SPX(IPX) NetBios NetBeui Database Server Dbsrv7.exe Dbsrv7.dll Language DLL Dbl??7.dll Version 7.x • Personal Server (Standalone Engine)

  24. Version 7.x – What Changed? • The dbport6.dll has been eliminated • Functionality rolled into the dbodbc7.dll and dblib7.dll • The exception is that IPX (as opposed to SPX) support is still maintained in a separate dll since it has been deprecated • This step simplifies the EBF process for client applications by eliminating one file from the deployment list

  25. ODBC Translator Dbodtr8.dll Dbsrv8.dll Client Application ODBC Driver DBODBC8dll Database Engine Dbeng8.exe Shared Memory Named Pipes Language DLL Dbl??8.dll Language DLL Dbl??8dll Client Application ESQL Library Dblib8.dll Language DLL Dbl??8.dll Client-Server TCPIP SPX(IPX) NetBios NetBeui Database Server Dbsrv8.exe Dbsrv8.dll Language DLL Dbl??8.dll Version 8.x • Personal Server (Standalone Engine)

  26. Version 8.x – What Changed? • No significant changes in the file components required to deploy an 8.x client-server application relative to a 7.x client-server application • The previous slides have highlighted the differences between major versions and the core files required for deployment • Additional files would be required to support External Function Calls, Java in the database, NT Performance monitor

  27. Topics • Introduction • Upgrade Tools • Client-Server Applications • Architecture • Upgrading • SQL Remote Applications • MobiLink Applications • UltraLite Applications • Question Period • Behavior Changes

  28. Upgrading V5 Embedded SQL Apps • Install current version of software at each client machine • Use compatibility library only if you have V5 clients that need to communicate with a V8 server • Create a new connection description • Capture dbclient command-line information • ODBC data source – use START parameter • Batch file – move parameters into Commlinks connection parameter • Hard-wired connection string – alter source of application and recompile • Upgrade database server • Use new connection description at each client

  29. Upgrading V5 Embedded SQL Apps

  30. Upgrading V5 ODBC Apps • Install current version of software • Create a Version 8 ODBC source • Changes made depend on connection parameters used in data source • See: • Start Parameters and the compatibility library • Capturing dbclient command-line information • Use new data source • Additional client-server steps same as ESQL

  31. Upgrading V5 ODBC Applications 8

  32. Centralized Upgrading of Applications • iAnywhere Mobile Manager • Remote command execution • Built-in scripting • Distribution of files • Schedule upgrades • M417 Managing Your Mobile Devices and Applications • AM34 iAnywhere Manage Anywhere Administration • Upgrading SQL Anywhere Studio • Upgrading server-side database • Upgrading client applications

  33. Upgrading SQL Anywhere Studio • Distribute custom installation image to users • User input • Setup program starts once install image is downloaded to user • Silent install • Setup program runs in background

  34. Upgrading Server-Side Database • Scripts to automatically validate and backup database • Automatically unload data and schema and reload into new ASA 8.0 database Execute <ASAdir>\win32\dbunload.exe -c "dbf=sademo.db;start=dbeng8;uid=dba;pwd=sql" Unload Execute <ASAdir>\win32\dbinit.exe asademo.db Execute <ASAdir>\win32\dbisql.exe -c "asademo.db;start=dbeng8;uid=dba;pwd=sql" -q reload.sql

  35. Upgrading Client Applications • Schedule upgrade at any time • Distribute new install files to users Copy from package to “<temp path>\VQinst” Execute <temp path>\VQinst\setup.exe Delete File: “<temp path>\VQinst” • Automatically remove old application and replace by new one • Program files and registry entries

  36. Upgrading Client Applications • Automatically update ODBC settings

  37. Upgrading Client Applications • Automatically update Windows shortcuts

  38. Topics • Introduction • Upgrade Tools • Client-Server Applications • SQL Remote Applications • MobiLink Applications • UltraLite Applications • Question Period • Behavior Changes

  39. SQL Remote Applications • DBXtract uses internal reload(7.0.0) • .dat file path is now relative to the server • Need to specify –xx to get the historic behavior • Message link parameters stored in the database (6.0.3) • If not found in the database, will still be read from the registry, .ini file, or environment variable • Message format changed (6.0.0) • Compression of the messages was introduced in 6.0.0 • To continue to use messages with the 5.5.x format, you need to set the database option Compression = -1 SET OPTION public.Compression = ‘-1’

  40. Topics • Introduction • Upgrade Tools • Client-Server Applications • Architecture • Upgrading • SQL Remote Applications • MobiLink Applications • UltraLite Applications • Question Period • Behavior Changes

  41. MobiLink Applications – Upgrade • Upgrade scripts are provided under: • %asany8%\MobiLink\upgrade\6.0.x • %asany8%\MobiLink\upgrade\7.0.x • Readme.txt file in that directory provides instructions on using the scripts • Existing MobiLink applications do not need to be upgraded to communicate with a newer version of the Mobilink server • Recommended ODBC Drivers for MobiLink http://my.sybase.com/detail?id=1011880

  42. Topics • Introduction • Upgrade Tools • Client-Server Applications • Architecture • Upgrading • SQL Remote Applications • MobiLink Applications • UltraLite Applications • Question Period • Behavior Changes

  43. UltraLite Applications • All UltraLite applications need to be recompiled as part of the upgrade process. • UltraLite applications do NOT need to be recompiled to upgrade the MobiLink server. • If you upgrade the Hotsync conduit, then you must recompile the UltraLite application • This applies to EBF’s as well

  44. UltraLite Applications (8.0) • Running the UltraLite generator automatically upgrades the analyzer components in the reference database. • Version 8.0 UltraLite applications require a MobiLink server of version 8.0 or above.

  45. Topics • Introduction • Upgrade Tools • Client-Server Applications • Architecture • Upgrading • SQL Remote Applications • MobiLink Applications • UltraLite Applications • Question Period • Behavior Changes

  46. Summary • New versions offer significant enhancements • Upgrading requires • Checking behavior changes • Backups • Testing • Required files listing • 7.0 ASA User’s Guide – Chapter 28 • 8.0 ASA Programming Guide – Chapter 12 • Use tools like iAnywhere Mobile Manager to facilitate upgrades

  47. SQL Anywhere Studio 8.0 Resources • iAnywhere web site: • www.ianywhere.com • iAnywhere Developer Community • www.ianywhere.com/developer

  48. Upgrading SQL Anywhere Studio Applications Additional References – Behavior Changes

  49. Behavior Changes • Outline major behavior changes • Version 6.x • Version 7.x • Version 8.0 • For comprehensive list, check out the Online Documentation Home Page http://www.sybase.com/detail_list?id=4296

  50. Behavior Changes • Behavior Changes in 6.0 • Behavior Changes in 7.0 • Behavior Changes in 8.0

More Related