MS Access Isn't The Answer

Got a simple task? Want to get it done really fast? Don't care about the long-term implications, potential business growth, disaster recovery, performance, or allowing other apps or users to use the data? Then MS Access is right for you!

But really, that's not a very common scenario, is it?

MS Access is frustrating for IT because it is installed with the Office suite and allows power users to begin collecting, storing, and reporting on data that is dead-ended. A lot of clients we've seen have made dangerous assumptions about the limited use and limited lifespan of their MS Access applications, and have ended up in one of these nightmare scenarios:

1. Mission critical data just one cup of coffee away from disappearing, because it's sitting in an MS Access db on a VP's desktop PC (with no backup)

2. Lack of user concurrency forces a single office administrator to handle both AP and AR, a clear GAAP violation

3. The company has invested 15 years in an omnibus Access app for all mission critical activities, only to find that they've hopelessly outgrown it

4. The company has invested 15 years in employing Access '97 developers, only to find they have no developer skillset from the 21st century

5. Unaware of Access's 2gb size limitation, forced to rearchitect before payroll is due

6. Access database had never been backed up, and now that the data is corrupted, Company has no alternative but to manually re-enter data

7. To satisfy government contract, lab data was dumped from Excel into Access so that it is "queryable", without any validation or integrity or usefulness

When it comes to storing enterprise-level data for your company on anything resembling a recurring basis, Microsoft SQL Server is the platform of choice.

It isn't like these limitations are secret, they're wide out in the open in Microsoft technical documentation:

• 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 ...it was not intended (or architected) for high-stress performance... - http://support.microsoft.com/kb/222135/en-us

Here are some key advantages for SQL Server over Access:

• Higher concurrency: Microsoft SQL Server is designed for multiple, concurrent read/write access by multiple users, and is superior to Access database when asked to handle simultaneous reads and writes with integrity.  

• Performance: Microsoft SQL Server is optimized for multiple processor environments with many gigabytes of memory, and has superior caching ability to improve performance. MS Access is multi-threaded, but will use only a single processor.

• Disaster Recovery: The most important advantage of Microsoft SQL Server is disaster recovery capability, including point-in-time recovery with transaction log backups, which can roll forward to the point of a disaster in the case of data corruption, and reduce the data loss tolerance in the case of physical drive failure.  

  • Access database corruption is a danger and the only fallback would appear to be the restoration of a previous version of the database, without any standardized Data Loss Tolerance or point-in-time recovery.

• High Availability: Using database mirror (SQL Server 2008) or AlwaysOn Availability Groups (SQL Server 2012), Microsoft SQL Server can provide manual or automatic failover across geographic distances using Windows Failover Clustering. Access has no ability to provide available or in the case of SQL Server 2012, a readable, transactionally live remote copy of the data that can server reports.

• 64-bit Architecture: Microsoft SQL Server can take advantage of 64-bit architecture, providing a very large increase in memory capacity and utilization, resulting in superior performance. The Jet Database engine that is begin Access uses 32-bit architecture only.

• File Size: Access has a file size limitation of 2gb per file, as well as caps to the size of the recordset, a single table, text in a field, sorting, and table joins. Access also has a cap of 1000 modules (including forms and reports) in a single database file.  Microsoft SQL Server has no such limitations on size or on memory. 

• Scalability: Reporting Services and Integration Services are two built-in features of Microsoft SQL Server that have superior scalable performance, automation, and modern development environments. These technologies are much faster to develop, maintain, deploy, over custom-built efforts and highly-customized Access forms and reports.

  • “Archiving” data by moving it out of active tables and into older passive tables is fully automated, but 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 changes to applications to recognize “live” from “archive” tables.

• Relational Integrity and Indexing: SQL Server has superior foreign key, primary key, uniqueness and constraint enforcement.  Indexing and compression are far more robust to improve data integrity, performance and disk optimization.

• Security: Microsoft SQL Server with Enterprise edition satisfies military and government standards for security, internal auditing and encryption, and more granular security permissions.

Information and material in our blog posts are provided "as is" with no warranties either expressed or implied. Each post is an individual expression of our Sparkies. Should you identify any such content that is harmful, malicious, sensitive or unnecessary, please contact  marketing@sparkhound.com