Tools for Migrating Access to SQL Server

One of the truisms about databases is that they always grow. It’s true whether the database is an enterprise SQL Server database or a desktop Microsoft Access database. Access provides many good tools that make it a great choice for quickly creating desktop applications. However, as those desktop databases begin to get popular in the organization, more people want to use them and somewhere in the five-to-10-user range, Access database begin to run out of steam. Moving those Access databases to SQL Server is an effective way to give them improved scalability. I recently evaluated four tools in an Access-to-SQL-Server migration project.

4. Access Upsizing Wizard

Definitely the most readily accessible of the different Access-to-SQL-Server-migration options. To use the Access Upsizing Wizard, you open Access, then go to Database Tools, SQL Server. This solution is best if you want to use the existing Access front end, then convert the back end to linked tables. Overall, I found this to be suitable for very simple migrations, but it didn’t handle the conversion of many of the queries. I also ran into many problems because the source data wasn’t clean enough, which caused key parts of the migration to fail. I opted for another solution.

3. SQL Server Access Migration Assistant

The SQL Server Access Migration Assistant 4.2 (SSMA) is a free tool from Microsoft that can migrate Access 97 and higher databases to SQL Server 2005, 2008, 2008 R2 and SQL Azure. SSMA is definitely a step up from the Access Upsizing Wizard, both in terms of granular control over the migration as well as in the complexity of setting up the migration. Personally, I didn’t find it intuitive or easy to use. Its strength was its ability to easily rerun migrations. However, like the Upsizing Wizard, it didn’t deal well with the dirty data in the Access database I was migrating. You can download the SSMA from the Microsoft download site (http://bit.ly/9i2tJA).

2. SQL Server Import Export Wizard

After trying the Access tools, I jumped into the tools offered by SQL Server. The SQL Server Import Export Wizard is available in all of the editions of SQL Server, and you run it using Start, All Programs, SQL Server 2008 R2, Import and Export Data option. This option was easy to use and fast, but here again data conversion errors caused me to look into a higher octane solution.

1. SQL Server Integration Services (SSIS)

SSIS was the tool I wound up using for my migration. Up front, the SSIS solution was definitely the most time-consuming and complicated to use. However, in my case that was mostly offset by my familiarity with SSIS: I knew what I would get and didn’t need to deal with the unexpected results of the other tools. However, the main reason I chose SSIS was the explicit control the tool gave me over the migration process. I was able to inject SSIS tasks into the migration that cleaned up the data errors and made the required schema changes as the migration package ran. In addition, SSIS’s ability to debug the running package, view the transferred data, and restart failed migrations really sped up the conversion process. The ability to easily rerun SSIS packages and schedule their execution was also a contributing factor for selecting SSIS for the Access-to-SQL Server migration.