Bring out your dead databases: migrating from Access to SQL Server
Big organisations can have thousands of `home-grown' Access databases - some critically important - that users have created over the years without telling IT managers, creating a potential security, management and data protection nightmare. Mark Anderson, data platform technical specialist at Microsoft UK, explains how to modernise these old systems.
Many organisations use Microsoft's database application, Access, to store data, create data entry forms and produce reports. It has proven to be very popular with Microsoft Office users, but tends to be implemented without the involvement of the IT department, so that when modifications are required, there is no need to fill in those tiresome development change request forms or to follow strict IT procedures.
That sounds like the holy grail of IT for the user community, but what does it mean for the IT and compliance departments?
Too many disconnected databases
Having worked with customers across sectors ranging from finance, to government, to retail, the issue of a large number of Access databases throughout their estates is a recurring theme. It is common for a customer to tell me that, following an audit, they have discovered more than 10,000 Access databases across an organisation.
"It is common for a customer to tell me that, following an audit, they have discovered more than 10,000 Access databases across an organisation."
What is even more unbelievable than the sheer number of databases is that some of them have managed - through a bizarre process of evolution - to become as mission-critical to a department's operation as a line of business application running with five nines high availability and a 24/7 team of database administrators.
Who has got what data?
Aside from the operational and technical issues that organisations face, how many have stopped for a moment to consider the impact of basic legislation such as the Data Protection Act? How many different locations have your customers' details managed to propagate to within these databases? Who is using this data and for what purpose? One department in a company may, for instance, be unaware that a customer has phoned another to say that they do not wish to receive telesales calls, because each department has its own Access database.
We can therefore be faced with a system that is critical to an organisation's operation where we have several areas of concern such as security, reliability, availability, recoverability and scalability. How do you address these concerns with a user-developed Access database that the IT department has had no involvement with? In short, you probably don't.
A spring clean to remove dead files
So what is the answer? Well, there isn't a definitive one but there are a series of things you can and should be doing.
The first is to start with an audit of your network to find all of these databases. The second task - which may be seen to be optional but which I highly recommend - is to perform a "spring clean". You will probably find that the majority of these databases have not been used or accessed for many years, or were simply a project that was started and never finished. A quick email to the owner to check whether a given file needs to be kept is normally all that is required and most of the time the file can be deleted and removed from the network.
The final task (before we get into the technicalities), which is slightly trickier, is to identify which of these databases have become fundamental to a department's operation or have a large number of users. This is often the most painful task as it may involve reviewing specific databases with their owners and users.
Once you have completed the three initial steps involving the discovery, removal of "dead" databases and identification of the data which is vital to the daily operation of your business, you can move on to reviewing several other key factors:
Database size
Typical number of concurrent users
Impact on the business in the event of loss of service
Application profile (i.e. read-only reporting system or data entry)
Data security / sensitivity
Your upgrade options
"A 3MB Access 97 database used by the facilities team manager to track maintenance supplies is probably just in need of a simple upgrade to the latest version of Access."
Do not be surprised to find that you only end up with 5 to 10 per cent of the total number of databases you originally discovered on the network. Once you have the answers to the above questions, you will be able to start making decisions on the most appropriate way forward.
In my opinion, there are three main courses of action:
Upgrade the database to the latest version of Access: Access is still a great tool for small-scale, rapid application development of databases and simply moving the database to the latest version and placing it in a centrally managed location for backup may satisfy your needs.
Migrate the data/keep the Access application: Keep the Access user entry forms and reports, migrate the data to SQL Server and link the two together.
Rewrite the application: Open up Visual Studio and start coding your application as an ASP.NET or .NET Forms application using SQL Server as the data repository and SQL Server Reporting Services as the reporting tool.
So, for example, we may have a one gigabyte Access 2003 database used by 15 to 20 people in a busy call centre for customer issue tracking that involves not only the querying of existing call data but also the input of updated information.
Applying the rules mentioned above - such as business criticality, database size, concurrent connections and data sensitivity - this kind of database is a likely candidate for an application rewrite, where the data store is moved to a SQL Server platform and the front end recreated as an ASP.NET or windows forms application.
At the other end of the spectrum, a 3MB Access 97 database used by the facilities team manager to track maintenance supplies is probably just in need of a simple upgrade to the latest version of Access.
Get expert help with migration
The good news is that the above process is not as scary as it may first appear. A large volume of databases can be quickly reduced in the initial clean-up phase.
Later this year, Microsoft is releasing a SQL Server Migration Assistant for Access that assists with the discovery and migration phases of the process. If the skills do not exist in-house to perform any upsizing or rewrites needed, there are many organisations that specialise in migrating databases in Access to a SQL platform.
The key is thing is to ensure that those `home-grown' databases created by users which are operationally important are moved to a platform where they can be managed and maintained in line with all other corporate data sources. This will not only help you to meet your operational IT commitment but also secure your important and sensitive data, making legislative compliance far simpler.
Related links