Aug 082017
 

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!

Continue reading »

Jul 272017
 

In the last blog post, we added additional drives to the SQL Server machine so that we can scale out our storage workload. Let’s move the tempdb objects to one of these new drives!

By default, our tempdb files are in the system database folder at /var/opt/mssql/data.

We previously created a folder at /var/opt/mssql/data/tempdb01 for these files. Moving them is straightforward, once you know the file system structure. The following commands will move them to the new location, and I also add additional files to equal the four vCPUs I have on this SQL Server VM. The file growth is my model database’s default of 64MB for this instance. Do as you would normally do with SQL Server on Windows with tempdb file counts and separation of duties for your workload.

Restart the instance to make the new settings take effect.

You’ll notice you now have the properly configured tempdb data and log files in this location.

Also, just like in Windows, the old files are not removed upon relocation. Go clean those up.

That’s it!

In the next step we’ll set up this VM to be able to write backups across the network to a file share, just like we do in Windows! Stay tuned!

Jul 242017
 

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.

Continue reading »

 Posted by at 9:29 am  Tagged with:
Jul 052017
 

If you are actively managing VMware environments with workloads that have high performance needs (such as all of the virtualized SQL Servers that we work on), this new book called VMware vSphere 6.5 Host Resources Deep Dive, written by Frank Denneman and Niels Hagoort, is a must read book! Designed for VMware-oriented system architects, this book walks the reader through each layer of host resource allocation and management in a way that no other book has ever accomplished.

This book redefined the phrase “deep dive”, and I’m taking a lot of notes for future authoring and presentations. The depth of content is unparalleled in tech authoring.

Topic sections such as CPU, memory, storage, and networking, the four main areas of resource management on any virtualization host, are presented. Anyone can talk about these settings in general, but the advanced VCDX-level topics are covered in incredible depth. We work with a lot of advanced virtualized SQL Server VMs, and significant discussions from this book for performance tuning these SQL Servers include:

  • Advanced vNUMA balancing and optimization
  • CPU core counts versus clock speed
  • vNUMA memory speeds and non-local memory access
  • Clearing up misconceptions about vSphere Balanced Power Management
  • Queues and resource allocations

Go get this book – NOW! It’s a must read, and read it twice. No, three times. Then give the book to colleagues who can benefit. I’ve got two copies on my desk right now, and know who these will be sent to after I’m done re-reading them!

May 302017
 

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.

Red Hat Linux installation guide

CentOS Linux installation guide

SUSE Linux installation guide

Ubuntu Linux installation guide

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.

Poke around, install, configure, and explore your new operating system’s basics! (Admittedly, it’s not too exciting at the moment. You’ve got a console. Woohoo!)

Next stop – installing SQL Server on Linux!

 Posted by at 11:58 am  Tagged with:
May 082017
 

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!

 Posted by at 3:12 pm  Tagged with: