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 Servers 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.
Increased availability
Microsoft SQL Server databases, can be backuped, either incremental or complete, while the database is 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.
Improved security
Microsoft SQL Server can integrate with the Windows Server 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. Inbuilt Access security has been removed in later versions. Relying on application security features in Access is never as secure as in SQL Server.
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.
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 back—and 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 server—ensuring that all servers on the network are maintained in a consistent state.
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 client/server application can do processing where it's done best - on the server.
Applications can also use stored procedures and triggers to centralise 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.
The Future
SQL Server is an integral part of Microsoft's core component and it's future strategy.
Increasingly, the new versions of Access, are making it easier to use Access Front End with SQL Server database, and to port access applications to SQL Server.
Different Versions of SQL Server
SQL Express is FREE and for many small/medium size business application this is all you need, and highly recommended by myself. There are some very good 3rd party add-ons and utilities which help fill what is not included with the Express edition:
SQLBackupAndFTP - creates MS SQL Server database backups and sends backups to a folder, or FTP site. Can be scheduled to run as required.
Express Maint - Utility for automating the backup and maintenance of databases for SQL Server 2005/2008/2008R2 Express Edition
Compare the different versions of SQL Server