Feeds

MUST is a must

A good addition for migrating Access DBs

  • alert
  • submit to reddit

Internet Security Threat Report 2014

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

Security for virtualized datacentres

More from The Register

next story
Microsoft WINDOWS 10: Seven ATE Nine. Or Eight did really
Windows NEIN skipped, tech preview due out on Wednesday
Business is back, baby! Hasta la VISTA, Win 8... Oh, yeah, Windows 9
Forget touchscreen millennials, Microsoft goes for mouse crowd
Apple: SO sorry for the iOS 8.0.1 UPDATE BUNGLE HORROR
Apple kills 'upgrade'. Hey, Microsoft. You sure you want to be like these guys?
ARM gives Internet of Things a piece of its mind – the Cortex-M7
32-bit core packs some DSP for VIP IoT CPU LOL
Microsoft on the Threshold of a new name for Windows next week
Rebranded OS reportedly set to be flung open by Redmond
Lotus Notes inventor Ozzie invents app to talk to people on your phone
Imagine that. Startup floats with voice collab app for Win iPhone
'Google is NOT the gatekeeper to the web, as some claim'
Plus: 'Pretty sure iOS 8.0.2 will just turn the iPhone into a fax machine'
prev story

Whitepapers

Forging a new future with identity relationship management
Learn about ForgeRock's next generation IRM platform and how it is designed to empower CEOS's and enterprises to engage with consumers.
Storage capacity and performance optimization at Mizuno USA
Mizuno USA turn to Tegile storage technology to solve both their SAN and backup issues.
The next step in data security
With recent increased privacy concerns and computers becoming more powerful, the chance of hackers being able to crack smaller-sized RSA keys increases.
Security for virtualized datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.
A strategic approach to identity relationship management
ForgeRock commissioned Forrester to evaluate companies’ IAM practices and requirements when it comes to customer-facing scenarios versus employee-facing ones.