Which Path from Access 97 to SQL Server 6.5?

Downloads
6108.zip

For small and large businesses, the pressure is on to migrate all databases to SQL Server. The recent release and widespread acceptance of SQL Server 7.0 has caused many corporate decision-makers to question the wisdom of committing critical applications and functions to desktop databases such as Microsoft Access. SQL Server is powerful and, in some situations, makes a significant difference in performance, productivity, and users' ability to share data. If you're considering migration, you probably already have an Access 97 solution that has been functional for a while.

In this article, we discuss migrating from Access 97 to SQL Server 6.5, which many companies will choose because of its proven track record and broad support base. We first look at a migration operation step by step. Then we address the concerns, limitations, and restrictions involved in using the migration tools Microsoft provides.

Upsizing Wizard

Get a jump start on migrating an Access 97 database to SQL Server by using the Microsoft SQL Server Upsizing Tool for Access 97. You can obtain this tool by going to http://officeupdate.microsoft.com/ downloadDetails/aut97.htm and downloading the executable to a separate directory on your hard disk. Then, use Windows Explorer to locate the downloaded Upsizing Wizard executable file (aut97.exe), and double-click it to activate the wizard installation. Screen 1 shows the resulting dialog box. The wizard installs as an add-in to the Microsoft Access Tools menu (Tools, Add-ins, Upsize to SQL Server), as you see in Screen 2.

The upsizing procedure is a sequence of SQL Server logged events. Because you're creating the database dynamically during this process, you don't have the opportunity to activate the Truncate log on checkpoint option, so make sure that the transaction log file is large enough to accommodate the entire procedure. For instance, you need about 3.2MB of transaction log space to migrate the Northwind database in the example that follows. The log device needs to be slightly larger than the destination database device.

Upsizing to SQL Server

Let's step through an example that uses a copy of the Access 97 Northwind sample database. You can find the original Northwind database file in \Office97 \Office\Samples\northwind.mdb.

1. In Explorer, create a new folder to hold a copy of the Northwind.mdb file to migrate. Name the new folder DatabaseUpsizeTest.
2. Copy Northwind.mdb from Samples to DatabaseUpsizeTest. To avoid confusing it with the original Northwind.mdb file, rename the copy Northcopy.mdb.
3. Start Access, then open Northcopy.mdb. Select Tools, Add-in, Upsize to SQL Server. Screen 3 shows the Upsizing Wizard opening display, which offers two options: Use an existing database, or Create a new database. Select Create a new database, and click Next.

The next screen (Screen 4) prompts you to select a data source. Choose the Machine Data Source tab, highlight LocalServer (if you're logged on to the database server), and click OK. If you're upsizing from a client machine, select your SQL database server from the machine data source list. A machine data source is specific to the computer it's on, so anyone logged on to your computer can use it.

The wizard might prompt you for a login ID and password. The default login ID is your SQL Server login, but you must enter your password.

At the next Wizard dialog box, select database devices on which to place the new Northcopy database. You can use an existing device or create a new device. For the example, we created two devices: UpsizeData.dat for the database device (10MB) and UpsizeLog.dat for the transaction log file (5MB), as you see in Screen 5. (See T-SQL for Starters, "Creating SQL Server 6.5 Databases," for details on devices and databases in SQL Server 6.5.)

To create devices for your Northcopy database, follow these steps:

1. At the same dialog box in Screen 5, click the drop-down list for Database Device and choose the first entry, Create New Device, as in Screen 6.
2. In the next dialog box, enter UpsizeData, then click OK to make UpsizeData appear as the entry in Database Device. Make the size of this device 10MB.
3. For the log file device, select Create New Device from the Log Device drop-down list.
4. In the resulting dialog box, name the device UpsizeLog and click OK. UpsizeLog appears as the entry in Transaction Log Device. Make the size of this device 5MB.
5. Click Next.

The resulting dialog box prompts you to name your new database. The default name, NorthcopySQL, is a fine choice for this example. Make sure that your database size is no less than 4MB and your log size is 4MB. The upsizing routines cause a lot of write activity to the log file, so make the log the same size as the database. Click Next.

In the next display, which you see in Screen 7, select the tables to export to SQL Server. Click the right-pointing double arrow button (>>) to move all tables listed under Available Tables to the Export to SQL Server list. Click Next.

The export table attributes dialog box in Screen 8 contains many options. For this demonstration, accept the defaults and click Next. SQL Server uses Transact SQL (T-SQL) triggers to reproduce the established indexes, validation rules, default values, and relationships from your Access database—see the sidebar "Declarative Referential Integrity vs. Triggers" on the SQL Server Magazine Web site, http:// www.sqlmag.com (October article index) for details about these options. The upsizing process links the newly created SQL Server tables to the Access file that you're migrating. The process also renames the original tables, adding _local to each name, and the SQL Server tables inherit the original table names. The Access queries, forms, and reports don't change. The wizard copies the data from the Access tables to the SQL Server database.

The wizard final screen offers you the option of creating an upsizing report. Select that option, and click Finish. You'll see a status bar tracking the progress of the upsizing process. If the transaction log file doesn't run out of space during the upsizing process, you receive a message that the upsizing is complete. When you click OK, you see the new SQL Server tables linked into the Access table container, as in Screen 9, and the upsizing report opens on the screen. You can print a copy of the report directly from Access, then save the report in Word format for future reference. You lose some of the formatting and organization features when you convert the report to Word, but you can't save the report in the Access database you just migrated.

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.