Published on April 24th, 2015 | by Alejandro Cordero0
From Access to SQL with Microsoft SQL Server Migration Assistant
In a Previous Post we showed how to move from Access to SQL Server using the SQL Server Import Wizard. This is a very good option, however certain bits of our access project is left outstanding, such as the queries that are designed to return the data from the access tables.
When moving to SQL Server the concept of queries changes, and this means that more complex structures will be available for you. The more natural way to migrate a query is to convert it into a view. A view is a logical abstraction of a query, you will need to name this view and it will behave pretty much like another table, you then are able to build new queries on top of views as well.
You can use those views as reference from your VB code or you C# code, which leads me to a good point, when migrating from access to sql server you don´t necessarily need to migrate the application immediately to a web based architecture or another language like C#. What I mean here is that an Access application is normally conceptualized as a monolithic project, where you will have both the user interface and database structure in the same place, when moving to SQL Server you can actually break this and use your application on one side and your VB application in another layer. So you can probably now have the application in diverse machines and connect to the same database that is now a brand new SQL Server.
The architecture can now look like this:
So how do you accomplish this? this time it will be by using the Microsoft SQL Server Migration Assistant for Access. You can download it for free from this Microsoft site
Next a step by step on how to migrate a Northwind database.
Step #1 – Opening the Migration Wizard.
After installing the migration assistant you should double click on the icon and start up the wizard
Step#2 – Configuring your Migration Project
In the next screen you will be able to browse and specify the location of your migration project. In here you will be able to specify if you want to migrate to SQL Server 2005/2008/2012 or even to SQL Azure.
Step#3 – Choosing your Source Access project
In the next screen you will be able to browse and specify the location of your access project/file.
Step#4 – Pick which Tables and Queries you are going to Migrate
In the left pane of the tool, you will be able to browse your access database and it will be divided into 2 main folders, tables and queries. You can check the entire folder, which means you want to migrate the whole thing or just pick whatever you need.
Step#5 – Choose target SQL Server Instance
If you are looking to move to a SQL Server, naturally you will need to have one available and already installed. In the next screen you will specify the connection information to this SQL Server. In this case I am migrating to SQL Server 2014 and will specify user, type of authentication and password if using SQL Authentication. You have available 2 options for encrypting or using a server certificate as well in case you need those.
Step # 6 – Reusing your VB application
If you want to modify your access project so it will now point to the new SQL Server database, you can do so by checking “link tables”, this will allow you to keep using your application and still connect to a upgraded SQL Server. The wizard is actually pretty good doing this, however some manual tweaking could be needed at the end. Have this in mind and do test this first so you make sure it will actually work with your project.
Step # 7 – Start Migration
Give it a go, and see the assistant start doing most of the work for you
Step # 8 – Match VB references to the new SQL Server tables
In the next screen you will do the linking mentioned before. Normally you can just go “Next” in this screen, but do check in case you want to match an access table to specific SQL Server table.
Step # 9 – Check the final Report
Check the final Report and see if you had any issue. When moving from access to sql you will realize some data types changed. This means that depending of the value that you are using in a column, you will need to manually convert it, this is the manual part, so…..the wizard is not doing all the magic, just most of it. I will cover how to fix common issues in a future post. If you click the report button it will show you a detailed list of conversion errors, check below images for reference:
Step #10 – Work with your migration Project
After this step you will realized you now have a complete migration project. The migration assistant is not just a wizard, it will help you troubleshoot data conversions and have a general view of what can be migrated and what is presenting compatibility issues. The designer or main screen of the assistant looks like this:
As you can see the tables with issues are marked with a red icon, this means you need to do manual work on those. Stay tuned for the next article on how to fix those conversion issues.
The SQL Server Migration Assistant is probably the best tool to migrate your access project to a SQL Server database. It allows you to keep your original VB code and link references to the SQL Server tables. It will also allow you to migrate to diverse SQL Server versions or even to the cloud which opens a lot of possibilities for your migration plans.