Migrate from Access to SQL Server safely with SSW Upsizing PRO!

Russell Sinclair
SQL Server Professional

In this review, Russell Sinclair takes a look at Upsizing PRO!-a tool that's designed to help you upsize your Microsoft Access databases to SQL Server.

Most developers who have attempted to make the migration from Access to SQL Server probably realize that Microsoft's own Upsizing Wizard that's included with Access is far from perfect. Considering the number of years that it's been around (and touted by Microsoft as a genuine migration tool), the Upsizing Wizard is still surprisingly littered with oversights and even bugs. It's poorly documented, and it doesn't handle errors gracefully. The most common of these errors-a simple message box that says "Overflow"-is an excellent example of the problems inherent in this tool.

Happily, the Australian-based SSW sells a product called Upsizing PRO! that's designed to help you prepare an Access database for migration using the Microsoft Upsizing Wizard. It analyzes the tables in your database, looking for design issues that will cause problems along the way.

In other words, Upsizing PRO! isn't a replacement for the Upsizing Wizard-it's a complementary tool that's designed to help you find the problems with your Access database before you run it through the Microsoft Upsizing Wizard.

Using the tool
Upsizing PRO! analyzes a database for problems that will cause the Microsoft Upsizing Wizard to fail or generate errors, and it can be used to check the tables in your database both before and after upsizing takes place. When you choose to run the before-upsizing check, you'll be able to find out about potential problems and specify which database to test.

This screen shows a series of tabs describing some of the problems that you may encounter with your database. Although it's not necessary to read each tab, it can help you to avoid problems with future product development. The last tab on this screen is where you can actually specify a database for analysis. Select a database using the folder button on this screen and click the Go button to begin the analysis. A progress bar will appear showing you the status of the operation.

Once the analysis is complete, a report alerts you to the problems that Upsizing PRO! has found with your database.

Not only does this report show you what problems exist in your database, it will in many cases tell you exactly what you need to do to find or fix the problems. Once you've made the necessary corrections to your database, you can go ahead and upsize it using the Microsoft Upsizing Wizard.

The Upsizing Wizard itself generates a report that's supposed to include the details of the migration and any errors that occurred. However, this report typically lacks vital information-even about some of its failures. In order to check that your database was upsized properly, you should return to Upsizing PRO!.

The post-upsizing check requires that you enter an OLE DB connection string for the Access database and the new SQL Server database. Use the builders to create the string or enter one manually. Click the Go button to start the analysis when you've made the selections.

Once again, Upsizing PRO! will generate a report. This report tells you the differences in the table design and any differences in the data that was brought over.

Evaluating the tool
Upsizing PRO! has a number of really great selling features. I was really impressed by the thoroughness of the coverage of the problems you may encounter, and the suggestions on how to avoid them. The various tabs on the before-upsizing screen explain in detail what sort of issues your database may have that could prevent a successful migration. This information alone can help you to avoid problems with future migrations.

But the real strength in Upsizing PRO! is the report that's generated about your database. This report not only explains what problems were encountered-in the case of a few different error types, a SQL statement is shown on the report that can be used to help find the problematic objects or data in your database. Instead of having to spend hours hunting through numerous tables to diagnose each problem, you can simply run Upsizing PRO! and find the problems in minutes.

Among the problems this tool found on a test database (a database that was first developed back in Access 95, and has been growing and upgrading ever since) were primary and foreign key fields with different sizes (an Access legacy problem), tables without unique indexes (a problem if you're linking SQL Server tables into Access using ODBC), spaces in field names (a problem for some versions of SQL Server), and duplicated relationships.

All of these problems would have taken me a day or two to weed out if I'd done the check manually-and I would have been much more likely to miss something. The real advantage to this is checking a database that was developed by someone else. If I didn't have intimate knowledge about this particular database, it could have taken a week to find all of the problems.

The report has only one issue that I could find, and that had to do with language. At least one of the errors-the duplicate foreign key problem-started out with the line "Field tblPODetails!ProjectID has duplicates." When I first glanced through the report, I couldn't figure out why Upsizing PRO! thought that duplicate values in this column were a problem. On closer inspection, and after using the SQL statement it suggested, I realized that the problem was actually the relationships on the table, not the data it contained. Although it seemed like an error in the report, it was simply a matter of semantics.

Where and how much
More information on Upsizing PRO! is available at SSW's Web site at www.ssw.com.au/ssw/UpsizingPRO. There you can check out the product features, order a copy, or download a fully functional demo that will analyze 70 percent of the tables in your database (to do the other 30 percent, you need to order it). The product sells for US$199 for a single developer license, or US$499 for an enterprise site license. If you think of how much time it could save you, it's definitely worth the price.