When to upsize a Microsoft Access database to Microsoft SQL Server
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Over time, most database applications grow, become more complex, and need to support more users. At some point in the life of your Microsoft Access database application, consider upsizing to Microsoft SQL Server to optimize database and application performance, scalability, security, reliability, recoverability, and availability. This topic provides reference information about the main reasons to upsize to an SQL Server database.
High performance and scalability
Increased availability
Improved security
Immediate recoverability
Reliable distributed data and transactions
Server-based processing
High performance and scalability
In many situations, Microsoft SQL Server offers better performance than an Access database. SQL Server also provides support for very large databases, up to one terabyte, which is much larger than the current limit for an Access database of two gigabytes. Finally, SQL Server works very efficiently on Microsoft Windows NT by processing queries in parallel (using multiple native threads within a single process to handle user requests) and minimizing additional memory requirements when more users are added.
Return to top
Increased availability
Using Microsoft SQL Server, you can do a dynamic backup, either incremental or complete, of the database while it's in use. Consequently, you do not have to force users to exit the database to back up data. This means your database can be running up to 24 hours a day, seven days a week.
Return to top
Improved security
Microsoft SQL Server can integrate with the Windows NT operating system security to provide a single log on to the network and the database. This makes it much easier for you to administer complex security schemes. An SQL Server database on a server is also better protected because unauthorized users can't get to the database file directly but must access the server first.
Return to top
Immediate recoverability
In case of system failure (such as an operating system crash or power outage), Microsoft SQL Server has an automatic recovery mechanism that recovers a database to the last state of consistency in a matter of minutes, with no database administrator intervention. Critical applications can be up and running again right away.
Return to top
Reliable distributed data and transactions
Transaction processing is a vital requirement for a system that is designed to support critical applications, such as banking and online order entry. Microsoft SQL Server supports atomic transactions with transaction logging, which guarantees that all changes performed within a transaction are either committed or rolled back.
Consistency and recoverability of a database transaction are guaranteed even in the case of system failure and in the middle of complex updates by more than one user. SQL Server treats all database changes inside a transaction as a single unit of work. By definition, either an entire transaction is completed safely and all resulting changes are reflected in the database, or the transaction is rolled backand all changes to the database are undone.
Using a two-phase commit protocol, SQL Server can even support synchronized transactions that span more than one serverensuring that all servers on the network are maintained in a consistent state.
Return to top
Server-based processing
Microsoft designed Microsoft SQL Server from the beginning as a client/server database. Data and indexes reside on a single server computer that is often accessed over the network by many client computers. SQL Server reduces network traffic by processing database queries on the server before sending results to the client. Thus, your client/server application can do processing where it's done best - on the server.
Your application can also use stored procedures and triggers to centralize and share application logic, business rules and policies, complex queries, and data validation and referential integrity code on the server, rather than on the client.
Return to top