Ready for the Future

Microsoft SQL Server 2017 has been available since October. The new version opens a new chapter in the history of the database server in several respects. Microsoft is looking to make its relational database fit for the Internet of Things (IoT), big data, and machine learning, and when it comes to performance, SQL Server now acts much more intelligently.

Much like other Microsoft server products, SQL Server development is strongly driven by the Azure Cloud, big data, and IoT. The SQL Server landscape is now identical to version 2016: Enterprise, Standard, and Express. Hardware limitations and supported features, as they existed in 2016, remain the same.

SQL Server on Linux

SQL Server on Linux has become a reality with SQL Server 2017. It is currently supported on Red Hat Enterprise Linux (RHEL) 7.3 and 7.4 (see also the "Migration Discount" box), SUSE Linux Enterprise Server v12 SP2, and Ubuntu 16.04. As expected, the RPM package for RHEL can also be installed and executed on CentOS 7 without problems (Figure 1). You will not find the installation packages for Linux on the SQL Server image; you must download them. The easiest way to do this is to include the corresponding Microsoft repositories, as described online (Figure 2) [2]. The size of the download is 150 to 200MB, and after calling a configuration script, your SQL instance is ready for use.

Figure 1: The RHEL version of SQL Server 2017 runs on CentOS.

Figure 2: Additional downloads streamline deployment of SQL Server 2017.

Remember also to install the SQL Server Agent service (separate mssql-server-agent Linux package) and full-text search (mssql-server-fts ) before commissioning the new SQL instance, because the mssql-server service requires a restart. The sqlcmd command-line query for Linux should reside on a database server, as well, in case network connectivity is interrupted. This utility is also in a separate package (mssql-tools ). Overall, deploying SQL Server 2017 on Linux takes significantly less time than on Windows. However, the range of functions are significantly different between the two versions.

Courage to Fill the Gap

The database engine is currently the only SQL Server component available on Linux. Reporting services are still missing, as are SQL Server Analysis Services (SSAS), Data Quality Services, and Master Data Services. The database engine on Linux is not multi-instance capable – a feature that is controversial even under Windows – and currently lacks the following important features:

• File tables, Stretch database, change data capture, and PolyBase.
• Extended stored procedures (XP_CMDSHELL).
• Transactional replication.
• Cross-platform high availability. Although Always On high availability is possible in the Enterprise Edition, all members of an Always On Availability Group must be installed on the same operating system platform, so merging Windows and Linux is not possible.
• SQL Server R Services.
• SQL Server Browser. However, with one instance per operating system and fixed TCP port assignment, this service is easy to do without.
• Extensible Key Management (EKM). Database encryption features such as Transparent Data Encryption work on Linux, but the keys cannot currently be managed in an external EKM or a hardware security module (HSM).

SQL Server Management Studio (SSMS) [3] is based on Visual Studio 2017 and is therefore not available for Linux anymore than SQL Server Data Tools. Therefore, Microsoft currently does not have a dedicated graphical management tool for Linux. With SSMS for Windows, you can connect to a Linux-based SQL instance as usual. If you also want to change the graphical SQL administration to Linux, you can use Visual Studio Code (VS Code), for example, with the mssql extension (Figure 3). Both are available for Windows, Mac OS, and supported Linux distributions [4].

Figure 3: The VS Code mssql extension provides graphical SQL administration under Linux.

Of course, you can use all GUI tools that exist on Linux platforms and support SQL Server to manage your Linux-based SQL instances. The SQL Server Agent does not fully correspond to the Windows variant in its scope of services. The intelligence of the maintenance plans is currently not implemented, and neither are the cmd.exe and PowerShell subsystems. Of course, the agent functionality for services that are not implemented on Linux, such as Analysis Services, is also missing.

Licensing

In addition to the installable Linux packages, SQL Server 2017 also offers a Docker image (Docker 1.8 and higher), which means your database instances can now benefit from the flexibility of a container infrastructure, provided they have the appropriate licensing. The Docker image has the same performance as the Linux version.

To take full advantage of containers, you will usually need to license a high-density environment, which requires an SQL Server Enterprise license for all CPU cores in all affected container hosts plus Software Assurance (SA) [5]. You can use this licensing variant to increase the number of SQL instances provided as required. This type of licensing is also ideal for classic server virtualization with a large number of SQL instances; SA gives you unlimited mobility of your SQL licenses within the licensed virtualization farm.