What’s new with SQL Server 2019 Linux features
With SQL Server 2017, Microsoft entered the world of multi-OS platform support for SQL Server. For many technical professionals, the ability to run SQL Server on the same open source operating system as the rest of the application stack is not just a goal, but a dream that Microsoft made come true. With the release of SQL Server 2019, the inclusion of Linux now includes new features, support, and capabilities.
As a long-time Linux database administrator (DBA), in this post I’ll share my top five focus areas for the Microsoft data professional to become knowledgeable of as they embark on the brave new world of Linux.
1. Embrace the command line
Yes, there is a graphical user interface, (GUI) for Linux, but the command line rules in Linux. We can’t stress enough how important it is to learn how to navigate directories (cd), change permissions (chmod), and list contents (ls). Your best friend will become the -h argument to any command to get the help menu for whatever you’re attempting.
It will be essential to know how to install and update your server and applications, (apt-get, yum, and zypper) as it may be your responsibility not only to just perform this task for the database tier, but also the server if a Linux administrator isn’t already on staff in your organization.
You will also need to learn how to update your existing PowerShell scripts to work on Linux or learn how to write BASH scripts. This is commonly done from a terminal editor and one of the best is VI/VIM. Knowing how to use VI will give you credibility when working with Linux and deter too many questions when in the middle of critical work or even day-to-day tasks.
2. Understand the operating system
There are many distributions (aka flavors) of Linux, which has grown from the original release. Each distribution, although very similar, may have their own unique requirements and quirks. Understanding what distributions are best for enterprise deployments can ensure what you have deployed into the environment can withstand the demands the business puts on it. It was only two decades ago that Unix DBAs were hesitant to run relational databases on any version of Linux. In that time, every distribution has come a long way, but many distributions have a purpose. If we take a very specific flavor of Debian, called Raspbian, it was specifically designed to run the operating system for Raspberry Pis, which are single Advanced RISC Machine (ARM) processor computers. Understanding the difference in a distribution like Raspbian vs. enterprise Linux distributions like Enterprise Red Hat, Enterprise SUSE, and Ubuntu is important. Just because you can get SQL Server to run on non-supported Linux distributions such as CentOS doesn’t mean you should run the business on them. Stick to the supported versions and the user experience will be more satisfying along with new features available in regular release intervals.
3. Use the tools you already have
Just because SQL Server is running on Linux doesn’t mean that your previous robust tools for management, monitoring, and querying won’t work any longer. As a DBA, you can still use SQL Server Management Studio (SSMS) and preferably, use Azure Data Studio to connect to your Linux SQL Server 2019 databases. You can’t install Server Management Studio onto Linux, but you can create a Windows “jump box” with Server Management Studio and configure it as the primary log in point for administration tasks. Developers can still access SQL Server 2019 on Linux with Visual Studio Code with the SQL Server extension and have full use of SQL Server Data Tools (SSDT).
As this is Linux, the command line is still king. Sqlcmd is available to log in from on the Linux server to execute queries, run scripts, and perform management. Sqlcmd doesn’t change in the Linux version and executing a script is as simple as the following example, which logs in as the admin user to the database and password, then runs a script and logs it all in a simple text file:
sqlcmd -U $username -S $servername -P $spassword -d $database -i $script.sql > $logfile
4. Build on existing knowledge with new tools
PowerShell is taking center stage on Linux with new releases on a regular basis, so keep those skills sharp and with a few changes, many of the scripts you use today can be updated to work on SQL Server 2019 on Linux. Although sqlcmd is available for your querying command line needs, there is a new tool in town called mssql-cli.
This installation is a simple, on-line command:
sudo pip install mssql-cli
If there are any issues with installation, Linux is commonly quite good about telling you what library or tool dependencies that must be installed before the installation needs to be run again for success.
Always remember, if you get stuck, help is always available to offer assistance:
mssql-cli –h
To log into a database, the following is required:
mssql-cli -U $username -S $servername -P $password -d $database
If you notice the command is very similar to the example we used for sqlcmd rest easy, your world hasn’t changed as much as you were concerned about. Once you’re logged into mssql-cli, you’ll be able to use this to access SQL Server 2019 databases inside Linux. Features like auto-complete with IntelliSense, multi-line editing, and a metadata repository makes it easier to work with the command line for those just getting up to speed with SQL Server 2019 on Linux. There are a few limitations in mssql-cli, like the ability to execute scripts or send an output aren’t present, but the same queries that worked on-premises in sqlcmd will work here. Keep in mind that this new tool is in its infancy and still maturing on a regular release schedule.
The third new tool isn’t that new but is the next powerhouse to take on the DBA since SQL Server Management Studio and is called Azure Data Studio. If you haven’t embraced this cross-platform tool specifically designed for data professionals, you’re missing out. No more 32-bit interface, this is a modern interface with IntelliSense, code control integration, and customizable dashboards.
The biggest benefit is that it can be installed on Linux with no local installation or jump box required. All three primary Linux distributions are supported, as well as a Mac OS installation. Database administration tasks that are less likely to be required with cloud implementations, such as on-premises physical management, backups, etc. still will require SQL Server Management Studio, but if you’re part of the next generation of cloud administrators that have automated much of the mundane, then Azure Data Studio is a must.
5. Consider the power of containers
With Linux and SQL Server 2019, the addition of Big Data Clusters with Kubernetes support has been added. The reasons behind using containers are vast including simplified deployment, ease of management, and quicker recovery, but then add Big Data Clusters on top of this and it becomes quite the powerhouse.
Containers with Kubernetes offers DBAs the opportunity to manage the database without the added layer of separate OS for each node, or group of nodes, that is also called a pod. Less resources have to be allocated per database and without the additional layer, it can recover quicker.
Using Kubernetes, or similar sorted container orchestration, Big Data Clusters can be deployed, then manage a cluster of SQL Server, Apache Spark™, and the Hadoop Distributed File System (HDFS). Polybase is then used to virtualize data from various data sources with SQL Server, while an HDFS data pool can be used as a data lake for all big data using Apache Spark™. The SQL Server master instance simplifies the management and includes read, write, and processing of all data, no matter the source and is an exciting new frontier in the SQL Server 2019 release for Linux.
The list above includes just a few of the significant opportunities to learn and build your skills in Linux with SQL Server 2019. To help you on the way to mastering SQL Server 2019 on Linux, a number of resources have been made available through Microsoft, including this incredible new Packt e-book Introducing Microsoft SQL Server 2019. Don’t miss out on the next generation of SQL Server. Learn all the things and let Microsoft help you get there. If you’re ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.
