[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.
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.].
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 http://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.
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:
For more information, see SQL Server Product Information http://www.microsoft.com/sqlserver/2008/en/us/high-availability.aspx.
Once you’ve decided to migrate to SQL Server, you need to prepare your Access database so that the migration process will go smoothly.
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.
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.
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.
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.
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.
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 http://msdn.microsoft.com/en-us/library/ms189822.aspx.
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.
None of the original Access objects have been deleted.
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? http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLAccessWhatsRight.doc
Moving Access Data to the Cloud http://social.technet.microsoft.com/wiki/contents/articles/moving-access-data-to-the-cloud.aspx
FMS Microsoft Access to SQL Server Upsizing Resources http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html
SQL Server Developer Center http://msdn.microsoft.com/en-us/sqlserver/default
SQLCAT http://sqlcat.com/
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 http://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446/.
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?!
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.
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
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.