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.