vancouver database specialists

Access to SQL

by Goran Urosevic on 06/Sep/2016
Microsoft Access to SQL Server upgrade

Microsoft Access has been around since late 1992. It was developed as a desktop database and it has done its job quite well. With each new version of MS Access, Microsoft has been pushing the limits of its capabilities. At the time of this article, the latest version of Access can handle a database of up to 2 gigabytes.

Of course, in reality things look a bit different. After a database grows over a couple hundred megabytes and tables have over 100,000 records, Access starts to slow down. Some forms, queries, and reports may take a long time to open.

What can you do?

Well, you can always re-analyze your Access database to see if you can optimize it by tweaking tables, indexes and queries, or perhaps by archiving old data. These steps may improve things somewhat.

After that you can start thinking about upgrading your Access database to a SQL Server database. Here are some pros and cons of doing so:

Pros

  • SQL Server has huge database size limits which you will probably never reach (currently 524,272 terabytes).
  • SQL Server supports tens of thousands of simultaneous users.
  • SQL Server is a dedicated server product whose sole purpose is to manage databases and therefore is much faster than the database portion of an Access application.
  • All processing is done on the SQL Server so there is less network traffic and less processing on the client workstations.
  • Access Queries can be replaced with SQL Server Views which are optimized and executed by the SQL Server.
  • Various automated tasks and processes can be scheduled to run on the SQL Server.

Cons

  • Price of hardware: to run a SQL Server you will require a separate physical machine or separate virtual machine. Although it can be installed to run together with other Microsoft server products, it is best practice to have a dedicated server.
  • Price of software: to run SQL Server you will have to buy: Windows Server, SQL Server and CALs (Client Access Licenses) for each user accessing the database.
  • Price of installation, configuration and management: SQL Server is a complex product and it requires expert setup and configuration. Once up and running it is low maintenance.

How can we help?

CompuWork Pacific has done many Access to SQL Server database upgrades and conversions. We have many happy clients who we have helped start using SQL Server. We have upgraded their Access databases to SQL Server databases and modified their front-end applications to work with a SQL Server back-end.

What are the steps to upgrade from Access to SQL Server?

Each upgrade has its own unique requirements, but there are some steps that apply to all jobs:

  • Identify the need to upgrade (analyze your current database and usage).
  • Obtain a computer to run the SQL Server (physical box or virtual machine).
  • Obtain a copy of Windows Server.
  • Obtain a copy of SQL Server and CALs.
  • Setup Windows server on the server computer.
  • Setup and configure SQL Server on server computer.
  • Convert the Access database to a SQL Server database.
  • Verify the conversion.
  • Modify the front-end application to work with the SQL Server database. This may involve some query and code modifications depending on how the front-end application has been written.
  • Analyze the new SQL Server database and determine if it can be further optimized by introducing additional indexes, views, triggers and stored procedures.
  • Analyze the front-end user interface and determine if performance can be improved by modifying the code or converting existing Access queries to SQL Server views.

Contact us

CompuWork Pacific is reliable business partner for converting your MS Access database to SQL Server. We can help you plan and execute your database upgrade.

Contact us for a free consultation today!