Which Path from Access 97 to SQL Server 6.5?

Two paths for the great migration

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 OfficeSamplesorthwind.mdb.

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:

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.

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 \SQLServerlog).

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.