>> When you think about
disaster recovery, there's a lot of other
things you have to think about besides the database. Join Chrissy as we learned about DBATools and ways you can automate your disaster recovery for SQL
Server this week on Data Exposed. [MUSIC] Hi, I'm Anna Hoffman and welcome to this episode of Data
Exposed MVP Edition. Today, we're very lucky
to be joined by Chrissy, and I'm going to let Chrissy go
ahead and introduce herself. >> Hey, my name is Chrissy LeMaire. I'm a dual Microsoft MVP. Awarded for my work in both
SQL Server and PowerShell. For my day job, I'm currently
a Security Engineer, but for over 20 years, I was a SQL Server DBA, and I use my PowerShell skillset
to make that transition. I am also the creator of DBATools, which is the SQL Server module that was built by and
for the community. It's available on GitHub, it's free, it's open source,
it's really exciting. >> Thanks so much,
Chrissy. We're really excited to have you here today, and just for our viewers, this is a big deal to have
Chrissy on the show because she is the creator of DBATools. So really excited to have you, and today specifically we're
going to be talking about how to automate disaster
recovery in SQL Server. I'd really just love to pass it over to you Chrissy, you're the expert. Can you tell us how to automate
disaster recovery in SQL Server? >> Absolutely. The first
thing that I would like to do is define what is HA and DR. A lot of times whenever
you're working with HA and DR, you'll hear them couple together. HA is high availability, and high availability deals with minor outages and the failover
solutions are often automated. Things like availability
groups, and failover clusters, and the goal ultimately is to restore full system functionality
in a short period of time. In my experience, that's been
anywhere from 2-30 seconds. Whereas with disaster recovery, this deals with major outages, such as natural and
man-made disasters. If you have a tornado that takes
down your datacenter, or a fire, or even some internal threat, then that's where disaster
recovery would come in. With DR, it focuses on
manual processes and procedures to restore your systems
back to their original state. The expectation is that it does take longer than high availability. So whenever you're talking about DR, there are certain federal regulations
that actually require it, and that's one of the reasons
that you would want to set up, create, and test a DR plan. Also, business partners and
customers often demand proof of disaster recovery plans before
they even sign the contract. But this one is the
most important to me, and it's supporting
ongoing availability of IT services for
business continuity. When I was taking my class
at Regis University, I was shocked when I read that
a company that experiences a computer outage lasting
more than 10 days will never fully recover financially, and within five years, 50 percent of those companies
will be out of business. With the prevalence of
malware and ransomware, it's especially important
to have a DR plan in place. When it comes to SQL Server, a lot of people's first
thought is databases, and I had this really
awesome presentation. Tracy Boggiano created one on HA/DR, and you can find it at
sqlps.io/hadr, and in there, she goes in-depth about
database disaster recovery. One of the options is
backup and restore, then also Bacpacs and Dacpacs,
replication, log shipping, which is one of my
favorites, mirroring, and I know this has been deprecated, but a lot of us still have to manage
older versions of SQL Server. Also multi-site failover clustering,
and availability groups. The scenarios, this is something that my
professor had said, he said, ''The faster you want
to get your data back, the more you will pay,'' and
I thought that was really interesting and I wanted
to apply it to SQL Server. The simplest and the most affordable is Ola Hallengren's scheduled backup. You can go to ola.hallengren.com, get it's maintenance solution, and this is a solution that's
recommended by Microsoft. It's free, it's open source, and I have used it at nearly
every place that I've worked. Then, you can take
Robocopy and replicate those backups out to a secondary
datacenter or to the Cloud. Then, you also have the most
complex and the most expensive, and that is a geo-replicated
distributed availability group. One of the reasons that it's
the most expensive is not only are you triplicating
your hardware and everything, you also have to get somebody, a consultant who specializes in this to get it set up
right the first time, and you also have to have
specialized staff that knows how to deal with geo-replicated
distributed availability groups. We've covered databases,
but there's so much more, and I find that a lot of
people forget about this part. Things like logins with their passwords and all
of their properties, everything in SQL Server agent, you have your jobs, your operators, your schedules, and so on. Then, all of this, your extended event, your linked
servers with their passwords, your credentials,
audits and audit specs, the replication, all your
configuration options, and so on. This is a lot, and you can export it through
SQL Server Management Studio. There are options where you
can go and then right-click, but imagine here's like what, 20 options on the screen, and then each of these
things can have 20, and then you multiply that
times 20 SQL Servers, and that is just way
too much clicking, and I can see DR falling
through the cracks there. But what if it could be less painful? That's where DBATools comes in. As I've mentioned earlier, DBATools is an open source, freely available community project. You can get it at dbatools.io, and it's super easy to install. You just Install-Module dbatools, and this will install it for
all users on the computer, including the SQL Server
Agent service account. Then, also if you can't
install two program files, you can also set your scope to current user and install
it just for yourself. You can also go to
dbatools.io/download, and we offer several different
ways to download it. You can get it from GitHub, you can get it from
Chocolatey, and so on. We do want to thank Microsoft for the SQL Management Objects or SMO. What's really awesome is
when you install DBATools, this is all that you need. You don't need to install SQL Server Management Studio
because they have allowed us to redistribute the DLLs that power SQL Server
Management Studio, and we super appreciate that. I know it can seem overwhelming, we actually have over 500
commands within DBATools, but we also provide extensive documentation
within each of the commands. This is actually a test. If you commit a change
to our repository, a test will run to
ensure that everything is documented as it should be. We also have extensive
documentation on dbatools.io, and we've even made a few videos. If you go to dbatools.io/youtube
or youtube.com/dbatools, you could see some
of the usage there. With that being said, we're going to do a demo. >> Awesome. We can't
wait to see this. I mean, you've already
covered so much, and I think I'm excited to
see how this works in action. >> This is really, to me, I was so excited when we built
this command because it really simplifies all of that down
to one single command. This one, it actually wraps
a number of other commands, but that's all done
behind the scenes. You don't have to
worry about anything. I do want to make a note, I will be running this
against my local machine, but that's because I'm a developer and I'm developing on this machine, and then I'm presenting about
it through the machine, but when you install DBATools, you should only install it on a centralized management system similar to SQL Server
Management Studio. You don't want that to proliferate
all over your network, and the same is true of DBATools. The first thing that we're
going to do is we're going to take a look
at the SQL Server, and you can see here,
this is my workstation. I have some databases, I have some logins, login 1, 2, 3, 4, 5, and we have all of these sample
objects that we expect will be there once our DR has completed. Then, I have something called Pester. Pester is PowerShell's
test-driven framework, and it's really awesome. We use this for
environmental validation, and here you can see it shows
that it still has all the things. It has all the databases
that are expected, all the mail accounts, extended events, agent jobs, agent alerts, and so on. Everything is there. This is the command
that you can use with DBATools to export
everything that you need to unless you want
to exclude something like your replication settings
because you completely host it on your machine like I did. What we're going to do is we're
going to export DBA instance, the SQL instance, its workstation. We're going to give it a path, and if you don't give it a path, it'll actually create one for you. In this case, we will exclude
the replication settings. Now, we run this and right now, it's exporting all
the configurations, all the custom errors, all of the server roles, the credentials, the logins, all of your DB Mail settings, your registered servers and CMS, the backup devices, server
triggers, Resource Governor. Imagine doing all of this by hand. It would be torturous, and no, this is
torturous seeing this. There's something wrong
with my PowerShell Proxy. >> Oh, no. >> It's usually prettier, and it was prettier earlier today
when I was testing this out. So what I'll do is I'll just pretend that didn't
happen by hitting clear, and then what we're going to do
is we're going to go and look at the actual folder
where it was exported. You could see that it
took the server name and then it also has the date there, and then here's all the audits, the audits' specs,
credentials, and so on. I do want to emphasize that it's
not a DR plan without testing, and we do have a command that also makes testing your database
backups extraordinarily simple. All you have to do is
issue this one command, and what it'll do is it
goes and it gets a list of your databases and then it gets
the most recent full diff and log. It performs a restore using an alternative name
and then it performs a DBCC CHECKDB to make sure that
all of the data has integrity. Yeah, it's super, super nice. A lot of people have integrated
this into daily checks. So what we're going to do is we are going to test those
output scripts that I created and we're going
to execute this drop.ps1. This also kills my SQL
Server Management Studio in case I forget to go
and refresh everything. Now, it's dropping all
of those databases. It's dropping all of the credentials. Every single thing that
you had seen earlier, it is performing all of those drops. Once that's done, we are going to test that everything was
successfully dropped. Awesome. Now, it's opening up SQL Server Management Studio
and we'll go and take a look. Now, I do expect that since there was an issue for the first
time ever, of course, while it's on screen,
with the credential, I do expect that at least one test is going to fail, but we'll see. Here, we have the credentials. These are minimal credentials. Do you remember login 1, 2, 3, 4, 5, 6, 7? All of those are gone, so we have no databases here. When we go to agent, we have no jobs and so on. So what we're going to do is we are going to test that
everything has been dropped. Oh my God, what a disaster. Look at all of that red. There's 22 tests that it ran
and 22 tests that failed. This means that your SQL Server is out of commission and it is time to use the scripts that were output by the export Dba instance command. There are different ways
that you can restore your databases and
restore your objects, and what I'll do is
instead of restoring each of those SQL agent scripts, I am going to restore MSDB. To do that, I need to stop the agent. Then, once that has stopped, now what we're going to do
is we're going to do a Get DbaProcess and we'll find all of the processes that are using MSDB and then we'll just
pipe that and kill those. We had one that was using it, and now we are going to
perform the restore. We're going to grab the files. We're going to exclude
agent and replication, even though it didn't
make replication, and then we will invoke that query. Of course, what you're going to
want to do is go through this and evaluate the scripts that
are output and run this. But because it's a demo and
I want to make it quick, we're just smashing everything in and we're ignoring any of the errors, and now we're going to run these Pester test after
we start the agent. We'll run the Pester test to see that everything has been restored. All right. Let's check
to see if everything is back except for probably
that credential. No, the credentials came through. Awesome. We have 22 tests that
ran and 22 tests that passed. Now, when we go back and
we look at our SQL Server, when we perform a refresh, we can see that the
databases are there, the logins are back, all of the SQL agent
jobs are back and we have successfully recovered
from our disaster. >> Wow. Chrissy, that was a lot, but it seemed like you are able
to do it in such few commands and I'm really impressed
how you even encountered an error and we were still
able to push through. >> I am too, I'm not going to lie. I'm really excited that we had
all that green at the end. >> I have a few questions, but I'll limit it to just a few. I'm sure with your experience, you've seen a lot of this over time. What's the thing that people
most frequently forget? Or what's a big "got you" that people should be
aware of when they go about using DBATools to
implement disaster recovery? >> Well, I think before DBATools, and this I think really talks
to the power of PowerShell, we've made those
checklists and we have invested so much time studying what is it that needs to be restored. A lot of times people
will only focus on their databases because that's
what's easy to backup and restore. But I think it's especially important
to have all of your logins, to have all of your agent job. So my answer to that is
when it comes to DR, people often forget
everything but the databases, but DBATools makes it easy to
implement your entire instance by exporting all of those files
and all of those objects. Then, what's really cool is
that you can put that into source control and each night commit that to an external source so that it'll be available in
the event of a disaster. >> Right. I think it's
really awesome that not only are you saving
us a ton of clicks, but you're helping us a lot
by helping us understand what else should we actually be exporting and making sure we keep track of. This is just awesome stuff, Chrissy. For the viewers watching that don't use PowerShell or super
new to PowerShell, do you have any advice on
how they should even get started with SQL
Server and PowerShell? >> Yeah. So if you go
to dbatools.io/start, that actually has a guide of all
the things that we recommend, everything from books, whatever
media it is that you use to learn. For me, I like to learn from books, but other people like
to learn from videos. There's even something
called PSKoans, that's a PowerShell module. It makes it an interactive
game to work with PowerShell and that's
listed there as well. Then, also the community
is always there, both in the SQL Server Slack in dbatools.io/slack and at
the PowerShell Slack, which I don't remember
the short URL for. But it is available in
everybody, super helpful. >> Awesome. Thanks so much, Chrissy. I've learned a lot
personally in this session. I'm sure our viewers have as well. I really want to thank you
for coming on the show. It's such a huge honor out of
the creator of DBATools here. For all of our viewers, if you liked this video, please like this video, leave us a comment, and let
us know what you think of DBATools and some of the tips
and tricks that Chrissy shared. We hope to see you next
time on Data Exposed. [MUSIC]
