>> You're probably aware that there are some differences
between SQL Server on Linux versus Windows.
In this episode, learn from
David Klee on how to set yourself up for
success and less storage problems
from the get-go with SQL Server on Linux.
This week on Data Exposed.
[MUSIC].
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed MVP edition.
Today, we have a special guest with us, David Klee.
David, thanks so much for joining us today.
Can you tell us a little bit about what you do?
>> Absolutely. Thanks for having me.
My name is David Klee,
and I'm the founder of
Heraflux Technologies and SQLibrium Education.
We do performance tuning, HADR architectures,
and a lot of education around how everything around
SQL Server comes together in place nice most of the time,
but usually sometimes it doesn't.
>> Awesome. Well, we're excited to have
you on the show and today you're going to be
talking about something that I don't know a ton about,
so I'm really looking forward to learning from you.
The topic for today is SQL Server on Linux storage secrets.
Before we get into the secrets,
I want to learn a little bit more about
this scenario or scenarios that
you see that lead you to find these secrets.
>> Yes. I've been using Linux
for a long time and here's the challenge.
Let's, it's Tuesday afternoon,
everything is doing just fine,
all of a sudden somebody runs a big transaction,
fills up a drive underneath one of
your big primary production servers.
Well, we've got to add storage right now.
If you're on Windows, on-prem, on the Cloud, wherever,
just grow the drive,
expand the partition, and away you go.
On Linux, it's not so easy.
That's a really big challenge because now you can't grow the drive
and you're out of space and everything is
down and you got people standing behind you going,
"When is my business going to be back
up and running?" We need to fix that.
>> Got you. Is this a challenge that applies to
just SQL Server on Linux in
the on-prem world or does this also apply if you're running,
say, SQL Server in a VM in Azure or somewhere else?
>> SQL Server in a VM or physical bit on-prem,
Cloud, wherever, it directly applies everywhere.
>> Cool. This is the scenario,
seems like a challenge,
what's the secret sauce you're going to tell us about today?
>> The secret sauce is how to use
the Linux Logical Volume Manager to be
able to set this up ahead of time so that when you need to,
you can expand these disks appropriately
and seamlessly without an outage.
>> Awesome. Sounds pretty cool.
I'm not sure what it means,
but I'm looking forward to learning more about it.
Can you take us through
the steps and I think you also have a nice demo for us.
>> Absolutely. Before you go into production with this,
just add the storage as you normally would.
This next task is not the easiest.
We have to make sure that we can
set up our disks in a way where they
can be expanded and moved around after the fact.
By default, we don't get that with Linux.
We are going to sudo su as roots so that we ever
command is running essentially
with administrative permissions here.
We're going to list all of our block devices.
You can see we have our four discs connected to the SQL Server.
We're going to do a Logical Volume Manager disk scan
so we can see the individual disks.
We're going to create three different disk pulls.
Now, we're going to create
a Logical Volume Manager volume group called TempDB on disc B.
We're going to do the same thing for data,
and then we're going to do the same thing for logs.
Now, we're going to create a logical volume on
here with the Logical Volume Manager.
We're going to use all free space on
each of these Logical Volume Manager groups.
Let's list our block devices, again,
you'll notice now for each of the disks,
we have a logical volume number under each one.
If you lists /dev, which is devices,
you can actually see these items under there in blue on the right.
Now, we're going to format these with make filesystem XFS,
and we're going to format each one of those
with the XFS file system.
We'll do data, then log, then TempDB.
Now, we're going to go into the SQL Server root folder,
and we're going to make folders here
that correspond to each of those volume groups.
Data 01, Log 01, TempDB 01.
We're going to change our owner with
a recursive parameter to mssql.mssql,
that's our user and our group,
and we're going to do that for each of these three folders.
Linux is really strongly secured.
This makes sure that our service account,
the SQL Server Service,
can actually access these folders.
Now, we're going to edit our file system table or etc/fstab.
We're going to add three entries under here for each one.
You can see they simply maps the folder and
mount those logical volumes as XFS defaults.
We're going to mount each one of these because we
have the entries in the fstab table.
At this point, instead of, E, F, G,
we now have mount points under the SQL Server
mounted folder corresponding to data, log, and TempDB.
That's how we use these extra disks in Linux.
Now, what happens if we need to expand these?
This is the magic.
We have our three data,
log, and TempDB disks.
We're going to double the size from 100-200,
and then 160 and then 120 gigs respectively.
With Windows, it's easy,
click "Expand" you're done.
With Linux, not quite so easy,
but it is straight forward.
We're going to log back in and we're
going to go back to our superuser,
look at disk free for each one of these things.
We're going to go back and we're going to
list our block devices again.
You can see there 100 gigs,
80 gigs, 60 gigs.
It doesn't know how to do a scan to find the free space.
But this command here will take care of that for you.
We're going to scan each one of these drives specifically.
Now, we're going to pvdisplay and we're going to look to see here,
can we see our free space?
You'll notice right now,
it has scanned and has seen it,
but now we need to expand our volumes to fill this space.
Now, pvresize, and then simply resize each disk, boom.
It's scanned and it's now resized.
If you take a good look,
it now is fully aware of the extra space that we just added.
No downtime, no impact, no problem.
Now, we're going to extend the logical volume
to fill the physical disk space extension.
For our data, we're going to add that 100 gigs that we added.
Then we're going to do the same thing for our logs and then
TempDB and that was 80 gigs and then 60 gigs.
Now, let's list our block devices again,
and we're going to see our free space.
Not too shabby.
That is how you can expand disks in Linux and
have exactly the same functionality that you do in Windows.
Not the easiest to set up,
but it is straight forward,
especially if you follow the directions here.
We need to change the default locations for database.
Data files and log files are
automatically created if the location is not specified.
Normally, we could do this by
going into the instance level properties,
go to database settings,
and then specify our data and log default locations.
But on Linux, we can't do that.
We have to go to the command line.
SSH into the server,
now we are logged in as root,
let's go to opt/msql/bin and then run mssql-conf,
set filelocation.defaultdatadir,
and then specified that new disk
and that mount point that we just added.
Go ahead and do the same thing for our logs.
Once you've applied these, you can
literally copy and paste the command
right there to restart SQL Server. That's it.
Nice and easy.
You'll notice now here in the instance level database settings,
our locations have changed.
One last thing to worry about is that we need to go ahead and
relocate our TempDB database data and log files.
You'll notice here's the command.
It looks exactly to the regular syntax,
except for our filenames show
our paths as var/opt/ mssql/data/tempDB 01,
one of those new mount points we just created.
If you run this, it works just like in Windows.
Go ahead and restart SQL Server to let this take effect.
Then just like in Windows,
it's moved it, but it hasn't cleaned up after itself.
If you look at the TempDB database properties,
you'll see under files here that it
has successfully moved everything.
Var, opt, mssql, data, tempdb,
that is our new physical virtual list.
There's a files on the file system.
Now, if you go back one,
you'll notice it didn't clean up after itself.
We're just going to go ahead and remove all these.
We can put a question mark in there and do
each file individually since they're sequentially numbered.
Now, tempdb.mdf and templog.lts,
and we have now cleaned up and successfully relocated our TempDB.
>> Wow, David, that was a really interesting demo.
I think I learned a lot,
but I still have a few questions.
Once you go through this process,
do you have to go through
all the steps again or is it
just the last few steps you have to complete?
>> Just the last couple of steps.
Everything else is literally setting the stage so that you
can do this in the future when and if you need to.
>> Got you. If you are a SQL Server on
Linux user or customer or using it in any capacity,
is this something you recommend that
everyone sets up or is it case-by-case?
>> Quite frankly, I say,
if you're SQL Server on Linux in general anywhere,
just do this upfront because you never know when you're
going to need to add space and it's never proactive.
It's literally middle of the night,
everything's down or a boss is standing
behind you with a baseball bat saying, "Fix this now."
You need this done
well in advance so that you just hit a couple buttons.
Problem is solved. No big deal.
>> Awesome. Cool. Well, this is definitely some cool secrets.
For our viewers who are
going to go through this and get started with this,
do you have any tips or tricks or
gotchas that they should look out for?
>> Bottom line, read the funny manual.
I use the acronyms carefully there.
Just read the manual, Linux is not a crazy black box,
it's not mystical being.
It is very formulaic, it's very logical.
It's actually a little more logical
than Windows in a lot of cases.
But you just have to understand how to do
a lot of these things because it is different than Windows.
We've been doing Windows for 25-30 years,
Linux is different, but you can do it.
I know you can, you just got to understand where to click and
what to type for just the normal tasks that we do.
Once you get the foundation set,
it's just business as usual.
>> Awesome. Well, thanks so much, David.
I think this will be really useful for our viewers.
I definitely learned a lot.
For our viewers, we are going to put
a few links in the description for
you to go through this yourself or read more,
or you can even leave a comment and let us know what you think.
David, again, thanks so much for joining us on the show.
To our viewers, please like, subscribe,
and let us know what you think and we hope to see you
next time on Data Exposed.
>> Awesome. Thank you everybody.
[MUSIC]