Introduction to SQL Server 2017

Screenshot of SQL Operations Studio (SQLOPS).

SQL Server is a relational database management system by Microsoft. SQL Server 2017 enables us to run SQL Server on Linux and Mac machines for the first time since its inception in 1989.

Prior to the 2017 edition, SQL Server was only available for Windows. One of the biggest changes in SQL Server 2017, is that it is now available on Linux and Docker containers. This means you can also run SQL Server on a Mac.

What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It's one of the most popular DBMSs on the market.

SQL Server is extremely versatile and it can be used at all ends of the spectrum — from storing the content for an individual's blog, to storing customer data and providing analytics for small to medium enterprises, to being an integral part of the enterprise systems of some of the world's largest companies.

Some of SQL Server's competitors include Oracle, MySQL, PostgreSQL, etc.

Client/Server Database Systems

SQL Server is a client/server database management system (DBMS). This means that you can have many different "client" machines all connecting to SQL Server at the same time (or at different times). And each one of those client machines could be connecting via a different tool.

For example, one client might use a graphical tool such as SQL Server Management Studio (SSMS) while another might use a command line tool like sqlcmd. And at the same time, a website could also be connected to SQL Server from within the web application. And there could be many other clients all connecting for their own purposes, using their own tools. So it could look something like this:

Diagram of clients accessing SQL Server
Multiple clients accessing SQL Server. Clients could include developers, database administrators, and even applications such as websites, CRM systems, and business intelligence tools.

SQL Server can also be managed from the server that it's installed on, but the main benefit of a client/server DBMS is that multiple users can access it simultaneously, each with a specific level of access.

If the database administrators have done their job properly, any client that connects to SQL Server will only be able to access the databases that they're allowed to access. And they can only perform the tasks that they're allowed to perform. This is all controlled from within SQL Server itself.

So we could modify the above diagram to look something like this:

Diagram of clients accessing individual databases on SQL Server
Multiple clients accessing individual databases on SQL Server. In this case, one particular client has access to two databases, whereas all the others have access to just one. In practice, there would also be at least one DB admin login that has full access to everything.

Note that the management the tool you use doesn't determine which databases you can access. The tool simply provides the means for you to access the databases that your login has been granted access to. Any of these tools would provide you with full access to all databases if your login has been set up that way. In fact, database administrators use these tools themselves in order to grant access to others.

Database Management Tasks

Some typical database administration and programming tasks could include:

These are just some of many tasks that a database administrator (DBA) might need to perform. SQL Server provides the database engine, while various tools can be used to perform these tasks. We'll look at some of these tools later in this tutorial.

SQL Server also includes optional services that you can choose whether or not to install, depending on your needs. For example, there's Reporting Services, Analysis Services, Integration Services, R Services, etc. However, the availability of these services may depend on your platform (most of these weren't available in the first release of SQL Server 2017 for Linux).

SQL Server 2017 Editions

SQL Server 2017 comes in the following editions:

Edition Description Price
Enterprise Aimed towards mission critical applications. Provides the highest service and performance levels for Tier-1 workloads. The most expensive option. $14,256 per core (sold as two-core packs).
Standard For mid-tier applications and data marts. Core data management and business intelligence capabilities for non-critical workloads with minimal IT resources. $3,717 per core (sold as two-core packs).
Web SQL Server Web edition is a low total-cost-of-ownership option for web hosts to provide scalability, affordability, and manageability capabilities for small to large scale websites. Available to third party software service providers only. See your hosting partner for pricing.
Developer For developers to build, test, and demo apps in non-production environments. Includes all the features of the Enterprise edition, but licensed for use as a development and test system, not as a production server. Free download.
Express For small-scale applications. Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications Free download.

What's New in SQL Server 2017?

SQL Server 2017 includes a number of improvements over SQL Server 2016. The main ones include:

Runs on Linux and Docker
SQL Server can now run on Windows, Linux, and Mac.
Adaptive Query Processing
For better query performance.
Graph data support
SQL Server now supports graph databases. Graph databases are ideal for when you have complex many-to-many relationships.
New tools
Microsoft is also developing new tools that can be used with SQL Server, such as SQL Operations Studio, and the Visual Studio Code extension (which allows you to query your database from within VS Code).

The Microsoft website provides a more detailed list if you're interested.