>> Join Bob Ward, and myself to
get a bunch of tips and tricks about
migrating to Azure SQL this week on data exposed.
[MUSIC].
Hi, I'm Anna Hoffman.
Welcome to this episode of data exposed.
Today, I'm joined by Bob Ward.
Bob, thanks so much for coming
on the show again. It's great to have you.
>> Yeah, here we are in 2022 Anna,
my first data expose episode of the year.
Great to be back with you again.
>> Awesome. It's great to have you.
I know folks are really enjoying
The Sequels Server of 2022 series that you kicked off last year.
You've been working a lot on Sequels Server of 2022,
I wanted to have you on the show to
find out because I know you're always working on a torn of things.
What else is going on in your world?
>> Yeah, thanks for asking, Anna.
Late last year, not just sequel server of 2022,
but it came to my attention that
maybe I could spend some more time
teaching customers that were on older versions of
SQL Server how to migrate to Azure,
make it very SQL Server focused.
I built something called
The Migrate SQL Server Azure to Workshop and delivered it,
actually an in-person event, if you can believe that.
I did it virtually at the past summit and then did it in
person at the MySQL config in Las Vegas.
It's been a lot of fun putting it together,
taking my knowledge of SQL Server,
and translating that to how customers can move to Azure.
You see here on the slide Anna.
I've got a slide here that shows here a couple of links.
One of them is a link.
The very first one is to a GitHub site based on
our famous SQL workshops where we give you,
it's in preview right now.
It's just starting and you can keep track of it as we update it.
But it has the slot full slide deck
of what we used to present the workshop,
and it has examples of going through various aspects to migrate.
The second link is Data Migration,
and we'll talk about that and I think
maybe at the very end of our discussion today.
But what I thought I might do if it's okay with
you is just give
folks a sneak peek of what this workshop looks like.
>> Yeah, I think that would be great.
You've been working on this workshop,
you delivered it a couple of times,
you are planning to deliver it.
Maybe you could also share with us,
you're planning to deliver it in person a
couple more times with some upcoming events.
>> Yeah. Can you believe that delivering
it in person at a couple of events.
I had to say that twice to myself to believe it.
But yes, you may have heard of
a very famous conference call Sequel Bits in March.
I'll be traveling across the pond to go over,
and deliver a full training day with this exact workshop.
Then in April, the MSC will config the SQL Server and
Azure sequel conference is coming back again to Vegas in April,
and I plan to deliver it in person there as well.
>> Awesome, cool. UK or US or Europe,
you might consider attending this in person,
but in the meantime, Bob,
I think it would be great to have a sneak peek.
What are some of the things you're going to
cover and what are people going to learn?
>> Well, Anna.
When I was thinking about doing this workshop,
I thought to myself and based on
my years of SQL Server experience,
how would I approach this if I had SQL servers and it
turns out we have modules like any workshop would.
I first built this slide to show
an organization of when you do the workshop,
what modules flow through what I call migration scenarios.
Module 1 is actually teaching people about Azure.
If you don't think about Azure,
we just teach you the fundamentals of Azure.
Module 2 is about discovering and assessing your sequel servers.
We've something called Azure Migrate,
and we specifically have a functionality,
it's in preview with VMware
where you can discover thousands of SQL servers
across your VMware Enterprise
and then you can assess what those look like.
But let's say that you figured
out a couple of SQL Server you want to focus on a migrating.
What we do also in Module 2 is we take two scenarios.
One is a SQL server 16 instance,
and a SQL 19 instance,
and we use the data migration assistant to
assess specifically looking at compatibility issues,
or perhaps even looking at your workload and deciding
based on the performance of my workload, which way should I go?
Should I go to Azure Virtual Machine?
Should I go to managed instance?
What specific choices should I make when I go to those?
How big is my VM?
How big should many Egyptians?
How many cores do I need? Anna,
you know what, I have work with you in Azure before.
Those are tough choices you have to make,
so the Migration Assistant tool can look at your workload,
and give you some of those recommendations,
and we walk through what that looks like.
Now, what we also do is we'll take a look at SQL 16,
and we'll come up with possible requirements for
your migration that will lead you down
the path of migrating to Azure virtual machine.
One of those could be that you have
an ISP application that requires SQL Server 16,
so even though Deb compart exists,
you have to use that certain version of SQL.
Therefore, virtual machine is a great option for you,
and we talked about how to do an assessment of that.
Look at your requirements, build a plan,
and then do an offline migration
during that situation based on your requirements.
Now for the SQL 19 instance,
let's say, for example,
your ISP says it's okay that you go to
a version less platform as a service. Oh, I'm sorry.
One of the things I forgot to tell you about is
that we have this idea of a life-cycle here.
When we're discovering, assessing,
and we start going into migrations,
we're not just going to show you how to
migrate, but when you're done,
we're going to show you how to optimize,
and manage that migration.
How do you make it the best once you get to that destination?
We'll show a quick example of that as well.
On the SQL 19 side though,
what we'll do is,
we'll assume that maybe the platform
as a service is okay for you,
managed instance could be one of those targets.
We'll show you how to do an online migration,
and we'll focus on using a new extension.
I think you've had these folks on your show
using Azure Data Studio to do that migration,
that's pretty cool, and then we'll also
show you how to optimize and manage that.
if you think about these scenarios,
you get a SQL 16.
There could be a common situation for customers,
SQL 16, you got to stay there.
VM is a great way to get to the Cloud, but for SQL 19,
maybe an online migration to
managed instance would be a good choice.
Then when I was building this workshop, I thought to myself,
now if you're in virtual machine,
but you want to go to managed instance because maybe
your ISP app allows you to do that, how do you do that?
The final module is to show you exactly that.
If I got into the Cloud already,
could I make that shift over to managed instance and
we'll talk about that using Azure Data Studio Log Replay Service.
Anna if you just look at this,
it's pretty cool idea because the concept is it's
a very common situation and scenario for customers today,
and how to go leverage
all the tools we've built to do the migration.
>> Awesome.
One quick question here.
These numbers, what are these numbers?
>> Yeah, I know it was hard
to not put a little table or legend in here,
but the numbers represent the module numbers.
Module 1 is just about introducing you to Azure.
We'll just talk about Azure,
so just getting those fundamentals down.
Module 2 is in this life-cycle thought.
How do you discover, and assess your current SQL servers?
Module 3 is how to migrate to a Azure VM.
Module 4 is to optimize,
and manage that migration to VM,
and then five is to take SQL
19 and how to migrate to managed instance,
and then six would be optimizing
manage that migration to managed instance.
Then seven is to put it all together by saying,
if I'm in VMware already,
how do I go over to managed instance where so
the end result would be when you're done with the whole workshop,
you've got everything in managed instance.
>> Oh, awesome. If I go through the workshop,
or if I look at the materials,
I'm going to learn all of these things.
>> I know. You've known me for a long time,
I'm never shy of putting a lot of information into something.
The folks that did this session
in person when I did this in Vegas,
they were just drained by the end of
the day because there was a lot of information.
But every one of them said,
I really feel more comfortable now going
forward to make these decisions to do a migration.
>> I love it. Bob,
can you give us a couple of highlights of
things that are going to be included in this workshop,
or things folks might want to know about migrating?
>> Sure. The first thing we're going to do is we're going to
show you how to use this Azure Migrate tool.
It's a one stop shop in Azure
where you download what's called an appliance.
It's a piece of software that you put in your VMware environment.
We wanted to show you a little bit of
what that looks like because you may
have a scenario where you have a lot of SQL servers,
and you need an inventory of what they all look like,
and you need recommendations from Microsoft about where to go.
Should I go to VM?
Should I go to managed instance?
What choices I should make in between there.
We're going to show you that one stop experience
using Azure Migrate.
But what we're also going to do is help you move from there.
Let's say you've made
some decisions on like I showed you in that scenario,
you made some decisions that you may want to go to Azure VM.
When you do that,
we're going to show you,
and demonstrate an offline migration.
Now, you can do an online migration too when we talk about it,
but we're going to focus on a simulation as
though an offline is the easiest best way for you.
We show you some choices, and I'll show it to visually here.
Now, you're going to laugh at this one.
Do you know that if you've got Azure Virtual Machine,
and you're going to move a SQL Server in there and it's offline,
so it's just a backup restore type concept.
If you have a very small database,
you can just copy the file
into the VM, and that's all, we're done.
Do a copy and paste into an already P session,
and then just restore the database.
If it's just a 30 megabyte database,
why go to a bit contrived process?
Just copy the file over it, and restore it.
But let's say it's larger,
it's a little more complex process.
This is where you can do something like this,
and this is a tried and true process
that people have done actually for a while,
and that is SQL Server supports backing up to Azure Blob Storage.
We show you how to backup
your existing SQL Server on premises to Azure Blob Storage.
Then you turn around in the virtual machine itself,
and you just restore from
the blob storage and you're up and running.
It's a very simple process,
but we walk you through the steps to
actually go do that and we actually
use Management Studio because it's
a great wizard experience to do it.
But we show you how to deploy your virtual machine
using called The Marketplace and then do your restore.
Finally, we do talk about the option of
using Azure Data Studio with its new migration extension,
where you'll use something called a self-hosted
runtime piece of software in your on premises environment,
you were going to back up
your database not to Azure Blob Storage now,
but to a local folder, which is shared.
Azure Data Studio uses the Database Migration service to copy
that backup and to restore it and recover it into VM.
We focus on Number 2 as
a demo to show you this right as an example,
but we give you the different options that are possible to you.
The one thing also that we do mention Anna,
that you and I have talked about before is
a lot of people may not be aware of this,
but let's say you just want to take the entire VM,
and just move the whole thing into Azure,
Azure Migrate comes with an option to lift
and shift like a snapshot of the VHD,
and just put it in Azure,
and then from there, go optimize and do what you need to do.
This is the offline migration process,
and this lines up with the third module in the actual workshop.
Once you've done this migration
that we talked about, optimizing,
managing and we cover all sorts of aspects to this,
but one really neat thing we
show you is something called SQL Assessment.
This is really good technology.
Ebru, from our team and our colleague has worked on this,
and the concept is this,
let's take all the best practices
we know from the community and from Microsoft,
and let's embed this into the portal where you can go scan
your virtual machine once you've migrated and
take a look at some best practices of things you may do.
We cover everything from making sure
your VMS is configured correctly for storage and so forth,
but we also look inside SQL Server.
Two of the things I call out on the workshop
is to make sure using things like
lock pages and memory and
instant file initialization that can really help your performance.
Well, this tool will catch
that if you don't have those configured.
You've done your migration offline,
you've restored your database in a VM,
and now you want to make sure you have
the best experience going forward in virtual machine,
and so SQL assessments are great examples in
preview right now to help you get to do that.
>> Awesome. I've migrated to VM,
I understand some of the ways to optimize.
What are Managed Instance?
>> In the SQL 19 scenario,
if we give you example requirements,
and part of this example was,
is that the ISV app is okay with you going to a version with
SQL Server and you want the full benefits of platform as a server.
It looks something like this.
Here's your SQL server,
here's your Managed Instances in this blue box,
that's the virtual cluster.
What you're going to do is you're going to use
the Azure Data Studio extension to
do similar to what I showed you in the previous slide,
where you're going to use
the self-hosted run-time on your premises environment,
backup your database and put it in a folder.
Azure Data Studio with DMS will take that,
put it in Azure Blob storage,
and then something will kick in,
called the log replay service
within Managed Instance to restore that database.
Now, you'll notice here that the status of the database is yellow.
That's because it hasn't been recovered yet,
so it's not online.
A typical scenario would be,
you do the initial full backup and restore,
but now you want to do a continuous set of
log backups and log restores because this is online.
Your concept is I'm going to stay online as
long as possible until I'm ready to cut over.
You can go through Azure Data Studio,
and do all of this here like a wizard experience.
Let's say at some point in time,
you're ready for this cut over process.
You go in Azure Data Studio,
you tell it, I'd like to cut over,
and then what happens is it will automatically do
a final log restore, run recovery.
Now you see it's green and Managed Instance,
and so the time window to migrate is a very,
very tight because you controlled when
the app is now going to get redirected to Managed Instance.
Now you do have one other option that we don't demo we talk about,
and that is, you could use
transactional replication to do something like this.
It's not as efficient as doing here with our ADS scenario,
so you could have your managed instance as a subscriber and you
could be publishing transaction changes and eventually cut over,
or the log replay service is available to you
directly through power-shell to do a very similar thing.
But I really love the ADS experience because it's nice wizard.
It's very directed, very guided,
gives you a lot of progress information,
and so that's how you would do
an online migration to Managed Instance.
Now, just like we talked about the VM,
you're going to want to optimize and manage this.
There's a lot of stuff we talk about,
but a couple of things to consider, but you may not know about.
Number 1, to take advantage of licensing is
to use Azure hybrid benefit because you want to take
advantage of your SQL Server licenses to only have to pay
for the compute and storage of Managed Instance.
You want the sequel license to be
paid for with something you've already paid for.
We also have something called maintenance windows
where you can decide when would
Microsoft apply fixes or
patches or update the service in the back end.
You have some customization of when that occurs,
you also have security options,
things like Azure Active Directory authentication,
different ways to secure a Managed Instance.
We go to those and then you
can use things like SQL Insights or use
the portal or set up ways to go
monitor your service and resources.
Now, if you think about the modules we've gone through,
we did the offline scenario,
and then we did the Managed Instance.
We use an online process and you can do it off-line there as well.
We talk about it, but we show
you the online process, and then of course now,
we would dive in and show you some of the best practices
to optimize and manage that
Managed Instance once you've migrated there.
>> Awesome. This feels really great already,
and I think you had mentioned there was
one more module that comes after all this.
>> Yeah. The thought is this.
You're in Azure virtual machine
already and maybe you're there for months, maybe a year,
I don't know, but you've worked with your ISV app,
maybe you running SQL 16 here.
The ISV app has said to you, hey,
we're ready now to support something
like a Managed Instance and you
as a customer have a desire to go to
that. Here's a really cool thing.
We show you inside the workshop how to
backup your databases to
Azure Blob Storage like using automated backups.
You are through a process of having
existing backups already in Azure Blob Storage.
Remember, in our other scenarios,
we use something called the self-hosted
run-time with a folder on your machine.
Because your backups are in Azure now,
to go from VM to Managed Instance,
we can still use Azure Data Studio,
but we can take advantage of the fact
your backups are already on Azure.
Now you can do an online migration using Azure Data Studio.
Just use the existing backups already in
the Cloud and go through that same process that we showed
you on how to backup
logs and restore them and eventually cut over,
and now you're back online.
This is more efficient,
but it makes sense because you're already in the Cloud and
you have your backups that already exist in Azure Storage.
We've completed the circle here.
We've shown you if you're in
VM and you want to make that choice to go to Managed Instance,
there is an online way to do that,
and it's more efficient than if you
just had to keep your backups over on-premises.
>> Awesome. I love it. Did you see customers making
things phased approach to
migration where they go to
VM and then they go to Managed Instance?
I think it makes total sense to end with this.
As we wrap,
do you have any tips and tricks for
folks that are just getting started?
>> Absolutely. One of the things,
we have a whole team that works on
migration and you probably have hosted them on
your show and I work with them closely as we do these materials.
But one of the things I love
that we've built is something called the
data migration guide or hub.
If you look at AKMS data migration,
this is a one-stop shop for everything to go to Azure,
whether it's SQL Server or third party databases,
if you want to make that move,
you go to this site.
It's an easy site to remember.
You just pick and choose your source,
where do you want to go,
and we have a complete step-by-step guidance.
You can think of the workshop as a practical set of examples on
how to apply the best practices
that we show you in the data migration guides.
>> Awesome. Well, thanks so much Bob. This is an awesome.
I learned a lot, even just through the sneak peek,
and I think our viewers probably have too.
To our viewers be sure to grab some of these URLs.
We'll put the links in the description for you to learn
more about the data migration guide,
the data migration workshop that Bob is building,
so you get all the slides as well as our
migrating to SQL series on data exposed.
Bob, once again, thanks so much.
Our viewers, leave us a like, leave us a comment,
let us know what you think, and we hope to see
you next time on Data Exposed.
[MUSIC]