Feeds

MUST is a must

A good addition for migrating Access DBs

  • alert
  • submit to reddit

Beginner's guide to SSL certificates

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

Internet Security Threat Report 2014

More from The Register

next story
Download alert: Nearly ALL top 100 Android, iOS paid apps hacked
Attack of the Clones? Yeah, but much, much scarier – report
NSA SOURCE CODE LEAK: Information slurp tools to appear online
Now you can run your own intelligence agency
Whistling Google: PLEASE! Brussels can only hurt Europe, not us
And Commish is VERY pro-Google. Why should we worry?
Microsoft: Your Linux Docker containers are now OURS to command
New tool lets admins wrangle Linux apps from Windows
Soz, web devs: Google snatches its Wallet off the table
Killing off web service in 3 months... but app-happy bonkers are fine
First in line to order a Nexus 6? AT&T has a BRICK for you
Black Screen of Death plagues early Google-mobe batch
prev story

Whitepapers

10 ways wire data helps conquer IT complexity
IT teams can automatically detect problems across the IT environment, spot data theft, select unique pieces of transaction payloads to send to a data source, and more.
Why CIOs should rethink endpoint data protection in the age of mobility
Assessing trends in data protection, specifically with respect to mobile devices, BYOD, and remote employees.
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.
Reg Reader Research: SaaS based Email and Office Productivity Tools
Read this Reg reader report which provides advice and guidance for SMBs towards the use of SaaS based email and Office productivity tools.
Business security measures using SSL
Examines the major types of threats to information security that businesses face today and the techniques for mitigating those threats.