Chapter 7 — Planning Phase: Assessing the Sybase Database Using SMT
On This Page
Introduction and Goals
The purpose of this chapter is to describe how to assess a Sybase database using the Sybase Migration Toolkit. The Sybase Migration Toolkit is available at http://www.microsoft.com/services/microsoftservices/default.mspx. The assessment will provide an understanding of all of the transformations of database objects that are necessary to migrate the database. The results of this assessment will indicate how much work it will take to migrate each database and help you finalize the project plans described in Chapter 8.
One goal during this step is to understand how much of the migration effort can be automated to reduce work. By automating tasks, you can also reduce the potential errors that manual code changes introduce.
Deciding which databases to migrate is a strategic business decision and requires careful consideration. Your plan may be to migrate every database, or perhaps to omit any databases in your environment that are slated to be retired because of product end-of-life or a change of requirements.
Developing the Solution Components
The assessment focuses on gathering information necessary to assess the database objects that may require effort, whether performed automatically (by the SMT) or manually (by a database developer).
For each target database, use the SMT's assessment function to:
Identify Sybase Transact-SQL syntax and dependent database objects that require code changes for the SQL Server environment. In the remainder of this chapter, these changes are referred to as transformations.
Document the results of the assessment data for each database; these results will be used in Chapter 9, "Developing Phase: Database." The SMT produces HTML reports for each database it assess.
Microsoft created the Sybase Migration Toolkit (SMT) to support the development effort. This tool automates many migration tasks. It comprises Perl scripts behind a Microsoft
Windows-style tabbed graphical user interface, as shown in Figure 7.1. The script behind each tab performs a specific migration function, as indicated by the tab names. The assessment function is used for Planning; other functions will be used later.
The following list describes the purpose of each tab:
Assessment. This tab identifies the specific instances of Sybase Transact-SQL syntax that you must convert.
Note This is the only functionality from the SMT that is utilized in this chapter.
Extract Schema. This tab extracts the Sybase data definition language (DDL) schema.
TSQL Conversions. This tab converts the Transact-SQL syntax using automated scripts. You can customize the scripts to extend the range of code changes that can be automated.
SQL Comparison. This tab thoroughly tests Transact-SQL behavioral and syntax changes.
Object Comparison. This tab tests the behavior of migrated schema objects to ensure they operate in the same manner as the original items in the Sybase database.
For more information about using the SMT, see Appendix I, "Sybase Migration Toolkit User Guide."
Performing an Assessment
A required task in developing the database migration solution is to analyze the database for the various database objects that require code changes. The Assessment tab of the Sybase Migration Toolkit (SMT) automates this task. It runs the Sybase to Microsoft SQL Server Analysis Wizard, which prompts you for connection information for accessing the Sybase database (you must have installed an appropriate Sybase Open Database Connectivity [ODBC] driver) and generates a report that identifies the specific issues that your migration must address. The Wizard allows you to examine every item in the database, or just selected types of objects (for example, logins, tables, views, stored procedures, and so on).
The Assessment report is created in Hypertext Markup Language (HTML) format. Figure 7.2 shows an example:
The report provides the following three types of information:
Summary Information . This section of the report lists the number of each type of database object found in the target database, the number that pose migration issues, and the percentage of the total that each number represents. These database objects will require the majority of conversion work. The Analyzed Objects section of the report provides details about each object type analyzed.
Source Configuration Information . This section displays the configuration options in effect in the Sybase database. This information can help you configure the relevant server and database options in the new SQL Server database.
Analyzed Objects Information . This section lists each object analyzed (grouped by type) and, for each migration issue identified, suggests appropriate actions.
Using the Assessment report, you can identify which issues fall into each of the following task categories so that you can estimate time frames for the actual code-change database migration tasks:
Transact-SQL conversion issues that can be performed automatically by using the Transact-SQL Transformation tab of the SMT.
Transact-SQL conversion issues that you can automate by customizing the script provided with the SMT.
Transact-SQL conversion issues that require manual coding.
Database objects that require code changes.
Interim Milestone: Database Assessments Complete
The assessment process, which began with surveying the current Sybase environment, ends when the team has used the SMT to assess all databases that have been selected for migration and documented the results. The team should now have all the information it needs to finalize the project plans.