>> In Azure SQL Database,
you might have reasons to export a BACPAC file,
and you might want to do it with Azure Automation.
Learn all about it this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and welcome
to this episode of Data Exposed.
Today, I'm joined by Mike Bhatt,
our Customer Engineer working on Azure SQL Database,
among other things in the Cloud.
Mike, thanks so much for joining us today.
Can you kick us off by telling us a little bit about what you do?
>> Sure. Thank you, Anna. Hi, everyone.
I'm Mike Bhatt, Senior Customer
Engineer here in the Northeast CSU,
working with our customers,
supporting their digital transformation
with everything SQL-related as well as On-Prem and Azure.
>> Awesome. You have actually quite a large scope
of things you look at.
I should also mention that this is part
of the SQL Insider Series,
so really excited about this series,
getting to work with folks like you and
learn about things that are going on with customers.
One of the things you are going to talk about today is
exporting backpack files with Azure SQL Database.
I'd love before we get into the nitty gritty and the demo of it,
what's the scenario that customers have and why do they need this?
>> For example, it's great that Microsoft does
SQL database backups for
our customers so they don't have to handle all that stuff.
However, from time to time,
customers have a need to archive it
locally or archive it in larger storage for later,
bringing it back to On-Prem,
perhaps to do some testing or
deploy other things and then import it back into the Cloud.
Of course, we have automated backup,
but they want to do it ad hoc from time to time.
We do have the capability to
generate a backpack and then we'll export
it out to the Azure Blob Storage using Azure Automation.
>> Oh, I got it. I think we could do this manually,
go on and export backpack file.
What are you going to show us is how you can have automate it,
maybe on a schedule, maybe do some other cool stuff.
I'd love to see and hear how this works.
>> Sure. Absolutely. I do have a demo here, so let's get to it.
>> Awesome.
>> The first thing we need to
do is create an Azure Automation account,
which I already created here on my portal here.
It's called NBA Insiders Demo,
which is my Azure Automation account.
I have already created a couple of Azure SQL Databases as well.
Once you create your Automation account,
the next step is to import a runbook.
We're going to runbook and then here we can
click "Import" and we can import it from a gallery.
Within the gallery, we have lots of useful runbooks which are
actually written by Microsoft colleagues
as well as people within the community.
What's important, you would publish it.
I've already taken care of that as well for us.
If you go to the gallery and browse,
you will come up with lots of
good information and lots of good stuff. Let's take a look.
As you can see, if I browse,
there is actually a runbook
for backpack for Azure SQL Database to Blob storage.
I click on it, you "Select"
it and then you click "Import" and once you import it,
you have to provide the name of
everything which I already taken care of.
I'm going to go back and I'll show you where it is.
Let's go back to the runbook.
I'm going to go back to the runbook. Here's my runbook.
As you see, it says published,
which means I already have it.
It's ready to go.
Once you have a runbook,
next step for us is actually to link it to a schedule.
But I'm going to show you something about runbooks as well.
If you go to the Edit pane,
it would actually bring up the runbook for you.
Then good thing about this is if you click on a "Test" pane,
it will actually come up with all the parameters you need to
pass to generate your backpack.
You pretty much specify
your resource group here, database server name,
admin name, passwords,
as well as your storage information,
for example, the account name.
That points for your Blob storage,
storage key, the container name.
The good thing about this runbook
is it has something called retention days,
so you can actually specify for a number of days you want to
retain it in your Blob storage and it will
purge if you don't want to keep
it for more than a certain number of days,
which is cool that we don't have to
go and maintain your Blobs either.
Once that's done, and if you click on "Start," it will
actually run it for you
and if you have any problems, it will report it.
For example, in my case,
I was actually missing a couple of partial modules.
That will be brought to my attention
that you don't have a module here,
you're not going to be able to run the runbook.
Well, pardon the pun, run the runbook.
But you're not going to be able to go ahead and set
it up to generate your backpack file.
I'm going to go back and
then we're going to go back to the runbook.
If you're missing modules,
you have to go to your Automation account
and then there's actually a place to click for modules,
which is under runbook, actually.
Right here and then you can
actually import modules from gallery as well.
These are all the modules that are available
and if you specify a specific module you are missing,
you can download it from there and import it into your account.
Once we have the runbook set, we can schedule it.
I'm going to go back to the runbook. Here is my runbook.
It's in edit right now because we clicked on it to take a look.
I'm going to go back to the edit and then just
say revert to publish because I already published it.
We're going to go back and then once you have it,
you click a "Link" to schedule and here I have schedule.
As you can see, I've created one already,
which is set to run today at 1:52
PM for a database backpack generation.
I'm going to go back again.
Then of course, you have to specify all the parameters as well.
But the good thing about this is actually within
the account for your automation,
you can specify them as variables here as well.
You can add them here as well or you can
specify from the menu that we had,
depending on how you create your runbook, guys.
As I mentioned earlier,
you can have your own runbook as well.
You don't have to use the one from gallery,
but you can create one as well.
Once that's done, it's pretty much to schedule it and let it run.
It actually runs a PowerShell in the background to a journey to
a backpack so we can actually
take a look at job status once it's done.
As you can see, I have three failed,
which I failed on purpose just to make sure that it reports both.
If I go to complete it,
it will give us a status of pretty much everything that was done.
Then there are other places you can also check.
I'm going to go back to my container and show you that I do
actually have a backpack of a database, rather.
Let's go back to my status account.
Here's my storage container. Blob storage.
If I click on a container called "DB
Exports," here are all my backpacks that I generated.
Once they're here, you can actually bring them
On-Prem using maybe a Storage Explorer,
or you can actually import them back
into SQL Server using Management Studio.
>> This is really cool.
I didn't know you could do all this using Azure Automation.
>> Yeah, it's pretty cool and the best thing about
this is actually you can just schedule it.
For example, if you're going to have
a major deployment or something,
you can schedule it ahead of time,
let it get done,
and best thing is automation gives you a lot of options.
You can actually even create alerts and things like that as well.
So it's pretty neat.
There are other places you can actually check schedules as well.
If I go to my logical SQL Server
and then if I click on Import-Export history, here you go.
These are all the imports or
exports you had created for your databases.
>> Got it. That's pretty cool.
You can not only track an Azure Automation,
but you also have a central place to track
within the database server itself.
>> Absolutely. You can actually track
these activities on the database plate itself.
If you go to the database itself and look at activity log,
it will tell you when it generated the backpacks for you.
>> Got it. That's pretty cool.
Azure Automation, even though it's just running PowerShell,
is able to communicate with
other bits and pieces of managing in the portal.
When it comes to using Azure Automation, this is a great example.
Are there other examples or
use cases you've seen with customers doing this,
whether it's for databases or something else?
>> Sure, absolutely. Best one that
comes to mind, and it's pretty handy.
For example, if you want to maintain
your indexes or update statistics,
you can schedule your jobs from here since there's
a SQL Server agent and there is
a SQL Database, it can do it through here.
The other good use is, let's say, for example,
if you have a lot of VMs and you want to
start them or shut them down at a specific time,
you can use that as well.
The other useful part of it is that, for example,
if you need to scale up your tier for your databases,
you can scale up or down using
a runbook as well and we do have a runbook for that too.
There are lots and lots of good use for automation, actually.
>> Got it. That makes sense.
It seems like you mentioned a couple of
really great Platform as a Service example,
but it sounds like you can also use this for VMS.
I'm assuming you can use this for other other services as well.
When we come back to
exporting and importing using
the backpacks for Azure SQL Database,
do you have any tips or tricks as
people get into this for their workflows?
>> Sure. The thing to remember is
there's a limit with respect to the Blob storage.
It's 200 gig and it's for standard storage only.
Note, premium is not supported yet.
The other thing is make sure there are not a lot of rights
happening when you're generating
a backpack, they will speed it up.
Last but not least, make sure your database as
well as your storage account are in the same region.
That will also speed it up as well.
If you need to scale up just to generate it,
you can scale up, scale it back
down once your backpack is generated.
>> Interesting. You could scale up so that
the backpack expert takes less time to get it done.
>> Exactly.
>> Maybe you can even build that into
the runbook then it scales up.
>> That is a run book as well,
so you can definitely piggyback on top of that too.
>> Awesome. Cool. Mike,
thanks so much for coming on the show.
This has been really useful.
I personally learned a lot.
I think our viewers probably did as well.
To our viewers, if you like this episode,
go ahead, give it a like.
Check the description for links to learn more about
exporting backpack files in
Azure SQL Database and using Azure Automation.
We hope to see you next time on Data Exposed.
[MUSIC]