Tuesday, January 07, 2014

Don't Hitch Your Wagon To MS Access

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 managersdepartmental 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
So Tempting

Microsoft Access is very easy to use for non-developer folks to develop basic data entry screens, data storage, and reports. In this sense, it's also great for students. My first database class at LSU was taught entirely within the confines of Access 2000.

Think about that.

It's perfect for untrained students. Should you really be saddling your enterprise with mission-critical functionality based on a consumer office product? There are more capable alternatives that will scale up with your company's growth and the historical data collected.

It's just too easy to create a screen to capture this data, present a decent report, all while creating important data that is stored on a consumer-grade hard disk. You might be shocked to realize that many IT departments don't "back up the whole computer" with any more regularly than weekly - if you're lucky. Your poor IT folks won't be pleasantly receptive to recovering or supporting Access databases you created without their knowledge. 

At the very least, storing the Access databases on a network drive, and breaking the database out into separate .MDE (front-end forms) and .MDB (back-end data) files is what your IT folks will want to do, if they are familiar with handling these types of files. However, that's still not enterprise-ready.

It "Just Works", Until It Doesn't

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

Get a Real Database Solution

Open up the lines of communication to your IT managers and inquire about skillsets in custom development, SQL Server and SharePoint. The payoff is worth it. The project may take longer to develop, but satisfaction with how well this new projects works for capturing and reporting your data will be higher, not to mention the basics of disaster recovery.

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.