Microsoft SQL Server is a database application whose primary function is to store and retrieve data as requested by other software applications. Access developers call this a back-end. One of those other software applications that requests data could be a Microsoft Access front-end. The front end would request data from Microsoft Server Server and would request updates to data on Microsoft Server Server.
The two could be on the same computer or on other parts of the world using the internet for the communications path.
Compared to Access Microsft SQL Server is roughly 10 time more powerful. Its database file can be more than 10 times bigger, it can have more than 10 times the number of users, much more reliable and so. It also has many other features that are not available in Microsoft Access Access. However, It costs about 10 times more than the price of Microsoft Access.
The downside to Microsoft SQL Server is that it requires expensive infrastructure and high level IT Support. It also makes the application more complex. For this reason, it is only used where its power is required.