Database Migration from MS Access to SQL Server: February 2009

I just migrated a few classic ASP applications' databases from MS Access to MS SQL Server 2005 for my client. So just wanted to share some interesting things I have learnt passing through this phase.

I started this project by analyzing the databases and the application source code.

I used Upsizing wizard, that is part of MS Access 2003, to migrate MS Access database (.mdb file) to SQL Server 2005 database. This wizard is very easiest way to automatically create SQL Server 2005 database from MS Access database. Just open MS Access database file (.mdb), go to Tools --> Database Utilities --> Upsizing Wizard. This will open Upsizing wizard. Follow the steps in that and your SQL Server 2005database will be created. Follow the steps below:

1) Open Upsizing wizard.

2) Specify if you want to create a new database in SQL Server 2005 or want to use existing one. Click Next to proceed.

3) Specify SQL Server instance name, user name and password to connect to SQl Server and database name for new database. If you are using windows authentication to connect to SQL Server instance, click "Use Trusted Connection" check box. Click Next

4) This will display all the tables in the Access database. Select the tables you want to migrate to SQL Server. Click next

5) Now select the options in the following screen as displayed. Some noteworthy points about following step are:

-Indexes Checkbox: If you select this checkbox, all the indexes from MS Access database will be exported to SQL Server 2005.

- Defaults Checkbox: Click this checkbox to export all the defaults of the tables.

- Table Relationships: select "Use DRI" to create constraints in SQL server to represent table relationships. Or select "Use triggers" to create triggers in SQL Server to maintain table relationships.

Click Next to proceed.