Introduction to SQL Server 2017
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:
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:
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:
- Create & maintain databases
- Create & maintain tables
- Create & maintain other database objects such as stored procedures, views, etc
- Create & maintain and schedule data backups
- Import/export data
- Replication (eg, create a copy of the database)
- Create & maintain users, roles, etc
- Optimization tasks
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.