Access versus SQL Server

Access and SQL Server are very different systems. SQL Server is a client/server RDBMS, whereas Access is a file-based application. They have very different limitations and work very differently.

Size Constraints

One of the major differences between SQL Server and Access databases is the maximum size of a database. Despite what many people believe, Access databases can support a great deal of data. Access 97 and earlier versions have a documented size limit of 1 gigabyte. In Access 2000, this limit has been increased to 2GB. However, anyone who has experience with a shared Access database over 10MB is well aware of the fact that the larger a database gets, the worse the performance from the Jet engine. This is especially true if the majority of the data is housed in only a few tables. Attempting to retrieve data from one very large table can take a long time. Attempting to retrieve data from multiple large joined tables can take even longer. Needless to say, this is not the desired behavior for an application.

SQL Server can support much larger databases. The maximum size of a single database in SQL Server 6.5 is 1 terabyte. In SQL 7, this has been increased to 1,048,516 terabytes. A terabyte is equal to 1,024GB. Consequently, the limit of a SQL Server database in version 7 is approximately 536 million times larger than the limit of an Access 2000 database.

Recently, some changes were made to SQL Server to optimize its support for what the industry has termed Very Large Databases (VLDBs). These are databases that house millions of records of information. VLDBs can be very difficult to manage without the appropriate RDBMS. If the RDBMS is not designed to handle these types of databases, the methods they use to work with data may cause large degradations in the performance of the application that accesses that database. The definition of this name is somewhat subjective , but it is doubtful that anyone would dispute that VLDBs include databases over 100GB. Microsoft SQL Server can handle these types of databases and even does it well. When a SQL Server VLDB is designed correctly, it can support lots of users without any problems and still respond quickly to requests from other applications.

Transferring Data

SQL Server and Access return data in very different ways. Access must retrieve all of the data in a table to the local computer in order to filter and manipulate the data. SQL Server has the capability to process a request for data, perform any data manipulation on the server, and only return the resulting records to the client application. How the data is handled by an application once it is requested can also differ a great deal between Access and SQL.

When you run a query against a Microsoft Access database, the Jet engine goes out to the database, retrieves the entire contents of the table that the data is requested from, and copies that data locally for the client. The Jet engine then handles this data in local memory to filter it according to the specifics of the request that was generated. When you run a similar query in SQL Server, the server takes the request for data and processes it on the server. The requested data is extracted from the table by the server, and then sent across the network to the client. The client then works locally with the data that was requested.

Due to these differences, more memory is required on the client when using Access than is required with SQL Server. If the Access database resides on a disk drive on a remote computer on the network, the network load is also much higher because more data must travel over the network to the client.

Another major difference between how Access and SQL Server deal with data is how they support cursors. Cursors define where and how data is updated. They can be server-side or client-side. With server-side cursors, a constant connection is held to the database. Any changes to data are immediately transmitted to the server. All indexing of data and changes to data take place on the server. As a result, server-side cursors use the network a great deal. As each change is sent back to the server, sorting of the data and data changes made by other users must be passed back and forth between the client and the server. With client-side cursors , all sorting, updating, and manipulation of data takes place on the client. Once the server has sent the data to the client, it becomes the client's responsibility to handle the data until it is resubmitted to the server in a batch update. The server only tracks the fact that the data is currently being used by another system.

SQL Server 6.5 and 7 fully support client-side and server-side cursors. You can request data from the server, and then completely disconnect from the database once the data has been sent to the client. The application then works with the data locally. You can also maintain your connection to the server and have each update sent to the server as it occurs. On the other hand, the Jet engine does not truly support cursors through DAO. Although it is possible to implement cursor types in ADO, you cannot use the DAO interfaces to implement cursors. When using DAO, the server handles all updates to the data. Sorting changes and updates to the data are transmitted to the client as they occur. Access 2000 offers better support for cursors using ADO, but in Access 97, you cannot bind any objects to data that uses client-side cursors because ADO binding is not supported. Essentially, this means that an Access 97 application must maintain a constant connection to the server. This increases the load on the network as changes are constantly being sent back and forth between the backend database and the client application.

Multi- User Constraints

Anyone who has developed an Access application that is used by more than 10 people concurrently has probably run into the dreaded "record locked by another user" error that is common to multi-user Access databases. This error is a result of how Access locks records and how concurrency is managed.

When a user is updating a record, an RDBMS locks that record or the table the record resides in, so that other users cannot make modifications to it. This prevents multiple users from being able to update the same record at the same time. Access's record locking locks not only the record that is being edited, but also some of the other records physically stored close to it. As a result, two users may not be able to add records at the same time. Access locking information is stored in an .LDB file that is kept in the same directory as the database that is being used. This file is created when a user connects to the database and is deleted once the last connected user leaves the database. The locking that is implemented by Access in this file tends to have a number of problems when many concurrent users are accessing the database. This can create a severe limitation to an application when the user base starts to increase. SQL Server handles locking considerably better than Access. SQL Server can handle thousands of concurrent users, all accessing the same data. Of course, they can't all be updating the same information at the same time, but the potential to have a large user community sharing data is there. Access can have a large community too ”as long as users don't all show up at the same time.

Implementation of the security model is much different in Access than in SQL Server. In Access, all security information is kept in the system.mdw file. This file contains all of the user IDs, groups, and passwords for each defined user on the system. In order to connect to a database using customized logon information, users must have access to a copy of the version of this file that was used to develop the application. If this file is lost or damaged and proper documentation on it is not kept, a database can become completely inaccessible. Also, in order to implement security on the database, either a new version of this file must be created or all permissions must be explicitly removed from all objects for the default user "Admin." The Admin user ID is common to all Microsoft Access systems and if the permissions for this user are not properly removed, anyone with a clean copy of Access can open the database and modify it. The security administrator must remove these implicit permissions, which can be a long and tedious process.

SQL Server security is implemented locally to the system. All user and group information is stored in the master database on the server, so there is no conflict with different versions of the security implementation. Security is also set to the highest possible level by default: Only the system administrators have access to all databases, for instance. User permissions must be assigned as needed and are not given explicitly. The default admin user, "sa," can have his or her password changed for the entire server, so that users cannot access the server where the change occurred using the "sa" logon from their own server. This prevents unauthorized access to the database at a level that Access cannot even come close to.

Maintenance and Administration

Administration in Access may seem rather simple. However, there are some maintenance tasks that must be done to a database in order to ensure the optimal performance of the system. When using Access, each database must be repaired and compacted on a regular basis. Repairing rebuilds the indexes in the database, and compacting clears out any empty space or deleted objects in the file. Repairing and compacting can prevent the database from becoming corrupted by high volume usage or improper application shutdown. Databases should also be backed up on a regular basis as a precaution against unexpected events, such as hard disk failures or accidental deletion. Each of these Access maintenance functions must be run separately for each database either manually or by using a third-party tool that comes at an extra cost. And, there is no facility in Access that records this activity. There is no central administration point for all Access databases on a computer, and there is no common way to schedule these tasks to occur at times when users are unlikely to be in the system. When fatal errors, such as a corrupted database, are encountered , the only way for administrators to know about the problem is when users contact them. This notification is inefficient and can become a problem if the support staff is not around to take the call.

SQL Server administration and maintenance is run through stored procedures included as system utilities or through SQL Server Agent/SQL Executive. Maintenance for all databases on a server is performed through one entry point and is therefore centralized. Rebuilding indexes, removing unused space from databases, checking database integrity, and backing up the database and transaction logs can be done for one or many databases using the tools supplied with SQL Server. Jobs can be scheduled at times when users are not on the system and can even be executed from remote servers. These tools maintain logs of all activities and, if configured to do so, notify users if any of the jobs failed or page someone if a fatal error has occurred on the server. You can even configure the server to email or page someone when an unexpected error occurs during the normal operation of the databases.

Replication

Access has the capability to create databases that replicate their data to other Access databases. This can be quite useful if users outside the normal network require access to information contained in a local database, if you're trying to reduce the number of users on a single MDB, if you're supporting a data warehouse, or for many other reasons. However, replication in Access is not without its problems. Replicated databases that become corrupted are no longer replicable and trying to determine what changes occurred in the database since the last time it was synchronized can be a difficult, if not impossible task. Access replication must also be managed through a separate utility called Replication Manager and can be difficult to administer. Replication Manager does not handle conflicts between the replicas very well and if a replica becomes damaged and needs to be repaired, it will lose its capability to be replicated. Also, you cannot create a database in Access that automatically replicates its data to another RDBMS.

SQL Server replication is much more robust. The replication that SQL Server uses is much more reliable and less prone to conflict errors or outright failure. It can replicate SQL Server data with another database on a separate SQL Server system, with an Access database, with an Oracle database, or with any other ODBC or OLE DB compliant data source. A SQL Server database can, therefore, share some or all of its data with completely different database systems. SQL Server Replication is configured through the same interface that is used to manage the rest of SQL Server's functionality. It can also be managed through system stored procedures or programmatically. In addition, replication can be scheduled through these interfaces and notification of job status can be configured.

Cost

The difference in cost between a purely Access-based application and one that uses SQL Server is considerable.

The costs of the software alone are quite different. A copy of Microsoft Office Developer Edition can be purchased for approximately $1,000. This allows royalty free distribution of the Access runtime engine with your Access application. SQL Server, on the other hand, must be purchased as a separate application and costs approximately $1,500 with five client licenses. Extra licenses must be purchased depending on the number of users that will be accessing the server. These extra licenses cost approximately $100 per additional user and can become very expensive if the number of users who will be accessing the server is sizable . On top of this, there is still the additional cost of the Office Developer Edition (or all users must have a licensed copy of Access) if you want to create an Access database or project to manipulate the data in the SQL Server database.

The hardware requirements for SQL Server are more stringent than the hardware requirements for Access. Access applications rarely require purchasing new hardware because the computers and network that the applications run on usually exist before an application is designed. Users can run Access on the equipment they already use for their word processing and spreadsheet applications. SQL Server, however, is best run on a dedicated server, so that the maximum performance of SQL Server can be extracted. Because servers are not usually purchased and left unused, a new server will almost certainly have to be acquired for SQL Server. For a mission-critical application, the minimum cost of an adequate server is about $3,000.

The cost of development and maintenance of a SQL Server application is also higher than one that is purely designed in Microsoft Access. Often, a professional database administrator (DBA) must be hired . This person must have the knowledge and the experience to maintain the server in optimal condition and to ensure that developers are using best practices when designing the database. If any of the developers is unfamiliar with SQL Server and how it works, there can be extra costs in development trying to get these developers up to speed with a new development environment.

The difference in costs between a SQL Server application and one developed solely in Access can be anywhere from $500 to $50,000.