pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

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. 


10 comments:

  1. I concur with your assessment about using the database engine within Access...

    Does Access still support Access Data Projects (.adp)? A few years ago I found these to be useful for rapid prototyping.. You had the ability to use Access for Forms/Reports - and the engine was the sql server instance you bound the project to.

    I still wouldn't use at the Enterprise (they have issues as well) - but I did find them amazingly useful...

    ReplyDelete
  2. Totally agree that access [Jet database] is a rubbish database, but Access is a very good front end client for SQL Server. It's a highly configurable VBA front end with good data controls and tools already built.. I use an access front end client that is so highly customised you can't tell what it is, all coded in vba and not a single access table. Integrated version management and updating for local client front-end Access Application. My horse and cart are Access and SQL Server. They are hitched.

    Yes [Access Data Projects] are the correct way to use Access, though later office versions do not use updated forms of .adp you can still use the principle of no data in the client.

    [ADP] firles and [Access Runtime] was a fantastic combo from 2003...

    ReplyDelete
  3. Anonymous from Feb 5-

    I have to add that the VBA dev platform is comparatively immature in terms of source control, testing, deployment and release management compared to modern alternatives. There is also a considerable opportunity cost in staffing the skillset to develop for VBA in Access.

    I have no doubt that you can develop something configurable and robust in Access when using SQL Server as the back end, I've seen some good examples, but lifecycle has got to be a factor for the IT decision makers.

    In my consulting work, like I wrote above, I've seen dependence on the Access mindset and the Access VBA skillset really become a logistical and financial bottleneck for businesses. That was the crux of my blog post. I certainly mean nothing personal by that.

    -William

    ReplyDelete
  4. One thing to note, however, SharePoint lists are limited to 5,000 rows :(. So, enterprise solutions still may not work with SharePoint.

    ReplyDelete
  5. Anonymous commenter: While I am not to be confused for a SharePoint consultant in any way, but I believe the limit for rows in a list is much, much higher.
    30,000,000 items per list, according to this link: http://technet.microsoft.com/en-us/library/cc262787(v=office.15).aspx
    You may be thinking of 5000 as the limit for a list view or a query result set, but those values is only a default, and can be configurable by an administrator.

    I definitely recommend storing table data in a SQL Server, not SharePoint, and any Access-replacement solution would contain a "combination of Microsoft SQL Server and Microsoft SharePoint." SharePoint could be used to replace Access forms and provide "a quick and easy way for developers to create data entry forms that run in your web browser."

    Thanks for commenting!

    -William

    ReplyDelete
  6. This article is a nice one about MS Access not to use but it is far away of beeing a complete picture. Critical Data belongs in "a real Database solution" such MS SQL. That is common sense and does not speak against the use of Access as it can be used as a FE to MS SQL. So its up to the programmers/skilled users decsion where to store the data.
    There are a lot of pros using Access I miss beeing mentioned here. At the end its a trade-off wether to use it and if you use it to what extend.

    ReplyDelete
  7. It is not apple to apple when you compare SQL Server with Access. Access is a rapid application development tool. No all solutions need the enterprise scale.

    ReplyDelete
    Replies
    1. Au Kay Wah-
      I don't disagree at all, but rapid application development with such limitations and lack of scalability aren't appropriate in most professional, multi-user settings. Access is very good at what it does, and like many I learned from it while in school, but it should probably stay there, especially if any of the data will be contained inside an Access file.
      -William

      Delete
  8. You don't say what to use the all important front end? Assuming a company has an SQL Server to simply store and acces data but no web server for .net, java, php front ends and the hugege dev time and costs that come with them to create front end GUIs.
    And if I have a short term contract for a financial institute who won't allow their data off site or to netwoek into then my only option is to use Excel or Access a the quick and dirty goto tool. They get the job done and everyone is happy.
    This is just one sxample of a 'real world' scenario and not fantasy 'enterprise world' that alll looks fine and dandy when reading about it on a blog!

    ReplyDelete
    Replies
    1. Dear Anonymous-
      Thanks for reading. While Access may be a good rapid application development platform for an entire generation of IT professionals, it isn't the only rapid application development tool out there. I did in fact recommend that custom forms and report functionality be migrated to SharePoint and Reporting Services one by one. If SharePoint isn't an option, and a SQL Server license isn't an option but an MS Office license is already paid, then any good consultant should strongly advise their clients to reconsider their IT investment options before developing in Access, and be honest with the client about the risks and dangers involved in bare bones IT budgets. A company may also find alternatives in off the shelf software once they realize its highly unlikely that their business model/industry is not unique. Failing that, I'd recommend .NET, which is open source, and has many documented and user-friendly rapid application development tools and techniques. Yes, .NET may be outside the skillset of the everyday IT generalist - that doesn't mean those Access alternatives are not superior alternatives that won't saddle a small business with serious problems down the road.

      I didn't write this article out of some prejudice for Access or for the generation of consultants who made a living as Access developers in the late 90s and 2000s. I do work in the real world, where we encounter real clients whose businesses are in danger of ceasing operations because of over-reliance on student-grade database tools now failing them. I don't think it's fair to say that my blog is living in fantasy world - I have worked with dozens real world clients to escape the mess that some Access developer planted for them. Thanks for replying though.

      -William

      Delete

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.