Data Management

10 questions to ask before upgrading from Access to SQL Server

Upgrading to SQL Server might make sense for some of your clients, but it could be a bad idea for others. These questions will help them make the right call.

Access is a great desktop database that Microsoft markets to end users. Despite that specialized niche, developers often choose Access to develop some fairly sophisticated and robust databases. If business is good, the business sometimes outgrows Access. If your clients catch the upgrade bug, help them make the right decision by asking them these 10 questions.

1: Is the Access database mission critical?

Many small businesses depend on Access. If the answer to this question is yes, you should definitely upgrade. Even though Access is a great end-user tool, you shouldn't use it to store mission-critical data. The smaller the business, the more vulnerable it (probably) is. Don't delay.

2: Has your business outgrown the current database?

An Access database will start to show real growing pains before it reaches its limit of 1 GB. Just keep in mind that size isn't the only performance cruncher. A poor design and/or demanding processing requirements will also affect performance.

3: Does the database do what's needed?

As business grows, needs change. The business may need the database to do more than it used to. That's not a reason to upgrade, though; don't let the client confuse database growth with database enhancements.

4: Are you anticipating significant growth?

The current database may be handling its current load, but if the business is expecting a growth spurt, help them plan for it. Upsizing before the gold rush is definitely a good idea.

5: Do you consider the database a success?

This question seems odd, but it'll help you glean important information. If the database isn't producing as needed or expected, upsizing isn't going to help. A new or modified database might be a better solution.

6: Is the network stable?

Many symptoms of network instability show up as poor performance, but upgrading isn't always the right cure. Lost connections, slow access, slow performance in the database itself, and corruption can be a symptom of network incompatibility or other issues. If this is the most important (or only) reason for upgrading, investigate the network first. Upgrading the network or moving the backend to a more robust machine might be a better solution than upgrading the database.

7: Is the database properly split?

Check out the current database setup. If it isn't running in a proper front-end/backend configuration, split the database. Store the backend on the server and copies of the front-end on the local user systems. Doing so should improve performance immediately. Then, you can reassess upgrading from a more stable position.

8: What server will host SQL Server?

If the client wants to move up to SQL Server, be sure the current server is compatible. Windows Server 2000 doesn't support SQL Server 2008 and later. Upgrading might require a new server, and that'll add to the costs.

9: How many concurrent users do you want to support?

Access supports a maximum of 255 concurrent users. Remember, the term concurrent user refers to open actions, not actual users. A single user can have many concurrent openings at any given time. What all that means is that Access can support about 20 users at the same time. If your client has 20 or more users, or plans to acquire more than 20, upgrading is a good idea.

10: Do you have the budget to support an upgrade project?

Upgrading can be (but isn't always) expensive. At the very least, your client will be paying for your services to actually upsize the database. Quite possibly, they'll have to purchase software (SQL Server Express is free) and pay licensing fees. They may need new hardware. Once everything is up and running, they'll need someone in-house to support and administer the new database.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks