I know this might seem like an odd topic on a SQL Server blog, but it's a common and critical enough problem that I want to address it. (The audience of this blog post is for business managers, departmental decision makers, and yes, even human resources professionals.)
As a consultant for an information technology services company, I have seen far too often in my time the risks and outcomes of years of "lazy" "investment" in Microsoft Access as the front-end and repository for enterprise-level data.
I've seen multiple companies, across a wide variety of industries, commit these sins of Microsoft Access:
- Mission critical loan decision-making data in Access, sitting on a VP's desktop PC (with no backup)
- AR and AP inside a simple Access app, forcing a single office worker bee to do both, a clear GAAP violation
- Company without ability to fix/modify their billing system when the legacy developer left, and no skillset left in house for the tangle of VBA code embedded in Access
- Company unaware of Access size limitations, forced emergency redesign just to send invoices
- Access data corrupted because of inadvertent office worker use, no alternative but to manually re-enter data
- Field data dumped from Excel into Access so that it is "queryable", without any validation or integrity
- Entrenched Access data is difficult and expensive to integrate with newer, modern systems that the company is investing in
Many non-technical users are not aware that Access databases cannot exceed 2 GB in size. Aside from size limitations, Access also provides no enterprise-level point-in-time backup and recovery. It's backed up just like any other file, which is to say, no more survivable than nightly.
Access is also not designed for concurrency. It may get along fine when two or three people use it, but the lightweight database engine simply is not designed to support multiple users. If it works for your team of users, data corruption is probable.
You've probably already encountered this, and warned users to "stay out of the database" while important data entry is occurring.
Microsoft documentation is full of caveats about the lack of scalability for Access:
"Microsoft Jet [the database engine inside Access] can only handle a limited number of sessions." - http://support.microsoft.com/kb/225048/en-us
"...when multiple concurrent users make requests of a Microsoft Access database, unpredictable results may occur." - http://support.microsoft.com/kb/299973
Microsoft SQL Server is much more suited to handle your data with integrity, with disaster recoverability, and with performance. SQL Server Reporting Services can be used by developers to serve faster-loading, automatically-updated and highly securable read-only reports in your web browser to users above and below you on the org chart. SQL Server Integration Services can be used to pump data from heterogeneous data sources (excel files, text files, or other database platforms such as Oracle.) Microsoft SharePoint is an excellent provider of document sharing and office collaboration, while also providing a quick and easy way for developers to create data entry forms that run in your web browser.
Typically when consulting on a large legacy Access migration, where a company is feeling the squeeze from their overmatched old Access platform, I recommend a combination of Microsoft SQL Server and Microsoft SharePoint. Using a multi-stage migration, we can first move data out of Access and onto SQL Server using "linked tables", then migrate custom forms and report functionality to SharePoint and Reporting Services one by one.
These products work well together and you may be surprised to learn that they are already in your corporate IT environment.
The Real Advantages
Microsoft Access is not an enterprise database engine in the practical sense. It is a database simulator. It isn't until becoming aware of the advantages of an enterprise database server that this distinction isn't clear.
Using a real database platform like SQL Server also allows your IT department to be more involved in its maintenance and administration, which is a good thing, and will certainly help them sleep better at night.
Remember, your IT folks probably can't help you recover a mission-critical Access database they didn't know existed.
So to wrap us up, and because this is actually a SQL Server blog, let's go over some of the major differences between Access and SQL Server, from a bit of a technical angle.
MS Access vs SQL Server
Higher concurrency: Microsoft SQL Server is designed for access by multiple users, and is far superior to Access database when asked to handle simultaneous reads and writes. It's not the fault of Access, it just wasn't designed for use in all but the more simplistic environments.
File Size: Most versions of Access have a file size limitation of at most 2 GB per file, which may or may not be large enough to handle multiple years of your data. Access also cannot leverage all of the memory or processors (severely limited) on any given desktop or server. Microsoft SQL Server has no such limitations on size or memory.
64-bit Performance: Access' Jet database engine was not created to leverage multiple CPU's, and still relies on old 32-bit processing. In the past decade, most operating systems and applications have moved to more efficient 64-bit processing. SQL Server provides both 32-bit and 64-bit versions, but 64-bit is highly recommended.
Disaster Recovery: The most important advantage of Microsoft SQL Server is disaster recovery capability, including point-in-time recovery, which can roll forward to the point before a data was lost or corrupted, and reduce the data loss tolerance in the case of disk failure. Using SQL Server backups, a database currently in use can be automatically backed up on a schedule without user interruption.
- Access database corruption is a danger and the only fallback would be the restoration of a previous version of the database file, if such a backup exists.
- To perform an Access database backup within the application, you must manually initiate this and close all objects first.
High Availability: Using AlwaysOn Availability Groups in SQL Server 2012, Microsoft SQL Server can provide manual or automatic failover across geographic distances using Windows Failover Clustering. This can also provide a readable, live remote copy of the data that can serve reports. Access has no ability to provide this kind of failover or redundancy.
Scalability: Reporting Services and Integration Services are two built-in features of Microsoft SQL Server that are much faster to develop, maintain, and deploy custom-built reports. They can be used to replace highly-customized Access remote queries, linked tables and reports, for many users and for large amounts of data.
- “Archiving” data by moving it out of active tables and into older passive tables is an undesired source of complexity in the Access database environment. SQL Server Enterprise edition’s Horizontal Partitioning feature on tables can transparently achieve the advantages of spreading data across multiple physical disk volumes without coding applications to read from both "current” from “archive” tables.
Relational Integrity and Index: SQL Server has superior foreign key, primary key, uniqueness and constraint enforcement. Indexing and compression are far more robust in SQL Server, which improves data integrity, performance and disk optimization. Datatypes in SQL Server closely resemble those in Microsoft Access, but with a much wider range of capability.
Security: Microsoft SQL Server Enterprise Edition satisfies military and government standards for security, internal auditing and encryption, and with more granular security permissions for users in different roles in the company.
