Feeds

MUST is a must

A good addition for migrating Access DBs

  • alert
  • submit to reddit

Boost IT visibility and business value

The task of migrating or upsizing Microsoft Access databases is a common one, particularly for small businesses that are growing well and feel a real need to move. Microsoft's SSMA (SQL Server Migration Assistant) is an obvious candidate, but here is an alternative that compares well.

Microsoft's SSMA

In October last year I took a look at SSMA. While it was much, much better than doing the job by hand, it still suffered from a whole series of bugs, mainly to do with translating queries into views and the creation of validation rules. It also had a somewhat idiosyncratic interface.

The good news is that the most recent version – 1.2.852 - corrects all the anomalies I discussed earlier and the good points of the tool are still as described. In addition, a wizard has been added that papers over the cracks of what is still a pretty unfriendly bit of software.

It's free and available for download.

ASC's MUST

However, I've also been looking at a tool called MUST from ASC Associates: A demo version with limitations can be downloaded from this site.

Given that both SSMA and MUST do the same job, it is surprising they manage to be so different. Before you even run the package you notice that ASC's Migration Upsizing SQL Tool (MUST) is written as an Access database so it approaches the problem from an Access perspective.

The company concerned has vast experience in Access development and has also drawn on the experience of upsizing a large number of databases to create the tool. And as soon as you run MUST it becomes apparent that this is a tool written by developers for developers.

The bad news (but not very bad) is that the interface, while entirely logical and easy to follow, is nothing fancy. There are no exotic fonts, no designer splash screens. On the other hand, do you want designer software or something that was written by like-minded souls with experience? I'll take the latter.

SSMA tends to attempt the entire upsizing process, where it fails on all sorts of points and presents you with a list of failures, errors, and warnings which you then set to and fix. Naturally, this means that errors compound – if a table cannot be created then queries that use it will also fail.

In direct contrast, MUST walks you through the process of upsizing the database one step at a time. MUST runs an assessment of the database to be converted and highlights the problems that it encounters as soon as they arise. The software helps you to resolve the problems there and then by providing helpful suggestions and offering sensible default solutions to common upgrading issues.

For example, Access doesn't automatically provide an explicit default value for a Yes/No field. MUST suggests setting the default in SQL Server to No and automatically offers to do this for you unless you decide otherwise. Errors are prevented from compounding and by the time the conversion is run, it has a very good chance of completing successfully. It almost feels as if there is an experienced developer watching over your shoulder as the work proceeds. It offers three levels of control from simple to complex - while database migration is never easy, MUST takes a good stab at making it so.

However, MUST has an odd omission. It doesn't even attempt to convert the Access queries to SQL Server views, it simply leaves them as queries in the Access database. This works if you plan to continue to use Access as your front-end, but certainly doesn't if you don't.

I am painfully aware that deciding whether to move queries is a complex area: some are easy to move, some are a nightmare for reasons of performance, nesting, scalability, and complexity. To further complicate the issue, some may be better converted to stored procedures.

However, if you do want to move them, MUST can't help at present. ASC is looking at this issue and is likely to incorporate it into a future version sometime in the next two months.

MUST costs £319 (£280 plus VAT) for a single-developer license (46 per cent discount to full members of the UK Access User Group) and just under £1,000 for a five-seat license. Microsoft' SSMA is free.

So, which would I recommend? That's easy. The cost of ASC's tool is trivial compared to the work it will do for you. SSMA is free so downloading it is a no-brainer. Both tools have their strengths and weaknesses, neither is difficult to learn, so get to know both and use them appropriately.

As a general rule, I've been using MUST to do the main upsizing of the database and then using SSMA to upsize only those queries I want to move. So far this has proved an unbeatable combination. ®

The essential guide to IT transformation

More from The Register

next story
Munich considers dumping Linux for ... GULP ... Windows!
Give a penguinista a hug, the Outlook's not good for open source's poster child
The Return of BSOD: Does ANYONE trust Microsoft patches?
Sysadmins, you're either fighting fires or seen as incompetents now
Intel's Raspberry Pi rival Galileo can now run Windows
Behold the Internet of Things. Wintel Things
Microsoft cries UNINSTALL in the wake of Blue Screens of Death™
Cache crash causes contained choloric calamity
Eat up Martha! Microsoft slings handwriting recog into OneNote on Android
Freehand input on non-Windows kit for the first time
Time to move away from Windows 7 ... whoa, whoa, who said anything about Windows 8?
Start migrating now to avoid another XPocalypse – Gartner
You'll find Yoda at the back of every IT conference
The piss always taking is he. Bastard the.
prev story

Whitepapers

5 things you didn’t know about cloud backup
IT departments are embracing cloud backup, but there’s a lot you need to know before choosing a service provider. Learn all the critical things you need to know.
Implementing global e-invoicing with guaranteed legal certainty
Explaining the role local tax compliance plays in successful supply chain management and e-business and how leading global brands are addressing this.
Build a business case: developing custom apps
Learn how to maximize the value of custom applications by accelerating and simplifying their development.
Rethinking backup and recovery in the modern data center
Combining intelligence, operational analytics, and automation to enable efficient, data-driven IT organizations using the HP ABR approach.
Next gen security for virtualised datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.