Converting Microsoft Access to SQL Server Express Edition 2016

Signs that you have outgrown the limitations of MS Access include slow MS Access performance with more users or more data, poor MS Access system reliability, continual need to compact and restore MS Access, or the need to creating multiple copies of MS Access to manage historical data.   If you are experiencing any of these symptoms — you are living on borrowed time — and it is time to seriously consider migrating your native MS Access jet engine datastore to SQL Server Standard Edition 2016 or SQL Server Express Edition 2016.  Doing nothing will only increase your risks and cost over time.

MS Access Limitations

MS Access has several important product limitations. Expect to run into problems (slow performance, corrupt database, application crashes, etc.) if your MS application goes into the "Caution" or the "Stop" zones for any one of the criteria below:
  OK Caution Stop
LAN Users <5 5-10 >10
Internet Users 0 1 >1
Size of DB <100 MB 100-300MB >300MBs
Data Structures Simple Moderate Complex

SQL Server Express Edition 2016: Capabilities and Limitations

SQL Server Express Edition 2016 (previously call MSDE) is Microsoft's license-free version of the SQL Server database. SQL Server Express is much more powerful, stable and scalable vs. the MS Access datastore engine, but it does come with some limitations, which can be quite manageable depending upon your needs. Limitations of SQL Server Express Edition 2016 vs. the Standard fully-licenced version of SQL Server 2016 include:
  • Limited to one socket with a maximum of 4 cores for CPU power
  • Limited to utilize only up to 1 GB of memory
  • Limited database size, max 10 GB
  • Does not include SQL Agent (no built-in automatic/scheduled maintenance, backup, or SQL jobs)
SQL Server Express Edition 2016 is the intermediate step between the MS Access and MS SQL Server Standard Edition. PCA database experts can help you convert MS Access to SQL Express or Enterprise Editions.

MS Access, SQL Server Standard or SQL Server Express Edition Business Considerations

Each Microsoft database product offers provides different capabilities, and different limitations that are mostly related to scalability and automation.   The following business considerations will help you decide which one is the best suited for your business:
  • Mission Criticality of the Business Application
  • Location and End Users (LAN vs. Internet)
  • Frequency of Use (Moderate vs. Heavy)
  • Number of End Users (5 users vs. Enterprise-wide deployment)
  • Business Product/Process Complexity (and associated Database Complexity)
  • Static vs. Dynamic Content (Read vs. Read-Write)
  • Auditability, Industry Compliance (e.g. SarbOx, HIPAA)