>> 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]