Preparing Access Databases for Migration (AccessToSQL)
Before you migrate Access databases to SQL Server, you must determine which databases to migrate, and then make sure those databases are ready for migration.
The Jet database engine, which is used as the database engine for Access, is a flexible, easy-to-use solution for data management. However, as databases become larger and more mission critical, many users find that they require greater performance, security, or availability. For applications that require a more robust data platform, consider moving the underlying databases for those applications to SQL Server. For more information about deciding when to migrate, see the migration information page on the SQL Server Web site.
After you migrate databases to SQL Server, you can continue to use Access by using linked tables, or you can manually migrate your applications to Microsoft .NET Framework-based code that interacts directly with SQL Server.
SQL Server Migration Assistant (SSMA) for Access can locate Access databases for you. You can then export metadata about those databases to SQL Server. For more information about how to export and query metadata, see Exporting an Access Inventory.
Note Not all Access features and settings are supported by, or can be easily converted to, SQL Server. Before you start migrating databases, see Incompatible Access Features.
Use the following guidelines to help you prepare your Access databases for migration to SQL Server.
SSMA for Access supports Access 97 and later versions. If you have databases from earlier versions of Access, open and save the databases in Access 97 or a later version. For more information about how to convert Access databases to a later version, see the Converting Access Databases page at the Office Online Web site.
SSMA cannot migrate databases that use workgroup protection. You can do the following to remove workgroup protection from an Access database:
Copy the Access database file to another location.
Open the copied database.
On the Tools menu, point to Security, and then select User and Group Permissions.
Select the Users option, select the Admin user, and then make sure that the Administer permission is selected.
Select the Groups option, select the Users group, and then make sure that the Administer permission is selected.
Click OK, and then on the File menu, click Exit.
You can now use SSMA to migrate the copied database. After you load the schema into SQL Server, you can manually secure the database on SQL Server.
Before you migrate your Access databases to SQL Server, you should back up the Access databases that you will be migrating and the SQL Server databases into which you will migrate Access objects and data.
To back up an Access database, on the Tools menu, point to Database Utilities, and then select Back Up Database.
For information about how to back up SQL Server databases, see "Backing Up and Restoring Databases in SQL Server" in SQL Server Books Online.
You might also want to document properties of your Access databases, such as lists of database objects, file sizes, and permissions. To generate this documentation in Access, on the Tools menu, point to Analyze, and then click Documenter.