Before we start though, there’s a few things you’re going to need to have already set up:
An Active Directory Domain to test in, and rights to administer it. Since we’re going to be creating (and possibly deleting, if there are errors) computer objects and a service account, you’ll need a domain account with adequate permissions.
My example assumes you have a Microsoft DNS server running alongside your domain services. It is possible to use a separate DNS server to get this to work, but you might need some additional network configuration (see below). Also, depending on your environment, you might need a reverse lookup zone defined. If you notice long ping times or other weird lookups, I’d set one up in your DNS.
A machine (virtual or otherwise) that is running CentOS 7 or later (and this guide was written and tested against CentOS 7). For this demo, we’ll be using the Server (minimal install) installation option. If you’re new to Linux, you might opt a desktop version (server with a GUI). When you download a CentOS disk image to install it, you get all these options on the default media; you won’t need separate downloads
There are a few more prereqs, so read the whole thing. This route is easier than Ubuntu, as Drew notes.
SQL 2017 bits are generally available to customers today. One of the most notable milestones in the 2017 release is SQL Server on Linux. Setup has been relatively simple for SQL Server on Linux, but often there are questions around unattended install. For SQL Server on Linux, there are several capabilities that are useful in unattended install scenarios:
You can specify environment variables prior to the install that are picked up by the install process, to enable customization of SQL Server settings such as TCP port, Data/Log directories, etc.
You can pass command line options to Setup.
You can create a script that installs SQL Server and then customizes parameters post-install with the mssql-conf
The sample script link seems like it’s broken, but you can see it all on Denzil’s Github repo.
I have been using SQL Server 2017 running on Linux for a while now (blog post pending) and use the official images from:
To get the latest I used to run
docker pull microsoft/mssql-server-linux:latest
However today I noticed that the :latest tag had been removed:
Click through to see the tag you probably want to use.
Use the cmdlet Get-Variable to find them, and keep in mind, these variables are not found in Windows PowerShell 5.x.
Although, the results will display four variable, but let’s pay attention to three of them. Below are the variables with their default values:
These three variables can help in identifying which Operating System the script are been executed. This way just adding the necessary logic, in order to take the correct action.
Read on for a code example showing how to use these variables.
When running demos and experimenting with containers I always clear down my environment. It’s good practice to leave a clean environment once you’ve finished working.
To do this I blow all my containers away, usually by running the docker stop command.
But there’s a quicker way to stop containers, the docker kill command.
Sending SIGTERM isn’t particularly polite and doesn’t let processes clean up, which could leave your process in an undesirable state during future runs. But if you’re just re-deploying a container, you don’t really care about the prior state of the now-disposed container.
With strace up and running let’s turn on the trace flags to enable output for Instant File Initialization and create database that has a 100MB data file and a 100MB log file. Check out this post from Microsoft for more details on the trace flags. This database create code is straight from their post. I changed the model database’s data and log file sizes to 100MB each. Also, it’s important to note Instance File Initialization is only for data files, log files are zeroed out due to requirements for crash recovery. We’re going to see that in action in a bit…
Read the whole thing.
In addition to business matters, Hal’s article highlights key drawbacks which need to be addressed around engineering as well as product supportability for the endeavor to be successful. The article affirms that bringing SQL Server to *nix platform is not the hardest task compare to the additional work that the journey would require. Indeed, in order for the project to be successful the team would have to:
- Bring other SQL Server inbox products such as SSAS along
- Implement platform specific features such as CLR, AGs, and much more
- Guarantee adequate performance and scalability
- Create new ecosystem around product support, engineering systems and more.
So throughout the course of the work, I have continued to question the path we have been on and if we succeed at the end or not. Every time I would go back, reread the article and every time I would come to the same favorable conclusion. But why?
Slava links to this article from December from the data platform team explaining what SQLPAL is. In the end, I think the benefits of this model will be much larger than SQL on Linux (which is itself large).
Microsoft have stated that transactional replication isn’t supported on Linux and we’re not sure if they intend to in the future. This means that if you try to add the server to a publisher, you get the following message. So you can’t use the GUI and it also means that you can’t use pull as the necessary files won’t be there.
Despite that warning, there is a way to set up a push subscription; click through for that way.
In the following post, we’ll walk through joining a Linux SQL Server on Unbuntu to an Active Directory domain, and here’s the steps we’re going to take:
- Installing the required software and services to enable a Linux host to talk to and join an Active Directory Domain,
- Configuring the Linux host’s network configuration to talk to the Domain Controller(s),
- Setting up Samba, Kerberos, Winbind, and the System Security Services Daemon (SSSD) to properly talk to and digest authentication tokens from Active Directory, and
- Creating a Kerberos Keytab file for the SQL Server service to run as a domain service account.
Seems like a lot, doesn’t it? If you’re new to Linux, a lot of this configuration can seem a little daunting and a lot tedious, but as we walk through it, I’ll stop and talk a little bit about each step and what it does.
Active Directory integration was a critical piece of functionality for SQL Server on Linux. There are still some odd edge cases (like weirdness when going cross-domain) but for the normal scenario, it works fine once you’ve configured Linux correctly.
SQL Server 2017 runs on Linux, and the similarities between that and Windows Core are eerie:
- Both present big stumbling blocks for traditional Windows DBAs
- Both work mostly the same, but not exactly, as you can see in the SQL Server on Linux release notes
- Both solved perceived problems for sysadmins
- Neither solved a problem for database administrators
So why will you hear so much more about Linux support? Because this time around, it also solves a sales problem for Microsoft. Somebody, somewhere, has a spreadsheet showing that there will be a return on investment if they spend the development, marketing, and support resources necessary. (And I bet they’re right – if you compare this feature’s ROI against, say Hekaton or Polybase, surely Linux is going to produce a lot more new licenses sold.)
He does make some good points (though seriously, Polybase is awesome), but I think SQL Server on Linux is going to be quite a bit more popular for a couple of reasons. First is core-based licensing in Windows Server: that’s another big price increase that you get when upgrading to Server 2016, and at the margin, companies with a mixed OS setup will be more likely to move to Linux. Second, Brent’s focus in the post is on current installations—that is, taking your Windows SQL Server instance and moving it to Linux. As Koen Verbeeck mentions in the first comment, there’s a whole different market: companies whose infrastructure is entirely Linux and are currently using MySQL, Oracle, or Postgres for their relational databases. It’ll probably take a couple of years to get market penetration—especially because of the old guard Linux admin types who remember the Ballmer years with appropriate disdain—but this is a new market for Microsoft and they’ve already got a product which meets (or exceeds, depending upon your biases) the top competition.