Lessons Learned: Migrating Access to SQL Server

Recently we’ve been involved with transitioning an internal business database from a split Access database (one Access file holding the UI, another Access file holding the data tables) to a Access front-end with an SQL Server back-end. The purpose of this is to eventually migrate away from Access to an ASP.NET web application, but still providing backwards compatibility and an easier migration path. In the course of doing this, we’ve learned a few things:

  1. If set up correctly, the SQL Server Migration Assistant for Access (SSMA) is your best friend and will save you hours. If set up incorrectly, it’s your worst nightmare. In particular, we had a lot of trouble migrating straight from Access 2013 to SQL Server 2016 (as the software is built for SQL Server 2014). Our workaround was to migrate to an Azure SQL database (which is supported) and then move the database to our local server. Note that there are some data security concerns doing this compared to a straight local migration (as your data briefly goes off-site), but in our situation it worked really well.
  2. SSMA also defaulted to using DateTime2 as the SQL Server data type to replace Access’ Date/Time type. Unfortunately, Access doesn’t recognise DateTime2 and treats it as a text field instead, meaning you have to rewrite a lot of queries and code if you stick with that. However, there is the option to use DateTime in the migration, which Access does recognise. DateTime2 does have some added accuracy compared to DateTime, but DateTime should be sufficient in 95% of cases.
  3. After migrating, our second new best friend was the SQL Server Profiler, which allowed us to see what Access was requesting from the server. What was it requesting? e-v-e-r-y-t-h-i-n-g. There were some badly written queries in the Access front-end where the query ended up pulling every row from every related table, one row at a time. Very slow even run on the server. Over the local network, it took literal hours to complete a reporting run. Which brings us to our next point…
  4. If you have a complex query in your front-end, rewrite it as an SQL Server View. Because Views are executed on the server, and only the result set is passed to the client, it can hugely speed up processing on large datasets. It also means you can write queries in standard T-SQL rather than Access SQL. The one downside to this is that if you have a form in Access that is backed by a query, you’ll need to rewrite parts of the form, as an Access form cannot (easily) be backed by a View.

One we realised and adopted these techniques, we were able to complete the migration with much less pain and a lot more fun. Bringing 1990’s technologies and business processes kicking and screaming into the 21st century gave us a lot of satisfaction, and laid the groundwork for the ASP.NET website to come.

New Website Theme! (Available On GitHub)

This website’s new theme is a slightly-modified version of WordPress’ built-in “Twenty Seventeen” theme. We’ve modified the footer and front page to be a little simpler than the base theme.

We’ve just released our changes to the theme as a child theme, and made them available on GitHub as “TwentyFoo“. This public repository will track the website’s theme as it undergoes any code changes.

It’s also our first public (and very small!) WordPress contribution. Whilst we’ve done a lot with WordPress for our clients in the past, we haven’t been able to make any of it public yet, and that made us a little sad. So with this, we’re making the first forays into the world of open-source CMS contributions.

Check it out!

My Six Months With The Foo Project

Coming to the end of a PhD presents students like myself with something of a dilemma. Although the experience had been extremely valuable and I am, in as modest a fashion as can be permitted, proud of my achievements during that time, I can’t earnestly say I’m interested in continuing an academic career. Research has been now the focus of my professional life for 7 years, and it may well continue to be but the lack of a concrete objective has sometimes made the work difficult and, rarely, unsatisfying. Some of my colleagues have found the same, and moved out of research into engineering, software development and other fields.

To fund the last few months of the writing up process, I signed on with The Foo Project, a one-man business software company. The owner, Jack Scott has been running the company since 2012 (around the same time I started my PhD incidentally), and I joined up to work as a software engineer and where necessary lend a little UX expertise to the work.

When I first started, the company was a bedroom operation, and I worked from home and communicated via Slack, although after a month we moved into a small office on Elizabeth Street. The project I spent most of my time on here was the ‘VAS Reading Echo’ system, a piece of business software designed to test a child’s VAS, or ‘visual attention span’, a metric designed to help quantify and explain early-development reading skills and suggest solutions. For my part, that meant learning web programming effectively from scratch and working with a large pre-existing code base across two platforms and five languages.

The work has been an extremely refreshing change from my previous experience; web development is the polar opposite to blue-sky research. A day on this project would typically involve adding additional functionality per a spec, identifying issues with existing functionality and resolving them; each step discrete, concrete and solvable. Working with an existing code base is challenging but for someone used to the fruit of their work being a publication, watching a website come together is pretty satisfying.

It’s been a great six months here. I’ve learned a lot about commercial software development, and rather pleasingly another little bit of the internet now has my mark on it.

10 Things To Look For In A Software Development Consultancy

Photo courtesy of HackNY.

1. Technical Ability

First of all, figure out to the best of your ability whether the firm can actually do the work. This is by far the hardest aspect to judge – most software developers don’t even know themselves whether they can do something (or indeed whether it can be done at all!) until after they’ve done it for the first time.

2. Approach to Project Management

Amongst IT consultants, you’ll find two different groups when it comes to project management. The first half take a few notes down on a scrap of paper, then work away by themselves, bringing back a product that vaguely resembles their notes. The second group meets you several times to gather a full specifications document, then as development progresses they meet you regularly to discuss progress and show what has been done so far. Have a guess which group will get you a better product.

3. Specialisation

No software developer is good at everything, and you should be wary of any that tell you they are – you’ll find they just don’t know how much they don’t know! In looking for a consultancy, search for one that specialises in the area you’re after. If you want a marketing website don’t ask a database expert to build it, as it won’t be any good. Likewise, if you’re after a customer management database (commonly known as a CRM), a firm specialising in marketing websites won’t do the best possible job.

4. A Good Portfolio

Taking a look at a company’s portfolio will tell you a lot about them. If they’ve got a portfolio page full of projects previously completed, that’s a good sign that they can follow through on their promise. Go and have a look at the results of their work. You’ll be able to judge some aspects of the quality, though do keep in mind that the client’s budget does impact on the result.

5. Delivery Time

There’s no point in finding the perfect consultant that will do a fantastic job if they’re busy for the next three months and you need your project done in two. A consultant with a busy schedule generally means they’re popular and do good work. On the flip side, it can mean that their project management skills are lacking – this is definitely a double-edged sword!

6. Quality

If you have access to the portfolio of a consultant, you can have a look and check some basic things that are tell-tales of quality, no matter the area of development. Is spelling and grammar correct? Do elements on the page line up with each other? These sorts of things can be checked by everybody, not just other developers.

Some developers will also share programs with each other (and sometimes pieces of programs, called libraries) in a community known as open-source. If a developer is willing to share their work with others, this is a general indication that they’re not ashamed by the quality of their work. This, of course, is a good sign. We share some of our code on both GitHub and BitBucket.

7. After-Development Support

Before entering into a contract with a development firm, make sure they can provide support and additional development down the track. You’ll probably have questions about the software down the track, and it’s probable that you’ll want additional development work done at some point. Any firm worth their salt will provide a support and maintenance contract, though you’ll generally have to pay for this privilege.

8. Reputation

Once you have a few firms short-listed, it’s worth checking out what other people think of them. Search for their name on Google and try to find reviews, both positive and negative. Alternatively, ask around your networks (friends, business contacts, etc.) to see if anybody has heard anything good or bad about the firm.

9. Professionalism

Whilst wearing a collared shirt doesn’t have any impact on a software developer’s ability, you’ll want to find a consultant who looks presentable, is reliably on-time to meetings, follows up emails quickly, and gives you the impression that they take business seriously.

10. Recommendations

By far the best way to pick any IT consultant is to get recommendations from people who’ve previously engaged one. They’ll be able to tell you straight away whether a firm was easy to deal with, whether they got the work done, and most importantly, whether they would choose that firm again.

The Basics of Search Engine Optimisation (SEO)

A lot of people consider search engine optimisation a black art, something completely unintelligible to us mere mortals. That’s not true. Whilst some of the more advanced techniques really are magic, 90% of SEO boils down into one simple statement:

If you make your website better for a human reader, then it will be better for a computer too.

So what does this look like in practise? Well, here are five ways.

1: Content is King

If you create better content, whether that be blog posts or better static pages, there is more information for a search engine crawler to look through. With more words on the page, you’re more likely to get a match for something somebody types into a search engine. However, if you over-use keywords to the point where a human reader won’t want to read it, you will be penalised by all the major search engines. The search engines want you to have lots of well-written content!

2: Make your website easy to navigate

Make sure every page on site is easy to get to. If it’s impossible for a human reader to get to a page, a search engine cannot find it either. And with better navigation and more descriptive links, you will find both users and search engines having a much easier time. If you really want to make sure that a search engine can find everything on your site, create a sitemap, which is a computer-readable menu designed specifically for search engines. If you’re using the WordPress blogging engine, installing the Google Sitemap Generator plugin will do this for you.

3: Make sure your content isn’t duplicated

There’s two parts to this: First of all, don’t steal content from other websites and have it on your pages verbatim. You’ll be penalised by the search engines, and your users will be unhappy. Secondly, make sure there is only one URL for each page on your site. Do you have http://example.com/about and http://www.example.com/about? That looks like two different pages to the search engines. You’ll want to use HTTP redirects to point everything to a single canonical URL. On The Foo Project’s website, we have five possible URLs for our about page: http://www.fooproject.com/about, http://fooproject.com/about, http://fooproject.com.au/about, http://www.fooproject.com.au/about and https://fooproject.com/about. If each of those pages was treated separately by Google (or any other search engine, such as Bing or Yahoo!), we would be splitting any possible traffic five ways. We point everything to our SSL-enabled site, in order to make search engines only look at that single domain.

4: Add descriptive text to all images

Computers can’t look at images and understand them. By adding alt tags to your images, as well as captions where appropriate, you can give search engines some idea of what the image might contain. Google can’t guess that an image is a photo of a tree, but if you tell it, then Google won’t have to guess at all! Providing captions and alt tags has another benefit: it helps blind users! The blind (and other vision impaired users) can also use the alt tags and descriptive text to understand an image.

5: Make your website as fast as you possibly can

Readers don’t like to wait for a slow website. Research suggest they won’t wait longer than a few seconds for a page to load. If your website takes more than two seconds to load over a reasonable broadband connection, there is scope for improvement. By having a slow website, not only are you annoying your users (who have to wait) but most search engines will penalise you for speed as well. The best resource here is Google’s PageSpeed Insights, which will give you a score and suggest how to improve.

So there you have it. Five basic things you can do today (Seriously! Start now!) to make your website nicer for your users and rank higher on the search engines.

Integrating IIS with JIRA or Confluence Results in HTTP 500 Error

While I was setting up our internal JIRA and Confluence instances, I wanted to give them “proper” URLs, such as jira.fooproject.com and confluence.fooproject.com. We use Microsoft’s IIS web server for all of our other websites, so we wanted to use this as a reverse proxy. Atlassian has instructions for doing this, which I dutifully followed. The result? This:

jira-500-error

After googling away for several hours, I was stumped. Nobody else was having the same issue I was. It was obvious that the request was never getting as far as the actual JIRA or Confluence web service – the problem was very much with IIS.

Eventually, I found the solution: bad file permissions (isn’t it always?). I had forgotten that the configuration for an IIS website is not stored in a database or the registry; it is stored in the file system, in the subfolder for that IIS website (in the web.config file, for the curious). By not setting file permissions on the website’s subfolder correctly, IIS wasn’t able to read the web.config file, which contained the URL rewrite information.

The final solution is to add the Read permission for Everyone on the website’s subfolder:

confluence-file-permissions

Viola!

We Have an Office!

Inside the upstairs of our new office.
Inside the upstairs of our new office.

The Foo Project is now in it’s first offices. We’ve taken up residence inside Parliament Co-working, a co-working space on Hobart’s waterfront. We’re pretty excited, the offices are well laid out and have everything a growing company like ours needs (Internet access and a kettle, basically)…

Our New Website

You may have noticed some changes recently on our website. We’ve done a complete overhaul, with too many changes to mention, but here are some of the biggest:

  • We’ve completely overhauled the appearance of our website (obviously). The main page is the most drastic difference, but almost everything has had a bit of a change.
  • Our platform has changed from Drupal to WordPress. We felt that Drupal’s development was becoming stale, with no new significant features in a couple of years now. WordPress, on the other hand, has come leaps and bounds in the past few years, and now is a fully fledged CMS.
  • We’ve put more information on our services pages: for development, for IT support, and for managed hosting. Hopefully this will help you find the information you need on what we can provide for you!
  • There is now a lot more information on our staff page, with a lot more to come in the future.

To sum up, we’re not excited about how far we’ve come with the new website, but how far we have to go. And WordPress is definitely the tool to take us there.