If you are using the Microsoft Access database management system, your demands will likely outpace both its capacity and computational limits. Handling these hurdles (hopefully with the right database support solution) before they become problems will be critical to keeping your databases running smoothly. You and your team may have identified inefficiencies or performance issues and determined your database is, or will soon be, ready for an upgrade.

Just as your business quickly outgrew those handy Excel spreadsheets, your database is now slowing down and taking up more and more of your precious IT resources. If you fit this description, it’s probably time to transition to Microsoft SQL Server, either locally or in the cloud.

Why upgrade from Microsoft Access to SQL Server?

While Access is adequate for individual and small team data management, its limitations often interfere with business growth in a variety of ways:

• Network failures, which typically lead to data loss.
• File and application sharing is limited to 255 concurrent users/editors and just 100 user-defined reports.
• The maximum database size allowed is 2 gigs, but any database past 1 gigs will be cumbersome and slow.

On the other hand, SQL Server 2019 supports business growth because it:

• Offers mission-critical availability to keep your SQL Server running with enhanced security-enabling confidential computing.
• Allows users from disparate sources and locations to access data on any desktop, smartphone, etc. to generate an unlimited amount of reports.
• Harnesses the ability to accommodate large data sets with heightened security and better protection of data sets

Why upgrade to SQL Server 2019?

If you have already transitioned to SQL 2017 and are wondering whether you should upgrade to SQL 2019, consider the following improvements and features in SQL Server 2019:

• Memory grant feedback available in both batch and row mode, versus previous updates that were limited only to batch mode.
• Multiple enhancements protect and secure sensitive data with advanced data protection policies and legislation for end-users.
• New features to better support business’ data management and data-driven applications.
• Advanced analytics that run the query algorithms directly in the core SQL Server.
• Mobile device support for native apps for Windows 10, iOS, Android, and HTML5 to allow easier access to reports and dashboards on various devices.
• Simplified management of relational and nonrelational data through dual querying.
• Faster backups, high availability, and scenarios for disaster recovery to restore your on-premises databases.

Your SQL Server upgrade options

The benefits of a SQL Server upgrade are numerous, and your organization will feel the positive impact throughout. But you likely may still be wondering how can a large organization like yours efficiently execute a SQL Server 2019 upgrade?

Consider this planning advice from Microsoft's knowledge library:

"There are several approaches to consider when you are planning to upgrade the Database Engine from a prior release (to) SQL Server 2019 in order to minimize downtime and risk. You can perform an upgrade in-place, migrate to a new installation, or perform a rolling upgrade."

You can further minimize your database downtime during an upgrade by working through a decision tree and identifying which database engine upgrade method is best for you. The three different methodologies include:

SQL Server upgrade in-place

This, according to Microsoft, is the easiest to do but requires downtime. If something goes wrong, and fallback is necessary, anticipate even more recovery downtime. This approach is for those organizations with a development, non-mission-critical environment with a constant need for high availability.

Migrate to a new SQL Server installation

You might consider this approach when upgrading your hardware and operating system. With this migration approach, you keep operating under your old SQL Server environment while SQL Server 2019 loads and prompts you to follow a set of steps that allow the migration of your old data into the new structure. When the new SQL Server 2019 accrues the same system objects as the older environment, you can begin a careful backup and restore process to migrate your user databases.

Rolling SQL Server upgrade

This is required in environments with multiple SQL Servers, so the upgrade will be executed in a very specific order to preserve database functionality. You perform a rolling SQL Server upgrade on new hardware with the goal of maximizing uptime and minimizing risk.

A basic checklist for an efficient SQL Server upgrade

Regardless of the SQL Server upgrade method employed, reviewing the following checklist will keep your team organized and on-pace to properly execute an efficient SQL Server update:

1. Review the system requirements—hardware and software—for installing SQL Server 2016 and later.
2. Know the details of your current environment so that you can understand what SQL Server components connect clients to your server. Consider upgrading your system provider for each client so that SQL Server 2019 will work well with each.
3. Ensure that your target environment—e.g., a virtualization project consolidating multiple servers—meets the SQL Server 2019 hardware and software requirements.
4. Read all about SQL Server Database Engine Backward Compatibility. Not each version of the SQL behaves the same.
5. Download and run the SQL Server 2019 upgrade advisor and system configuration checkers.
6. Plan on additional time to convert the memory-optimized database tables to the 2019 on-disk format. Sufficient disk space must be available to store your existing database as well as additional storage equal to your memory-optimized data filegroup.

Once you have decided on your upgrade method, these three steps will help you avoid costly mistakes and keep your project on track:

1. Have a rollback plan. You'll need a backup of the original in case you have to restore your original SQL Server.
2. Develop acceptance criteria. You have to know that the SQL Server 2019 upgrade is acceptable before you bring all the users on board.
3. Test your upgrade plan under actual workload conditions. The Microsoft SQL Server Distributed Replay Utility simulates a mission-critical workload and provides guidance for command line access to the Distributed Replay Utility.

Whether you are upgrading to SQL Server 2019 from an MS Access Database or an earlier version of SQL, you probably agree that the goal is worthy, but the process seems daunting.

The great news is that we have executed upgrades over and over and over again! Here at ManageForce, we have a team of technical experts that are intimate with the best practices and techniques to optimally scale up, monitor, and support your database environment. Let us know if you have any questions about upgrading your database. We’re ready to take your call.

If your database management isn’t helping you with your SQL Server upgrade, it probably isn’t delivering the maximum ROI either. Download our Database ROI Resource to see where your database management stands.

Editor’s Note: This post was originally published in February 2016 and has been updated for freshness, accuracy, and comprehensiveness following the SQL Server 2019 Upgrade release.