Free ESG report : Seamless data management with Avere FXT

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

5 ways to reduce advertising network latency