Feeds

Migrating Access to SQL made (almost) easy

Very useful, apart from the dumb bits

  • alert
  • submit to reddit

High performance access to file storage

Access dominates the PC platform and, over the years, has been used to create vast numbers of departmental databases. In their turn, many of these have slowly become mission critical and now need to be upgraded to a secure client-server engine.

In Microsoft's grand plan for world domination, it would prefer that engine to be SQL Server and, with that in mind, has provided, free of charge, SQL Server Migration Assistant for Access (SSMA) that does what it says on the can and you can download it here

For developers, migration tools can be a huge boon. It is unrealistic to expect this tool to migrate an entire application because Access has facilities (such as Form and Report capabilities) that simply don't exist in SQL Server. However it is reasonable to expect it to do a huge chunk of the grunt work – creating the appropriate tables, moving the data, turning queries into views and so on.

As well as XP or 2003, SSMA requires the .NET Framework version 2.0 or later, the J# 2.0 redistributable package and at least 1 GB RAM.

It has a fairly minimalist GUI, essentially just four panels. After creating a new project you add one (or more) Access databases and then connect to the appropriate SQL Server database. Next step is to convert the schema to SQL Server.

Note that this process does not run the schema against the SQL Server engine; it simply produces a potential SQL Server schema which is visible in SSMA. It also produces a set of error, warning, and information flags.

The simple GUI for SSMA

At this point, the power of the tool becomes apparent. For a start the flags point you to conversion problems, for example, the fact that Access functions like DateDiff are not supported and cannot be converted (they can, of course, be converted, but not by SSMA).

You can also browse the Access schema, look at the type mapping the Assistant is proposing, and so on. In addition, if you don't like the default mapping, you can globally change the default and/or change them for a particular project or even a particular table.

Queries are a somewhat special case. They are converted into SQL Server Views: you can edit the Access query and then regenerate the appropriate SQL Server code. This editing occurs in the schema held by SSMA, not in the Access database itself.

When you are happy with the schemas you can use SSMA to run the SQL Server schema against the database. That creates the structure to hold the data so that you can then migrate the data. In theory it all sounds wonderful, but what is it like in practice? Well, trying to migrate from any database engine to another is painful and this tool will do 90 per cent of the work for you. It's also free, so it has to be great. That said, some of the bugs are mind-blowing.

For example, although it isn't part of the SQL Standard, Access requires all dates to be wrapped up in hash symbols. Sadly, no one appears to have told SSMA. The net result of that failure is that all my queries that reference dates failed to convert. A sample error message looks like this:

/* * SSMA error messages: * A2SS0058: Following SQL statement is not supported and cannot be converted: * * SELECT DISTINCTROW EMPLOYEES.EmployeeNo, EMPLOYEES.FirstName, EMPLOYEES.LastName, EMPLOYEES.DateOfBirth, EMPLOYEES.DateEmployed * FROM EMPLOYEES * WHERE ((EMPLOYEES.DateOfBirth)>#1/1/1970#); * */

PRINT 'ERROR: SSMA failed to convert the previous statement.'

Now, dates are common in databases so this failure is going to impact most database conversions. How was this overlooked? It isn't as if the solution is difficult:

SELECT EmployeeNo, FirstName, LastName, DateOfBirth FROM dbo.EMPLOYEES WHERE (DateOfBirth > CONVERT(DATETIME, '1970-01-01'))

returns the correct set of data from the sample.

(We can argue about whether, for example:

CONVERT(DATETIME, '1970-01-01 00:00:00', 102)

might be more appropriate but, either way, we can convert the date handling.) And if we can do it by hand, SSMA should be doing it for us.

And it gets worse (no, really). Access, by default, uses double quotes around text, for example:

SELECT EMPLOYEES.EmployeeNo, EMPLOYEES.FirstName FROM EMPLOYEES WHERE ((EMPLOYEES.FirstName="Norma"));

SQL Server doesn't, it demands single quotes, as in:

WHERE EMPLOYEES.FirstName='Norma';

Nevertheless, SSMA leaves the above double-quoted code exactly as it is. This fails to trigger an alert during the schema generation; the alert only occurs during the process of loading the schema to the SQL Server database. Then SSMA throws up an error saying there is an invalid column name Norma and the View is not loaded into SQL Server. All of which tells us that SSMA is not performing an adequate syntax check on its own code.

Now, let me stress again, Access uses double quotes BY DEFAULT. The fact that the SSMA fails to deal with this incredibly simple and common Access syntax leaves me gob-smacked. It's as if a human French-English translator could process the majority of the language but was flummoxed by the word "Bonjour".

One more example. Access allows you to add a validation rule to a column such that, for example, the only acceptable values in a field called "Title" might be Mr., Mrs., Miss., Ms, etc. SQL Server doesn't support exactly the same type of validation so, very sensibly, SSMA converts the validation rule into a table constraint. Brilliant, well done. Only it misses out the name of the column in the code it writes:

ALTER TABLE [dbo].[NAMES] ADD CONSTRAINT [SSMA_CC$NAMES$Title$validation_rule] CHECK (In ('Mr.','Mrs.','Miss','Ms','Dr.','Prof.'))

This not only fails to run when the schema is loaded into SQL Server, it actually fails to generate an error message as well. The correct syntax for the last line should be:

CHECK (Title In ('Mr.','Mrs.','Miss','Ms','Dr.','Prof.'))

So, am I going to remove SSMA from my machine? Of course not. It does do a huge amount of work for me entirely automatically. It also provides a reasonable environment where I can see the problem areas and fix them. In addition, complaining about a free tool feels like looking a gift horse in the mouth. Despite all of that, I can't help wishing that SSMA had better teeth.

The reason we need conversion tools like SSMA is partially based on the fact that dialectic differences exist between different SQL implementations. Some of these differences are very esoteric and will impact a very small number of real-world queries. Others (such as hash symbols and double quotes) are widely used in most Access applications. What is so surprising about SSMA is that it is the common-place dialectic differences that it fails to convert. What were the developers of SSMA for Access thinking about? ®

High performance access to file storage

More from The Register

next story
Android engineer: We DIDN'T copy Apple OR follow Samsung's orders
Veep testifies for Samsung during Apple patent trial
Windows 8.1, which you probably haven't upgraded to yet, ALREADY OBSOLETE
Pre-Update versions of new Windows version will no longer support patches
Microsoft lobs pre-release Windows Phone 8.1 at devs who dare
App makers can load it before anyone else, but if they do they're stuck with it
This time it's 'Personal': new Office 365 sub covers just two devices
Redmond also brings Office into Google's back yard
Half of Twitter's 'active users' are SILENT STALKERS
Nearly 50% have NEVER tweeted a word
Batten down the hatches, Ubuntu 14.04 LTS due in TWO DAYS
Admins dab straining server brows in advance of Trusty Tahr's long-term support landing
Windows XP still has 27 per cent market share on its deathbed
Windows 7 making some gains on XP Death Day
Internet-of-stuff startup dumps NoSQL for ... SQL?
NoSQL taste great at first but lacks proper nutrients, says startup cloud whiz
US taxman blows Win XP deadline, must now spend millions on custom support
Gov't IT likened to 'a Model T with a lot of things on top of it'
prev story

Whitepapers

Securing web applications made simple and scalable
In this whitepaper learn how automated security testing can provide a simple and scalable way to protect your web applications.
Five 3D headsets to be won!
We were so impressed by the Durovis Dive headset we’ve asked the company to give some away to Reg readers.
HP ArcSight ESM solution helps Finansbank
Based on their experience using HP ArcSight Enterprise Security Manager for IT security operations, Finansbank moved to HP ArcSight ESM for fraud management.
The benefits of software based PBX
Why you should break free from your proprietary PBX and how to leverage your existing server hardware.
Mobile application security study
Download this report to see the alarming realities regarding the sheer number of applications vulnerable to attack, as well as the most common and easily addressable vulnerability errors.