Up next in 10
SQL Server Configuration with dbatools & dbachecks by Tracy Boggiano | SQL Server Virtual Conference
Oct 30, 2023
Automating the configuration of SQL Server instances and being able to check the configuration for standard configuration is an important role for any DBA when you have lots of instances. But how can we automate the configuration easily? How can we check to make sure the proper configuration is in place and hasn’t changed? In this session, we will look at how to configure your SQL Server Instances using PowerShell and the dbatools module. Then we will look at how to use dbachecks to confirm the configuration of our instances and how to check for changes in the future of the configuration. Attendees will see how easy it is to use PowerShell via dbatools to configure their SQL Server instances. Attendees will see how easy it is to use PowerShell via dbachecks and PowerBI to see if their servers are configured properly. Attendees will be given a set of scripts that take back to use to configure and test the configuration of their SQL Server instances. About Speaker: Tracy is the Database Superhero for DocuSign. She has spent over 20 years in IT and has used SQL Server since 1999. Tracy covers all aspects of administration and deals heavily with performance tuning and high availability and disaster recovery. Tracy is a co-organizer of her local user group, TriPass, in Raleigh, NC. Before she worked full-time as a DBA she was formally a developer and network administrator. She also tinkered with databases in middle/high school to keep her sports card collection organized. Conference Website: https://www.2020twenty.net/sql-server-virtual-conference/ C# Corner - Community of Software and Data Developers https://www.c-sharpcorner.com #SQL #dbatools #dbachecks #conference #sqlserver
View Video Transcript
0:00
Thank you
0:29
Good afternoon
0:40
I'm going to be talking about SQL Server configuration with DBA Tools, DBA Checks
0:45
I'm Tracy Bozziano. A little bit about myself. I work at DocuSign
0:49
I have over 20 years experience with SQL Server. There goes my contact information
0:54
The easiest way to find me is just go to databasesuperhero.com. My email address is available there along with my Twitter, LinkedIn, and all the other various ways you can contact me
1:06
I have a whole lot of icons on here. Some of them need to be removed because PASS disappeared on us
1:11
So I will talk about my volunteer work, which I normally do in the top right-hand corner
1:17
I'm a guardian ad litem in the state of North Carolina, which means I advocate for foster children in the court system
1:23
So I've done that for 18 years and for 56 kids at this point
1:28
And down in the bottom right-hand corner, I am on a mental health council for Disability Rights of North Carolina, trying to set the course for what type of things we advocate for in the state of North Carolina
1:40
And I'm part of SpeakingMentors.com so that we can try to get more speakers into the community
1:45
So those are the things that I like to broadcast. All the rest is just fluff
1:51
So we'll talk about installing DBA tools and DBA checks. just so you know, it runs on Pester, but Pester 5 breaks DBA checks, so don't install it by using
2:03
the defaults. The first thing you're going to want to do is install Pester with the correct version
2:08
so this is the correct syntax for that so that you don't end up with Pester 5. If you go to
2:13
install the module for DBA checks, you will automatically end up with Pester 5, and then
2:18
you will be in the SQL community Slack asking what happened. Then you can install the module
2:24
for DBA Checks, which will automatically download DBA Tools because it's dependent on it, and
2:29
then you'll be off and running and ready to use it. So that's pretty simple
2:35
So we'll talk about what DBA Tools and DBA Checks are. DBA Tools is an open source PowerShell module made, started by SQL Community over in Europe
2:45
but there's like over 100 contributors to it. It has like over 500 commandlets at this point
2:49
and it's made to do just about everything you can do in script and SSMS
2:57
Of course, there's some things that still haven't been added to it yet, and as you come up with those, you can go to GitHub and put in issues
3:03
or contribute to it yourself so that we can eventually have everything scripted out
3:07
and have PowerShell do everything for us. To illustrate some of the commands that there are
3:14
you can do a git command on the module, and this is the full command here
3:19
and where you can get a list of the commands. And as you can see, there's a bunch of different commands
3:24
for like adding things to an AG, backing up things, clearing out your latch statistics, your weight statistics
3:32
copying a number of things between servers. Just you got a slew of things that you can actually do here
3:40
So it's a very worthwhile project to get involved with and use because you can automate a lot
3:45
of your processes using it. So what does this have to do with DBHX
3:50
Well, DBHX is basically the same thing as far as it's developed by the SQL community
3:56
But this actually checks your configuration on your SQL server for your default configuration
4:01
which you can customize with some parameters and make sure that all your servers across your whole environment
4:07
are configured the correct way, and you can run audits on it and make sure things are running correctly
4:12
and nobody's changed like your max memory to something you don't want or your max doc
4:17
never know, you might have some system out there where somebody accidentally ended up as a sysadmin
4:22
and changed things on you or it's just not following a correct policy
4:26
I've programmed the CIS security compliance stuff into DBA checks. So if you have to be compliant on security for that, that's available for there too
4:37
So you can make sure you're compliant on there. And we're actually working on a module for the government in America for DISA so that we can check those configurations as well
4:51
But it provides output to a Power BI by default, which is the easiest way to visualize it
4:56
But you can also get it to a table or to a JSON file so you can get to a table so you can query easier or to a JSON file so you can import it somewhere else that you find beneficial
5:06
And it all uses DBA tools functions to retrieve values to be tested behind the scenes if there is one available
5:13
If not, it's got T-SQL probably encoded in it or it's using some sort of PowerShell that if it's a Windows-related thing that it needs to check
5:23
And as I mentioned before, it uses the testing framework for Pester so that it can actually do the testing part of it
5:31
And it's a nice, easy framework to learn. So if you're looking for a project to contribute to, DBA tools and DBA checks is a good one to consider contributing to
5:42
So DBA checks itself has very few commands because it's just checking things for you
5:48
Everything's behind the scenes, but they're very customizable. So this is all it has
5:52
So we're going to go over the invoke DBC check and a few on setting the DBC config values so that we can actually customize our environment for us
6:06
And as you can see, when we do a get DBC check, these are all the different things you can actually check for and then some
6:13
So there's a lot of SQL agent checks, database instance checks. So we're checking on the agent to make sure your database mail is enabled or that your service account is enabled
6:25
You've got a database operator, whether you've got failed job things running, whether it's got a valid job owner, whether jobs have failed, whether jobs have been running long, things like that
6:36
And there's a slew of tests that can run on here. And it's just nice to have
6:41
like we didn't put suspect pages in here, so now you can see if a page ever became corrupt on your system
6:46
and you just didn't know about it. And so if you run these things, you can keep up to date on your environment
6:52
and know what's going on, as well as making sure the config is correct
6:57
This is a command to run to pass it all through to Power BI. And then once you do that, you just hit refresh
7:04
because by default it's showing data that's stored in Power BI, because that's what Power BI does
7:09
And then you'll have your report customized as to what you have sent in
7:14
So this is what the default report looked like when I first started this presentation
7:19
I have it up on my VM right now to see where we're at. It has 126 failed things
7:26
We're going to see how many failed things we have now. We're going to try to fix as many of them we can
7:30
and the amount of time we have for this presentation. So now we're on to demos for the rest of the presentation
7:41
So we're down to 125 failed things, 126, so that's good
7:51
As you can see, our top one is all about agent alerts. If you click on these, click on it, it'll actually narrow down the results down here
7:59
at the bottom and tell you which things it's missing. So let's mention agent alerts for the severities
8:06
And there'll be several other things like database mail in there and things like that that we need to add on to agent alerts that are notified down towards the bottom
8:14
and not showing up here at the top. So we're going to go in and do some of these basic things
8:19
after I show you a couple of basic things about using PowerShell. So I've already ran my update modules on here
8:25
so we got the newest and greatest version. This is one way to actually find commands
8:30
This is a module built into DBA tools, so you can actually go in and find one of their commands
8:35
because they have so many of them. It can be kind of hard to find
8:40
Where did my grid go? So you can actually come in here and then you can use the criteria on the grid view
8:49
and look for something that has a keyword in it and try to find a command that does something And as you can see there a number of commands that you can actually use oh great my mouse is disappearing okay it back then you can do the same thing and tag it
9:06
for agent so if they have a tag for agent it'll come up with an agent due to time we will skip
9:12
running that but we've got our checks also for uh dba checks as you can see there's a number of
9:22
these that run on the system. And again, you can go in here and search by adding criteria
9:28
and looking for keywords. So it gives you an idea of how to go and find things. Then
9:39
you've got basic commands in PowerShell where if you need to look up what a command actually
9:43
does, you can just do a get help. Some of the stuff you may know just the basics of
9:49
But I wanted to cover it because it's kind of gets you started and tells you how to find examples of how things going
9:54
So if you don't know how to use something in PowerShell, you can use a get help and it give you the basics of what's how to use this
10:01
And maybe some information on where to go to examples on how to do things as well in the descriptions
10:07
And then you can get more detailed. Like if you only want to see examples or you want to get a detailed description or you want to see the full help that they put in there, you can do that as well
10:16
And you can tell it to pull from online just in case you don't have the newest documentation on your system
10:22
So that's the basics of how to go about PowerShell and use DBA tools and DBA checks to kind of check things out before you actually get started
10:31
So now let's go in and try to fix up our SQL agent alerts
10:35
So first I'm going to clear the host so that the bottom part is kind of empty for us
10:40
I'm going to set our instance name to our instance name, which is just dbaTools2
10:47
The first thing we need to do is set up a mail account. So I've set a number of variables here that are going to be our defaults
10:55
Nothing major going on here. Then I'm going to set our config where these values up here are going to be our defaults that it checks against
11:04
So if you have 100 instances, you're going to always want your DBmail account to be named DBmail
11:13
You're always going to want your email address to be this and so forth. And then we're actually going to take a look and see that those are set
11:22
As you can see, we've got your mail right here and DBmail above it
11:27
and our operator names and stuff is going to be set later. Then we're going to pull in a connection to the server because we're going to need this to do some SMO because everything's available in DBA tools for some of this
11:44
But the first thing we're going to want to do is enable our database mail if it's disabled
11:50
So we'll go ahead and do that. And we can see here that the previous value was zero and the new value is one
11:58
so it was indeed disabled by default. Then we're going to actually add a database mail account
12:06
and we know it doesn't have a database mail account because I just installed SQL this morning
12:13
Then we can see that it created us a database mail account on the SQL server
12:19
Then we actually need a database mail profile if there's a new one, and we need to set the mail profile
12:25
to the default on the system. So this is the part where I had to use a little bit of SMO to get everything set correctly
12:36
But as you can tell, DBA tools went in and it created everything for you
12:41
Then we're going to set as an operator. This is actually a new command link that I wrote because of this presentation so that we could actually create an operator
12:50
so now we got an operator created that's going to get paged you know every day and get alerted on
13:00
their emails and things like that then we're actually going to set it to be a failsafe
13:05
operator so that if we don't have another operator it will be the one that gets notified
13:10
now so far they they've got a get dba agent alert and a copy i'm going to work on one where you're
13:19
going to add a new DBA agent alert. So we're going to actually use SMO to add alerts at this point
13:27
But this 16.325 will tell it to add all the severity 16
13:31
through 25. And we're just going to name them severity alert 16 through 25
13:36
and add them. And then we're going to add these severity numbers here
13:40
for corruption type errors. And it's just a for each loop on both of those
13:49
Then we shall run our Power BI report and see if we get our numbers down from what was failing
14:00
When that Power BI report comes up, we'll look at SQL agent and see that we do indeed
14:08
have alerts. As you can tell, we have alerts now. We do have an operator
14:21
Those type of things work for us. And we'll be able to tell by our Power BI report, too, once it looks
14:27
I was supposed to close that so it would actually work. Good thing it hasn't gotten to the part where it opens
14:38
This is normally the part where I take questions, but I don't know if this is interactive or not
14:47
I shall look in the comments and see if there's anything in there
14:57
And there is not. So if there's any questions, feel free to throw it in the comments at any time
15:02
and I'll be checking them every time I run the Power BI report
15:11
This takes a minute or two to run so that we can actually see results
15:16
Let's go check our results as we go along. Our next steps are going to be we're going to work on all the Halligrand scripts and installing those because those are going to show up as our next biggest thing
15:41
And we'll go through that in a minute after we look at our Power BI report
15:46
And you can run this on more than one server at a time
15:59
You can actually run it with different names like prod and test so that you can actually separate out your different environments so that you can see differences because sometimes
16:07
test isn't configured the same as prod and so forth like that
16:12
So there's a number of things that you can do to customize this. I'm just doing the basics of running against one server to get everybody familiar with it
16:30
Rookie on it. Yay. Come on, Power BI. You can do it
16:35
okay we're down to 86 failed from I think it was 126 so we're doing better so now we're going to
16:46
look at fixing up all this all the hologram stuff because that's like four for each one of these
16:50
that's uh failing so let's let's try to get each one of those fixed up now remember to close power
16:56
RBI this time. I'm going to set a couple of things, just the instance name, where I want my backup to go
17:11
and how often I want my backups to be cleaned up so I can feed it into each one of these jobs
17:16
And then I'm going to email what my email operator is going to be because we set that up in the first page
17:22
Now, one thing you can do, which is kind of neat, is you can install the solution right off the internet
17:32
With the appropriate variables that you need, log in a table, install jobs, get everything in it
17:37
We're going to place the existing one, so within seconds, you actually have the newest, greatest version of all of Helogram scripts installed
17:44
So now we're going to take a look at these jobs right here
17:49
Our system backups and our user backups are going to run Sunday at midnight and that going to be the name of our schedule And for each one of those we just going to add a schedule to it That tells it to run weekly
18:05
We're going to do the same thing on our diff. Tell it to run all the other days
18:08
at midnight. We have to put two different schedules on here because you're going on
18:15
because of Saturday. but now those jobs are scheduled to run now we want our logs to run i say every 15 minutes
18:25
so we got our frequency set to daily every day minutes 15
18:32
and we're going to set our integrity checks to run at 2 a.m on saturday sounds like a good time
18:42
right? Nobody's busy at that time. If you can afford to run integrity checks live on your system
18:49
And the same with the optimized job for our indexes, 10 p.m. on Saturday seemed like a good time
18:55
And then all these miscellaneous jobs for cleanups and things like that, I just said all those to
19:00
run every day at midnight because they'll keep things clean. Now for each one of these jobs in here, we do not have an operator for those yet because you can't define those when you install it
19:16
So we're going to get back each agent job that does not have an operator, and then we're going to set the operator for it to own failure to email us
19:27
That way we can have alerts. so I am going to set my recovery mode on my msdb to full so that we can simulate having some
19:42
backups later and because we do have a check for backups in here I'm going to go ahead and run my
19:49
full backups and my tech and we have integrity checks checks too I'm going to go ahead and start
19:55
those jobs up so that those will disappear off our reports as well because those should only take a
20:01
few seconds to run and we'll give that a few seconds to run and I will go check for questions
20:09
while I go kick off the report again And no questions, that's okay
20:29
So you can see how easy it is to install all the Halligrand scripts
20:34
They have a number of other things that you can actually install very easily that we'll go over
20:38
in the next slide deck, like Brent Ozars Blitz Scripts. The SP who is active is installed right off the internet
20:49
So there's things you can do to automate these and keep them up to date, because I know Brent changes his nearly monthly
20:54
So you can always have the newest and greatest versions set up, and all of you probably want to check for features
21:00
before you upgrade it, but the others you probably just would automatically update
21:08
Come on Power BI, you can do it. Power BI is so slow sometimes
21:31
Some background music for this. Huh? We need some background music. I know
21:37
This is usually when I get questions. So it's like, I know I was asking questions
21:47
Good news is I only have to load it one more time after this. And I'm talking a little bit fast, but we'll get back on schedule so everybody can eat lunch
21:56
and because I'm not taking questions getting questions it's not taking as long either
22:07
so hit refresh so while that's running we'll go ahead and go check out what my next set of codes going to be
22:23
oh we won't yes we will probably guys trying to take over look at it
22:34
drop my screen hate when things like this happen can't run two things at once
22:40
with power via okay it's all right it's good yeah I'll just be patient I just
22:52
don't like to silence. Say salads. Paul has a question. Do you want to take that? Sure
23:02
Let me go back over here to look. Yes, I do keep them in master
23:12
I prefer them in master. The only time I move things out in master
23:16
is if I was capturing SP who is active to a table
23:21
or Brent's first responder stuff to a table, I would put that in a DBA database
23:28
All the scripts I'm not worried about. I know he has the command log table
23:32
but he also has the thing that automatically cleans it up. So I put the others in a DBA table
23:38
just in case I don't have something to automatically clean them up and then make them more efficient
23:42
So if I have all three of those installed and for some reason I'm keeping one to a table
23:47
then I would put all of those in a DBA table. Otherwise, if you're not keeping anything to a table, I would just put them in master
23:55
So. Can I run my checks in SQL Server Agent? Yes, you can
24:05
There's a blog post out there somewhere that shows you how to run PowerShell in SQL Agent
24:09
and then you can write it to a table or to a JSON and have something to automatically sweep up and get the results
24:20
So now we're down to 20, 29 failed tasks. We're doing really good
24:27
We're not going to fix them all, by the way, just in case anybody thought we were. But we've pretty much got this
24:36
pretty much cleaned up at this point. We've got a few other things we run on
24:39
like SP was active. We need to rename SA, and a few of our SP configure things need to be turned on
24:46
Query store needs to be enabled on something. We want to install a CU on our system so we can do that
24:53
And a few other things. We need our DAC turned on. That's important. Our backup compression
24:58
So we're going to go in and do some of the miscellaneous stuff now. Hence my name on my miscellaneous file
25:04
So I always like to have an optimized for ad hoc workloads turned on since like 2008
25:10
I love to have my DAC turned on since it was invented in 2005
25:14
I don't remember when backup compression was invented, but I like it. And as you've seen, it wanted our remote access turned off
25:23
So we will configure those real quick. And you can tell if we scroll back up that it changed values for us
25:34
And this one requires a restart of SQL Server for you to be able to see
25:39
So anytime you use a command like the set dba spconfigure, it will warn you if you need to restart SQL Server
25:45
because your config value needs to restart it. We won't be doing that because we don't really want to at this point
25:54
We're kind of using it. So when you install spusactive and all the Halligrandscript
26:01
as the question before was, you can actually tell it what database to install it in
26:05
This one requires it, so I put it on there. So now I've got spusactive installed
26:11
and despite some errors you might see when installing the first responders kit
26:17
it does actually install correctly. And there's another project called SQL Watch that's made for monitoring your servers
26:28
that you can actually install using this as well. And you can actually run Glenn Allen Barry's diagnostic queries as well
26:35
and get the output saved somewhere. So there's a number of things you can do with DBA tools to do these
26:40
because these are all DBA tools commands. So we're going to turn our query store on
26:45
as you've seen we had an alert for that and the only one that we can actually turn it on
26:49
is the MSDB because we don have no user databases So we got that turned on and that the only system database you can actually turn it on in and you actually can see any data
27:01
without going to the catalog views because there's no GUI for it. Then we're
27:09
going to actually rename our SA account. Go back change. What
27:23
Did not like that. We'll figure that out someday and figure out why it's not working
27:27
Let's make a note of that. I know it's worked before, so we'll have to see if there's some override that needs to be stuck in there
27:37
Let's try to force. I'll have to take a look at that and I'll fix that when I upload these scripts to my
27:51
website or to C Sharp's website if they have us upload them
27:55
There's a couple of trace flags that were popping up too that you need to enable
27:59
One is the backup thing that keeps it from going in your error logs because if you're
28:03
backing up tons of databases every 15 minutes with your T logs, you're just filling it up
28:09
The other thing we're going to tell it to do is to not expect 117 because this is turned on by default in 2017
28:18
And we're setting these as config values at this point so that DB checks knows to check it
28:24
And we're going to tell it to skip tempDB 1118 because that's, again, turned on default by 2019
28:31
So then we can tell it to actually enable the trace flag 2226 because that's the one that we actually told it to expect
28:37
And because we're using query store, there are two trace flags that you normally would want to turn on in a different environment, but those are actually defaults in 2019 as well, so we don't have to worry about those
28:48
Now, if you for some reason wanted to check for orphan files, there is a check for that
28:53
You could check for those and then go through them and delete them. That check was not popping up, so we'll skip that one
29:01
And we'll go ahead and run the Power BI report again after we close it
29:07
So that's most of the things that are going to be fixed
29:15
Now, one thing to note is I told you I wrote some CIS security scripts for just checking security
29:23
If you just want to run something that's in a particular category, you can just put the tag here
29:28
Like you could run all the agent checks by itself and it'll output it to a file
29:33
So if you wanted to be CIS compliant, you could just run the CIS report separately
29:40
Some other commands that you might find useful while that's running is to set your power plan on your system
29:47
I know that's an old thing, but some systems still don't have them set correctly
29:53
Another thing is your cost threshold for parallelism. As I mentioned, Glen Allen Berry scripts can be ran to a temp folder
30:01
and you have all your data output to a CSV. You can actually run database compression using DBA tools and output what it does
30:10
and based on if it saves you 25%, it'll compress it. And then we're actually going to pull this trick and update our SQL Server instance
30:22
once it finishes checking everything to the new cumulative update, which I did not download the new security one, but I do have the last CEU before the security update
30:37
So I'm going to let that run so you can see we'll get a newer version of SQL Server
30:43
and it won't be on RTM, which is where we're at right now. Let's add some of the updated to CEU 8
30:56
yes because that's what it's right out my temp folder and it's preparing to
31:08
install it so that's kind of neat that it can do that automatically for you and
31:13
you can do this across multiple servers and automate your patching system and
31:17
that's one of the checks in DBA checks is to make sure you're on the latest build too so that'll help fix fix that one up now power bi refresh
31:50
And I'll go check for questions while it's refreshing, as always. INF, I believe they set a command up for INF
32:06
I would have to double check, but I believe that's already been set up. Is it possible to run tests where it's not included in the module
32:12
Yes, there is ways to write your own custom tests and have BVA checks run it for you
32:18
There is a blog post for that, I think, that Rob Sewell wrote. If you have problems finding it, email me or DM me on Twitter
32:26
and I will find it for you and send it to you. But you can actually write your own test to be included in DBHX
32:34
if they don't have what you want to be checked, and then have it all outputted to the same Power BI report
32:40
that you're getting everything else outputted to. And he has a tutorial on how to do it and everything, so
32:48
Okay, so we're down to 22 failed. Most of these are auto growth events, like one did auto grow because it didn't have any
33:04
space to actually do anything. And then we got, we need to set our auto growth config because it's just set up the percents
33:10
and the one megs. So these are all things that we would have to go back in and fix
33:15
but other than that you know that is the basics of running everything
33:21
and that's pretty much what I wanted to demo and we will actually see if our CU
33:26
when our CU is done we will check the version of SQL server if you guys hang around for that
33:32
I kind of talked fast plus I wanted to leave room for questions
33:36
so I talked fast hopefully not too fast this normally takes me an hour but I did in 40 minutes
33:44
I did good they only gave us 50 minutes so i don't see any more questions see if our cu is done our cu is indeed done
34:08
But we can actually check our SQL version now. You can see that it's not RTM
34:15
That is CU8. Go on Azure. Why are you being so slow
34:30
And we can see that, well, CU6 is evidently the one I had downloaded
34:36
So that's the one that installed. Maybe I misread the 8 because 6 and 8 look awful out of light
34:41
But you can tell it's not RTM because that's where it would have been because I just installed SQL this morning
34:46
So that's all I had as far as demos go. Jump back into PowerPoint
34:54
There isn't much left in there. But as far as resources go, the best resources are at DBA Tools for their stuff and DBA Checks
35:02
They have a pretty good library. Rob Sewell blogs on DBA checks a lot because he's the main contributor to it
35:09
and DBA tools keeps a pretty good links of people blogging to stuff on theirs and
35:14
it's pretty easy to Google things as well. If you ever come across anything
35:18
that you don't know how to do the SQL community Slack has a very active
35:23
channels for both of these that people answer questions. If you need me to answer
35:29
questions I'm available as well I do not mind I answer my emails usually within a
35:33
a day or two so for my dms that quickly as well so if you have any more questions I'll let you put
35:41
them in there otherwise I'm going to say thank you