SQL Server Magazine

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.

   Prev. page   [1] 2     next page