Feeds

MUST is a must

A good addition for migrating Access DBs

  • alert
  • submit to reddit

The Power of One Brief: Top reasons to choose HP BladeSystem

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

Seven Steps to Software Security

More from The Register

next story
Secure microkernel that uses maths to be 'bug free' goes open source
Hacker-repelling, drone-protecting code will soon be yours to tweak as you see fit
NO MORE ALL CAPS and other pleasures of Visual Studio 14
Unpicking a packed preview that breaks down ASP.NET
KDE releases ice-cream coloured Plasma 5 just in time for summer
Melty but refreshing - popular rival to Mint's Cinnamon's still a work in progress
Leaked Windows Phone 8.1 Update specs tease details of Nokia's next mobes
New screen sizes, dual SIMs, voice over LTE, and more
Put down that Oracle database patch: It could cost $23,000 per CPU
On-by-default INMEMORY tech a boon for developers ... as long as they can afford it
Another day, another Firefox: Version 31 is upon us ALREADY
Web devs, Mozilla really wants you to like this one
Google shows off new Chrome OS look
Athena springs full-grown from Chromium project's head
prev story

Whitepapers

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.
Consolidation: The Foundation for IT Business Transformation
In this whitepaper learn how effective consolidation of IT and business resources can enable multiple, meaningful business benefits.
Application security programs and practises
Follow a few strategies and your organization can gain the full benefits of open source and the cloud without compromising the security of your applications.
How modern custom applications can spur business growth
Learn how to create, deploy and manage custom applications without consuming or expanding the need for scarce, expensive IT resources.
Securing Web Applications Made Simple and Scalable
Learn how automated security testing can provide a simple and scalable way to protect your web applications.