Access to SQL Server Migration: How to Use SSMA

  • Article

[Updated 2/7/2012 Selina Jia- Microsoft SQL Server Migration Assistant (SSMA) for Access v5.2.  The information provided below is still valid for SSMA for Access v5.2.  Users should download the lastest SSMA for Access]

In this blog post I’ll walk you through the process of migrating the Access 2007 Northwind database to SQL Server using the SQL Server Migration Assistant for Access (SSMA). You can use SSMA to do all of the heavy lifting, migrating your tables and queries to SQL Server 2008 or SQL Server Azure. SSMA can also link your Access solution to the new SQL Server tables after you have migrated your data.

Prerequisites for Testing SSMA for Microsoft Access

In order to complete this walkthrough you’ll need to have the following software installed on your computer:

You can view additional prerequisites for the SSMA tool from the download site or by viewing the Help file that is installed with the product. The default installation folder for SSMA is \Program Files\Microsoft SQL Server Migration Assistant 2008 for Access[ Updated: \Program Files\Microsoft SQL Server Migration Assistant for Access.].

Preparing to Migrate Microsoft Access Solutions using Jet to SQL Server

There are many compelling reasons to migrate your back end database to SQL Server, but fixing a broken Access application shouldn’t be one of them. Access applications can fail for a variety of reasons, so having a clear understanding of the problems you are encountering in Access should be your first step. Many common problems have simple fixes, and many Access applications simply do not require the features and overhead of a SQL Server database. A good resource for getting help troubleshooting Access issues is the UtterAccess discussion forums https://www.utteraccess.com/forum/forums.html. Other problems, such as poor database design, simply result in bigger problems when tables are upsized. Sometimes you just need to start from scratch, redesigning your database to take advantage of SQL Server features and functionality. You can always migrate your data later.

You should also be comfortable with SQL Server concepts before you attempt to migrate your Access application. The Access database engine Jet (.mdb) has been optimized for the desktop over many versions. The current engine, ACE (.accdb) has been optimized to work with SharePoint, and many features are incompatible with SQL Server (such as spaces in column names). The Access application layer sits on top of the Jet or ACE database engines, but it can just as easily connect to SQL Server (or a variety of other data sources). The SQL Server database engine is server-based, running over a network, and it does not contain any forms, reports or other application objects. It uses entirely different locking mechanisms and enforces ACID rules to ensure database atomicity, consistency, isolation and durability. It also has a completely different security model, which you will need to become familiar with. In Access, the default security setting is least restrictive, or off. In SQL Server, the default security setting is on – you must explicitly grant users permissions to work with server and database objects.

SQL Server has a high learning curve, so plan on spending some time getting up to speed if you are a novice. There are many books and online resources for learning SQL Server to choose from. Pick the ones that best match your skill level and learning preferences before you attempt to migrate your Access business application to SQL Server.

Back End Decision Factors for Moving to SQL Server

It is often the case that Access applications fail of their own success. What started as a small, departmental application grew over time as it evolved to become business-critical by adding on more users and more features, or it requires functionality that is not available in Access. Some of the decision factors for choosing SQL Server include the following:

  • High Availability—always on. You can back up databases while they are running without having to shut down and disconnect users. SQL Server can be brought back online quickly in case of a system crash or power outage without losing data.
  • Security. SQL Server uses role-based security that can be integrated with the Windows operating system.
  • Server-based processing. You can use stored procedures to centralize data processing and enforce security.
  • Transactions. SQL Server uses two-phase commit and has several models of replication to choose from.

For more information, see SQL Server Product Information https://www.microsoft.com/sqlserver/2008/en/us/high-availability.aspx.

Preparing Your Access Database for Migration

Once you’ve decided to migrate to SQL Server, you need to prepare your Access database so that the migration process will go smoothly.

  • Your database must be in Access 97 format, or later.
  • You must remove Access security features. They are incompatible with SQL Server.
  • Check over the list of incompatible features found in the Help.chm file that is installed in the \Program Files\Microsoft SQL Server Migration Assistant 2008 for Access\help\[ Updated:  \Program Files\Microsoft SQL Server Migration Assistant for Access\help\] folder and fix any of the issues you find. Incompatible features include tables that don’t have unique indexes, replication columns, date values that are out of the SQL Server datetime range, object names that are SQL Server keywords, special characters in column names, and hyperlink columns.

Migration Process Walkthrough

Open your instance of SQL Server and create a new database to hold the migrated objects. You will be prompted for the name of the destination database. For this demo I have created a SQL Server database named SSMANorthwind. Make sure that you can connect to your instance of SQL Server before launching SSMA.

  1. When you launch SSMA for the first time, the SQL Server Migration Assistant Wizard for Access launches. It displays the six steps that it will guide you through:

Step 1: Create a new SSMA project.

Step 2: Add Access database files to the migration project.

Step 3: Select objects for migration.

Step 4: Connect to SQL Server or SQL Azure.

Step 5: Link migrated tables (optional).

Step 6: Convert objects, load them to database, and migrate data.

Click Next.

  1. You will be prompted for the following information:

The Name of the SSMA project. Type any name you choose.

The Location. You can click the Browse button to select a different location.

The Migration To drop-down list. You can choose either SQL Server or SQL Azure.

Click Next.

  1. The Add Access Databases screen is where you specify the Northwind2007.accdb database. Click the Add Database button and navigate to its location on your computer. Click Next.
  2. The Select Objects to Migrate screen is where you select which objects you want to migrate to SQL Server.

clip_image002

Click Next.

  1. In the Connect to SQL Server 2008 screen, specify the server name and database you created earlier. Click Next.
  2. On Link Tables screen, select the Link Tables checkbox and click Next.
  3. The Migration Status screen displayed below shows you the status of the converted objects. Note the errors, warnings and informational messages.

clip_image004

  1. On the Synchronize with the Database screen, click the OK button. The SSMA will convert the selected objects and migrate the data, linking the SQL Server tables. The Migration Status screen will be refreshed.
  2. Click the Report button to load the Data Migration Report screen and click Save Report. The report will be saved as DataMigrationReport1.csv in your SSMA project folder.

Comparing the SQL Server Schema with the Migrated Jet Database

One of the most useful features of the SSMA user interface is the ability to examine both the SQL Server and the Access metadata at the same time. The screenshot below illustrates some of the problems with a straight conversion from Access. The Customers table in Access had SQL Server reserved words as well as spaces in the column names(First, Last). Therefore, you must always square brackets in all of your queries. You may want to consider renaming your Access tables and columns to conform to SQL Server naming conventions, which will simplify coding later on. For more information, see Reserved Keywords (Transact-SQL) in SQL Server Books Online https://msdn.microsoft.com/en-us/library/ms189822.aspx.

clip_image006

Another issue that you may want to consider is the use of data types. The SSMA converted all Text data types to Unicode data types, which take twice the storage. For example, Text(50) was converted to nvarchar(50). You may want to consider customizing the mappings of your data types if you do not require Unicode. I will discuss data types and data type conversions in another blog.

Open the Northwind 2007 database in Access and note that the migrated tables are displayed with a blue-green globe. The original tables have been renamed using the format SSMA$<tablename>$local.

clip_image008

None of the original Access objects have been deleted.

Additional Resources and References

I’ve only scratched the surface of what the SSMA Migration Assistant can do. For more information on SSMA features and functionality, consult the Help file that is installed with the product.

See the following resources for more detailed information on upsizing to SQL Server.

Microsoft Access or SQL Server 2005: What's Right in Your Organization? https://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLAccessWhatsRight.doc

Moving Access Data to the Cloud https://social.technet.microsoft.com/wiki/contents/articles/moving-access-data-to-the-cloud.aspx

FMS Microsoft Access to SQL Server Upsizing Resources https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html

SQL Server Developer Center https://msdn.microsoft.com/en-us/sqlserver/default

SQLCAT https://sqlcat.com/

 

Bio

Mary Chipman has written and spoken extensively about Microsoft data platforms and technologies. She is co-author of the classic Microsoft Access Developer's Guide to SQL Server https://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446/.

Comments

  • Anonymous
    January 08, 2012
    Why does SSMA now require a license key to use? And why is there NO INFORMATION ANYWHERE on how to obtain said license key, which the documentation says "can be obtained after quick and simple registration"???? Um, one question: WHERE IS THIS REGISTRATION SITE?! Seriously... how hard is it to include a simple link?!

  • Anonymous
    January 10, 2012
    License key is required for all SSMA products in order to allow us to better service customers using the products.  After you download the lastest SSMA product from MSDN, and open SSMA for the first time, the License Management window will appear.  There is a hyperlink that should take you directly to the license key request page.  Please follow the instructions provided on the License Management window and license key request page to obtain and import the required key.  Thanks.

  • Anonymous
    February 21, 2013
    Like everything else I have downloaded from Microsoft recently, it just does not work.  I carefully enter the Azure Server details, and login only to be told after a number of seconds that the server does not exist, yet I have no trouble connecting with the same details through Enterprise Manager

  • Anonymous
    February 21, 2013
    Hi Steve, What version of SSMA for Access are you using? Please try with SSMA for Access 5.2 alongwith the SNAC 11 client driver. You can download the client driver (SQL Server Native Client) from www.microsoft.com/.../details.aspx If you still get the error please specify the exact error message.  

  • Anonymous
    February 22, 2015
    Can I license SSMA to work on a network NOT connected to the internet.  I have an Access application and SQL server which are not connected to the internet - and I cannot take information from that network and sneaker net it over to the internet.  How do I license the product for non-internet use.

  • Anonymous
    March 11, 2015
    Hi, can i use SSMA to migrate multiple Database automatically from MySql . If so how is it possible. Please help.

  • Anonymous
    April 06, 2015
    Nice article, also stumbled upon this guide, sqlturbo.com/migrating-from-access-to-sql-server  really useful stuff, thanks!

  • Anonymous
    September 16, 2015
    Hi I am using SQL Server2008, which version of SSMA should I use to migrate. I have tried with ssma2014, but it wont find the server. What should I do, tried to login both ways using and sql and windows. No luck. Pls help. Thanks, Niaz