| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.collection | helpviewer_keywords | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Preparing Access Databases for Migration (AccessToSQL) |
Learn how to determine which Access databases to migrate to SQL Server or Azure SQL Database and ensure that those databases are ready for migration. |
cpichuka |
cpichuka |
08/15/2017 |
sql |
ssma |
conceptual |
|
|
Before you migrate Access databases to [!INCLUDEssNoVersion], you must determine which databases to migrate and ensure that 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 [!INCLUDEssNoVersion]. For more information about deciding when to migrate, see the migration information page on the [!INCLUDEssNoVersion] Web site.
After you migrate databases to [!INCLUDEssNoVersion], you can continue to use Access by using linked tables, or you can manually migrate your applications to [!INCLUDEmsCoName] .NET Framework-based code that interacts directly with [!INCLUDEssNoVersion].
[!INCLUDEssNoVersion] Migration Assistant (SSMA) for Access can locate Access databases for you. You can then export metadata about those databases to [!INCLUDEssNoVersion]. 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, [!INCLUDEssNoVersion]. Before you start migrating databases, see Incompatible Access Features.
Use the following guidelines to help prepare your Access databases for migration to [!INCLUDEssNoVersion].
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.
SSMA cannot migrate databases that use workgroup protection. To remove workgroup protection from an Access database, perform the following steps:
-
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 ensure that the Administer permission is selected.
-
Select the Groups option, select the Users group, and then ensure 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 [!INCLUDEssNoVersion], you can manually secure the database on [!INCLUDEssNoVersion].
Before you migrate your Access databases to [!INCLUDEssNoVersion], you should back up both the Access databases that you will be migrating as well as the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] databases, see "Backing Up and Restoring Databases in [!INCLUDEssNoVersion]" in [!INCLUDEssNoVersion] Books Online.
You might also want to document the properties, such as lists of database objects, file sizes, and permissions, of your Access databases. To generate this documentation in Access, on the Tools menu, point to Analyze, and then click Documented.
Migrating Access Databases to SQL Server
Linking Access Applications to SQL Server