SQL Server on Linux Series: Operating System Differences
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
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!
This post was originally posted at DavidKlee.net and syndicated here.
Upgrading to Modern SQL Server - eBook with ScaleArc
Recently David Klee contributed his thoughts in a new ebook from ScaleArc called “Industry Perspectives: Upgrading to Modern SQL“. Modernizing the datacenter is an ongoing struggle for those of us in the operational trenches, and a number of us in the industry contributed our thoughts to ScaleArc to help administrators sift through the options and learn how to make these upgrades easier. It’s a great read, and learning from the great contributors is fun! Check it out!
David Klee announced as speaker for PASS Summit 2017
Heraflux is proud to announce that David Klee has been selected as a speaker for this year’s PASS Summit conference, to be held in Seattle Washington from October 31 to November 3rd.
The specific sessions that will be presented are not announced, but we are excited and cannot wait until we can share more details. Stay tunes for more details here and at the PASS Summit 2017 site!
Also, stay in town until at least Saturday morning, as we are planning an event for Friday evening after the conference wraps up.
More details to be announced here as soon as they are made public!
In case you are not familiar with the PASS Summit, it’s the best Microsoft-ecosystem data-themed conference in the world. More details from last year’s Summit are found here.
SQL Saturday Atlanta Precon - The Complete Primer to SQL Server Virtualization & Cloud
If you are attending this year’s SQL Saturday in Atlanta, GA, on July 15, I urge you to attend my preconference training session – “The Complete Primer to SQL Server Virtualization & Cloud“. The architecture of the infrastructure underneath your mission-critical SQL Servers is vital to the well-being of your databases, and if any little component underneath is not running at its peak potential, your SQL Servers are artificially bottlenecked and your performance suffers. For any DBA out there who claims the infrastructure underneath the data, no matter if it’s in the cloud or your own datacenter, is someone else’s job, this session is going to show you just how important a working knowledge of these layers is to your livelihood.
Session Details
Microsoft MVP and VMware vExpert David Klee leads this full-day introduction to enabling and managing a virtualized SQL Server environment. The focus of the course is to help those new to virtualization and/or infrastructure concepts to become familiar with the concept and purposes of virtualization and how it can benefit them as data professionals. Participants will gain exposure to all layers of virtualization underneath SQL Server, from storage to hypervisor to the SQL Server instance, and will learn how to construct the entire stack with a strong emphasis on SQL Server performance.
Intended Audience. The intended audience of this course is information workers (both business and IT-centric) involved with architecting a virtualization strategy for SQL Server, or managing business-critical SQL Servers that have already been virtualized.
Course Topics
The following topics will be addressed during the course:
- The Physical Infrastructure Underneath the VM
- Virtualization and Infrastructure Fundamentals
- The SQL Server Virtual Machine
- Networking, Support, and Licensing
- Performance Investigation and Tuning of the Entire Stack
- Similarities and Differences with Cloud versus On-Prem
Course Objectives
Upon completion of this course, attendees will be able to:
- Understand key virtualization and cloud concepts
- Determine the optimal infrastructure configuration for best performance
- Construct a SQL Server virtual machine template with best practices for agility and performance
- Understand how and what stack metrics to benchmark and baseline to ensure proper objective performance measurement
- Identify and remediate common virtualized SQL Server performance killers
- Understand proper virtualized capacity management techniques for long term management
Prerequisites
Attendees with the following experiences will benefit the most from this course:
- Basic familiarity with the core components of a server
- SQL Server installation and configuration
- Exposure to SQL Server performance metric measurement and identification
A working set of the screens and configuration settings referenced in the session, together with the reference slides and documentation, will be provided to attendees, for strategic reference in working with virtualized SQL Server in your own environments.
I look forward to seeing you all there! Registration for this event is located at EventBrite.
SQL Server on VMware Workshop at VMworld US 2017
Heraflux is extremely proud to announce that we’re holding the second SQL Server workshop at this year’s VMworld US in Last Vegas on August 27th. If your SQL Servers are running in a VMware environment, this course is critical to your success!
This business critical applications and databases pre-conference workshop will cover all pertinent aspects of best practices for deployments and ongoing management of MS SQL Server running in virtualized infrastructure. This deep-dive full-day workshop will be delivered by VMware technical SQL Server specialists working in concert with world renowned external SQL Server and Virtualization experts, including David Klee from Heraflux! The attendee will experience a workshop rich with technical content to include subjects such as SQL Server availability, performance tuning, disaster recovery, and SQL Server VM construction. The content will be focused on best practices for design, implementation and management augmented with anecdotes of successful customer implementations.
SQL Server professionals, listen up. If your infrastructure admins are attending VMworld this year, please let them know about this course. Training them in the intricacies of the VMware platform as it relates to SQL Server, and having the training come from SQL Server professionals, is going to make your virtualization experience better.
Register for VMworld here! This event is going to sell out, so if you or anyone from your organization are going, register soon!
24 Hours of PASS: Data Security and Quality Wrapup
Thank you to all who attended my session on “Passive Security for Hostile Environments” back on the 3rd of this month. I consider it an honor to be part of such a wonderful lineup. I just received my evaluations and comments, and am very happy to report that the results were extremely positive. Thank you very much to the people who took the time to rate my presentation and offer feedback, which I will include at the end of this post.
I was also very surprised to hear that my session had 193 attendees, which puts it in the top five in terms of attendance – wow!
If you weren’t able to attend but would like to check it out, a recording is now available.
My slide deck is available for download on the 24 Hours of PASS site.
Demo scripts and other resources are available here.
Feedback
Sessions were evaluated based on four questions, and I received 78 total responses.
- How would you rate this session overall?
Excellent: 60 Good: 18 Average: 0 Fair: 0 Poor: 0 - How would you rate the speakers’ presentation skills?
Excellent: 64 Good: 13 Average: 0 Fair: 0 Poor: 0 - How would you rate the speakers’ knowledge of the subject?
Excellent: 71 Good: 6 Average: 0 Fair: 0 Poor: 0 - Did you learn what you expected to learn from this session?
Agree: 67 Somewhat Agree: 7 Neutral: 3 Somewhat Disagree: 0 Disagree: 0
I also received the following comments:
- excellent demos and real deep dive into the details of each area he covered.
- Your demo scenarios were very effective in showing the strengths and weaknesses of each option. Well done.
- great use of demos!
- Thank you!
- Great presentation. Good demos – be great to get a copy of the scripts.
- I had a lot of familiarity with DDL/DML triggers and Event Notifications. There were some new aspects you showed that I had not considered. Impersonation, for instance. Policy based management is something I haven’t used, but have read about. The session helped reinforce what I’ve learned in the past. Slide decks are great. But I prefer live demos and the code. You had a good, complementary mix of both.
- Wow, this was incredibly good! So well organized. You covered a lot of territory.
- Great overview of the different tech
- Very clear explanations and demos, great pace for a webinar. Packed full of useful examples for real projects. Thank you!
This post is syndicated from BobPusateri.com.
SQL Server on Linux Series: Why?
As you all have heard, Microsoft is supporting Linux as a supported operating system for the next version of SQL Server (recently announced to be version 2017).
What?? Why??
Didn’t Steve Ballmer call Linux a “cancer” a while back?
Times change. Companies change. Technologies change. It’s how I named my company.
(People rarely change, but that’s a different story…)
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.
Microsoft is so committed to this strategy that they are now a Platinum member of the Linux Foundation. Microsoft has released the open source .NET Core. Ubuntu now runs on Windows. HDInsight was recently released with Linux support. R Server can run on Linux. PowerShell is open sourced and on Linux!
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.
This post originally appeared at DavidKlee.net.
New Series – SQL Server on Linux Administration
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!
(Blog post originally posted at DavidKlee.net)
SQL Nexus Wrap Up
This past week, David Klee presented a preconference session and regular session at this year’s SQL Nexus conference in Copenhagen, Denmark.
The preconference session called “The Complete Primer to SQL Server Virtualization“ was well received! The attendees were well prepared for deep discussions on SQL Server virtualization, infrastructure, and cloud XaaS topics, and the questions were wonderful.
The SQL Server on Linux session was also solid and well attended, as David discussed common administration tasks around tasks that are necessary when making the transition to SQL Server on Linux, such as backing up databases and logs to a file share, and how to configure and mount additional disks for objects such as tempdb, database data, and database log files.
The slides for the SQL Server on Linux session are available for you to download here.
SQL Nexus, thank you for a great time! This is a great conference for folks in the region to attend. We really enjoyed our discussions and deep-dives with attendees and other speakers, and would be very happy to return next year to this conference!
24 Hours of PASS Webinar Wrapup
Our very own Microsoft Certified Master Bob Pusateri presented last week for this year’s 24 Hours of PASS Data Security and Quality webinar series a new session entitled “Passive Security for Hostile Environments“.
Track: Enterprise Database Administration & Deployment
Ideal database security settings usually exist in books, but rarely in reality. Is your CIO a member of the sysadmin role because they demanded it? Or maybe some users have rights for purely political reasons? Just because you can’t enforce security through typical means doesn’t mean you’re powerless. Attend this session to learn about the features SQL Server provides that will allow you to keep track of what your users are up to at all times and sleep a little easier. Through various scenarios and demos, see how technologies such as event notifications, auditing, and extended events can help ensure nothing happens on your system without you knowing about it. Even in optimally secured environments these techniques can still come in handy. The best security is often that which cannot be seen.
The slides have been uploaded to the PASS site here. The recordings should be online soon at PASS’s learning center site.