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.

About

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.

8 comments
sysop-dr
sysop-dr

There are many sql servers available for people to use. One of the worst to pick is MS SQL Express. IF you feel you need to use MS SQL buy the full version, it will make your life much easier. BUT why limit your search to MSSQL? You can use your Access front end with almost any SQL database. Don't limit yourself to a single option. learn about the others out there, like Oracle, DB2 (if you need robust and long lived with stability go with the big boys) or if you want less expensive try out the open souce alternatives, MySQL and PostgreSQL. But if you insist that you must use Microsoft buy the real version and not the express one.

manishmertia
manishmertia

Hi, are there way for self learning & mastering the access, I am tired to different books as most of them are basic only, I need something higher level to develop some applications for personal use, I do have prior knowledge of developing small application in dBase and FoxPro and now I want to switch and try to access. Please advise Manish Mertia

kallal
kallal

Nice list and hard to add to - about only addition here is that moving the data to SQL server still means the application part remains as Access front end unless one has budgets and people in place to re-build. So moving access data often suggests to the client that Access will not be required and this is not the case.

wrmosca
wrmosca

Thanks for the good advice, Susan. I deal with Access front ends and SQL back ends in almost all of my projects when I've determined the need. One point...Access files can be up to 2 GB. I believe the change was made with Acc2000. Splitting lets you store more data by putting the tables in several back ends. But there comes a time when you should consider SQL Server especially when performance has been tuned to th emax and it still isn't good enough.

HENpp
HENpp

I have several borderline Access databases that I may need to consider for upgrade. As things tend to get busy, deploying an Access database that meets the users' needs for the time being is fine. But I will definitely review each of them with these ten items as a guide.

kallal
kallal

You might want to "enlighten" the readers here as to what or why you think the free expression edition is not up to the task at hand? The express edition is limited to 10GB database size. And this edition while limited to one physical CPU, if it is a duel core then two threads are started (so it uses two processors). With today's super-fast hardware, such a setup means SQL express will not even break into a sweat with 75 users. I mean, really, how many small business using Access applications will even become CLOSE to hitting the limits and capacity of the free edition of SQL? So what or why we are to advoid this edtition warrants some further explains by you. So please explain what it is that is so bad about the express edition of SQL server and why one should purchase the full edition? On the other hand, there is GREAT number of reasons to use Access with SQL server (as opposed to others like MySql). First of all, you not need as a general rule to install any ODBC drivers on each desktop computer when you deploy the Access front end since windows includes the required ODBC drivers (unlike MySql, Oracle etc, you need to install + deploy drivers along with the Access front end). There also a good number of built in tools inside of access for exporting + up-sizing data to sql server. You can thus with great ease send a table up to SQL server, or send + create a linked table. These are built in Access options and again they don't work with other products like MySQL, PosgreSQL etc.). The other advantage of using SQL server here, is if you outgrow the free edition, you can move up to the higher commercial editions without needing any changes. And speaking of choices you can also take that SQL express database and then move it to the cloud edition (Azure) of sql server. And I want to stress that the cloud edition of SQL server is a SIGNIFICANTLY and VERY different choice than that of choosing a hosted server option. I could write on for a good many more pages, but I await your reasoning as to why the express edition of SQL server should be avoided? Albert D. Kallal (Access MVP) Edmonton, Alberta Canada

ssharkins
ssharkins

I don't agree with you in regards to SS Express. It's a great tool! But you're right, Access can be used with other databases, not just SQL Server, so no one needs to limit themselves to just SQL Server -- thanks for the reminder!

wrmosca
wrmosca

@manish - Access 2010 Inside Out is an excellent book. There is a lot of indepth knowledge to gain from it.

Editor's Picks