Access to SQL Server Migration: Understanding the Assessment

Access SQL Server

Server / June 10, 2021

Over time, most database applications grow, become more complex, and need to support more users. At some point in the life of your Microsoft Office Access application, you might want to consider upsizing to it to a Microsoft SQL Server database to optimize performance, scalability, availability, security, reliability, and recoverability.

About upsizing a Microsoft Office Access database

Upsizing is the process of migrating some or all database objects from an Access database to a new or existing SQL Server database or new Access project (.adp).

Benefits of upsizing a database to SQL Server

  • High performance and scalability In many situations, SQL Server offers better performance than an Access database. SQL Server also provides support for very large, terabyte-sized databases, which is much larger than the current limit for an Access database of two gigabytes. Finally, SQL Server works very efficiently by processing queries in parallel (using multiple native threads within a single process to handle user requests) and minimizing additional memory requirements when more users are added.
  • Increased availability SQL Server allows you to do a dynamic backup, either incremental or complete, of the database while it's in use. Consequently, you do not have to force users to exit the database to back up data.
  • Improved security Using a trusted connection, SQL Server can integrate with Windows system security to provide a single integrated access to the network and the database, employing the best of both security systems. This makes it much easier to administer complex security schemes.
  • Immediate recoverability In case of system failure (such as an operating system crash or power outage), SQL Server has an automatic recovery mechanism that recovers a database to the last state of consistency in a matter of minutes, with no database administrator intervention.
  • Server-based processing Using SQL Server in a client/server configuration reduces network traffic by processing database queries on the server before sending results to the client. Having the server do the processing is usually much more efficient, especially when working with large data sets.

    Your application can also use user-defined functions, stored procedures, and triggers to centralize and share application logic, business rules and policies, complex queries, data validation, and referential integrity code on the server, rather than on the client.

  • Ways to upsize

    The Upsizing Wizard moves database objects and the data they contain from an Access database to a new or existing SQL Server database.

    There are three ways to use the Upsizing Wizard:

  • Upsize all database objects from an Access database to an Access project so that you can create a client/server application. This approach requires some additional application changes and modification to code and complex queries.
  • Upsize only data or data definitions from an Access database to a SQL Server database.
  • Create an Access database front-end to a SQL Server database back-end so that you can create a front-end/back-end application. This approach requires very little application modification since the code is still using the Access database engine (ACE).
  • Before you upsize an Access database

    Before you upsize your Access database to a SQL Server database or Access project, consider doing the following:

  • Back up your database Although the Upsizing Wizard doesn't remove any data or database objects from your Access database, it's a good idea to create a backup copy of your Access database before you upsize it.
  • Ensure you have adequate disk space You must have adequate disk space on the device that will contain the upsized database. The Upsizing Wizard works best when there is plenty of disk space available.
  • Create unique indexes A linked table must have a unique index to be updateable in Access. The Upsizing Wizard can upsize an existing unique index, but can't create one where none exists. If you want to be able to update your tables, make sure you add a unique index to each Access table before upsizing.
  • Assign yourself appropriate permissions on the SQL Server database

  • To upsize to an existing database, you need CREATE TABLE and CREATE DEFAULT permissions.

  • To build a new database, you need CREATE DATABASE permission, and SELECT permissions on the system tables in the Master database.
  • The Access 2007 Upsizing Wizard is optimized to work with Microsoft SQL Server 2000 and SQL Server 2005.

    Use the Upsizing Wizard

  • On the Database Tools tab, in the Move Data group, click SQL Server.

    The Upsizing Wizard starts.

  • Step 1: Choose to upsize to an existing database or a new database

    On the first page of the Wizard, you specify whether you want to upsize the Access database to an existing SQL Server database or create a new SQL Server database.