#0 Why NOT SQL Server?
The most common reason for going to SQL Server is because the existing database is running too slowly. SQL Server will usually address speed problems, but in many cases improved Access design can achieve the same thing without the expense, hassle and time needed to move to SQL Server.
- Before committing to SQL Server it is vital to be sure that it is the best option. The list below contains the limitations of access. If your requirements are beyond these then you have to consider something else. However, if for instance you have an issue with the speed of your database it may be easier to tweak the database or your network rather than go to SQL Server which requires much more administration than Access.
Although Access can be password protected and encrypted, these databases do not have the same level of security as SQL Server. If data security is critical, a SQL Server solution is the best choice. SQL Server offers 128-bit encryption and storage in a remote location from the user and application.
2# Too Many users
Access databases have problems with too many simultaneous users. The number depends on what they are doing, but the practical limit is usually around 15-25 before performance is affected. SQL Server also supports many more users.
3# Large amounts of data / calculations
Access is limited to 2GB (gigabytes) of data. If there’s a lot of data or a lot of calculation to be done, SQL Server is the better choice.
4# Network Speed / Bandwidth
A well designed application using SQL Server can significantly reduce the amount of data moving across the network, because only the requested records are passed from the database to the application. Access passes the whole table (or at least the index) across the network. This is not significant for small files, but performance suffers as the data grows.
5# Putting Data Online
Access just isn’t designed to run on Web sites. Data Access Pages are of limited use in intranets, but not on the Internet.
6# Backups / Maintenance Tasks
Backing up an Access database is easy. There’s just one file to copy. However SQL Server offers highly customisable backup and maintenance tasks integrated into the system.
SQL Server runs all the time your server is running. It can perform automated tasks such as sending texts/emails, updating data from outside sources, running analysis, sending out reports. Access isn’t suitable for running processes 24hrs a day 365 days a year.