News
Access to SQL Server Migration Made Easy
2SQL from Australia-based ConvertU2 aims to ease the migration from Microsoft Access to SQL Server.
Many organizations get started managing their data in Microsoft Access. But like a young couple buying a starter home, it doesn't take long to outgrow Microsoft's Office-integrated data management application. Moving to Microsoft SQL Server, however, can produce some tough challenges. One solution to moving up is the Microsoft Access Upsizing Wizard, but this tool often requires a lot of manual tuning.
A product called 2SQL from Australia-based ConvertU2 aims to ease the migration, moving 80 percent or more of the data automatically. The tool was recently announced in North America.
2SQL can move forms, code modules, tables and queries. It works in conjunction with Microsoft's Upsizing Wizard, stepping in to convert items the wizard is unable to process.
According to ConvertU2 officials, the Upsizing Wizard often handles about 10 percent of migration chores, leaving the rest to manual intervention.
"2SQL encompasses automatic repair of at least 80 percent of any migration project," says Steve Koop, principal technologist at ConvertU2. "A small project that takes, say, three weeks manually, can be conquered in less than two or three days with 2SQL. This doesn't include QA, of course."
Koop says the wizard encompasses only 10 percent of the problem: data and data schema. "We currently rely on the wizard for this part, but we make sure that each database is 'prepared' for the wizard," he says. "2SQL will generate an autonumber primary key for tables without a unique index. We don't depend on the wizard to convert the queries because it's too unreliable. Only simple queries get converted by the wizard. The wizard generates an ADP for us, but it won't fix the forms reports and modules. In fact, the wizard can often corrupt the record source property of a form or report."
The tool can convert the following:
- DAO to ADO
- Queries to stored procedures or views
- Access functions to their SQL Server equivalent
- Access functions without a SQL Server equivalent to user-defined functions
Company officials say the tool makes it easy for non-SQL gurus to help in the migration. "Knowledge and skill requirements are greatly reduced, so the R&D for any project is much, much less," Koop says.
During the migration, 2SQL records all changes and spits out an XML-based report.
Looking Ahead
ConvertU2 is already working on the next revision of 2SQL, with the intent to speed up the QA process.
"Currently, a project delivery requires manual execution of all the views. We'll be automating this and logging those views that executed with an error message," Koop says.
2SQL has an unusual pricing model: customers pay for what's actually converted. The tool can estimate how much work the conversion will take, what the cost would be and whether it would be cheaper to do the job manually. The tool is sold through partners.
2SQL was designed for .NET, requires a connection to the back-end SQL Server database and runs on Windows 2000 or XP desktops. The tool works with SQL Server 2000 and above.
About the Author
Doug Barney is editor in chief of Redmond magazine and the VP, editorial director of Redmond Media Group.