>> Learn how the new agitated studio extension
for Azure SQL migrations
is making it easier for you to prepare and migrate to Azure SQL.
This week on Data Exposed MVP edition.
[MUSIC]
Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed MVP edition.
Today I'm joined by Bjoern Peters an
MVP living all over the world.
Bjoern, thanks so much for joining us today.
Can you tell us a little bit about what you do?
>> Yeah. Hi, Anna. Thank you for having me here.
I'm in SQL Server since version 6.5.
That's my first contact was SQL Server.
Since then, I stepped through all the versions,
mainly started my DBA life with SQL Server 2008 or 2008 R2.
Since then, stepped through 2012,
2014 and so on.
As Azure SQL started as Azure SQL databases in the Cloud,
I also jumped on that topic and loved to talk about it,
and try everything around it and get to
know those two features technology around Azure SQL,
Azure SQL Databases or
Azure SQL Managed Instance and all those stuff.
That's my journey.
>> Awesome. Yeah, and you're a big force in the community.
We're just excited to have you on the show,
and today we're going to be talking about migrating your databases
to Azure SQL and how preparation is everything.
I think specifically you're going to talk
to us about how preparation is important,
but also how we can make it easier.
Can you tell us a little bit about
what you've learned and how you approach migrations?
>> Yeah. In the early phases,
we just had the DMA,
10 years ago we had just the opportunity to
move databases from server to server by scripting everything,
or using PowerShell and doing everything by hand manually.
Then Azure Cloud steps further and comes more into the companies,
or in the public, as a public Cloud service,
we got tools from Microsoft like Data Migration Assistant.
That's the point where I wanted to
start and show you the old world,
Data Migration Assistant.
Then how you can use
the new technology which are provided by Microsoft right now,
and just shortly announced SGA
and how to make your life easier migrating databases,
or prepare for migration into the Cloud much easier.
Let me show you my screen.
The old database migration assistant,
as most of us might know is this great tool here.
We can see we have the source feature
and then we can connect to a SQL Server on prem or,
on your virtual machine, something like this.
We're able to choose the databases and add them to our assessment,
do we assess our assessment?
It's checking the server,
it's checking the databases and we just get some information if
that on prem SQL Server or that
underlying databases are able to be moved into the Cloud.
For example here into Azure Managed Instance,
and you can see here there's
an unsupported feature on my SQL Server here.
This is provided by me,
and I have to show you this migration issue database,
as the name says has a migration blocker.
It has multiple log files just to make
an easy failure for the migration blocker, just to show you.
But this is everything we
are getting from the old Data Migration Assistant.
There's nothing more what we can get out of this tool.
One of the most main reasons,
or main questions we have in preparation to
move databases into the Cloud is not just,
are my databases ready for the Cloud,
but which service or
which performance class I have to provide in the database.
As we know on prem,
or in the virtual environment,
we have to set up our virtual machines with CPUs,
and RAM, and disk performances and so on.
On prem, it might be much easier to
adjust by moving for example in VM where
the slider to increase the virtual memory on the machine.
In the Cloud, it's not that easy.
It's still easy, but not that easy.
If we start deploying machines into the Cloud,
we have to guess a performance class.
For example, if you deploy Azure SQL Managed Instance,
you can choose between several performance classes
like course disk space and so on.
But which one do you have to choose?
This is the task of
a new extension or
this new extension point as
a data studio makes your life much easier.
The new Data Migration Assistant also has the feature inside,
but it makes it more
easier to use it in our daily based tools like Azure Data Studio,
which some of you already might use.
Here we have Azure Data Studio.
This is the starting point for the fresh Data Studio.
If you just click here on the extensions,
and then we search for migration.
Which is the search phrase for the Azure SQL migration extension.
This is shortly named as a GA tool, and it's released.
You can see it just click on the "Install" button.
It is installed immediately and doesn't take that much time.
This extension allows you to do
everything for the migration and
the preparation from your Azure Data Studio.
As you can read here,
there is the installation, how to do this,
how to connect to your on
prem SQL Server or to your virtual machine.
Then you get this cool feature here,
assessment and SKU recommendation.
The new DMA and this extension,
are able to give you a recommendation
on how to deploy your SQL Server,
or to deploy your databases into the Cloud,
and to choose which performance class.
Let me show you this in a demo.
On this note, I just install the extension.
Here you can see I have installed Admin Pack Azure,
Arc and so on.
Here we have the Azure SQL migration extension.
Then I have a connection to my virtual cluster here.
You can see here I can manage everything from here.
This is the normal behavior of Azure Data Studio
and through installation of the new extension,
I have a few new features here,
SQL Assessment and Azure SQL Migration.
I can do a SQL Server assessment from my connection here.
Just by clicking on the Invoke Assessment it connects to
the SQL Server and checks everything what is on my SQL Server.
This is a first overview of what might happen,
or what might occur during my assessment for
the preparation or for
the movement of my SQL Server or databases to the Cloud.
You can see here some warnings,
some information which trace flag I enabled and so on.
>> The main part,
this is just for a overview and you can take this overview
for your daily base or for other assessments,
for auditing, and so on or inventory.
But we want to talk about Azure SQL Migration.
Here, you can see this is the Migrate to
Azure SQL feature of the overview.
By clicking on "Migrate to Azure SQL",
we open a new window.
You should start with Start a New Session.
But as a preparation for this demo,
I already selected this.
I just started some assessment which takes some time,
which is extensive,
the demo session.
Everything is prepared.
You can see here,
I already had some assessments done.
For example, you can click on those "Databases" here,
which you want to move into the Cloud.
Report Server DBs,
I don't need them in my Cloud as I might use,
for example, Power BI online.
I just want to use the workload databases.
Then you can see it's just checking again,
in my assessment here.
As you can see here,
we have just one of two databases which can be migrated.
You can find those information down here.
By clicking on "View and Select",
it shows you the errors or
those blockers we have during the assessment.
You can say here we are not able to
select the migration issue, which is correct.
By clicking on that "Migration Issue" database,
or on the name of that database,
we can see why it is happening that we can't migrate this all,
which is the migration blocker.
Here you can see we have two transactions required,
which is not supported on a managed instance.
We can just click here on the "DemoDB". There is no issues,
so I can select this. Then they'll show me,
for example here,
as a simple managed instance.
It provides me also recommended configuration.
It says or recommends me a size gen5 General purpose
for virtual core was 32 gigabytes of storage.
Then I can click on the "View Detail" and it shows me
some more information about how much CPUs are used,
memory requirements, block storage and so on and
what the assessment has
gathered on information or data during my assessment time.
Speaking about assessment time,
you can start selecting.
Or if you do this initially,
it will select data for at least 10 minutes.
The longer you do this,
the more information you will get.
You're more accurate the recommendation will be.
If you do this on a fresh assessment,
you have to run the assessment for at least 10 minutes.
For example, you do it overnight or complete day, that's okay.
It will collect all those data from your local machine.
Then you get those recommendations.
You can adjust some parameters to
your recommendation if you want to have
some buffer size or some spare CPU,
for example, or more buffer in your compute power,
then you can adjust all those parameters
to be recognized in the recommendation.
Here, for example,
in the SQL Server on Azure Virtual Machine, you can have,
in my configuration and during my assessment you can see I should
deploy a E2ads v5 machine with just two with CPUs.
As you might know,
Azure SQL virtual machines,
we are able to adjust the storage configuration
on our or the disk configuration for these virtual machines.
If I can find those information in the "View Details"
button on the View. We can see here for
example, SQL tempdb should be placed on the default D drive,
as this is a local fastest drive
on your virtual machine and we should
add a P30 end for a data files and
another P30 for SQL transactional files.
They write down all the overview,
or the recommendations right on
which data all those recommendations are based.
If we decide, for example,
we go here on to a SQL Server Virtual Machine.
Why? Because we want to deploy both databases into the Cloud.
We can select "Next".
We will be asked to provide an Azure account, an Azure tenant,
our subscription ID, location where we want to deploy this,
and you can see here, I can select my virtual machine.
Then I will be asked in
the next step how I want to migrate my databases.
If I wanted to do it online to cut
down the time of all the downtime for the migration phase,
then I should choose "Online
Migration" and I have to provide a migration service,
Azure Migration service in
my Azure portal or just using the offline migration.
Just to show you here.
If I select "Offline Migration" it gives me
the opportunity to choose a storage account in Azure,
where I can do my backup to URL.
For example, this ADS tool allows this extension,
will do everything for me.
I have to provide the backup for this manually.
I can do this also by providing a source credential,
do it on a backup on a local.
Then I have to copy it onto
the blob storage and everything can be done manually.
If I want to do this in an online mode,
you have to provide the local backup account
or where you want to store your files,
your backups on your local storage.
Or you can also provide here the blob container for
the online migration with the migration service.
Then we can choose here
which migration service or where
this migration service is located,
which one do we use here.
I already provided one and then I can click on the "Next" button.
It gives me the summarize of this migration phase.
I have the estimations over here in Azure.
If I could perform an online migration,
we have blob container in which subscription and so on.
Then I can just start the migration.
You can see here in the overview
that we have one database migration in progress.
Also in my portal,
I can see or I can monitor
this progress all by
clicking here on that button that you can see here.
It's running, it's ongoing,
in which phase it is.
You can monitor the progress.
This is how easy and how fast you can do
an assessment for your secret server databases.
How to migrate them into the Cloud.
>> Awesome. Thank you.
This has been really interesting.
I had seen this you recommended before,
but it's pretty interesting to see how it actually
works with your workload, with your databases.
I also hadn't seen how well DMA
integrates into this Azure SQL Migration extension.
As we wrap up,
are there any final tips and tricks you have for folks
that might be just getting started with
the Azure SQL Migration extension?
>> Just give it a try.
It's so easy, so self explaining.
You don't have to be an expert in migration.
You just install the extension, do an assessment.
As I said, do it even longer than just 10 minutes.
After 10 minutes, you would get in first steps.
But if you have, for example,
a data warehouse where you have
nightly processing jobs on a data staging,
data processing, you will get more accurate assessment data
or recommendations if you do it
overnight or even let it run for a week.
No problem. Yeah, just give it a try.
It's so easy.
>> Awesome. Well, thanks so much Bjoern for
coming on the show. This is really great.
I think I learned a lot.
I'm sure our viewers learned a lot as well.
To our viewers, check the description to get
some links for learning more about
Azure Visual Studio and this great extension.
If you like this video, go ahead, give it a like,
leave us a comment and let us know what you
think about Azure SQL Migration extension.
We hope to see you next time on data exposed.
[MUSIC]