Heraflux Technologies Blog

Keeping you informed about trends and news around the convergence of data, cloud, and infrastructure

Next in our SQL Server on Linux series is one important question. On Windows, if you’re about to run out of space, you get your VM admin / storage admin to expand one or more of your drives, and you go to Disk Management and expand the drive with no downtime. How do we accomplish this same task on Linux?

Times are certainly changing with Microsoft’s recent announcement to adopt the Linux operating system with the SQL Server 2017 release, and you should be prepared to support it. But, what is Linux? Why run your critical databases on an unfamiliar operating system? How do I do the basics, such as backing up to a network share or add additional drives for data, logs, and tempdb files? This introductory session will help seasoned SQL Server DBAs understand the basics of Linux and how it differs from Windows, all the way from basic management to performance monitoring. By the end of the session, you will be able to launch your own Linux-based SQL Server instance on a production ready VM.

Times are certainly changing with Microsoft’s recent announcement to adopt the Linux operating system with the SQL Server 2017 release, and you should be prepared to support it. But, what is Linux? Why run your critical databases on an unfamiliar operating system? How do I do the basics, such as backing up to a network share or add additional drives for data, logs, and tempdb files? This introductory session will help seasoned SQL Server DBAs understand the basics of Linux and how it differs from Windows, all the way from basic management to performance monitoring. By the end of the session, you will be able to launch your own Linux-based SQL Server instance on a production ready VM.

I bet never in your career you thought SQL Server would run on anything other than Windows. Times are changing, and are you ready? SQL Server on Linux is right around the corner! What is Linux – really? Why run your mission-critical databases on an unfamiliar operating system? This introductory session will help seasoned SQL Server DBA understand the basics of Linux and how it is different from Windows, all the way from basic management to performance monitoring. By the end of this session, you will be able to launch your own Linux-based SQL Server instance and get hands-on with this paradigm shift to see if this new platform is right for your organization.

One of the challenges with any SQL Server business continuity strategy is backing up your databases and logs on a frequent basis. With Windows, we’ve known how to accomplish this for years. But, with SQL Server on Linux, you can accomplish the same task with just a few different twists. It is similar to mounting a network share as a new driver letter in Windows. Let’s explore how to back up your databases to a network share with Linux!

One of the biggest differences with managing SQL Server Linux is with drive presentation. With Windows, we’d all scream if we saw a SQL Server with just one drive (C:) and everything piled on it. The same goes for any other operating system, including Linux. But, how do we get the additional drives added and configured properly? It’s (not) the easiest, but it’s straightforward! Let’s walk through it.

SQL Server administration, once installed and running, doesn’t vary too much based on your operating system, but the differences in the operating systems are pretty significant. To properly administer a production SQL Server on Linux, an administrator needs to understand the core basics of the Linux operating system.

I’m going to limit the scope of these discussions to the currently supported (as of CTP 2.1) operating systems for SQL Server on Linux, namely Red Hat / CentOS, SUSE, and Ubuntu. Most of the images in the screenshots will be from Ubuntu, my preferred distribution of choice, but the core concepts are very similar across all distros. If you’re looking for production systems, you should want to pick a distribution where you can purchase production-grade support.

First, installing Linux is pretty straight forward. Similar to Windows, an installable image is downloaded, usually in the form of an ISO. Go to their respective web sites, download the ISO image, and follow their installation guides to perform the most minimal installation possible of their distro.

Just do a basic installation with nothing else but an SSH server installed. One of the primary benefits of Linux is that the installable footprint is tiny, keeping the security and maintenance as small as possible. I personally don’t even want a GUI installed, as I’d just use it as a front end for a terminal anyways.

Once installed, now what?

Use SSH to get access into the console of this new server. Log in with your login and password that you created when you installed the operating system. To make things even more secure, you can use certificates to streamline your login process.

Now, let’s explore some of the commands that we need to use to get around the operating system. This is just a short list of the main commands you need to know when navigating the console of a Linux system.

The primary ones are ‘cd’ to change directory, ‘ls’ to list folder contents, mkdir and rmdir to make and remove folders, ‘rm’ and ‘mv’ to remove or move a file. ‘pwd’ tells you your present working directory.

You’ll want to learn the syntax for one of the console-based text editors. My personal favorite is ‘vi‘. It’s quick, streamlined, but does have a significant learning curve. Emacs is another editor that works great. Many others are out there, and your options open even more if you’re using a GUI. You’ll need an editor to edit configuration files.

The folder structure of Linux is one of the biggest changes. Whereas Windows is based off of an arbitrary drive-letter assignment system that dates back to the DOS era, Linux is is based off of a tree structure. All folders and files are based on a single point, ‘/’ or the root folder, and everything is based off of folders from this point. Certain folders from Windows, such as C:\Windows, C:\Users\username, or %WINDOWSTEMP%, are mapped to certain folders within the Linux operating system.

Your home directory is at /home/yourusername. SQL Server for Linux installs at /var/opt/mssql, at as of CTP 2.1 cannot be changed.

But… as you start to poke around the operating system, you’ll start to notice that some folders state ‘access denied’. Everything that you normally execute runs in a minimal least-privilege level of access.

Elevated Access

The biggest concept of Linux is the concept of ‘root‘.

We’ve all seen this in Windows:

This prompt is so that you are running your normal commands without elevated privileges, so that any significant system change cannot be made without an additional layer of authorization. The ‘root’ user in Linux is essentially a full-access account, similar to ‘sa’ in SQL Server.

Issuing a command that requires elevated privileges usually ends with an error like this.

You start with the ability to issue a single command with ‘sudo command’, or super-user do this. You can execute one of these elevated commands as follows to execute this single command with elevated privileges.

Use sudo sparingly, just as you would with Windows and good security principals. If you need to run a series of commands as the elevated user, issue ‘sudo su –‘ to switch to the root user for the session. Type exit to exit this level of elevation and switch back to your normal user account.

You’ll want to explore the system logs at /var/log, as these are a significant source of information about how your operating system is running.

Use ‘cat file’ to view these files to the console, and ‘tail -f file’ to see the changes to these files as they occur.

Things you’ll want to do before you continue to installing SQL Server:

Patch and get the operating system up to date

Set a static IP address

Verify and set your hostname

Specify your DNS servers and DNS entry

Verify your security to ensure that the operating system is locked down. Your respective operating system distribution is sure to have hardening documentation for you to follow.

One tip – want to visually manage the system remotely and quickly? Install Webmin! Follow the directions specific to your operating system. Browse to your server with a web browser at https://servername:10000/ and log in with your credentials.

What?? Why??

Today’s Microsoft is a new Microsoft. Ballmer is not in charge. Satya Nadella is now CEO of Microsoft, and he’s letting the technology drive the company. And drive it is. They are listening to the world with an openness to non-Microsoft technologies in a way that I’ve never imagined.

It is helping me bring back some of my system administration roots. I was such a geek in grade school that I was compiling Slackware Linux kernels when I was in middle school. It wasn’t until high school that I started exploring Windows server administration. This announcement is bringing back some great memories while exploring the possibilities of this new platform for my DMBS of choice.

Microsoft has listened to the technical communities around the world, and the world wants cross-platform. The world wants choice.

And they’re making it happen.

First and foremost (IMHO), Microsoft wants to appeal to developers. They want their development stack to run anywhere there are developers. Notably, Microsoft just released Visual Studio 2017 for Mac on May 10th! Many developers out there run on non-Microsoft workstations, notably Apple computers. Apple’s OSX operating system is originally derived from the FreeBSD operating system. FreeBSD and other *BSD operating systems share much in common with Linux. So, if you can make SQL Server work on the Apple, you’ve probably made it work on Linux. Arguably, covering these two platforms nails just about every widely adopted development platform out there.

Microsoft also wants to appeal to a broader customer base, which means exploring the other environments that software runs on. An exceptionally high number of the world’s servers are powered by Linux. It’s lean, mean, stable, and powerful. Lots of shops refuse to run a Windows-based server because of a number of reasons, including that their in-house IT staff only have Linux knowledge. These same shops are most likely pressured to run a SQL Server for various applications. I know a number of third-party vended application that require a SQL Server, and previously if an organization dictated no Windows-based servers, that meant that this application would never be adopted in the organization, no matter how well it would function.

By appealing to these customer bases, they can now facilitate greater opportunities to migrate other DBMS workloads to SQL Server, and largely Oracle. Organizations can now migrate from these DBMS systems to SQL Server but keep the operating system the same (or at least similar). It’s another jab at Oracle. You can run Oracle on Windows. Now you can run SQL Server on Linux!

Another bonus benefit of having native Linux support is that it can now run in a container. Containers are in some ways the next generation of virtualization. Containers allow you to package an application and all its dependencies into a bundle that can be easily transported. You no longer have to worry about the operating system itself, and it makes your application a lot smaller and even more portable.

Finally, Linux is a fantastic cloud platform. The footprint of the Linux kernel and it’s runtime dependencies is absolutely tiny compared to other operating systems. The performance overhead is minimal, and the space consumed is small. For hosting environments or larger scale-out SQL Server farms, the resource consumption savings could be significant.

This list is in no way exhaustive, and I could ramble on with more ways that fit this list, but I don’t want to bore anyone.

The target audiences are quite diverse as well.

Software developers are a no-brainer with this. Small businesses trying to keep their software licensing costs to a minimum are sure to be attracted to this offering. New startup software businesses with Linux-oriented development platforms can leverage SQL Server. Enterprises and hosting providers can save on Microsoft Windows licensing.

As you can tell, I’m excited. You might not be running out to replace your servers today, but what this new release provides is monumental.

It provides choice.

I cannot possibly convey here how elated I am at this complete shift in Microsoft’s direction. I think (I hope!) this is not the last time we’ll see this platform expansion for other Microsoft products.

Stay tuned for more SQL Server on Linux topics, such as how they did it, operating system selection, supportability, command line basics, OS similarities and differences, SQL Server installation, and then moving on to more advanced topics such as how to configure multiple disks to spread out your SQL Server storage workload footprint and back up to the usual network share.

Starting this week, I’m introducing a new series of blog posts for the SQL Server DBA on how to properly set up and administer a production SQL Server on the Linux operating system.

As you know, Microsoft is shipping SQL Server 2017 (currently at CTP 2.0) with support for the Linux operating system. You might not think you’ll ever use this in your organization, but you might be quite surprised at the adoption rate I’m already experiencing in the wild. You should prepare yourself as a SQL Server administrator for some key similarities and differences with this new platform for tasks such as server build and construction, operating system basics, installation and patching, backups and recovery, availability, etc.

This blog post series is designed to help ramp you up on these tasks and answer common questions for scenarios that SQL Server DBAs will encounter while running SQL Server on Linux. Stay tuned!