Feeds

Migrating Access to SQL made (almost) easy

Very useful, apart from the dumb bits

  • alert
  • submit to reddit

Top three mobile application threats

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? ®

Combat fraud and increase customer satisfaction

More from The Register

next story
Ubuntu 14.04 LTS: Great changes, but sssh don't mention the...
Why HELLO Amazon! You weren't here last time
Next Windows obsolescence panic is 450 days from … NOW!
The clock is ticking louder for Windows Server 2003 R2 users
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
Microsoft TIER SMEAR changes app prices whether devs ask or not
Some go up, some go down, Redmond goes silent
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
Red Hat to ship RHEL 7 release candidate with a taste of container tech
Grab 'near-final' version of next Enterprise Linux next week
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
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.
Combat fraud and increase customer satisfaction
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.
SANS - Survey on application security programs
In this whitepaper learn about the state of application security programs and practices of 488 surveyed respondents, and discover how mature and effective these programs are.
3 Big data security analytics techniques
Applying these Big Data security analytics techniques can help you make your business safer by detecting attacks early, before significant damage is done.