Chapter 1
SQL Server on Linux: Getting Started
In This Chapter
Installing Additional Components
Installing on SUSE Linux Enterprise Server
Although SQL Server has been a Windows-only software product for more than two decades, it originally started as a database engine for the then-new OS/2 operating system. The year was 1989 and the product was actually called Ashton-Tate/Microsoft SQL Server, originally written by Sybase using the C language. By the summer of 1990, after ending a marketing and distribution agreement with Ashton-Tate, it was renamed Microsoft SQL Server.
After the failure of OS/2 to gain market acceptance and the huge success of Windows, Microsoft SQL Server was eventually moved to the then-new Windows NT platform. SQL Server 4.21a, released in 1993, was the first version to run on Windows. The last version of SQL Server for OS/2, SQL Server 4.2B, was released that same year.
From then on, Microsoft focused on its software product as a Windows NT–only strategy. But that was about to change more than 20 years later when Microsoft announced in March 2016 that SQL Server would be available on the Linux platform. In 2017, Microsoft indicated that this version would be named SQL Server 2017 and would be available on Red Hat Enterprise Linux, Ubuntu, and SUSE Linux Enterprise Server, in addition to Docker containers. The product was released in October 2017.
I started my career in information technology with Unix applications and databases in the early 1990s and was, at the time, mostly unaware of these SQL Server developments. I worked with all the popular implementations of Unix, including Linux, for about a decade. These Unix platforms included System V Release 4, IBM AIX, and Hewlett-Packard HP-UX. I was one of the early Linux users, which I later deployed in production, but mostly to run web servers. I remember that Linux was not an immediate success, and nobody knew about Linux in those early days. I eventually decided to specialize in SQL Server and left the Unix/Linux world behind.
When I heard that SQL Server would be available on Linux, I was really excited about the possibilities. It was like going back to the old times. It was ironic and interesting that SQL Server would bring me back to Linux.
Although Chapter 2 continues the discussion of SQL Server history with different operating systems, this chapter will show you how to install SQL Server on Linux so you can start playing with the technology as quickly as possible. Chapter 2 also describes decisions about SQL Server on Linux architecture, software implementations, and how SQL Server interacts with the operating system, among other related topics. The remaining chapters focus on all the details and more advanced topics.
Creating a Virtual Machine
This chapter will show you how to install SQL Server on a virtual machine image with Linux preinstalled. You can obtain the Linux virtual machine in several ways—for example, by using a cloud provider such as Microsoft Azure or Amazon Web Services (AWS), or by using an image of a virtual machine created in the virtualization environment in your data center. You can also install Linux first on your own virtual machine or hardware and then follow the rest of the chapter.
Later chapters focus on more advanced details, such as configuring SQL Server or implementing high availability or disaster recovery solutions. The following Linux distributions are currently supported to run SQL Server:
Red Hat Enterprise Linux 7.3 or 7.4
SUSE Linux Enterprise Server v12 SP2
Ubuntu 16.04
Docker Engine 1.8+
If you want to install Linux directly on a virtual machine or hardware, these versions of the Linux operating system software are available:
Red Hat Enterprise Linux https://access.redhat.com/products/red-hat-enterprise-linux/evaluation
SUSE Linux Enterprise Server https://www.suse.com/products/server/
Ubuntu https://www.ubuntu.com/download/server
NOTE
You can sign up for the Red Hat Developer Program, which provides no-cost subscriptions where you can download the software for development use only. For more details, see https://developers.redhat.com/products/rhel/download.
In this section, I’ll show you how to create a virtual machine in Microsoft Azure. If you need to use Amazon AWS, you can create an account and a virtual machine at https://aws.amazon.com/.
Start by visiting the Azure web site at https://azure.microsoft.com, where you can use an existing Microsoft Azure subscription or sign up for a free account with a US$200 free credit. After logging on with your credentials, proceed to the Microsoft Azure portal (https://portal.azure.com) by following the links. You may need to spend a few moments getting familiar with the Microsoft Azure portal.
To create a new virtual machine on the Microsoft Azure portal, choose Virtual Machines | Add. Hundreds of images may be available, so you need to enter a filter to help with the search. As of this writing, you can use two kinds of virtual machines:
Virtual machines with only Linux installed
Virtual machines with both Linux and SQL Server installed
If you select a virtual machine with both Linux and SQL Server installed, you will need to set the system administrator (sa) password and start the SQL Server service, which is a simple process that is covered in the section “Configuring SQL Server.” If you select a virtual machine with only Linux installed, you’ll also learn how to install SQL Server in that section.
If you are interested in finding a virtual machine with SQL Server already installed, enter SQL Server Red Hat Enterprise Linux or SQL Server SUSE Enterprise Linux Server or SQL Server Ubuntu in the Search bar. As of this writing, you can find images available for the Express, Developer, Web, Standard, and Enterprise editions for all the supported Linux distributions mentioned earlier. As suggested, the available choices may change at a later time. An example using SQL Server Red Hat Enterprise Linux is shown in Figure 1-1.
Figure 1-1 Microsoft Azure virtual machines search
Clicking any of the results will show you the image details on the right side of the screen. For example, selecting Free SQL Server License: SQL Server 2017 Developer on Red Hat Enterprise Linux 7.4 (RHEL) will show you the following information (note that the message refers to the free SQL Server Developer edition; you still have to pay for the cost of running the Linux virtual machine):
This image contains the Developer edition of SQL Server 2017 on Red Hat Enterprise Linux 7.4. This free edition (no SQL Server licensing cost) includes all the functionality of Enterprise edition, but it is licensed for development and testing only, not production. It provides comprehensive capabilities for mission-critical transactional processing, data warehousing, and real-time business intelligence.
After provisioning, please run $sudo /opt/mssql/bin/mssql-conf set-sa-password to configure SQL Server.
See documentation for additional details. https://docs.microsoft.com/azure/virtual-machines/linux/sql/provision-sql-server-linux-virtual-machine.
If you need to find a virtual machine without SQL Server installed, enter Red Hat Enterprise Linux or SUSE Enterprise Linux Server or Ubuntu in the Search bar. Be sure to select a supported version, as indicated earlier—for example, 7.3 or 7.4 for Red Hat Enterprise Linux 7.3 or 7.4.
Because I am going to show the entire installation process, for this exercise, select Red Hat Enterprise Linux 7.4. You may also choose a virtual machine with SQL Server installed, such as Free SQL Server License: SQL Server 2017 Developer On Red Hat Enterprise Linux 7.4 (RHEL) and still follow the rest of the chapter content. My selection shows the following description:
Red Hat Enterprise Linux is the world’s leading enterprise Linux platform built to meet the needs of today’s modern enterprise. Red Hat Enterprise Linux is the preferred choice for enterprise Linux virtual machine (VM) workloads on Microsoft Azure. Red Hat Enterprise Linux is an open, reliable, and secure platform designed for customers who want deployment flexibility for their business-critical workloads—from the data center to the Azure cloud—backed by tightly integrated, enterprise-grade support from Red Hat and Microsoft.
Click Create To Continue. Leave the default deployment model set to Resource Manager. Although there is a second choice, Classic, the Azure Portal recommends that you use Resource Manager for new workloads.
The portal, which consists of four sections, will then ask you to provide information to create your virtual machine.
Step 1. Basics: Configure Basic Settings
The first section, shown in Figure 1-2, asks you to configure the virtual machine basic settings, such as server name, disk type, and authentication information.
Figure 1-2 Configure basic settings
Figures 1-2 through 1-5 do not show all the entire portal screens. You may need to scroll down on the web portal to see some parts of a screen, but all the choices are explained here. Some items offer a description. For example, you may wonder about the VM Disk Type or Resource Group choices shown in Figure 1-2. VM disk type is described as follows:
Figure 1-3 Configure virtual machine size screen
Figure 1-4 Configure optional features screen
Figure 1-5 Purchase and summary screen
Premium disks (SSD) are backed by solid state drives and offer consistent, low-latency performance. They provide the best balance between price and performance, and are ideal for I/O-intensive applications and production workloads. Standard disks (HDD) are backed by magnetic drives and are preferable for applications where data is accessed infrequently.
A resource group is defined as “a collection of resources that share the same lifecycle, permissions, and policies.” To learn more about resource group, see https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-portal.
For Authentication Type, choose either Password or SSH Public Key. Finally, select a location near you if one is not already selected. Currently a large number of locations are provided; in my case, West US is selected by default. When you’re done, click OK.
NOTE
For more details on how to generate a SSH key, see “How to Create and Use an SSH Public and Private Key Pair for Linux VMs in Azure” at https://docs.microsoft.com/en-us/azure/virtual-machines/linux/mac-create-ssh-keys. You can also find “How to Use SSH keys with Windows on Azure” at https://docs.microsoft.com/en-us/azure/virtual-machines/linux/ssh-from-windows.
Step 2. Size: Choose Virtual Machine Size
In this step, you’ll choose a virtual machine size that basically defines the required number of processor cores, amount of memory, size of disk, number of data disks, and maximum input/output operations per second (IOPS). You made the choice between SSD and HDD (solid state drives and standard disks) in step 1. Be careful with your choices here, because they will impact your subscription cost. The estimated monthly cost is shown under each choice.
The portal recommends a few choices, but you can click the View All link to see all of them.
If you are new to Microsoft Azure or are not sure about what to choose, select the least expensive option and regularly monitor the resources used and cost. The recommendations are based on the hardware and software requirements for the virtual machine image you selected. A recommendation may also be indicated on the image description. For example, a selected choice may show something like this: “We recommend that you use a virtual machine size of DS2 or higher for development and functional testing. DS13 or higher for performance testing.” Choose your virtual machine size and then click Select to continue.
Step 3. Settings: Configure Optional Features
Fortunately in our case, defaults are provided for all these features. For now, go with the defaults, and we will review some of them in later chapters. For completeness, I’ve included the descriptions of the items on the Azure portal (see Figure 1-4).
Use Managed Disks Enable this feature to have Azure automatically manage the availability of disks to provide data redundancy and fault tolerance, without creating and managing storage accounts on your own. (For more details, see https://docs.microsoft.com/en-us/azure/storage/storage-managed-disks-overview.)
Virtual Network Virtual networks are logically isolated from one another in Azure. You can configure their IP address ranges, subnets, route tables, gateways, and security settings, much like a traditional network in your data center. Virtual machines in the same virtual network can access each other by default.
Subnet A subnet is a range of IP addresses in your virtual network, which can be used to isolate virtual machines from one another or from the Internet.
Public IP Address Use a public IP address if you want to communicate with the virtual machine from outside the virtual network.
Network Security Group (Firewall) A network security group is a set of firewall rules that control traffic to and from your virtual machine.
Extensions Add new features, such as configuration management or antivirus protection, to your virtual machine using extensions. (For more details, see https://docs.microsoft.com/en-us/azure/virtual-machines/windows/extensions-features.)
High Availability To provide redundancy to your application, I recommend that you group two or more virtual machines in an availability set. This configuration ensures that during a planned or unplanned maintenance event, at least one virtual machine will be available and will 99.95 percent meet the Azure service-level agreement (SLA). The availability set of a virtual machine can’t be changed after it is created.
Monitoring Capture serial console output and screenshots of the virtual machine running on a host to help diagnose startup issues.
Guest OS Diagnostics Get metrics every minute for your virtual machine. You can use them to create alerts and stay informed about your applications.
Diagnostics Storage Account Metrics are written to a storage account so you can analyze them with your own tools.
To continue, click OK.
Step 4. Summary and Purchase
In this Summary section, you can review all the selected choices, as shown in Figure 1-5. Click Purchase to continue and create the virtual machine, which could take a few minutes.
After the virtual machine is created, the virtual machines page on the Azure portal should show something similar to Figure 1-6.
Figure 1-6 Virtual machines page
As shown in Figure 1-6, after the virtual machine is created, it will be automatically started and you can connect to it. For more details about how to connect, click the virtual machine name, in this case, sqlonlinux. Microsoft Azure will show something similar to Figure 1-7.
Figure 1-7 Virtual machine details
You can see the public IP address you can use to connect to. Clicking Connect on the Azure portal will also show you how to connect. In my case, I see “To connect to your Linux virtual machine using SSH, use the following command: ssh bnevarez@104.210.38.105.”
We will connect to this virtual machine via Secure Shell (SSH) in the following section. You can also find the public IP address on the Networking section on the Azure portal.
Installing SQL Server
Now we’ll download and install SQL Server. First we need to connect to the created Linux virtual machine—but to do that, we need a terminal emulator that can support an SSH session. A popular and free terminal emulator is PuTTY, which you can download from www.putty.org.
To connect, you need the public IP address provided earlier on the virtual machine details (Figure 1-5) and the user name and password provided during the configuration (Figure 1-2). Figure 1-8 shows an example of opening an SSH session using PuTTY.
Figure 1-8 Creating a PuTTY session
SSH is a cryptographic network protocol and a suite of utilities that implement the protocol. It is used to connect to remote computer systems and designed as a replacement for other unsecured protocols such as telnet, rlogin, and rsh. (I remember using telnet heavily before SSH was available.) SSH uses the standard TCP port 22, which is selected by default in Figure 1-8.
Once you’re connected to the Linux server, you will see the Linux prompt ($), or, more exactly, the Bash shell prompt. It may also contain the username and hostname:
I will first show you how to install and configure SQL Server for Red Hat Enterprise Linux. Instructions to perform the same operations on Ubuntu and SUSE Linux Enterprise Server are covered later in the chapter. How to run SQL Server 2017 on Docker is covered at the end of the chapter.
To continue with our Red Hat Enterprise Linux virtual machine, you need to download and install SQL Server using the curl and yum utilities. The curl command is designed to transfer data between servers without user interaction, and yum is a package manager utility that we will use to install the latest version of a package.
First, switch to superuser mode:
sudo is a Linux command that enables you to execute a command as another user or as the superuser or root. A user needs to have prior privileges to perform such activity. The first time you run sudo, you will see a message such as the following:
If you have created a virtual machine without SQL Server installed, continue reading. If you already have SQL Server installed, you may still want to continue reading here to learn a bit about packages and package managers, especially if you are new to Linux, but you don’t have to run any of the following commands. If you selected a virtual machine with SQL Server installed, you have to run only the following two steps:
1. Configure a system administrator password. Run the following script:
2. Start SQL Server. Run the following command as hinted by the previous output:
I will cover more details about configuring SQL Server later in this chapter and also in Chapter 4. More details about starting and stopping SQL Server are provided later in this chapter and also in Chapter 3. The remainder of the section assumes you do not have SQL Server installed.
Run the following commands to download the SQL Server Red Hat repository configuration file:
This should finish almost immediately, because this downloads a small file. If you look at its contents, at /etc/yum.repos.d/mssql-server.repo, you will see the following:
TIP
You can use several commands to view the contents of a file. For example, you can try more/etc/yum.repos.d/mssql-server.repo. For information on these commands, see Chapter 3.
Next, run the following commands to install the SQL Server package mssql-server:
NOTE
Another difference from Windows is that in Unix and Linux commands are case-sensitive. An incorrect command name will return an error message and, in some cases, perhaps a recommendation as in the following examples:
In this example, yum installs the package named mssql-server and the –y parameter is used to answer “yes” to any question during the installation. Here’s the output, formatted for better readability:
At the moment the package was installed, SQL Server was not yet properly configured. We will do that in the next step. Also, close your root session as soon as it is no longer needed by running the exit command or pressing CTRL-D.
But before we continue, here’s a quick introduction to package managers, which may be a new concept for most SQL Server developers and administrators. The process of installing, upgrading, configuring, and removing software is quite different on Windows platforms. Software in Linux distributions uses packages, and there are several package management systems, which are collections of utilities used to install, upgrade, configure, and remove packages or distributions of software. In this chapter, we will cover some package management utilities such as yum, apt, and zypper, which we will use with Red Hat, Ubuntu, and SUSE, respectively.
The yum (which stands for Yellowdog Updater Modified) package manager was written in Python. We use it to install the SQL Server package mssql-server by running the following:
If you want to update the package, which is useful when there is a new CTP, RC, or cumulative update, you can use this:
If you want to remove the package, use this:
NOTE
CTPs (Community Technology Previews) or RCs (Release Candidates) are versions of SQL Server used during their beta program before a final release. A final release is called RTM (release to manufacturing).
Finally, one of the major changes in SQL Server 2017 compared to previous versions is the new servicing model. Although service packs will still be used for SQL Server 2016 and previous supported versions, no more service packs will be released for SQL Server 2017 and later. The new servicing model will be based only on cumulative updates (and GDRs [General Distribution Releases] when required).
Cumulative updates will be released more often at first and then less frequently in the new servicing model. A cumulative update will be available every month for the first 12 months and every quarter for the remaining four years of the full five-year mainstream life cycle.
As of this writing, three repositories are defined for SQL Server on Linux:
Cumulative updates This is the base SQL Server release and any bug fixes or improvements since that release. This is the choice we selected earlier using https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo.
GDR The GDR repository contains packages for the base SQL Server release and only critical fixes and security updates since that release. The repository URL for GDR is https://packages.microsoft.com/config/rhel/7/mssql-server-2017-gdr.repo.
Preview repository This is the repository used during the SQL Server beta period, consisting of CTPs and RCs. The preview repository URL is https://packages.microsoft.com/config/rhel/7/mssql-server.repo.
NOTE
Be aware that there may be a lot of code, articles, or documents published out there using the preview repository. So make sure that you refer to the correct repository.
Configuring SQL Server
The next step is to configure SQL Server. I’ll show you how to install and run SQL Server on three supported Linux distributions, so I’ll mention different options, depending on the choices.
Run the following command to configure and start SQL Server:
Answer the requested questions, including providing a strong password for the sa account. You may be asked if you want to start SQL Server and if you want to enable SQL Server to run at boot time. Answer yes in both cases. Also answer 2 to select the Developer edition.
The mssql-conf utility can accept different parameters and can be used to initialize and set up SQL Server and to perform some other activities such as enable or disable a trace flag, set the collation of the system databases, or change the sa password. To see the list of possible options, run mssql-conf without any parameter or specify the –h or --help parameter.
Note that you need to specify a strong sa password or you may get one of the following errors:
If you inspect the mssql-conf file, you will notice it is a basic bash script that calls a /opt/mssql/lib/mssql-conf/mssql-conf.py python script. /opt is a standard directory in the Unix file system that is used to install local software. There are many other standard directories, such as /bin, /dev, /etc, /home, /lib, /tmp, and /var—to list a few. More details about the Unix file system are covered in Chapter 3.
You can verify that SQL Server is running using the ps command old-school method:
This returns something similar to the following:
Or, more appropriately, use the systemctl command as in the following example:
This will show you some interesting information, as shown next (plus the last ten lines of the SQL Server error log file, not included here, called the “journal” in systemctl terminology):
This will show you how long the SQL Server service has been running; the Linux processes IDs, which are the same as those shown earlier with the ps command; and even the location of the SQL Server documentation.
Intro to Unix Commands
A short introduction to some Unix commands is worth including here. The ps command is used to display information about the processes running in the system, and in this case we are using the pipe symbol or | to send the output of the ps command as the input to the grep command. The grep command searches the provided input for lines containing a match to the given pattern.
Many Linux commands have multiple options, all of which you can find in the command documentation. You can also get more help about these commands by reading the documentation, which you can access using the man command followed by the name of the command. Here are three examples:
Or
Or the following, to learn more about the man command itself:
In addition, systemd is an init or initialization system used in Linux distributions to manage system processes that will run as daemons until the system is shut down. systemd has several utilities, such as systemctl, which you can use to start, restart, or stop a service or, as shown previously, to show runtime status information. The systemctl command is covered in detail in Chapter 3.
Connecting to SQL Server
Now let’s connect to the instance we just installed. Although SQL Server is installed and running, the previous installation did not install SQL Server tools. Let’s install them now. This time, we need to download them by switching to superuser mode again and running the following curl and yum commands:
Similarly, the content of /etc/yum.repos.d/msprod.repo is
Run the installation process:
An abbreviated output to fit the book page is next. Notice that this time we are installing two packages—mssql-tools and unixODBC-devel:
Now we can connect to SQL Server using the Linux version of the familiar sqlcmd command-line tool:
Run the following:
This is what I get with my current release:
Inspecting the /opt/mssql-tools/bin directory will also show you the familiar bcp (bulk copy program) utility, which is used to import data from data files into SQL Server.
So far, we have been able to connect from inside the virtual machine. Could we connect from a SQL Server client outside the virtual machine? We could try using SQL Server Management Studio (SSMS) on Windows, connecting to the listed public IP address.
NOTE
At this point, you may need to install SQL Server Management Studio, preferably the latest version, which you can download from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms. As of this writing, the latest version is 17.3.
Trying to connect now will show a familiar message:
Cannot connect to 40.112.137.60. Additional information. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53). The network path was not found.
The virtual machine needs to be configured to allow SQL Server remote connections. We were able to connect before using SSH as this protocol TCP port is configured by default, as you can see next, but connections to the SQL Server port, and basically anything else, need to be explicitly configured.
To configure your new virtual machine to accept SQL Server connections, open the Microsoft Azure Portal and go to Virtual Machines. Select your virtual machine, in this case sqlonlinux; click Networking; select the only available network interface in this example; and then select Network Security Group and Inbound Security Rules. You will see a configuration similar to the one shown in Figure 1-9, where only SSH on TCP port 22 is enabled by default. This was the reason we were able to connect from outside the virtual machine previously.
Figure 1-9 Inbound security rules
We need to configure a new inbound rule on the network security group. We want to add a new rule to allow SQL Server connections using TCP port 1433. Click Add and change the configuration as shown in Figure 1-10. Type a name such as MSSQL and select MS SQL on the Service drop-down list. Protocol and Port Range will be automatically configured to TCP and 1433, respectively.
Figure 1-10 Adding a rule to accept SQL Server connections
Leave the other fields set to their default values and click OK. For completeness, these are the definitions as shown on the Azure portal:
Priority Rules are processed in priority order; the lower the number, the higher the priority. We recommend leaving gaps between rules—100, 200, 300, and so on—so that it’s easier to add new rules without having to edit existing rules.
Source The source filter can be Any, an IP address range, or a default tag. It specifies the incoming traffic from a specific source IP address range that will be allowed or denied by this rule.
Service The service specifies the destination protocol and port range for this rule. You can choose a predefined service, such as RDP or SSH, or provide a custom port range. In our case, there is a predefined service, MS SQL, which already specifies the TCP protocol and port 1433.
Protocol You can provide a single port, such as 80, or a port range, such as 1024-65535. This specifies on which ports incoming traffic will be allowed or denied by this rule. Enter an asterisk (*) to allow traffic from clients connecting on any port.
Click OK to finish the configuration. Refreshing the content shown in Figure 1-10 will now show both SSH and MS SQL services, using the TCP ports 22 and 1433, respectively. Only for Red Hat Enterprise Linux, you also have to open up port 1433 on the firewall. On Linux, run the following commands
Now you can connect from SQL Server Management Studio on Windows or any other SQL Server client outside the virtual machine, including applications on a large number of programming languages or frameworks such as .NET, Java, Python, and so on.
If you are accustomed to connecting using Windows authentication, as I am, keep in mind that for now you need to use the only login available at the moment, sa, and the password provided earlier. For example, Figure 1-11 shows Object Explorer from my current connection using SQL Server Management Studio.
Figure 1-11 SQL Server Management Studio connected to a Linux instance
Let’s go back to our Linux SSH connection to create a couple of databases and explore where the database files are stored in the file system. Connect to the SQL Server instance using sqlcmd, as explained earlier, and create your first database by running the next statement:
So you now have your first user database in Linux. Where could it be located? There are a few ways to figure that out. Try the following:
Here’s the information returned:
The default data location in Linux is /var/opt/mssql/data. This is the equivalent of the familiar C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA in Windows. The location can be changed with the ms-conf utility as discussed in Chapter 4.
Run the following backup statement to find the default location of backup files:
You should be able to see the backup files at /var/opt/mssql/data/demo.bak (assuming you have the proper permissions). Similarly, the system databases files are at the following locations:
/var/opt/mssql/data/master.mdf
/var/opt/mssql/data/mastlog.ldf
/var/opt/mssql/data/model.mdf
/var/opt/mssql/data/modellog.ldf
/var/opt/mssql/data/msdbdata.mdf
/var/opt/mssql/data/msdblog.ldf
/var/opt/mssql/data/tempdb.mdf
/var/opt/mssql/data/templog.ldf
Note that, as with a default Windows installation, both data and transaction log files reside on the same directory, /var/opt/mssql, which also contains a log directory, which is also the same as in Windows; it is used to store the SQL Server error log files, trace files created by the default trace, and extended events system_health files. This should not be confused with the location for database transaction log files. Spend some time inspecting the structure and files located both on /var/opt/mssql and /opt/mssql.
Finally, there are several ways to move or copy your Windows databases and data to Linux. For now, I can show you how to copy a Windows database backup file quickly and perform a database restore in Linux.
First, we need a way to copy the database backup file from Windows to our Linux virtual machine. Once of these choices is to install the PuTTY scp client on Windows, which you can download from www.putty.org. (The scp stands for secure copy and it is a utility used to copy files between computers.)
Once scp is installed, run the following:
This will take a couple of minutes and will copy the database backup file to your home directory in Linux. In my case, the file ended at /home/bnevarez/AdventureWorks2014.bak.
Next, to avoid issues with permissions, use the following command to copy the backup file to /var/opt/mssql/data:
Run the next restore statement, which specifies the location of the backup file and the new location for the created database files:
You can optionally restore the database with SQL Server Management Studio, where you only have to browse to the location of the backup file and the database file locations will be handled automatically. The database will be restored as AdventureWorks2014, and you will be able to query it in exactly the same way you would have done it in Windows.
At this point, you may need the basic statements to start, restart, and stop SQL Server. More details about systemctl will be covered in Chapter 3.
To stop SQL Server, run the following statement:
To start SQL Server, run the following:
To restart SQL Server, run the following:
Installing Additional Components
This section covers how to install the SQL Server Agent, SQL Server Full-Text Search, and SQL Server Integration Services, which are provided in separate packages: mssql-server-agent, mssql-server-fts, and mssql-server-is, respectively. We will follow the same procedure for Red Hat Enterprise Server using the yum utility.
To install SQL Server Agent, run the following commands (again, output formatted to fit the book):
As hinted in the installation, you will need to restart SQL Server after the SQL Server Agent is installed:
To install SQL Server Full-Text Search, run the following yum command:
Again, you will need to restart SQL Server after SQL Server Full-Text Search has been installed.
To install SQL Server Integration Services, run the following command:
Finally, as hinted on the install output, run the following command to configure SQL Server Integration Services, and again select 2 to choose the free Developer edition:
There is no need to restart SQL Server at this time. It is also recommended that you update the PATH environment variable by running the following command:
NOTE
For details about permanently updating the PATH environment variable, see Chapter 3.
Installing on Ubuntu
You’ve seen the entire process of installing and configuring SQL Server 2017 using Red Hat Enterprise Linux. Let’s now install SQL Server on Ubuntu. After this I’ll focus only on what is different compared with the previous Red Hat installation. Let’s start by creating a virtual machine running Ubuntu.
Open the Microsoft Azure virtual machines portal. In the Search field, enter Ubuntu. This will show you a few results. Select Ubuntu Server 16.04 LTS. Every Linux installation must have at least 3.25GB of memory for a SQL Server 2017 installation, but at the moment any virtual machine image on the gallery has at least that much memory, so this should not be a problem. You need to be careful with this, however, if you are creating the virtual machine using other methods or if you’re installing the operating system software yourself.
Follow the steps described in the “Creating a Virtual Machine” section earlier in the chapter. Once you have a virtual machine running and you are able to connect to it, run the following statements to get the public repository GPG keys (more on this in a moment):
Next, run the following statements to register the SQL Server Ubuntu repository:
As with Red Hat Enterprise Linux, this was the cumulative update repository. The GDR repository is at https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017-gdr.list, and the preview repository is at https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list.
Finally, run the following commands to install SQL Server:
After this point, you can configure SQL Server in the same way as before by running the following, accepting the license terms, selecting the free Developer addition, and providing a strong sa password:
You’ll see something similar to the following output:
Installing client tools is also different from Red Hat Enterprise Linux. Start by importing the public repository GPG keys if you have not done so when you installed SQL Server in the previous step:
Run the next step to register the Ubuntu repository:
Now we are ready to update the sources list and to install both the mssql-tools and the unixODBC developer packages:
Now we can connect to the SQL Server instance same as before:
In this case, running SELECT @@VERSION returns the following:
Follow the same steps shown earlier to connect from outside the virtual machine. Basically, you will need to configure a new inbound rule on the network security group to allow SQL Server connections using the TCP port 1433.
As I did with Red Hat Enterprise Server, I will now cover how to install SQL Server Agent, SQL Server Full-Text Search, and SQL Server Integration Services for Ubuntu.
To install SQL Server Agent, run the following command:
To install SQL Server Full-Text Search, run the following command:
After you install these components, you will need to restart SQL Server:
Finally, to install and configure SQL Server Integration Services, run the following statements:
There is no need to restart SQL Server this time. As mentioned earlier, it is also recommended at this time that you update the PATH environment variable by running the following command:
NOTE
For details about permanently updating the PATH environment variable see Chapter 3.
Installing on SUSE Linux Enterprise Server
Now let’s use the same process with SUSE Linux Enterprise Server. Again, open the Microsoft Azure virtual machines portal. Enter SLES for SUSE Linux Enterprise Server in the Search field, and you will see a few choices. Select the recommended version and service pack. I am using SLES 12 SP2.
As with the previous installations, remember that every Linux installation must have at least 3.25GB of memory to run SQL Server 2017. Follow the steps described in the “Creating a Virtual Machine” section earlier.
Once you have a virtual machine and you are able to connect to it, start by downloading the SQL Server SLES repository configuration file. Run the following commands:
Same as the previous Linux distributions mentioned, this was the cumulative update repository. The GDR repository is at https://packages.microsoft.com/config/sles/12/mssqlserver-2017-gdr.repo, and the preview repository is at https://packages.microsoft.com/config/sles/12/mssql-server.repo.
Next, run the following command to install SQL Server:
Configure SQL Server the same way you did earlier using the mssql-conf utility:
Finally, install the SQL Server client tools. Use the following steps to install the mssql-tools package on SUSE Linux Enterprise Server. Add the SQL Server repository to zypper:
Install mssql-tools with the unixODBC developer package:
Connect as usual using the sqlcmd command-line utility:
Running SELECT @@VERSION this time shows the following:
Follow the same steps indicated earlier to connect from outside the virtual machine. You will need to configure a new inbound rule on the network security group to allow SQL Server client connections using the TCP port 1433.
As I did with Red Hat Enterprise Server and Ubuntu, I will next cover how to install SQL Server Agent, SQL Server Full-Text Search, and SQL Server Integration Services on SUSE Linux Enterprise Server. To install SQL Server Agent, run the following command:
To install SQL Server Full-Text Search, run the following command:
After you install these components, you will need to restart SQL Server:
Finally, as of this writing SQL Server Integration Services is not yet available for SUSE Linux Enterprise Server.
Running SQL Server on Docker
Docker is a software technology that provides containers as an additional layer of abstraction and automation to the virtualization technologies we are very familiar with. Docker can run on the Linux, Windows, or Mac OS operating systems.
A fundamental difference between using SQL Server on Docker and the previous Linux installations is that Docker will run a preexisting SQL Server 2017 image. The currently available image is an installation of SQL Server 2017 on Ubuntu 16.04. But keep in mind that this Ubuntu SQL Server 2017 image can run on any operating system that supports Docker containers.
To run SQL Server 2017 on Docker, you will first need a Docker installation. Because there might be different ways to configure Docker, in this exercise I will show Docker on an Ubuntu virtual machine.
For example, to run Docker on my previous Ubuntu virtual machine image, I use the following:
And I get the following response:
So I followed the recommendation and ran the following:
NOTE
SQL Server 2017 requires 3.25GB of memory on Docker, so make sure the host is configured with at least 4GB of memory. This was a common problem I found on some default Docker installations.
Next, we need to download the SQL Server 2017 image by running the following:
Here is some output in my installation:
Next we are ready to run the SQL Server container image on Docker, executing the next statement while specifying an appropriate sa login password:
For this example, port 1421 has to be available on the host computer. We cannot use the host port 1433 because we are already running an instance of SQL Server there (installed in a previous exercise). The second port, 1433, will be the port on the container.
Validate that the SQL Server 2017 container is running by executing the following:
The output will show something similar to this, where you can validate the STATUS information to verify that SQL Server container is effectively running:
If you have SQL Server client tools on the same server, you can connect directly to the new installation. Or you can install them as described earlier for the Ubuntu Linux distribution. Note that only client tools are required, because you will be connecting to a SQL Server image running in a Docker container.
You should be able to connect as indicated earlier. For example, you can use the following command using the host port 1421:
This time @@VERSION will return
Here’s a quick explanation of several parameters used for the container.
To confirm your acceptance of the end-user licensing agreement:
To specify the sa password:
Remember the sa login password policy requirements mentioned earlier. A problem with this password will be a bit more complicated to troubleshoot because you need to look at the SQL Server error log.
To specify the port mapping—the first value is the TCP port on the host environment and the second the TCP port on the container:
To specify the SQL Server container image:
To specify a custom name for the container:
Optionally you can connect to the container and run the client tools from there. For example, you can use the following command, which runs an interactive Bash shell inside the container, where e69e056c702d is the container ID shown earlier on the Docker ps command. The docker exec command is used to run a command in a running container:
After running the command, you will receive a bash prompt, #, where you can run the sqlcmd client as usual. Note that this time, we don’t need to specify a port since we are inside the container, which was configured to listen to the default SQL Server port 1433:
Follow the same steps indicated earlier to connect from outside the virtual machine. Basically, we will need to configure a new inbound rule on the network security group to allow SQL Server connections using the host TCP port, in this example, 1421.
Finally, here a few basic Docker commands, just to get you started. In any of the following cases you can use the container ID or the provided name.
To stop one or more running containers:
Or
To start one or more stopped containers:
Or
To restart a container:
When you no longer need the container, you can remove it using the rm option:
Note that you should stop the container before trying to remove it, unless you also specify the –f option.
To troubleshoot problems with a container, you can look at the SQL Server error log using the following command:
Uninstalling SQL Server
Removing SQL Server is essentially the same procedure for all the Linux distributions, and it involves using the appropriate package manager utility that we have used previously in the chapter. First, stop the SQL Server instance as indicated earlier.
To remove SQL Server on Red Hat Enterprise Linux, run the following:
To remove SQL Server on Ubuntu, run the following:
To remove SQL Server on SUSE Linux Enterprise Server, run the following:
In any of the listed Linux distributions, if you need to remove all the databases after uninstalling SQL Server, run the following:
Summary
This chapter covered installing SQL Server on Linux and provided you with enough information to get started using the product as quickly as possible. It covered how to install and configure SQL Server in a preexisting Linux installation, which could be either a virtual machine on Microsoft Azure or Amazon Web Services or your own Linux installation. You learned how to install SQL Server on Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu and how to configure an image of SQL Server on a Docker container.
If you’ve followed the instructions in this chapter, you will have a SQL Server up and running and will be ready to cover most advanced topics, such as configuring SQL Server on Linux for high availability and disaster recovery or covering the new query processing and security features. Chapter 2 covers architecture decisions, how SQL Server works on Linux, how SQL Server interacts with the operating system, and other related topics.