Frequently Asked Questions
When should we use SQL Server versus Access?
There are several factors to consider when choosing between Microsoft Access and Microsoft SQL Server as the database for a project.
These are:
The complexity of the database design. This is the factor with the most intangible elements. Both Access and SQL Server are capable of handling fairly intricate data relationships. However, SQL Server is more preferable than Access if there are complex calculations or analyses to be done with the data.
The amount of data to be stored. Access is capable of handling quite a large capacity of data. However, it does have its limits, and SQL Server far outperforms Access in general. The maximum size of an Access database is around 2 gigabytes, but SQL Server can handle much larger databases (a terabyte or more).
The number of users the application will have. When a large number of users (more than 25) share a single Access database, the database often tends to become corrupted and network traffic requirements for data transfer are heavy. Most editions of SQL Server are able to handle thousands of users and millions of transactions per day.
Security. Access has support for database security, but Access security can be cracked at relatively little expense. SQL Server has extremely robust security, with support for two security models - internal SQL Server security and Windows authentication, both of which are much more difficult to crack than Access security.
Replication. Access supports database replication, but its tools for managing it are kludgy and somewhat crude. SQL Server has advanced tools for replication and transfer of data.
Other features. SQL Server has features not available with Access which make it a much more powerful, robust database management system. It has tools to help perform tasks such as backing up, restoring, optimizing, and generally administering databases, and it has the capability to schedule these tasks to occur on a regular basis. Also, SQL Server can automatically notify support personnel if it detects problems with hardware, software, or databases. Additionally, several editions of SQL Server have data warehousing features which can be utilized to provide extensive data analysis.
Budget considerations. In a perfect world, this factor would disappear. However, budget limitations can preclude the use of SQL Server. There are several editions of SQL Server based on general scenarios, all of which vary in price and functionality. If a less expensive edition of SQL Server will perform well for the project, the cost to the client can be reduced considerably. In addition, if a client already owns a copy of SQL Server, the cost would be far less than if SQL Server would have to be purchased for the project.
Need help with that Access or SQL database project? Give us a call at 630-513-1280.
