1 / 55

E130 ASE UPGRADES & MIGRATIONS GUIDELINES FOR DBAS & DEVELOPERS

E130 ASE UPGRADES & MIGRATIONS GUIDELINES FOR DBAS & DEVELOPERS. Anthony Mandic Managing Director Mandic Consulting Pty. Ltd. anthony@isug.com. DEFINITION – MIGRATIONS. Migrations are a movement of data Not necessarily upgrades Can change versions and even downgrade

khuong
Download Presentation

E130 ASE UPGRADES & MIGRATIONS GUIDELINES FOR DBAS & DEVELOPERS

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. E130ASE UPGRADES & MIGRATIONSGUIDELINES FOR DBAS & DEVELOPERS Anthony Mandic Managing Director Mandic Consulting Pty. Ltd. anthony@isug.com

  2. DEFINITION – MIGRATIONS Migrations are a movement of data Not necessarily upgrades Can change versions and even downgrade Migrate to different: • Operating System • Hardware • Database Product

  3. DEFINITION – MIGRATIONS Cont. Two type of Migrations: • Simple • Using dump and load • Complex - using: • bcp or BCP API • CIS • Replication • Third-party programs

  4. DEFINITION – UPGRADES Upgrades are a change of version Remain on same platform Downgrades are unsupported Need not upgrade to next subsequent version Check Upgrading chapter in ASE Installation Guide

  5. DEFINITION – UPGRADES Cont. Two types of upgrades: • Minor version upgrades by applying: • EBForSWR ROLLUP • IR • One-Off • Full version upgrade by following upgrade process • For example from11.5.xto12.5

  6. JUSTIFICATION Establish the business case Upgrades and Migrations can be costly and time-consuming DBAs/developers may make recommendations Mandate must come from management Process needs to be budgeted and planned

  7. JUSTIFICATION Cont. Rationale may be due to support issues: • Current software is approaching EOL • Hardware is superseded or at end of lease • Management wants more cost-effective platform • Third-party vendors’ version support issues • Developers/DBAs wish to use new features

  8. PLANNING AND TESTING Planning and testing are crucial for success Establish plan from management brief DBA may be involved in project plan Make estimates of timeframes and costs Identify required resources Prepare to sit thru meetings – have facts ready Make recommendation report and get approval

  9. PLANNING AND TESTING Cont. Check hardware and software certification EBFs can only be applied to correct version Check ASE version against Operating System • 64 bit ASE only runs on a 64 bit OS • 32 bit ASE can run on either 32 bit or 64 bit OS Check third-party vendor recommendations

  10. PLANNING AND TESTING Cont. Setup a controlled test environment May be current development or test system Better to use functional copy of production Lease a system if budget allows Greater differences mean greater risk Remember Murphy’s Law

  11. PLANNING AND TESTING Cont. Copy production environment Restore backups of filesystems and databases Match layouts for EBFs and upgrades Full mirroring not required Create worksheets and test schedules Keep timing and performance records

  12. PLANNING AND TESTING Cont. Backend testing regime Test new backup and restore routines first Then: • Other maintenance routines • Batch processes • Other processing routines Note and investigate anomalies

  13. PLANNING AND TESTING Cont. Frontend testing regime Check ASE Open Client certification Check third-party vendor requirements Test all client applications with: • Automated tools (e.g. SQA Robot) • Manual tests Validate data as required

  14. PLANNING AND TESTING Cont. Performance Testing Check before and after Monitor & Historical Server for overall snapshots sp_sysmon for specific snapshots showplan and trace flags for individual queries Auditing or ribo to check queries Third party tools

  15. PLANNING AND TESTING Cont. Draw up final plan Prepare a fallback strategy Document expected duration and resources Determine cutover date Get management signoff Do practice runs Note point of no return

  16. EBF UPGRADES Determine current version Done in 3 ways: • In T-SQL: select@@version • On command line: dataserver –v • Top lines of server log

  17. EBF UPGRADES cont. Determine correct EBF Should be later than current Match platform, OS and bit size Check certification Examine accompanying cover letter Either download or order from Tech Support Uncompress download Ensure disk space is adequate

  18. EBF UPGRADES Cont. EBF Installation Copy production to test # Make backups of master and sybsystemprocs Shutdown ASE and backup servers Backup current installation filesystem Copy files as per cover letter instructions Restart ASE and backup servers Install required scripts (installmaster, instmsgs.ebf)

  19. EBF UPGRADES Cont. Post Installation Check server logs and databases In test, do full checks Check if existing issues are resolved Watch for new issues In production, use test as criterion In production, pick window for install and test

  20. EBF UPGRADES Cont. EBF Rollback If testing fails or issues arise, to rollback: • Shutdown ASE and backup servers • Restore filesystem backup • Restart ASE and backup servers • Rerun original scripts or reload master and sybsystemprocs

  21. UPGRADES Preliminaries Full version upgrades are more complex Pay more attention to planning and testing Acquire target version from Sybase Read accompanying documentation Supported upgrade versions listed in Installation Guide Two step upgrades are possible

  22. UPGRADES Cont. Preliminaries cont. Check Release Bulletin for: • Certification notes • OS requirements • Known issues • Special instructions

  23. UPGRADES Cont. Preliminaries cont. Become familiar with new features • Read What’s New or New Functionality Attend master classes Read white papers Seek out other information

  24. UPGRADES Cont. Upgrade Issues From 11.9.x, model upgrade size becomes 3MB From 12.0 on: • New directory structure and environment variables • Some system database dbids start at 31513 • buildmaster changes layout on master device • Text page chains and sp_rebuild_text In 12.5: no buildmaster, various page sizes

  25. UPGRADES Cont. Two types of Full Upgrades: • Direct or in place upgrade • Using sqlupgrade or sqlupgraderes • New install upgrade • Install new version • Dump and load to upgrade • Similar to simple migration

  26. UPGRADES Cont. Upgrade Preparations Check filesystem space Check for new system databases Check free space in all databases Check new minimum requirements: • Default stack size • Memory per connection • System database sizes (model, sybsystemprocs)

  27. UPGRADES Cont. Installation Copy production to test # Upgrade OS if required Install new version in its own location Apply EBF if required Decide upgrade method Perform integrity checks and backups appropriately

  28. UPGRADES Cont. Direct Upgrade Follow pre-upgrade tasks in Upgrading chapter Turn off db options, auditing, mirroring and replication Dump transaction logs Address issues such as sizes and reserved words Reset old RUN_serverto original contents Change original server’s port to keep out users

  29. UPGRADES Cont. Direct Upgrade Cont. Run new version of sqlupgrade Check: • progress messages • upgrade log • new interfaces file • new RUN_server script

  30. UPGRADES Cont. Direct Upgrade Cont. Follow post-upgrade tasks Check server is running Restore functionality previously disabled Check configuration settings and cache sizes Upgrade compiled objects with dbcc upgrade_object Copy other RUN scripts or upgrade them in 12.5

  31. UPGRADES Cont. Direct Upgrade Rollback Shutdown server Save old RUN_server, interfaces and config files Create server on same master device with old ASE Or use old mirrored copy of master device Reload dump of master and then other databases Restore old RUN_server, interfaces and config files Bounce server and check logs

  32. UPGRADES Cont. New Install Upgrade Safer but more time consuming than direct upgrade Easier to rollback Requires more disk space If memory is low, run one server at a time Break mirrors to create space if required You create new master and system databases

  33. UPGRADES Cont. New Install Upgrade Cont. Create and configure new servers (ASE, BS, XP) Match character set and sort order Match layouts of original user databases Create and alter with for load option Copy logins and roles with bcp or recreate Copy any other customizations

  34. UPGRADES Cont. New Install Upgrade Cont. Dump and load user databases Databases upgrade during online database Check output from online for errors Some errors may require fixing in original database Configure dboptions and database owners

  35. UPGRADES Cont. New Install Upgrade Cont. Post-upgrade tasks: • Use sp_checkreswords to check for reserved words • Use sp_rebuild_text to fix text • Use dbcc upgrade_object for compiled objects • Shutdown server • Check RUN_server and interfaces files • Bounce servers and check logs

  36. UPGRADES Cont. New Install Upgrade Rollback Stop new server Restart old server Check for client issues Only other outstanding issue is newly added data

  37. UPGRADES Cont. Final Upgrade Activities In test, do full checks In production, test vital routines Rebuild mirrors Reclaim space from old version Plan enhancement rollouts once stability confirmed

  38. MIGRATIONS Preliminaries If transitioning to new hardware, database or OS: • Learn its characteristics • Obtain correct versions For new hardware, use target as test If on same hardware, avoid impacting production Same server migrations require additional disk space

  39. MIGRATIONS cont. Migration Issues Endian issues – dump formats are physical IEEE 754-1985 and inexact numeric datatypes Page sizes with ASE 12.5 64bit to 32bit Downgrades – remove unsupported functionality

  40. MIGRATIONS cont. Simple Migrations Similar to new install upgrade Follow same guidelines: • Set up and configure new server • Use same character set and sort order • Copy logins and roles • Match user database layouts • Migrate via dump and load

  41. MIGRATIONS cont. Simple Migrations cont. Follow post-upgrade tasks if required Mandatory if upgrading the version as well When ready, swap IP addresses if required

  42. MIGRATIONS cont. Simple Migration Rollback Simply switch back to old server Swap IP addresses back if necessary

  43. MIGRATIONS cont. Complex Migrations When dumps and loads are not possible Allows more flexibility in configuration Free to change: • Device layouts and sizes • Character sets and sort orders • Cache sizes and configuration options • Page size (for ASE 12.5)

  44. MIGRATIONS cont. Complex Migrations cont. Need to copy: • Database schema • Data • All compiled objects • Object permissions • User logins • Roles

  45. MIGRATIONS cont. Complex Migrations cont. It helps if you keep your source Third party vendor product may generate it If not, reverse engineer with: • Sybase’s PowerDesigner • Embarcadero’s ER/Studio • Logic Works’ ERwin • Rational’s Rational Rose

  46. MIGRATIONS cont. Complex Migrations cont. You can also reverse engineer with: • dbschema.pl • ddlgen (since ASE 11.9.2) • defncopy for compiled objects • Sybase Central

  47. MIGRATIONS cont. Complex Migrations cont. To copy the data: • bcp or the BCP API • CIS • PowerBuilder Pipeline • Replication Server or SQL Remote • BMC’s SQL-BackTrack • Embarcadero’s DBArtisan

  48. MIGRATIONS cont. Complex Migrations cont. Pick the right tool for the job! BCP requires the use of files – watch size limits CIS needs configuration between servers BCP API requires programming PowerBuilder and DBArtisan requires a client Logical backup for BackTrack Evaluate tool and do timing and performance tests

  49. MIGRATIONS cont. Complex Migrations cont. There are two data loading modes with bcp: • Minimally logged or fast mode • Requires db option select into/bulkcopy/pllsort • Set trunc log on chkpnt too • No indexes or triggers • Logged or slow mode • No db option, needs at least one index

  50. MIGRATIONS cont. Complex Migrations cont. To prepare target databases for fast bulk copy: • Install user defined datatypes, defaults & table defs • Groups and permissions • Set any database option(s) • Load table data • Create indexes and constraints • Install compiled objects, logins and users

More Related