SSMA is used to migrate Access databases to SQL Server. This tool converts the Access database to an SQL Server or an SQL Azure database. It is not bundled with SQL Server – you will need to download and install this tool separately.
Check your system requirements and view the installation procedure for SSMA.
Preparing for Migration:
- SSMA supports only versions of Access 97 and later. If your database program and files are older than Access 97, you need to save your database in Access 97 or later.
- Take a backup of your database before migrating your database.
- You will need to remove any workgroup protections; SSMA cannot migrate while this feature is on. Take a backup, remove the function, and do the same for the copied file.
- (Optional) Create a document for your Access database, available in Tools->Analyze -> Documenter.
- Create a new SSMA Project and set your project option preferences – conversion, data type mapping and migration.
- Add your database files into the project.
- Connect to the SQL Server instance.
- If you are going to migrate more than one Access database, map your database source and target databases. If you are only migrating one database, this step is not necessary.
- Create an assessment report. This will give information about the migration like an estimated time for the conversion process, and the percentage of objects that will successfully be converted.
- This report also shows warnings, errors and potential data risks that may take place during the migration.
- Correct the errors before migration.
- Once all errors are cleared, the SSMA will convert your Access database to a SQL Server database. You can also change the data type of the columns and alter the table and its indexes. Make sure to click “Apply” to save your changes.
- To load the objects, use SSMA to create the objects or you can create and execute codes for the objects.
- Once all the database objects are created, right click on “Migrate Data” under “Databases” to transfer the data.
