Reviewing the Results
After the upsizing operation is complete, you can immediately see two changes. The first change is a new set of tables in the Access table container. The native Access tables now have a _local suffix, and the original table names are linked to SQL Server (the globe icon identifies the connection). The second change is in SQL Server Enterprise Manager, where you can see NorthcopySQL in the database list. (If you don't see the new entry after refreshing the Database folder, try stopping and restarting the server.)
This example upsizing operation will give you an idea of what is involved in migrating an Access 97 database to SQL Server. With the Upsizing Wizard, you can automate the migration of a set of database tables and related objects (the schema) and accompanying user data from Access to SQL Server. The Upsizing Wizard lets you quickly copy a database from Access into SQL Server. It even contains functions that automatically detach your forms and reports from the Access data and reattach them to the SQL Server copy of the data, leaving your Access user interface totally functional after the upsizing.
Wizard Gotchas
The Access Upsizing Wizard is convenient to use; however, Microsoft created it to serve the mass market. It might not handle data-type conversions exactly the way you want them done. Also, you can create objects and references in Access that violate the ANSI standards for database integrity. These violations cause problems when you try to upsize to the SQL Server environment, which can mean an incomplete upsizing operation or unsatisfactory performance after upsizing is complete. Let's look at some examples.
- You can use blanks in Access table and column names, but SQL Server doesn't support this scheme for its object names. Also, the maximum length for table or column names in Access is 64 characters; the maximum length for those names in SQL Server 6.5 is 30 characters. Any violation of SQL Server naming conventions results in the Upsizing Wizard taking two related actions. First, the wizard substitutes an underscore (_) for any blank or unsupported special character in the table or column name. Then, if the modified table or column name exceeds 30 characters, the wizard creates an aliasing query for Access, which maps the new name to the old, so that the user interfaces in Access still work. Unfortunately, this aliasing query can cause performance problems because of the extra layer of translation the query imposes.
- The upsizing process converts all columns that are of the Access text data type to a SQL Server variable character (varchar) data type. This choice is usually good, but for primary and foreign keys, which you use often for searches and retrievals, frequent compression and decompression can diminish performance. For columns 1, 2, or 3 bytes long or for columns that usually occupy the entire data length, this conversion can cause increased data-storage requirements. For more information on data-type conversion, see the sidebar "Data-Type Conversion."
- Access lets a foreign-key data type differ from its corresponding primary-key data type. So in Access, the referencing table's foreign key of type char(20) can reference a primary key that is char(18). SQL Server balks at accepting referencing tables and referenced columns of differing data types and lengths. When the Upsizing Wizard encounters this situation, it doesn't convert the dependent table. Then, any other table in the database that is dependent on the unconverted table also isn't converted, because it references a table that doesn't exist in SQL Server.
- In SQL Server, you need to use triggers for any enforced referential integrity in Access that includes a cascade update or cascade delete. You can't use the declarative referential integrity (DRI) feature, which is a method of the SQL Server database engine. Although DRI runs much faster than triggers and is more consistent in its operation, it doesn't support cascading operations in SQL Server 6.5, so you must choose triggers for the conversion.
- The Access column name LineNo is an undocumented reserved word for SQL Server and thus isn't accepted for the upsizing operation; you must modify it in Access before upsizing. Also, you need to scan your Access database for any documented SQL Server reserved words and change them before upsizing.
- You must declare a primary key (or at least a unique index) for every Access table, or the Upsizing Wizard won't upsize that table to SQL Server.
- The upsizing process converts all Access indexes, either system-created or user-created, to SQL Server indexes, thus causing redundancy in the SQL Server indexes. Access automatically creates a not-null unique index on any column designated as a primary key and on any column that an enforced relationship designates as a foreign key. The Access index editor doesn't list the foreign-key indexes. A conscientious Access developer who doesn't know that an index already exists on the foreign-key column might create another index on the foreign-key column. When you look at the Upsizing Report, you'll see a great redundancy in the number of indexes, especially on the foreign-key columns.
- The Upsizing Wizard tries to evaluate the dependency hierarchy in an Access database, but it doesn't do it well. The dependency hierarchy consists of tables that refer to one another. The referenced table, the master table, is at the top of the hierarchy. The referencing table, the detail table, is below the master table in the hierarchy. When you load data into SQL Server, if you try to load a detail table before its corresponding master table, the referencing that results from enforced referential integrity is violated, and the table load fails. Thus, an upsizing attempt that includes user data will fail to convert some tables because of mistakes the Wizard made in evaluating the dependency hierarchy.
- Bad dates can cause an upsize attempt to fail. Bad dates result from the differences between the Access date data type and the SQL Server datetime data type. Access valid dates range from 1/01/100 to 12/31/9999, whereas SQL Server valid dates range from 1/01/1753 to 12/31/9999. If for some reason Access data contains dates earlier than 1/01/1753, SQL Server flags these dates as incompatible with the datetime data type, and doesn't convert the table completely. The error message reads Subscript out of range and Skipping table. If the table containing the bad dates is a master table in the dependency hierarchy, all detail tables that reference this master table also fail to convert.
- The wizard doesn't completely upsize the enforced recursive relationships you establish in an Access table. In a recursive relationship, the referenced table and the referencing table are the same. If you have a table of employees, in which each employee has a unique employee number (empid) and each employee record contains the employee number of the employee's manager, the employee table has a recursive relationship with itself. Successfully upsizing this table requires loading all the manager records first, then loading all the non-manager records, so that the referenced empid values are present in the table when you load the referencing empid values. Any attempt to upsize this kind of table while it includes user data will fail.
- SQL Server doesn't support Access hyperlinks and thus doesn't upsize them.
- The wizard doesn't upsize validation rules you declare in an Access table; you have to manually code triggers to establish those validation rules in SQL Server. If you haven't written SQL Server triggers before, the task can be daunting because some Access validation rules are quite complex.
An Alternative Method
The upsizing gotchas listed above don't have to spell gloom and doom for your migration plans. You can use another, more complex, but more tailored, method to upsize an Access database to SQL Server. You can use the Upsizing Wizard to begin the process, then use other tools that Access and SQL Server provide to finish the job. In this procedure, you need to code several SQL scripts.
Prepare for the conversion. For this conversion scheme, you need three SQL Server databases: two to hold intermediate results and the one final target database. Name the databases that will hold the intermediate results TestSQL and DeveloperSQL, and name the target database ProductionSQL. Check that you've set the properties for the TestSQL database to Truncate Log on Checkpoint, so that its transaction log doesn't fill up. On the Access side, run a full Documenter report (Tools, Analyze, Documenter) so that you'll have a record of the database—all the tables and columns, their properties, the intertable relationships, the indexes, and the user and group permissions.
Use the Upsizing Wizard to copy the database schema. Use the Access Upsizing Wizard to migrate just the structure (the schema) of the Access database to the TestSQL database. As you see in Screen 10, check the option Only create table structure, don't upsize any data. Print the upsizing report that this operation generates.
Use Transfer Manager to create SQL scripts. Use the SQL Server Transfer Manager to copy the database schema from TestSQL to DeveloperSQL. In the process, the transfer operation generates a sequence of logs that you can edit. You initiate the Transfer Manager from Enterprise Manager, Tools, Database/Object Transfer. Screen 11 shows the resulting dialog box. Transfer the schema only. (You have no user data to transfer because the Upsizing Wizard carried none forward.) Save the logs to a directory for later modification (the default directory is \\SQLServer\log).
Modify the Transfer Manager scripts. After the transfer operation is complete, go to the directory you placed the transfer logs in. The log files that the transfer operation generates have a specific naming convention, database_server_name.source_database_name.extension. For example, a transfer operation that takes place on the BlackTower server and migrates the Pubs database generates a set of log files named BlackTower.Pubs.XXX, where XXX is an extension listed in Table 1, page 32. Table 1 lists the log files by extension, with a description of the SQL script in each file. Don't edit the original log files; make copies, and edit those.
Transfer Manager evaluates the dependencies between tables and lists the tables in hierarchy dependency order in these SQL scripts—either master table before detail or vice-versa, depending on the needs of the script. You need to evaluate the .TAB file, which is a script of the CREATE TABLE statements, and adjust any data-type discrepancies you find (char instead of varchar, for instance). Also, evaluate the .DR1 and .DR2 files. These scripts contain the primary-key constraints, which are applied with ALTER TABLE statements. Adjust the entries according to your needs. The .FKY file contains a script of all the foreign-key constraints, which you might also need to modify. To check the defaults, evaluate the .DEF and .BND files, which contain scripts to assign and bind default constraints. Pay close attention to .ID1 and .ID2, the CREATE INDEX files. If duplicate indexes existed in the Access database, the Upsizing Wizard carried them forward. These indexes show up in the index script files.
Create the Final Database. After you edit all the scripts to meet your needs, copy each script into an ISQL/w window, and run the edited scripts against the final database, ProductionSQL. Run each script or section of script alone. Although this process might seem tedious, it's the only way to catch errors when they occur. SQL Server returns the anonymous message This command did not return data, and it did not return any rows on successful execution of the command. The message doesn't specify which table, index, or constraint was created. Likewise, if the execution was unsuccessful, SQL Server doesn't name the table, index, or constraint involved.
Now, remove the Wizard-upsized tables from the TestSQL database. In Enterprise Manager, highlight the TestSQL database and open the table list. Hold down the Shift key, highlight all the tables, then (still holding down the Shift key) right-click and select Drop All.
Use the Access Export facility to copy user data. Access has an Export function, which you can use to export a copy of the user data from Access directly into the TestSQL database. Open Access, and from the top menu, choose File, Save As/Export, To an External File or Database. When the Save Table In dialog box opens, drop down the Save As Type combo box and choose ODBC Databases, as in Screen 12. The Export dialog box opens immediately. Here, you can change the name of the table while it's being exported to SQL Server, although for the sake of continuity, leave the table name in SQL Server the same as it was in Access (if it follows the SQL Server naming conventions). If the default login to SQL Server is a standard connection, you need to provide a password to SQL Server and choose a target database (TestSQL).
You can temporarily modify the ODBC data-source properties to facilitate this export process. Open the Control Panel, then edit the ODBC Source to reflect a trusted connection and identify the target database for the load, as in Screen 13. You can lengthen the timeout period if you have large tables to load. These simple changes make the export operation run much more quickly.
Create and run insert scripts. In a Notepad session, create a set of SQL insert statements, one insert command per table, following the examples in WebSQL Listing 1, which you can download at http://www.sqlmag.com. Follow the dependency hierarchy that was established and recorded in the .TAB log file, which the Transfer Manager execution generated. Copy the insert script from Notepad into the ISQL/w window, and run each INSERT statement separately, copying the data from the TestSQL database to the ProductionSQL database. If the data is clean and accurately represents all relationships, the insert operations succeed.
Jump Start on Migration
The Access Upsizing Wizard is the quickest, most convenient method for reproducing an Access database in SQL Server. As part of this process, the user interface of the Access database application disconnects from the Access data and reconnects to the SQL Server data, so the functionality of the user interface is minimally disrupted. However, the assumptions the Upsizing Wizard makes about how to handle some upsizing data types and structures might prevent it from being the best tool for you. In that case, you can use the manual procedure we outlined in this article.
The Access 97 to SQL Server 6.5 Upsizing Tool isn't perfect, but it's a quick way to remap your Access database structures to a SQL Server schema. You must define primary keys for all the tables that you migrate. You need to tweak and adjust the resulting SQL Server database after you migrate your Access database, and your Access interface might need some code adjustments to operate flawlessly. However, this upsizing tool gives you a jump start on migrating from a file server to a client/server system.
End of Article
Prev. page
1
[2]
next page -->
