Portfolio

MS Access to SQL Server migration

Recycling company solves data corruption issues through cloud database integration

Influential Software performed a Microsoft Access to SQL Server migration that empowered remote workers to access data with full stability and security.

People with database representing MS Access to SQL Server migration

Executive brief

Building resilience with Microsoft Access to SQL Server migration

Our client is a recycling company specialising in paper recovery. Influential Software delivered a Microsoft Access to SQL Server migration solution that enabled the company’s staff to work remotely. This Azure-based SQL Server database brought the following benefits:

data corruption issues solved

one source of accurate data

enabling remote working

secure & scalable database

Curious about how we achieved this through an MS Access to SQL Server migration? Read on to learn more.

The full story

Purple arrow encouraging readers to scroll down for the rest of the GDPR data solutions case study

The challenge

Corrupted MS Access database was wasting time

Years previously, the company had paid someone to create a MS Access database for customer, order, supplier, and sales data. But over time it became clear that the database was not behaving properly, as the data was regularly being corrupted. The cause of this corruption was the use of an inefficient fileshare connection which staff were using to access the database remotely.

 

As a result of the corrupted MS Access database, the company had to restore the database several times a week. This meant that all work since the last backup was lost, and no-one could use the system during the restore. And this contributed to the company losing around a day of work time per month.

 

Overall, the negative effects of the corrupted Access database were:

 

  • inefficiency caused by a lack of trust in company data
  • possible security breaches due to the unencrypted fileshare
  • loss of productivity with hours wasted on database restores

 

In brief, these poorly integrated technologies were costing the company time and money. It was clear that a modern and well-implemented database solution was needed.

The solution

Performing MS Access to SQL Server migration

Initially, the recycling company approached us to support its ailing MS Access database. On investigation we discovered that the database had not been split into two, as per MS Access best practice. Making this split — with the tables separate from the queries, forms, and reports — gave an improvement in performance, reliability, and security.

 

However, the database was still experiencing some corruption. The cause, we determined, was the fileshare connection used to interact with the database remotely. We addressed this issue by replacing the fileshare with a new router that supported a virtual private network (VPN). Because the VPN was stable and end-to-end encrypted, this solved the connectivity and security issues of the old fileshare.

 

In the short term, this solution worked perfectly — the staff now had uninterrupted access to reliable company data.

 

But Access also had long-term disadvantages, such as corruption, compliance issues, and lack of scalability. Our client’s management recognised this as a barrier to the company’s agility and growth.

 

For this reason, we proposed moving the company database to an enterprise-grade SQL Server in the Azure cloud. By keeping the Access application as the interface for the new database, we maintained the same user experience. This guaranteed full adoption and mitigated the time and cost of user training.

The benefits

An Azure cloud database for long-term growth

While this project started with a short-term goal (solving database corruption), it turned into an opportunity for long-term IT modernisation. By recommending that our client move from an MS Access database to a SQL Server one in Azure, we achieved both goals in one solution. This “two-birds-with-one-stone” approach was ultimately more economical for the client and gave them a cost-effective foundation for future IT projects.

 

Overall, the benefits of the decision to migrate a Microsoft Access database to SQL Server in Azure were:

 

  • enabling staff to access data remotely at all times
  • restoring faith in the accuracy of company data
  • regaining the hours lost to restoring corrupted data
  • providing a scalable and flexible cloud-based platform

Get help from the data experts

This recycling client is one of dozens that benefit from our data integration expertise. Whatever your industry or size, we’ll work to maximise the value of your business data. To find out more, visit our integration solutions page.

Info

5th November 2020
SQL Server, data migration, integration, Azure

Privacy Preference Center

Necessary

Advertising

Analytics

Analytics cookies collect information that is used either in aggregate form to help us understand how our Websites are being used or how effective our marketing campaigns are, or to help us customise our Websites for you.

Google Analytics
The cookie _gcl_au is used by Google Analytics to understand user interaction with the website.

For example, in order for Google Analytics to determine that two distinct hits belong to the same user, a unique identifier, associated with that particular user, must be sent with each hit.

The analytics.js library accomplishes this via the Client ID field, a unique, randomly generated string that gets stored in the browsers cookies, so subsequent visits to the same site can be associated with the same user.

By default, analytics.js uses a single, first-party cookie named _ga to store the Client ID, but the cookie's name, domain, and expiration time can all be customized. Other cookies created by analytics.js include _gid, AMP_TOKEN and _gac_. These cookies store other randomly generated ids and campaign information about the user.

Google Analytics
_gcl_au, _gid, _ga, gtm_preview

Other

WordPress uses cookies for authentication. That means that in order to log in to our WordPress site, you must have cookies enabled in your browser.

There are two types of cookies set by WordPress.
1 — Session cookies — These are ‘strictly necessary’ cookies as WordPress will not be able to function without it.
2 — Comment cookies — These are not ‘strictly necessary’ cookies and are set when users leave a comment on a post.

Wordpress Session cookies:
Users are those people who have registered an account with the WordPress site.
wordpress_[hash]
wordpress_logged_in_[hash]
wordpress_test_cookie
wp-settings-{time}-[UID]

Wordpress comments:
Comments are usually turned off by default.
If by chance they are still active on a post, asides being turned off when spotted, data from these are not saved by Influential.
- When visitors comment on a post, they too get cookies stored on their computer. This is purely a convenience so that the visitor won’t need to re-type all their information again when they want to leave another comment. Three cookies are set for commenters:
comment_author_{HASH}
comment_author_email_{HASH}
comment_author_url_{HASH}

Wordpress, Intercom
comment_author_{HASH} comment_author_email_{HASH} comment_author_url_{HASH} wordpress_[hash] wordpress_logged_in_[hash] wordpress_test_cookie wp-settings-{time}-[UID]
intercom-id-[app_id],intercom-session-[add-id]