Upsizing, or converting an application from a desktop database to a server-based Relational Database Management System (RDBMS), almost always becomes an issue in database management, except for the most trivial of desktop applications.
That’s the issue raised by Builder.com member Redolph, whose organization has quite a few Access-based productivity applications in use. In a recent discussion forum post, Redolph asked for help with an upsizing dilemma. He writes:
“One of my less-Access-savvy brethren has the idea that if we simply transform our tables to SQL Server or Oracle, our performance will dramatically increase. He seems to think that we can use everything the same way we have and everything will be more powerful and faster. I am concerned that that change would come at a price, that we would be far less nimble about making changes to table structures, and that to truly reap the benefits we would have to write queries in SQL. I have heard that there are some Access queries that cannot be reproduced in SQL? I am also concerned that our 40 users would have to learn SQL Server to function or really benefit. That would be out of the question, as many are doing all they can do to use Access.”
Redolph also says that, for the most part, his group has organized everything around the idea of providing one database per user task, and that his databases currently range in size from 100-400 MB, with the largest tables holding about 100,000 records.
What do you think—is upsizing a good move for Redolph, based on the information he’s provided? Check out my comments below, and then head on over to the discussion forums to offer any advice you have.
Bigger isn't necessarily better
If you ask me, when it comes to upsizing, there’s more to consider than just straight-line execution speed. While it’s true that SQL Server will generally outperform Access’s Jet database engine when running the same query, the additional time required for the request to travel across the network may outweigh any performance gains, unless you’re planning to install the desktop edition of SQL Server (also known as MSDE) on each client machine.
Upsizing’s real gains come from:
- Data sharing: If User A needs access to parts of User B’s data, it will be much easier to provide this access with a server-based database system, since both users’ data will be stored in the same physical location.
- Scalability: More users can access the same data at the same time without reducing performance. Depending on the version and table structure, an Access database can have problems with as few as three concurrent users.
- Security: Server-based database systems usually have better security features than desktop-based systems. For example, you can set SQL Server to grant or deny access to particular databases based on a user’s Windows networking credentials.
- Transaction logging: If you’ve used Access for any length of time, you know how problematic corrupted databases and the resulting lost data can be. Most server-based systems log each and every data operation and can recover from most failures without any loss of data, often without the user even knowing that a failure has taken place.
When considering whether or not to upsize a database, you’ll have to weigh these advantages against the time and effort involved in upsizing. For smaller and simpler applications, upsizing may not be worth the trouble.