COMPAREX

MS SQL Server
Good Reasons to Start Migrating Your Data

Excel And Access: When Does it Make Sense to Migrate Your Data to a SQL Server?

Currently, issues relating to Microsoft Azure and specifically the hybrid cloud are the talk of the town. But what happens if a company has stored its information in Excel files and Access databases? An intermediate step via SQL Server is necessary in these cases. At which point is it worth considering data migration to SQL Server, and what must be taken into consideration in this context? This article by our blog authors Jens Hocker, Senior Consultant MS SQL Server, and Alexander Perlbach, Software Developer at COMPAREX, is intended to shed light on the topic.

Not much collaboration, performance disruptions and availability problems: The limits of Excel and Access

It is not uncommon to encounter a situation in companies in which data is stored in a semi-structured form in Excel files and Access databases. There’s nothing amiss about proceeding this way, as long as the data volumes are manageably sized and only a few people work with them. But things get a little trickier when the amount of data grows over time or it eventually becomes apparent that the stored information is relevant to a larger group of people than was originally assumed.

When several employees are working simultaneously with the data, it is not unheard of that the employee who just opened the coveted Excel spreadsheet has just headed off for his or her well-deserved lunch break. But colleagues need to make changes to the data in the meantime. It goes without saying that the lunch break will be particularly lengthy on this day especially, laying bare the limitations on using Excel. Access is not much help in these situations, either: If exclusive access permissions have been defined by mistake, other users will not even be able to read the content of the database.

What’s more, Access databases have the unpleasant tendency of becoming larger every time they are used (temporary objects, e.g. temporary queries of drop-down menus in forms etc.). It is not unusual in these cases that Access databases that have worked wonderfully in the past will seemingly get slower every day, to the point at which they actually become more or less useless for any kind of productive work.

Information published by the Nielsen Norman Group indicates that the following limits apply to waiting times for websites to respond: 0.1 seconds are perceived as an immediate reaction, while users view 1 second as waiting time, without it becoming in any way annoying. It does not distract the user’s attention. But concentration tends to subside and the interruption is perceived to be annoying – and the user will turn to other tasks – as soon as the waiting time lasts longer than 1 second. If we transfer this insight to the use of Access databases, it is reasonable to assume that a waiting time of more than one second will interrupt the workflow and therefore impair efficiency.

Performance stability, cloud migration, security: The benefits of Microsoft SQL Server

Anyone nodding in agreement at these scenarios should check out what Microsoft SQL Server has to offer. SQL Server is designed to manage large data volumes without a reduction in performance, while also taking simultaneous access by several persons in its stride.

What’s more, additional options become available as soon as the data is hosted on SQL Server. For instance, the data can be migrated to the cloud or arranged in a hybrid cloud environment.

The security of stored data also improves through migration to SQL Server. Let’s remember, while the current version of Access (Format accdb) only permits the issue of one password for the entire database, SQL Server enables the assignment of granular permissions for each individual user (down to the column level). In addition, Stored Procedures in SQL Server allow users to perform certain activities, without having direct access to the underlying data itself.

But behind many silver linings there is a cloud as well. Employees that until now have managed their own data will initially lose some control after migration to SQL Server. After all, the information will no longer be located on the client computer and will instead be hosted on a central server, where it is accessible for a significantly larger group of people. This uncertainty may only grow more acute if the specific user is then only assigned restricted rights to “their” data, or some of the data is moved to the cloud. But these drawbacks are mitigated by the improved data security.

Besides that, the user is no longer responsible for data maintenance such as backups, regular checks of data integrity and similar tasks. The SQL Server administrators take care of these jobs centrally. This eases the strain on end users, who will have more time to focus on their core objectives.

Data migration to SQL Server: Things to remember

There is a number of things you need to remember during data migration to SQL Server. Alexander Perlbach, our consultant and expert for system integration, application programming and databases, has put together some pointers to keep tabs of what these things are:

“It’s important to consider the data structure before embarking on migration itself. How is the data stored in SQL Server? How normalized should the data be? It is also essential to give some thought beforehand to how the data can be restored to its original condition.

The same set of pitfalls tend to crop up during data migration itself, and they have to be kept in mind. A classic example is when BIT data types need to be created with a default value. Another important fact to remember is that a primary key or unique index must be defined for each table. Other aspects could be added to this list.

Basically, it is at the discretion of customers to hand data migration over to professionals or to take a lot of time and do it themselves. Not to forget, though, that the company will have to contribute to the process, even if they book external support.”

A suitable tool should be selected for the front-end before proceeding to migration, as SQL Server Management Studio is not suitable as an end user interface. The easiest method is to continue using Access as the front-end. In this case, only the data is migrated to SQL Server, and the SQL Server tables are connected in Access. The application itself can remain largely untouched, so users will continue to work in their familiar environments while benefiting from the added data security and from management by SQL Server. But proceeding this way will not make full use of the opportunities of SQL Server, for instance Stored Procedures or features like Views.

The second option would be extended modification of the current Access application to ensure that these features can also be used. And although this variant means a lot more work, it is recommended for larger data volumes especially.

Then there is the alternative of developing a suitable web front-end or programming a client application for database access. While the web front-end comes with the benefit of being independent of the operating system, the desktop application can use all of the features that the operating system offers. Viewed vice versa, the drawback of the web front-end is that all browsers need to be optimized before they are supported. The desktop application will usually require installation, and a suitable protocol for communication must be selected etc. At the end of the day, choosing the ideal front-end is highly individual and must be considered separately on a case-by-case basis.

Summary

We have outlined the limits of Excel and Access in regard to collaboration and performance, and from which point it would be sensible to migrate data to SQL Server. In addition, we have given an idea of how complex data migration like this can become. Feel free to get in touch if you require advice to plan the migration of your data or if we can help you to get the job done.

Leipzig, 08 / 10 / 2018


Comments

Write a comment