Migrating to Sybase® Adaptive Server™ Enterprise 11.
Document ID: 34982-01-1150-02 December 1997 Copyright Information Copyright © 1989–1997 by Sybase, Inc. All rights reserved. Sybase, Inc., 6475 Christie Avenue, Emeryville, CA 94608. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of that agreement.
Table of Contents 1. Introduction and Guide to Resources In This Chapter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . In This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Relating Documentation to Migration Phase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SyBooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Adaptive Server Enterprise 11.5 Physical Memory Utilization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-5 Software Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-6 Operating System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-6 Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Adaptive Server Enterprise 11.5 Datatype Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-5 Datatype Conversions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-6 ANSI SQL92 Support for Conversion Error Handling . . . . . . . . . . . . 5-7 Subquery Processing Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-8 in and any . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Adaptive Server Enterprise 11.5 Login and Password Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-2 RUN File Renamed. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-3 System Stored Procedure Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-3 Creating the System Stored Procedure Database . . . . . . . . . . . . . . . . 6-4 Moving User Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Adaptive Server Enterprise 11.5 Create Your Databases by Loading Backups . . . . . . . . . . . . . . . . . . . . . . . 7-2 If the Test Environment Is Not an Exact Duplicate . . . . . . . . . . . . . . . . . . 7-3 Prioritizing Applications to be Tested . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-3 Establishing Performance Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-3 Developing Fallback Procedures . . . . . . . . . . . . . . . . . .
Adaptive Server Enterprise 11.5 Backup and Restore Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . B-3 Database Dump Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . B-4 Maintenance Procedure Details. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . B-5 Data Architecture Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . B-6 Client Application Components. . . . . . . . . . .
Adaptive Server Enterprise 11.5 Design and Develop Server Migration Scripts. . . . . . . . . . . . . . . . . . D-4 Design and Develop Database Migration Scripts . . . . . . . . . . . . . . . D-4 Design and Develop Data Migration Scripts . . . . . . . . . . . . . . . . . . . D-4 Perform Other Pre-migration Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . D-4 Implement Migration (Using Install/.Load Technique) . . . . . . . . . . . . . D-5 Create Target Environment. . . . . . . . . . . . . . . . . .
Adaptive Server Enterprise 11.5 Cutover Migration Task List Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Set Up Adaptive Server 11.5 Environment on Development System . Define Test/Acceptance Criteria—Regression Test Suites . . . . . . . . . . Front-end simulation regression test suite . . . . . . . . . . . . . . . . . . . . Front-end regression test suite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Define Release 11.5 to 10.
List of Tables Table 1-1: Table 1-2: Table 2-1: Table 2-2: Table 2-3: Table 2-4: Table 2-5: Table 3-1: Table 3-2: Table 3-3: Table 3-4: Table 3-5: Table 3-6: Table 3-7: Table 3-8: Table 3-9: Table 3-10: Table 3-11: Table 4-1: Table 4-2: Table 4-3: Table 4-4: Table 5-1: Table 5-2: Table 5-3: Table 5-4: Table 5-5: Table 6-1: Table 6-2: Table 6-3: Table 6-4: Table 6-5: Table 6-6: Table 7-1: Table 7-2: Table B-1: Table B-2: Table B-3: Chapters in this migration guide ...........................................
Adaptive Server Enterprise 11.5 Table B-4: Table B-5: Table B-6: Table B-7: Table B-8: Table B-9: Table B-10: Table B-11: Table B-12: Table B-13: Table B-14: Table B-15: Table B-16: Table B-17: Table B-18: Table B-19: Table B-20: Table B-21: xiv Maintenance .................................................................................................................. B-5 Client applications........................................................................................................
1 Introduction and Guide to Resources 1. In This Chapter This chapter gives an overview of the topics covered in this manual. In addition, it points you to an extensive list of Sybase resources that can help you plan and execute a trouble-free migration to Sybase Adaptive Server Enterprise(TM) 11.5.
Relating Documentation to Migration Phase Adaptive Server Enterprise 11.5 Table 1-1: Chapters in this migration guide Chapter Number Chapter Title Contents 1 “Introduction and Guide to Resources” A survey of resources available from Sybase and third-party vendors. 2 “Analyze: Documenting Business Requirements” A series of worksheets for documenting the flow of information in your system and your business requirements.
Adaptive Server Enterprise 11.5 SyBooks Table 1-2: Documentation for Migration Document Time period covered Range of tasks covered • What’s New in Adaptive Server Enterprise 11.5 Planning/preparation prior to upgrade Assessing current system • Migrating to Sybase Adaptive Server Enterprise 11.5 Planning migration Making applications compatible Updating DBA procedures • Release Bulletins, TechNotes (http://techinfo.sybase.
Technical Information Library Adaptive Server Enterprise 11.5 To use SyBooks-on-the-Web, go to http://www.sybase.com, and choose Documentation. Technical Information Library Technical Information Library is a collection of technical documents, such as TechNotes, FAQs, and white papers, produced and used by Sybase engineers to troubleshoot customer issues and enhance the use of Sybase products. Technical Information Library also contains information for downloading EBFs from the Web.
Adaptive Server Enterprise 11.5 Migration Web Page • A Web browser that supports the Secure Sockets Layer (SSL), such as Netscape Navigator 1.2 or later • An active support license • A named technical support contact • Your user ID and password ➤ Note You can reach Support Plus Online Services by going to the Sybase home page at http://www.sybase.com and choosing “Technical Support”. Current EBF’s can be downloaded by registered Support Plus users.
Sybase Press Books Adaptive Server Enterprise 11.5 Migration Resource Guide on the Web (http://www.sybase.com/migration), or ordered on CD-ROM. Sybase Press Books Sybase Press produces commercial books about Sybase topics, including Upgrading and Migrating to Sybase SQL Server 11, by Mitchell Gurspan. This book contains valuable information about the migration process, using operating system tools to aid your migration, and technical issues up to SQL Server 11.0.
Adaptive Server Enterprise 11.5 Third-Party Tools uses the Sybase-developed and recommended methodology, SAFE/EM (Sybase Advanced Framework to Enable Effective Migration). The migration technology in this manual is based on SAFE/EM. For new or upgrading customers, Professional Services offers SAFE/ITA (Sybase Advanced Framework to Enable Information Technology Architecture), an architectural framework and ongoing process for making high-value IT investments that support your business goals.
If You Need Help 1-8 Adaptive Server Enterprise 11.
2 Analyze: Documenting Business Requirements 2. In This Chapter In this chapter you begin the first phase of migration planning: documenting your environment. This chapter helps you organize business information required for an effective migration plan.
Diagramming the System Adaptive Server Enterprise 11.5 Acme Brokerage Company System Architecture redwolf: 111.222.333.5 Sun SparcCenter 2000E (NY) 20 CPUs, 2Gb RAM SSA 250Gb Mirrored fatweasel: 111.222.555.
Adaptive Server Enterprise 11.5 Operational Business Requirements Operational Business Requirements This section suggests ways to document your operational business requirements. You will use these baseline requirements to help you plan the migration and develop success criteria.
Operational Business Requirements Adaptive Server Enterprise 11.5 • Table rowcounts and daily change rate (number of inserts, deletes, and updates. Database Dump Details Document your dump procedures, including times and devices, as in the following example: Table 2-2: Database Dump Details Database Name Frequency of Database Dumps Dump Device Used Frequency of Transaction Log Dumps Dump Device Used master every night master_dumpdev TRD every night TRD_tape1 TRD_tape2 Every 15 min.
Adaptive Server Enterprise 11.
Current Performance Metrics Adaptive Server Enterprise 11.5 Transaction Profile Using statistics io, showplan, and both dbcc 302 and dbcc 310 to capture application processing details, record transaction profiles on a chart like the following. Save showplan and dbcc output for all critical transactions. You will use this information as a baseline for postupgrade testing, as described in Chapter 7, “Test: Ensuring Stability and Performance.
Adaptive Server Enterprise 11.5 Additional Business Requirements - Use operating system monitors to measure I/Os per second per disk and controller, and I/O queue lengths per “time window” per server. - Use Sybase monitors to measure total I/Os, reads, and writes per second per Sybase device per “time window” per server. • Concurrency - Use Sybase monitors to determine the average lock contention.
Additional Business Requirements Adaptive Server Enterprise 11.
3 Analyze: Documenting Your Environment 3. This chapter provides guidelines for documenting system hardware and software in the SQL Server production environment. This information is used to identify resource issues during the planning phase of the migration. See Appendix B, “Worksheets for Your Current Environment” for worksheets like the ones used in the examples in this chapter.
Hardware Configuration Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.5 Hardware Configuration Table 3-2: Disk layout map Physical Device name Make and Model Firmware Revision Months in Service Controller Number Capacity (Mb) Throughput (I/Os per sec) Transfer Rate (Kb/sec) c0t0d0 Seagate ST43401N 2.15 9 0 2900 80 1500 c0t0d1 Seagate ST43401N 2.15 12 0 2900 80 1000 c0t0d2 Seagate ST43401N 2.15 24 0 2900 80 1600 c0t0d3 Seagate ST43401N 2.
Hardware Configuration Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.5 Physical Memory Utilization Table 3-6: Tape layout map Physical Device Name Make and Model Firmware Revision Months in Service Controller Number Capacity (Mb_) Transfer Rate (Kb/sec) /dev/rmt/0 Sun Sparc 10 2.15 9 2 2000 500 /dev/rmt/1 Sun Sparc 20 1.00 12 2 2900 500 Physical Memory Utilization List all the major processes running on a server machine and use the formulas given here to calculate their memory requirements.
Software Configuration Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.5 Sybase Configuration • Web page if applicable Applications Make a list of applications to be migrated to Adaptive Server 11.5. For each application, note: • Information about data and usage - Distributed data - Warehoused or used in transaction processing? If used in transaction processing, it needs to be accurate and in the right format. Warehoused data is more tolerant of format and slight computational differences, such as datatype conversion differences.
Sybase Configuration Adaptive Server Enterprise 11.5 • SQL Server Runtime Memory Map - Run dbcc memusage during non-peak time or in single user mode. This command tells you how memory is configured within SQL Server. See the Performance and Tuning Guide for more information about ways to capture server memory statistics. You can record the output of dbcc memusage in a table like this one: Table 3-8: dbcc memusage output Mb 2K Blocks Bytes Configured Memory 10.0000 5120 10485760 Code size: 2.
Adaptive Server Enterprise 11.5 Sybase Configuration Table 3-9: Database Devices Database Device Name Physical Device name TRD_dev1 Mirrored Device Name Virtual Device Number Size (Mb) /dev/rdsk/c0t0d0s3 2 10020 TRD_dev2 /dev/rdsk/c0t1d0s3 3 5020 TRD_log /dev/rdsk/c0t1d0s4 4 1020 CIS_dev1 /dev/rdsk/c0t1d1s3 5 4020 CIS_log /dev/rdsk/c0t1d1s4 6 420 Databases and Segments List of all segments and the objects on them.
SQL Server Objects Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.5 SQL Server Objects - Other database objects such as triggers and indexes You may also want need to extract and load data with bcp. These scripts can be used to help set up your test environment as well as the building a new production system and may be needed if you plan to maintain two server systems at different release levels.
SQL Server Objects Adaptive Server Enterprise 11.5 Use Sybase Tools You can reverse engineer server objects using Sybase DBA tools such as SQL Server Manager, Sybase Central, or Powersoft PowerDesigner 6.0. The the manuals for these tools. Use Third Party Tools Some third party vendors offer tools that you can use to analyze and write scripts for your objects.
4 Prepare: Writing a Plan and Getting Ready to Migrate 4. In this Chapter Now that you’ve collected data about your current system, you can make a plan for migrating. In addition to choosing a migration method, you may need to bring your system resources to the level required by Adaptive Server 11.5 and make changes needed in applications and system administration procedures.
Determine Migration Approach Adaptive Server Enterprise 11.5 The Table 4-1 highlights the advantages and disadvantages of each migration approach. Table 4-1: Migration approaches Approach Advantages Disadvantages When Used Parallel With Replication Easy fallback to release 10.0.2.5 or later. You do not need to rebuild release 10.0.2.5 or later databases. Can be complex in OLTP environments.
Adaptive Server Enterprise 11.5 Determine Migration Approach ➤ Note This migration guide does not cover other parallel migration approaches; such as, running two systems in parallel where you have to maintain both the systems simultaneously, or transaction duplication where you use one front-end to drive two parallel back-ends. These system operational approaches include factors too site-specific to detail effectively in this guide. Whenever possible, upgrade test and development databases release 11.
Determine Migration Approach Adaptive Server Enterprise 11.5 Table 4-2: Parallel migration (continued) Issue Recommendations and Tips Fallback Plan for all users to reconnect to SQL Server 10.0.2.5 or later after you take SQL Server 11.5 offline. Make TCP/IP address and port changes where appropriate. Test the fallback process as part of the application test suite. This suite should do both of the following: • Insert data into SQL Server 11.5. The data must be replicated and available in SQL Server 10.
Adaptive Server Enterprise 11.5 Determine Migration Approach Table 4-2: Parallel migration (continued) Issue Recommendations and Tips Environment The environment used for SQL Server 11.5 needs to be more powerful to handle the query and replication loads. See the Replication Server Configuration Guide. Be sure to account for any increased release 11.5 memory requirements that apply to your configuration. For more information, see: • The installation guide for your platform.
Determine Migration Approach Adaptive Server Enterprise 11.5 Table 4-3: Cutover migration (continued) Issue Recommendations and Tips Fallback Base fallback on the amount of time needed to restore earlier databases. For example, if users need the system Monday at 8 a.m. and the restoration takes 8 hours, the validation test must pass by Sunday at midnight. Note: Remember to include any client fallback in the calculation. You can use dump database or bcp out before an upgrade to prepare for fallback.
Adaptive Server Enterprise 11.5 Determine Migration Approach Phased Cutover The issues for a phased cutover are: Table 4-4: Phased cutover migration Issue Recommendations and Tips Method Change only one application and database to release 11.x at a time. Fallback Consider making daily bcp dumps of the databases. To fall back, you can then load the dumps into release 10.x or 4.x. Keep in mind: • You may need to modify the databases to support incremental bcp dumps. • Pre-release 11.
Write a Migration Plan Adaptive Server Enterprise 11.5 Table 4-4: Phased cutover migration (continued) Issue Recommendations and Tips Environment Be sure to account for any increased release 11.x memory requirements that apply to your configuration. For more information, see: • The installation guide for your platform. It gives basic RAM requirements. • Chapter 6, “Implement: Making Required Database Administration Changes”.
Adaptive Server Enterprise 11.5 Build the Adaptive Server Environment requirements you gathered in Chapter 3, “Analyze: Documenting Your Environment”. Producing the following documentation may also be useful: • A work breakdown that lists tasks chronologically and assigns them to specific roles like the one in Appendix D, “Sample Migration Task Lists”. • Specification for application changes.
Build the Adaptive Server Environment Adaptive Server Enterprise 11.5 See the installation guide for your platform for basic RAM requirements. See Chapter 6, “Implement: Making Required Database Administration Changes” for more information on cache requirements. See the System Administration Guide for information on backups. Verify Operating System Version and Fix Level Ensure that the operating system is at the proper version and level to run Adaptive Server 11.5.
Adaptive Server Enterprise 11.5 Update Applications and System Administration Procedures Update Applications and System Administration Procedures The following chapters, Chapter 5, “Implement: Making Required Application Changes” and Chapter 6, “Implement: Making Required Database Administration Changes” detail the 11.5 issues Migrating to Adaptive Server 11.
Update Applications and System Administration Procedures 4-12 Adaptive Server Enterprise 11.
5 Implement: Making Required Application Changes 5. This chapter discusses changes to Transact-SQL syntax, query processing, datatypes, objects, and reserved words that can cause application failure or unexpected results. It does not attempt to cover all changes that could affect applications. For a comprehensive listing of changes and new features, see What’s New in Adaptive Server 11.5.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 • New Reserved Words in Version 10.0 5-2 • New Login and Password Protocols 5-3 • Datatype Changes 5-3 • Subquery Processing Changes 5-8 • Additional ANSI-Related Transact-SQL Changes 5-13 New Reserved Words in Version 10.0 These words are reserved as of release 10.0 and can no longer be used as database object names, nor as user, group, or role names: Table 5-1: New reserved words in 10.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x New Login and Password Protocols Login/password changes include the following: • New logins and changed passwords require a minimum 6-byte password. Existing passwords smaller than 6 bytes are left alone during upgrade, but the 6-byte minimum is enforced if you add or change passwords. • Null passwords are not allowed. You need not change existing passwords. The next invocation of sp_password will enforce the new rules.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 Adaptive Server now defaults to the numeric datatype rather than float. For example, this constant: 5.1 is treated as numeric. If you want to use float, represent the constant as 5.1e0 In addition, the optimizer has trouble with clauses where float columns are joined to numeric arguments.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x Datatype Hierarchy The datatype hierarchy determines the datatype of computational results. The result value is assigned the datatype of its parent component closest to the top of the hierarchy. There is no loss of precision due to conversions. However, Adaptive Server may return datatypes different than those returned by earlier versions of SQL Server. In release 4.x, the money datatype was higher than float datatypes.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 Table 5-3: Datatype hierarchy Name Hierarchy sysname 19 nvarchar 19 char 20 nchar 20 varbinary 21 timestamp 21 binary 22 text 23 image 24 Example In release 4.x, money was above float in the hierarchy. It is now below both float and numeric. For example, the following query: select $12 * 8.9 returns a result of type numeric. In release 4.x, this query returned money. Likewise, the following query: select $12 * 8.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x scale, the results are truncated without warning. For example, explicitly converting a float to an integer causes SQL Server to truncate all values to the right of the decimal point. The rationale is that explicit conversions are done purposefully with an understanding of the implications and consequences. • Integer to character conversion: Conversions from integer to character return an error if an overflow occurs.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 arithignore arith_overflow The command: set arithignore arith_overflow specifies whether error messages are sent upon numeric overflow The command options are: ON: Doesn’t advise application of overflow or divide-by-zero errors OFF (Default): Advises application of overflow or divide-by-zero errors Subquery Processing Changes This section describes changes in subquery processing.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x Release 4.x Results Release 10.x and 11.x Results New Age Books New Age Books New Age Books New Age Books New Age Books Binnet & Hardley Binnet & Hardley Binnet & Hardley Binnet & Hardley Binnet & Hardley Binnet & Hardley Binnet & Hardley Algodata Infosystems Algodata Infosystems Algodata Infosystems Algodata Infosystems Algodata Infosystems Algodata Infosystems New Age Books Binnet & Hardley Algodata Infosystems not in SQL Server 4.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 Release 4.x Results Release 10.x and 11.x Results 0736 0877 1389 0736 or...exists/in/any Subqueries that contain exists, in, or any under an or clause now return correct results sets. SQL Server 4.x did not return rows when a subquery evaluated to “false”, even if the or clause was “true”. You now get correct results without having to write extra code.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x select title from titles t1 where t1.advance > all (select advance from publishers p, titles t2 where p.pub_name=”No Such Publisher” and t2.pub_id = p.pub_id) Release 4.x Results Release 10.x and 11.x Results NULL all rows in the titles table Correlated Subqueries Release 4.x erroneously suppressed duplicates if all columns in the outer query also were used in the subquery. If you rewrite release 4.x subqueries for release 11.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 Release 4.x Results Release 10.x and 11.x Results 0736 0877 1389 0736 0736 0736 0736 0736 0877 0877 0877 0877 0877 0877 0877 1389 1389 1389 1389 1389 1389 5 7 6 5 5 5 5 5 7 7 7 7 7 7 7 6 6 6 6 6 6 Aggregates with exists SQL Server 4.x sometimes returned the wrong answer when a query contained an aggregate, a correlated subquery contained an exists predicate, and the table in the subquery’s from clause contained duplicates.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x In release 4.x, this query returns 18 items, including 15 duplicates; in release 10.x and 11.x, this query returns 3 items. select distinct Prior to release 10.x, correlated in subqueries using distinct would cause the outer query to return no rows. These subqueries now return the correct results. This example compares releases: select pub_name from publishers where pub_id in (select distinct pub_id from titles where titles.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 In version 4.x, SQL Server switched expr2 and expr3 automatically at compile time if it knew that expr2 was greater than expr3. In other words, it would process “250 between 400 and 200” as though it were the same as “250 between 200 and 400” As of version 10.0, SQL Server processes such inverted statements as “false”.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x select columns from table1 t1, table1 t2 where clause ... When used, correlation names must be used throughout the query. For example, the following query is now invalid select title_id from titles t where titles.type = trad_cook It must be rewritten as: select title_id from titles t where t.
If Your Current Version is 4.x or 10.x Adaptive Server Enterprise 11.5 select title_id, avg_advance = avg(advance) into #tempdata from titles select title_id, avg(advance) avg_advance into #tempdata from titles select title_id, avg(advance) as avg_advance into #tempdata from titles If Your Current Version is 4.x or 10.x Continue with this section if you are upgrading from SQL Server 4.x. Begin here if you are upgrading from 10.x. Skip this section if you are upgrading from version 11.0.x.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x or 10.x Subquery Processing Changes Changes in subquery processing are described in the following sections: • Expression Subqueries 5-17 • No set dup in subquery 5-17 • union Limitations 5-18 • Subqueries and NULL Results 5-18 • No Subqueries in Updatable Cursors 5-18 Expression Subqueries Expression subqueries may be slower in release 11.x than 10.x where: • The outer table is very large and has few duplicate correlation values.
If Your Current Version is 4.x or 10.x Adaptive Server Enterprise 11.5 version 10.0 and is now obsolete. Applications that use it receive a warning message and subqueries no longer return duplicates. You may have used this option to obtain better performance. Because of subquery processing changes, you must rewrite your query as a join if you want duplicates. You should see better performance in Adaptive Server for these types of queries.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x, 10.x, or 11.0.x • Aggregate functions • union operators • at isolation read uncommitted See the Transact-SQL User’s Guide for details about using updatable cursors. If Your Current Version is 4.x, 10.x, or 11.0.x This section describes changes introduced with version 11.5 that affect all systems at lower versions. Read this section and all following sections. If you are at release 11.0.
If Your Current Version is 4.x, 10.x, or 11.0.x Adaptive Server Enterprise 11.5 Table 5-5: New reserved words in 11.5 activation identity_start connect membership consumers passwd exclusive proxy external session Changing Reserved Words in Your Applications Sybase-provided tools such as the reserved words check option of the upgrade program for UNIX and the stored procedure sp_checkreswords allow you to check for database name conflicts in Adaptive Server both before and after upgrade.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x, 10.x, or 11.0.x For more information, see the section on the set command in the Adaptive Server Enterprise Reference Manual. Sorted Order in Queries with Clustered Indexes Queries on tables with clustered indexes have, in past releases, returned results in a predictable order, that is, index order. However, this behavior was never guaranteed by select semantics. and was only an artifact of linear processing.
If Your Current Version is 4.x, 10.x, or 11.0.x Adaptive Server Enterprise 11.5 • Avoid mathematical manipulation of indexed columns when using search arguments. For example, do not use mathematical functions on the column itself, as shown in the example. Instead, perform the function against the constant on the other side of the operator.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x, 10.x, or 11.0.x select ship_to_address from customer_orders where last_order_date >= "01/01/1997" GROUP BY ship_to_address • Use the leading column of an index. If the leading column is missing from the query, Adaptive Server can’t utilize the index. In this example, Table A has a non-clustered index on columns ColA, ColB, and ColC.
If Your Current Version is 4.x, 10.x, or 11.0.x Adaptive Server Enterprise 11.5 General Tips • Try to use queries that can be “flattened” by the optimizer. The optimizer can treat certain subqueries like normal or existence joins, which are much quicker to execute. Queries that can be flattened include: - Many in, any, and exists subqueries - Expression subqueries like the following: column {<, <=, >, >=, !=, =} subquery - Expression subqueries with unique joins or that return unique columns.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x, 10.x, or 11.0.
If Your Current Version is 4.x, 10.x, or 11.0.x 5-26 Implement: Making Required Application Changes Adaptive Server Enterprise 11.
6 Implement: Making Required Database Administration Changes 6. In this Chapter This chapter discusses changes to Adaptive Server system administration that can cause problems if you are not prepared for them. It does not attempt to cover all new, helpful features. For a comprehensive listing of changes and new features, see What’s New in Adaptive Server 11.5.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 • System Stored Procedure Database 6-3 • Last Chance Threshold 6-5 New Reserved Words in Version 10.0 These words are reserved as of release 10.0.x and can no longer be used as database object, user, group, or role names: Table 6-1: New reserved words from 4.x to 10.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x • You need not change existing passwords; however Adaptive Server begins enforcing the new password rules the first time sp_password is invoked. ➤ Note SQL Server 11.1 enables you to protect sensitive data with several security features, including user identification and authentication, discretionary access controls, division of roles, and event auditing. See the Security Administration Guide and the Security Features User’s Guide.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 • Procedures that affect the master database cannot be run from within a transaction. For example, you cannot run a procedure such as this one from within a transaction: if @@trancount > 0 print • You may need to increase the open databases parameter. This parameter must be set to at least the number of databases on your system for the upgrade to succeed.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x New Backup Server As of 10.0, Backup Server manages all dumps. All backup and recovery procedures are executed through remote procedure calls (RPCs) between Adaptive Server and Backup Server. To allow these procedures to execute, the allow remote access config parameter must be set to “on”.
If Your Current Version is 4.x Adaptive Server Enterprise 11.5 To learn how to manage free space with thresholds, see the System Administration Guide. Sample Procedure The following code fragment is a sample threshold procedure that you can tune to suit your installation.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x or 10.x Troubleshooting When sp_who shows the status LOG SUSPEND, you know that users are hung and have reached the last chance threshold. SQL Server writes messages to the error log, listing the tasks that are sleeping because the log is full. You can change the last chance threshold behavior for a database to the old behavior of “abort the transaction with an 1105 error” by setting “abort xact when log is full” with sp_dboption.
If Your Current Version is 4.x or 10.x Adaptive Server Enterprise 11.5 Table 6-2: New reserved words in 11.0 online Configuration Interface Prior to release 11.x, SQL Server stored configuration values in the first page of the master device, known as the “configuration block”. Most of these values have been moved to the configuration file, $SYBASE/SERVER_NAME.cfg. Setting Configuration Parameters Beginning with release 11.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x or 10.x New Names for Existing Parameters Many configuration parameter names changed in version 11.0. If you have scripts that use sp_configure to set or report on configuration parameters, you may need to change them if the parameter names have changed.
If Your Current Version is 4.x, 10.x or 11.0.x Adaptive Server Enterprise 11.5 Table 6-3: Old and new configuration parameter names Old Name New Name remote access allow remote access remote connections number of remote connections remote logins number of remote logins T1204 (trace flag) print deadlock information T1603 (trace flag) allow sql server async i/o T1610 (trace flag) tcp no delay Databases Online / Offline Beginning with SQL Server 11.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x, 10.x or 11.0.x • isnull Function in Object Code 6-11 • Increased Memory Requirements 6-12 • Additional Devices and Databases 6-17 • Optional Space Considerations 6-18 New Reserved Words in Version 11.5 The following words are reserved as of version 11.0 and can no longer be used as database object, user, group, or role names: Table 6-4: New reserved words in 11.
If Your Current Version is 4.x, 10.x or 11.0.x Adaptive Server Enterprise 11.5 procedures, views, triggers, rules, and defaults. The problem is due to a bug that caused query trees of objects containing isnull to be stored incorrectly.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x, 10.x or 11.0.x Space in your machine’s physical memory (RAM) is apportioned roughly as depicted in Figure 6-1. Operating System Server Executable Server Processes and Structures Procedure Cache Shared Memory Data Cache Figure 6-1: Areas of memory The areas and their functions are: Operating system. This part of the physical space is used by the operating system and other non-Sybase processes running on the machine. Shared memory.
If Your Current Version is 4.x, 10.x or 11.0.x Adaptive Server Enterprise 11.5 I/O the server has to do and the faster it performs. However, increasing the data cache size past an optimum amount leads to diminishing returns and wasted space. ➤ Note This outline of memory does not include other Sybase products that may be running on the same machine. You need to take these into account when planning to increase memory.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x, 10.x or 11.0.x The following table shows you how to interpret these entries: Table 6-5: Interpreting size of memory structures Structure What it is Units proc buffers Contains information about a compiled object. There is one proc buffer for each instance of an object in memory. The number of proc buffers is the maximum number of procedures that can be in memory at one time.
If Your Current Version is 4.x, 10.x or 11.0.x Adaptive Server Enterprise 11.5 Use master instead of sybsystemprocs to run these commands on a 4.x server. This dbcc command reports the number of memory pages used for the stored procedure whose object ID you enter. Compare results on the old and new systems. Use these results to estimate the procedure cache size needed to accommodate the objects such as triggers, views, and stored procedures that you want to hold in cache simultaneously. 3.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x, 10.x or 11.0.x ➤ Note When you increase total memory, you probably don’t need to keep the same procedure cache percent value. A larger data cache can give you performance improvements, but increasing procedure cache in the same proportion may not improve performance and can waste space. If you double your memory and your current procedure cache percent is 20, you can try changing it to 10 or 15 for the same performance.
If Your Current Version is 4.x, 10.x or 11.0.x Adaptive Server Enterprise 11.5 which to extend the database, be sure to extend the log segment onto the new device. When you extend a database to a separate device with the alter database command, the command extends only the data segment, not the log segment. This can cause out-of-space errors for the log. See the installation guide for more information about this database.
Adaptive Server Enterprise 11.5 If Your Current Version is 4.x, 10.x or 11.0.x ➤ Note Parallel processing of inserts is not currently supported. See the Performance and Tuning Guide for in-depth information about parallel processing. dbcc checkstorage Disk Space and Memory A new dbcc function, dbcc checkstorage, provides the functionality of dbcc checktable and checkcatalog, without some of the drawbacks, such as poor performance and occasional spurious errors, of these earlier commands.
If Your Current Version is 4.x, 10.x or 11.0.x Adaptive Server Enterprise 11.5 Memory You need a dedicated named cache for dbcc checkstorage and it must have a 16K buffer pool. The size of the named cache used should be 20% of total workspace size or 640K per worker process (for parallel dbcc). You have to configure a maximum number of worker processes for dbccdb for each target database. These worker processes allow dbcc checkstorage to perform its checks in parallel, improving performance.
7 Test: Ensuring Stability and Performance 7. Introduction This chapter will help you evaluate testing methods and develop a testing plan.
Setting Up the Test Environment Adaptive Server Enterprise 11.5 Setting Up the Test Environment Ideally, you should set up a dedicated hardware configuration (including subnets) and SQL Server exactly like your production system. Creating an identical system lets you make valid comparison, perform real tuning as part of migration effort, and if you wish to do so, switch the test system to production later on. Make Backups Make backups of the production system.
Adaptive Server Enterprise 11.5 Prioritizing Applications to be Tested 1. Create the database with the for load option of the create database command. 2. Load the backups you made of the production database. 3. Issue the online database command. This command automatically upgrades the database if it is not at the 11.5 level. The online database command was added in SQL Server 11.0. For command syntax, see the Reference Manual.
Developing Fallback Procedures Adaptive Server Enterprise 11.5 - For an around-the-clock operation, you may decide that an initial goal of breaking even is reasonable. • For cutover without replication, gear the migration for equivalent performance between the old and new systems. A goal of breaking even the first week is reasonable. • A phased cutover is subject to the highest performance expectations. Some performance tuning of the production workload after cutover of the production server may be best.
Adaptive Server Enterprise 11.5 Summary of Testing Techniques • Production load capture Table 7-1: Summary of testing techniques Technique Description Advantages Disadvantages Ad hoc testing Manually walk through important application processes, screens, and reports. • Easy to implement. • For complex applications, code coverage is too small. • Tests front-end applications and back-end servers. • Difficult to distinguish front-end and back-end bottlenecks if response time is a determining factor.
Writing Performance Scripts Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.5 Writing Performance Scripts • Add function to funcs.
Writing Performance Scripts Adaptive Server Enterprise 11.5 Deadlock Handling You have to resubmit deadlocked transactions to get a realistic time measurement. You can count average response time with and without deadlocks. Result Handling You can capture query results by fetching the entire result set back to the client and binding it to local variables. Outputting results to a file may increase your time by requiring additional file I/O and operating system buffering.
Adaptive Server Enterprise 11.5 Test Cycle: Summary of Tests Using a separate file for each transaction can prevent skewing; however, it requires additional code to sync users. Using a separate file for each user is more work to create but also prevents skewing Using a memory generator works well to prevent skewing and makes the test easy to administer. However, the benchmark is then not 100% repeatable.
Test Cycle: Testing for Performance Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.5 Test Cycle: Testing for Performance • Post-Upgrade Multi-User Tests 7-13 ➤ Note Benchmarks test server processing speed. Therefore, run your benchmarks from a light-weight client so that all processing happens at the back-end.
Test Cycle: Testing for Performance Adaptive Server Enterprise 11.5 Untimed Benchmarks In multi-user mode, run untimed benchmarks as often as necessary, capturing response time and throughput metrics. Resolve: • Differences, such as cache hit rate, to the production system • Bottlenecks caused by saturated devices • Other problems or mistakes Between runs, restore your databases from backup or perform a quick reset.
Adaptive Server Enterprise 11.5 Test Cycle: Testing for Performance Post-Upgrade Single-User Tests In single-user tests, verify that: • The optimizer is creating the same or better query plans. Differences in query plan may be due to optimizer changes, depending on the SQL Server version you were running. Determine whether you need to change application code to compensate. See Chapter 7, “Test: Ensuring Stability and Performance”, for possible optimizer issues.
Test Cycle: Testing for Performance 7-14 Test: Ensuring Stability and Performance Adaptive Server Enterprise 11.
A The Cyrano Migration Pack A. What is the Cyrano Migration Pack? The Cyrano Migration Pack (CMP) is a comprehensive and integrated range of industry leading tools for accelerating, securing and certifying any migration from Sybase SQL Server 4.x, 10.x, or 11.0.x to Sybase Adaptive Server Enterprise 11.5. Cyrano Migration Pack is fully available today.
Benefits of the Cyrano Migration Pack Adaptive Server Enterprise 11.5 • Provides deployment testing with an explanation of unacceptable response times by identification of faulty transactions • Lets you reuse Cyrano WorkBench and Cyrano Production after Migration for development, testing and monitoring application databases on Adaptive Server 115. • Supports any Sybase migration strategy and methodology Benefits of the Cyrano Migration Pack The Cyrano Migration Pack...
Adaptive Server Enterprise 11.5 For More Information Looks for new reserved words, optimizer strategy issues, correlation names, subqueries, column headings, and SQL ANSI comments in your scripts or databases. • Cyrano Xact(ResDiff) Compares results set differences between Pre-11.5 releases of SQL Server and Adaptive Server 11.5. • Cyrano Xamine(TechDiff) and Xpose Compares performance differences in logical I/Os and client response time between Pre-11.5 releases of SQL Server and Adaptive Server 11.5.
For More Information A-4 Adaptive Server Enterprise 11.
B Worksheets for Your Current Environment B. This appendix provides guidelines and sample worksheets for gathering information that will help you in planning for migration. The topics are: • SQL Server Operational Worksheets B-1 • Data Architecture Worksheet B-6 • SQL Server Infrastructure Worksheets B-9 As part of migration planning, see the Sybase Migration Resource Guide at http://www.sybase.com/migration for current offerings.
SQL Server Operational Worksheets Adaptive Server Enterprise 11.5 Operational Business Requirements General operational information can help with documentation and success criteria.
Adaptive Server Enterprise 11.5 SQL Server Operational Worksheets Backup and Restore Procedures This worksheet is helpful for a general survey of your backup and restore procedures.
SQL Server Operational Worksheets Adaptive Server Enterprise 11.5 Database Dump Details Detailed backup and restore information is helpful for defining success criteria.
Adaptive Server Enterprise 11.5 SQL Server Operational Worksheets Maintenance Procedure Details Detailed maintenance information is helpful for defining success criteria. Table B-4: Maintenance Database/Object Name Frequency of dbcc checkdb/checktable Frequency of dbcc checkalloc/tablealloc Frequency of update statistics Migrating to Sybase Adaptive Server Enterprise 11.
Data Architecture Worksheet Adaptive Server Enterprise 11.5 Data Architecture Worksheet Documenting the flow of information is discussed in Chapter 2, “Analyze: Documenting Business Requirements”. The Data Architecture Worksheet includes these sections: • Client Application Components B-6 • Production Performance Metrics B-7 • Transaction Profile B-8 Client Application Components Application profile and performance requirements information is helpful for defining success criteria.
Adaptive Server Enterprise 11.5 Data Architecture Worksheet Production Performance Metrics Measure current production performance metrics, using operating system monitors, for the following: • CPU Measure the average and maximum CPU utilization (aggregate and per CPU on SMP servers) per “time window” (online, batch, and so on) per server. • Disk I/O - Measure I/Os per second per disk and controller, and I/O queue lengths per “time window” per server.
Data Architecture Worksheet Adaptive Server Enterprise 11.5 Transaction Profile Repeat this section for each database on the SQL Server. Application processing information at the transaction level is helpful for documentation and success criteria. Table B-6: Transaction profile Process (Xact) Name Process Type (OLTP, batch) Xact Priority Frequenc y per User (per hour) Source Code Type (stored procedure, ESQL, etc.
Adaptive Server Enterprise 11.5 SQL Server Infrastructure Worksheets SQL Server Infrastructure Worksheets Documenting your environment is discusses in Chapter 4, “Prepare: Writing a Plan and Getting Ready to Migrate”. The infrastructure worksheets include: • Host Configuration B-9 • SQL Server Configuration B-17 • Database Devices B-19 • Databases and Segments B-20 • Dump Devices B-21 Record this information for both the production and development environments.
SQL Server Infrastructure Worksheets Table B-7: Host hardware SQL Server Machine Technical account manager: • Name • Phone number (s) • Pager number Record CPU resources:. Table B-8: CPU resources CPU Number of physical processors: Chip speed: Number of processors available to SQL Server: Other CPU-intensive processes/threads: Processes/threads bound to specific CPUs: B-10 Worksheets for Your Current Environment Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.5 SQL Server Infrastructure Worksheets Table B-8: CPU resources CPU Processes/threads run at high priority: Physical Memory Usage List all the major processes and memory requirements running on each server. See the formulas in Table 3-7: Server memory map. Table B-9: Runtime memory usage Application Runtime Memory Usage Operating system Networking software SQL Server memory parameter Migrating to Sybase Adaptive Server Enterprise 11.
SQL Server Infrastructure Worksheets Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.5 SQL Server Infrastructure Worksheets Disk I/O Configuration General disk information can help with firmware incompatibilities and capacity planning. Table B-10: Disk I/O Controller Number Firmware Revision Make and Model Months in Service Transfer Rate (KB/sec) The following disk layout information can help with firmware incompatibilities, nearing Mean-time Between Failures (MTBF), load balancing, and capacity planning.
SQL Server Infrastructure Worksheets Adaptive Server Enterprise 11.5 The following disk layout information can help in case redistribution is required, load balancing, and capacity planning. Table B-12: Disk and partitions Physical Device Name Partition Number Used by (Sybase, UNIX File System, etc.) Device Name OS Mirrored Device Name Capacity (MB) Cylinder Range The following logical volume information can help in case redistribution is required, load balancing, and capacity planning.
Adaptive Server Enterprise 11.5 SQL Server Infrastructure Worksheets Network Configuration Network layout information can help with firmware incompatibilities, MTBF, and capacity planning. Table B-14: Network layout Physical Device Name Make and Model Firmware Revision Months in Service Supported Protocols Network Address Transfer Rate (KB/second) Tape Configuration Tape layout information can help with firmware incompatibilities, MTBF, and capacity planning.
SQL Server Infrastructure Worksheets Operating System Configuration Detail the operating system. Table B-16: OS details Server Hardware Name: Release level: Patch history: Kernel configuration parameters: Swap space size: Technical support phone number: Technical support hours: Technical account manager: • Name • Phone number(s) • Pager number B-16 Worksheets for Your Current Environment Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.5 SQL Server Infrastructure Worksheets SQL Server Configuration Document general information about the SQL Server configuration. Table B-17: SQL Server configuration General Configuration Home directory: Components, release and fix levels: Locations/names of scripts to rebuild database environment: sp_configure configuration values: buildmaster configuration values: Migrating to Sybase Adaptive Server Enterprise 11.
SQL Server Infrastructure Worksheets Adaptive Server Enterprise 11.5 Run dbcc memusage on SQL Server during an off-peak time or in single-user mode.
Adaptive Server Enterprise 11.5 SQL Server Infrastructure Worksheets Database Devices Database device information can help in case redistribution is required, load balancing, and capacity planning. Table B-19: Database devices Database Device Name Physical Device Name Virtual Device Number Capacity (2K Pages) Migrating to Sybase Adaptive Server Enterprise 11.
SQL Server Infrastructure Worksheets Adaptive Server Enterprise 11.5 Databases and Segments Database and segment information can help with load balancing and capacity planning.
Adaptive Server Enterprise 11.5 SQL Server Infrastructure Worksheets Dump Devices Dump device information can help with load balancing and capacity planning. Table B-21: Dump devices Database Device Name Physical Device Name Migrating to Sybase Adaptive Server Enterprise 11.
SQL Server Infrastructure Worksheets B-22 Worksheets for Your Current Environment Adaptive Server Enterprise 11.
C Sample Migration Plan Outline C. The following is a high-level migration plan produced by Sybase Professional Services for a Sybase customer. The plan is for a migration from SQL Server 4.9.2 to SQL Server 11.0.x and reflects site-specific needs. The plan touches on technical issues specific to an 11.0.2 migration and does not address technical issues particular to Adaptive Server 11.5. For information on technical changes in Adaptive Server 11.
Migration Approach Outline Adaptive Server Enterprise 11.5 After the successful compilation of all database objects, a Sybasesupplied system stored procedure will be run to identify changes due to subquery processing. Differences in SQL Server 11 can cause unexpected result to be returned by queries.These changes will be made manually after identification. Finally, some changes will neither be identified nor fixed during this phase of the migration process.
Adaptive Server Enterprise 11.5 Migration Approach Outline Example: update tableA set field1 = (select max(field1) from tableB TB where tableA.field = TB.field1)˙ • Identify subqueries using distinct/in/not/exists/any clauses. Example 1: select count(*) from publishers where exists (select * from titles where titles.pub_id = publishers.pub_id) Example 2: select pub_name from publishers where pub_id in (select distinct pub_id from titles where titles.pub_id = publishers.
Migration Approach Outline Adaptive Server Enterprise 11.5 PHASE 5: Other Issues Not Checked For or Changed Some issues will not be detected automatically or manually by Sybase staff.
D Sample Migration Task Lists D. In This Appendix This appendix provides the following samples: Sample Task List Template D-1 General Migration Task List Example D-1 Parallel Migration Task List Example D-8 Cutover Migration Task List Example D-13 Staged Cutover Task Overview D-17 Sample Task List Template The following table is a suggested format for detailing the tasks you need to perform as part of your migration.
General Migration Task List Example Adaptive Server Enterprise 11.5 Migration Analysis The following tasks are related to analyzing the environment: Document Current Configuration 1. Establish cutoff point for environment. 2. Document current server installation information. 3. Document current server configuration values. 4. Document hardware configuration. 5. Document applications per server. 6. Document application server requirements. 7. Document application client requirements. 8.
Adaptive Server Enterprise 11.5 General Migration Task List Example 3. Analyze other Sybase software compatibility. 4. Analyze non-Sybase software compatibility. 5. Analyze middleware/API compatibility. 6. Analyze communications compatibility. 7. Analyze client platforms compatibility. 8. Document analysis results. 9. Review compatibility analysis. 10. Update compatibility analysis. Develop Migration Strategy 1. Draft migration strategy. 2. Review strategy (team). 3. Update strategy (team). 4.
General Migration Task List Example Adaptive Server Enterprise 11.5 Prepare Applications For Migration 1. Search for new reserved words. 2. Search for subqueries. 3. Verify new database conversion and computations. 4. Search for set, arithabort, and arithignore. 5. Search for select into with NULL column headings. 6. Design code changes. 7. Get user signoff on application changes. Design and Develop Server Migration Scripts 1. Design and develop server configuration scripts. 2.
Adaptive Server Enterprise 11.5 General Migration Task List Example 4. Set up source code control environment. 5. Set up new user environment. 6. Develop other migration aids. Implement Migration (Using Install/.Load Technique) Create Target Environment 1. Verify target system readiness. 2. Move migration scripts to target system. 3. Configure file system. 4. Complete installation/upgrade worksheet from installation guide. 5. Install Adaptive Server, Backup Server, and Open Client.
General Migration Task List Example Adaptive Server Enterprise 11.5 Complete Server and Data Migration 1. Create indexes and triggers. 2. Run dbcc commands. 3. Dump databases. Perform Application Migration 1. Develop application code changes. 2. Unit test application changes. 3. Analyze and correct application changes. 4. Configure applications to access new server. 5. Perform preliminary tuning. 6. Allow user access to system and server. 7. Insure no access to obsolete files. 8.
Adaptive Server Enterprise 11.5 General Migration Task List Example 4. Configure applications to access new server. 5. Perform preliminary tuning. 6. Allow user access to system and server. 7. Insure no access to obsolete files. 8. Notify users of availability. Migration Quality Assurance Perform System Tests 1. Perform functional tests. 2. Compare functional test results to baseline. 3. Analyze functional test results. 4. Perform corrective actions. 5. Retest as necessary. 6.
Parallel Migration Task List Example Adaptive Server Enterprise 11.5 5. Retest as necessary. 6. Document capacity test results. 7. Obtain user signoff on capacity test results. Perform User Acceptance Tests 1. Perform acceptance tests. 2. Compare acceptance test results to baseline. 3. Analyze acceptance test results. 4. Perform corrective actions. 5. Retest as necessary. 6. Document acceptance test results. 7. Obtain user signoff on acceptance test results. Perform Production Data Refresh 1.
Adaptive Server Enterprise 11.5 Parallel Migration Task List Example Define Test/Acceptance Criteria—Regression Test Suites Back-end regression test suite—production loads 1. Identify back-end queries. 2. Encapsulate back-end queries. 3. Create back-end test suite (showplan, stat io, and stat time wrappers). Front-end simulation regression test suite 1. Identify target user functions. 2. Capture and map SQL for target user functions. 3. Encapsulate SQL for user functions. 4.
Parallel Migration Task List Example Adaptive Server Enterprise 11.5 10. Synchronize user IDs. 11. Run checkpoint. Set Up Replication Server 1. Install Replication Server on Server B. 2. Configure Replication Server on Server B. 3. Install Replication Server on Server A. 4. Configure Replication Server on Server A (secondary). 5. Verify replication functionality between the two servers. 6. Test replication on target objects. 7. Verify and checkpoint replication-ready environment. 8.
Adaptive Server Enterprise 11.5 Parallel Migration Task List Example 5. Verify and document SQL Server 10.x regression test. 6. Verify performance and functions on Server B’s SQL Server 10.x. Upgrade SQL Server on Server B (Shadow) 1. Alter sybsystemprocs. 2. Perform pre-upgrade verification. 3. Upgrade Server B to Adaptive Server 11.5. 4. Load Adaptive Server 11.5 environment from media dumps. 5. Run dbcc commands (checktable, checkalloc, checkcatalog). 6. Set baseline Adaptive Server 11.
Parallel Migration Task List Example Adaptive Server Enterprise 11.5 Front-End Phase 1 and 2 Regression Test Suite 1. Perform Phase 1 and 2 regression testing. 2. Monitor and capture system dynamics (sp_who, sp_lock...). 3. Verify and document this SQL Server 10.x regression test. Other Testing Verify 11.5 performance and functions. Run User Acceptance Tests on Adaptive Server 11.5 (Server B) 1. Production testers perform user regression testing of shadow Release 11.5. 2.
Adaptive Server Enterprise 11.5 Cutover Migration Task List Example Cutover Migration Task List Example This sample task list covers a cutover migration approach to upgrade SQL Server 10.x to Adaptive Server 11.5. The scenario for this task list is: • The mid-sized company requires a simple, somewhat faulttolerant upgrade. • In the event of failure, the company depends on nightly backups. The system cannot be down more than one hour and cannot afford more than eight hours of data loss.
Cutover Migration Task List Example Adaptive Server Enterprise 11.5 Define Test/Acceptance Criteria—Regression Test Suites Front-end simulation regression test suite 1. Identify target user functions. 2. Capture and map SQL for target user functions. 3. Encapsulate SQL for user functions. 4. Create front-end simulated test suite (showplan, stat io, and stat time wrappers). Front-end regression test suite 1. Identify front-end test scenarios. 2.
Adaptive Server Enterprise 11.5 Cutover Migration Task List Example Front-end Regression Test Suite 1. Perform regression testing for local team. 2. Monitor and capture system dynamics (sp_who, sp_lock...). 3. Make the “go/no go” call for the Release 10.x test system. 4. Verify and document this SQL Server 10.x regression test. 5. Verify performance and functions on the test SQL Server 10.x. Upgrade Release 10.x Test System to Release 11.5 1. Perform a dump of the Release 10.x databases. 2.
Cutover Migration Task List Example Adaptive Server Enterprise 11.5 Front-End Regression Test Suite 1. Perform user regression testing. 2. Monitor and capture system dynamics (sp_who, sp_lock...). 3. Verify and document Adaptive Server 11.5 regression test. Other Testing Verify 11.5 performance and functions on the test system. Run User/Acceptance Tests on the Release 11.5 Test System 1. Testers perform user regression testing of Release 11.5. 2. Monitor and capture system dynamics (sp_who, sp_lock...).
Adaptive Server Enterprise 11.5 Staged Cutover Task Overview 5. Upgrade the production system to Release 11.5. 6. Run dbcc commands (checktable, checkalloc, checkcatalog) on the Release 11.5 databases. Be sure to verify the dbcc log and error log. 7. “Baseline” the Release 11.5 configuration on the production system. 8. Perform user testing and verification. Perform Final Steps 1. Start production users on the Release 11.5 production system. 2. Upgrade the test system to Release 11.5.
Staged Cutover Task Overview Adaptive Server Enterprise 11.5 6. Configure a duplicate Adaptive Server 11.5 on the test platform. 7. Migrate the test/acceptance objects to the Release 11.5 test system. 8. Conduct regression test suites on the test system. 9. Verify synchronization of objects between the Release 10.x and 11.5 test systems. 10. Conduct user acceptance testing on the Release 11.5 test system. 11. Configure a duplicate Adaptive Server 11.5 on the production platform. 12.
E Migration Plan Checklist E. The following checklist, produced by Sybase Professional Services, can be used to verify that you have covered all important issues in your migration plan. For more information on writing a migration plan, see Chapter 4, “Prepare: Writing a Plan and Getting Ready to Migrate”.
Logical Technology Architecture Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.
Migration Strategy Design E-4 Adaptive Server Enterprise 11.
Index A Applications documenting 3-7 Applications, changing 5-1 to 5-25 from version 4.x 5-1 to 5-25 from version 10.x 5-16 to 5-25 expression subqueries 5-17 no set dup in subquery 5-17 NULL results 5-18 reserved words in 11.0 5-16 reserved words in 11.5 5-19 subqueries in updatable cursors 5-18 subquery processing 5-17 union limitations 5-18 from version 11.0.x 5-19 to 5-25 programming tips 5-21 to 5-25 results order with clustered indexes 5-21 two-phase commit 5-21 from version 4.
Adaptive Server Enterprise 11.5 D Database administration, changes 6-1 to 6-20 from version 4.x 6-1 to 6-20 Backup Server 6-5 create table permission 6-4 last chance threshold 6-5 to 6-7 login/password protocols 6-2 RUN file 6-3 stored procedure database 6-3 to 6-4 from version 10.x 6-7 to 6-20 configuration file 6-8 configuration interface 6-8 to 6-10 configuration parameter name changes 6-9 online database command 6-10 reserved words 6-7 from version 11.0.
Adaptive Server Enterprise 11.5 L N Last chance threshold 6-5 to 6-7 and bulk copy 6-7 open transactions 6-6 sample procedure 6-6 troubleshooting 6-7 Logins, changes from 4.x 5-3 Network configuration, documenting 3-4 M Memory areas of memory 6-12 increases in 11.
Adaptive Server Enterprise 11.5 system stored procedures 3-11 system tables 3-11 third-party tools 3-12 server object creation 3-10 to 3-12 to build 11.5 server 4-10 sp_thresholdaction procedure 6-5 Subquery processing, changes from version 10.x 5-17 to 5-19 expression subqueries 5-17 no set dup in subquery 5-17 NULL results 5-18 union limitations 5-18 updatable cursors 5-18 from version 4.
Adaptive Server Enterprise 11.5 disk and firmware use B-13 disk I/O B-13 disk partitions B-14 dump devices B-21 logical volumes B-14 maintenance B-5 network layout B-15 operating system B-16 operational requirements B-2 runtime memory usage B-11 segments B-20 server configuration B-17 server host hardware B-9 tape layout B-15 transaction profile B-8 Migrating to Sybase Adaptive Server Enterprise 11.
Adaptive Server Enterprise 11.