As many of us are familiar with, GDPR is approaching and we made some updates. In the past, file history stored entire T-SQL queries. However, if the query contained any secrets or passwords, it wasn’t smart enough to scrub those out. This is no longer the case, and now file history has been updated to no longer store secrets or passwords.
In addition, we have added 24-hour rotation of UserID when we collect telemetry.
Read on for other improvements.
To create the VMs, you need to go through these four steps:
Basics to configure basic setting of the VM
Size to choose the VM machine size
Settings to configure the features. In this case, the default values are used. You just need to click the Next button to proceed further
Once it’s running, Prashanth shows how to connect via PuTTY and configure the service.
So, I bit: the tweet he referenced was announcing a new version of the SQL Server module (21.0.17240). Here’s a quick list of the updates included:
- Added Get-SqlBackupHistory cmdlet
- Ported PS Provider to .NET Core for PowerShell 6 support
- Ported a subset of cmdlets to .NET Core for PowerShell 6 support
- Powershell 6 support on macOS and Linux in Preview.
- To use SqlServer provider on macOS and Linux mount it using a new PSDrive. Examples in documentation.
- Removed restriction of 64-bit OS for this module. Note: Invoke-Sqlcmd cmdlet is the only cmdlet not supported on 32-bit OS.
The bold lines are my emphasis: with PowerShell 6 support for Linux and macOS, that opens up new avenues for connecting to and automating SQL Server from any platform. This is exciting stuff. I couldn’t wait to take it for a spin, so I set up a quick demo environment to test it out.
It’s not perfect but it did give Drew the vapors, which is a good sign that they’re on the right track.
For some reason, I finished doing an “sudo apt update” follow by the “sudo apt upgrade“, and after doing some OS updates “my SQL Server stopped working”. The following message was logged in the log event: (see Image)
Click through to see how to force a rebuild of master.
If you have experience with PowerShell, some properties of Bash variables will feel familiar. In Bash, variables are denoted with a
$just like in PowerShell, but unlike PowerShell the
$is only needed when they are being referenced. When you are assigning a value to a variable, the
$is left off:
Above we assigned a value to
my_varwithout using the
$, but when we then referenced it in the
printfstatement, we had to use a
$. We also enclosed the variable name in curly braces. This is not required in all cases, but it is a good idea to get in the habit of using them. In cases where you are using positional parameters above 9 (we’ll talk about this later) or you are using a variable in the middle of a string the braces are required, but there is no harm in adding them every time you use a variable in a string.
The basic syntax is pretty familiar to most programming languages, and there’s nothing scary about outputs, even when Mark starts getting into streams.
A web search will almost certainly point you to lots of similar posts, mostly (if not) all of which start instructing you to add unofficial or unrecognized sources, keys etc. Therefore my intention with this post is not to replace official documentation, but to make the process as simple as possible, whilst still pointing to all the official documentation so that you can be confident you are not breaking security or other such things!
You can head over to the following Docker page Get Docker CE for Ubuntu for the initial setup and updates, but for simplicity, you can follow along below.
The installation instructions will also work for Ubuntu and other related variants.
Mark Wilkinson has started a new series on Bash. His first post is an introduction to the scripting language:
Bash (the Bourne Again Shell) was created in 1989 for the GNU Project as a free replacement for the Unix Bourne shell. Most modern Linux systems use Bash as their default command line shell, so if you have ever dropped to a command line on a Linux system, you have probably used Bash. Just like PowerShell, Bash is both a scripting language and a command shell/interpreter. So not only can you execute commands in an interactive shell session, but you can also write scripts that incorporate multiple commands.
Once you get your hands dirty with Bash you’ll notice a lot of features that were incorporated into PowerShell. Things like command substitution:
$(Get-Date)were directly pulled from Bash
$(date). Other features will look familiar as well, like the ability to pipe multiple commands together.
One thing you need to understand right away is that Bash is string based, not object based like PowerShell. This means you’ll find yourself doing a lot more string processing to get tasks done. Things like string splitting will be much more common. Bash does support objects, like arrays, but few if any commands output an array. As we go through this series you’ll see that this might not be as limiting as it sounds.
The best part about learning Bash is that you can then get into arguments about Bash vs ksh vs zsh.
Although enhancements have changed Windows installations for applications to run with a unique user, I created a mssql OS user even back on SQL Server 2000 on Windows as I had a tendency to use similar security practices for all database platforms as a multi-platform DBA. With that being said- yes, it introduced complexity, but it was for a reason: users should be restricted to the least amount of privileges required. To grant any application or database “God” powers on a host is akin to granting DBA to every user in the database, but at the host level. As important as security is to DBAs INSIDE the database, it should be just as important to us OUTSIDE of it on the host it resides on.
Security is important and has become more complex with the increase of security breaches and introduction of the cloud. One of the most simple ways to do this is to ensure that all application owners on a host are granted only the privileges they require. The application user should only utilize SUDO, stick bit, iptables, SUID, SGID and proper group creation/allocation if and when required.
It’s the same reason we don’t recommend giving everyone sa rights to databases. Read on for more.
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?
First, SSH into your VM. Get your appropriate system engineer to expand the drive that needs to be expanded. You won’t be able to see it at first in Linux because, just like in Windows, it’ll need to rescan the storage to ‘see’ the extra space. Sometimes Windows does it automatically, and sometimes you have to initiate it manually. In Linux it only does this on system startup.
Let’s grow our data drive from 250GB to 300GB first.
Click through to see how to do that.
The first thing I had to do was reproduce the issue. So on my Windows 10 test VM I installed the Windows Subsystem for Linux, steps to do so are here and I installed the Ubuntu app.
Then, I fired up a bash shell using WSL and then I installed SQL Server on Linux for Ubuntu as documented here.
Now, I completed the installation of SQL Server on Linux using mssql-conf when that program completes it attempts to start SQL Server on Linux. BOOM! I’m able to reproduce the same error.
Looking at the error, I decided to see if I could run SQL Server on Linux from the shell as the user mssql. This would remove systemd and mssql-conf from the picture. Basically I wanted to see if I could get another, more descriptive, error to pop out.
Anthony digs out a very useful debugging tool in Linux, strace. Sadly, he’s not able to solve the problem at the moment, but at least gets us a step in the right direction.