SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is Microsoft’s enterprise level RDBMS offering, and is a more sophisticated and robust system than Access, which has traditionally been a desktop system.
SQL Server’s main competitors are Oracle Database, MySQL (now owned by Oracle), PostgreSQL, and IBM’s DB2.
SQL Server is a client-server based system, which means that it operates as a server, typically containing many databases, with multiple clients accessing the databases from across a network. These clients are often other applications (such as a website or CRM system). This is in contrast to desktop systems, where the database will often (but not necessarily) reside on the user’s computer.
Client-server based systems typically have much higher specifications than desktop systems, and this holds true when comparing SQL Server to Access.
Here are a couple of examples to illustrate the difference:
- An SQL Server 2016 database can have a maximum size of 524,272 terabytes whereas an Access 2016 database can have a maximum size of 2 gigabytes.
- Each instance of SQL Server 2016 allows a maximum of 32,767 simultaneous user connections whereas an Access 2016 database has a limit of 255 concurrent users.
SQL Server Management Studio
SQL Server comes with SQL Server Management Studio, which is a graphical console that allows you to develop, configure, and administer your SQL Server.
Many tasks can be done either through the GUI or programatically by running an SQL query from a query tab.
SQL Server Technologies/Services
SQL Server is made up of various technologies & services. Here are the ones that make up SQL Server 2016.
Database Engine
The Database Engine is the core service for storing, processing, and securing data.
R Services
R Services provides two server platforms for integrating the popular open source R language with business applications: SQL Server R Services (In-Database), for integration with SQL Server, and Microsoft R Server.
The R language is widely used among statisticians and data miners for developing statistical software and data analysis.
Data Quality Services
Data Quality Services (DQS) is used for data-cleansing, using a knowledge base system.
Integration Services
Integration Services is a platform for data integration and data transformations.
Master Data Services
Master Data Services enables an organisation to create a and manage a trusted version of data as it changes over time.
Analysis Services
Analysis Services is Microsoft’s data warehousing solution.
Replication
Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronising between databases to maintain consistency.
Reporting Services
Reporting Services allows you to create reports from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.