Azure SQL Data Migration Demo

Video Activity
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or

Already have an account? Sign In »

Time
14 hours 28 minutes
Difficulty
Intermediate
CEU/CPE
15
Video Transcription
00:00
>> Hello, Cybrarians.
00:00
Welcome to lesson 3.3 of
00:00
Module Three of this course
00:00
titled Microsoft Azure Architect Design.
00:00
Here is a list of
00:00
all the tasks that we'll be covering in this demo.
00:00
Let's start out by reviewing the migration scenario.
00:00
We have two environment.
00:00
We have an Azure and an on-premises environment.
00:00
In Azure, we have a Web App Service that
00:00
is hosting the Tailwind e-commerce site,
00:00
in on-premises we have
00:00
a Windows 2012 Server that is running SQL 2012.
00:00
The shopping catalog of our e-commerce site
00:00
is hosted on this on-premises SQL Server.
00:00
Right now, I'm in the Tailwinds e-commerce site,
00:00
and as you can see,
00:00
the site has a list of
00:00
different items in the shopping catalog.
00:00
All these items and
00:00
all the information that you're looking at here
00:00
are stored in the on-premises SQL database server.
00:00
Here is my on-premises SQL database server.
00:00
You can see that as this database called Tailwinds,
00:00
and here are the different tables that contains
00:00
information about the products on the site.
00:00
What we'll be doing is creating
00:00
an Azure SQL database which would
00:00
be our database migration target,
00:00
and we will be creating
00:00
an Azure Database Migration
00:00
Service which we'll be using to
00:00
complete the migration scenario
00:00
from on-premises over to Azure.
00:00
We will then switch our website from using
00:00
the on-premises SQL database to
00:00
using the Azure SQL database.
00:00
Let's move to the next task,
00:00
which will be to create a new Azure SQL database.
00:00
Right now, I'm in the Azure portal.
00:00
If I click on "Create a Resource"
00:00
and type in SQL database,
00:00
and I click on "SQL Database".
00:00
I can click on the "Create" option and
00:00
>> I'm presented with
00:00
>> a menu to enter the details for my new SQL database.
00:00
I can call this AZ-301 Azure DB test
00:00
or you can call it any name that you prefer.
00:00
I can select the server that will be
00:00
hosting this SQL database.
00:00
Remember, when we're looking at the theory,
00:00
we talked about the server not been where we
00:00
specify our compute or storage resources.
00:00
It's just a logical administrative construct,
00:00
because a SQL database is an elastic tools,
00:00
have to be in a SQL server
00:00
which is an administrative construct.
00:00
In this case, I'll click on "Create New",
00:00
and I'll go create a new server.
00:00
I'll call it Server and I'll specify an admin login.
00:00
I can specify the location where
00:00
this database would be in,
00:00
and this is where I can specify if I
00:00
want to use an elastic pool or not.
00:00
If I want to use an elastic pool,
00:00
it means I'm going to have a group
00:00
of databases that I'll be
00:00
sharing the same compute resources.
00:00
In this case, I will select "No" and I
00:00
can go ahead and configure
00:00
the database to specify what tier I'm using.
00:00
You can see for the vCore model, there are three tiers,
00:00
general purpose, hyperscale,
00:00
and business critical models.
00:00
Why? If I go back to the normal DTU purchasing model,
00:00
we have the basic, the standard, and premium.
00:00
In this case, I'll just stick to
00:00
the vCore model and I'll leave
00:00
that with two virtual cores and 32 gig of data.
00:00
I'll just click on "Apply" and I'll
00:00
go ahead and go to the networking section.
00:00
This is where we can specify
00:00
network connectivity access to this database.
00:00
By default, it specifies to say, No Access.
00:00
I can specify it to say,
00:00
it's going to be a public endpoint which means it's going
00:00
to be accessible over the Internet.
00:00
I also want this option,
00:00
if I'm going to be using something like
00:00
Azure Database Migration Service,
00:00
I want the option to allow
00:00
Azure services to be able to access this server.
00:00
Also, I have the option to say go ahead and add
00:00
my IP address that I'm
00:00
connecting from to the Azure portal,
00:00
add it to be able to have access to this database.
00:00
I'll just click on both of those and I'll go ahead
00:00
and click on Additional Settings.
00:00
Here's where I can restore from backup if I have
00:00
a backup stored somewhere or from
00:00
maybe even a sample database that Microsoft provides.
00:00
I can specify other information like the collision.
00:00
In this case, I also have the option to start
00:00
the Advanced Data Security
00:00
or the Advanced Threat Protection.
00:00
I'll just go ahead and say Not Now for that,
00:00
and I'll go ahead and review and
00:00
create this database and I'll click on "Create".
00:00
The next thing that I'll be doing is I'll be creating
00:00
an Azure Database Migration Service.
00:00
This is what we have.
00:00
I will need to create
00:00
the Database Migration Service that we'll
00:00
use to complete our migration scenario.
00:00
I'm back in the Azure portal
00:00
here if I click on Create a Resource,
00:00
and I search for database migration service.
00:00
I select the option and I click on "Create".
00:00
Before I actually click on "Create",
00:00
you can see some of the options or
00:00
scenarios that are possible with the service.
00:00
I'll go ahead click on "Create".
00:00
I can specify some things like the resource group
00:00
that this Database Migration Service will be in,
00:00
I can specify a name for the service,
00:00
and I'll call this AZ301dbmigservice.
00:00
I'll specify the location for
00:00
that which I'll leave that as UK South.
00:00
This is where I can specify the pricing tier.
00:00
The pricing tier it's what's going to dictate whether I
00:00
can do online migrations or only offline migrations.
00:00
Standard only supports offline migrations
00:00
while if I select premium,
00:00
it supports both online and offline migrations.
00:00
I'll go ahead and select that,
00:00
and I'll click "Apply".
00:00
In this case, I'll go to
00:00
the networking section where I can either specify this
00:00
to be connected to an existing virtual network
00:00
or I can create a new virtual network.
00:00
I'll create a new virtual network,
00:00
azuredbmigservice.freenet. I'll Review and Create,
00:00
and I'll go ahead and click on "Create".
00:00
That begins the creation of
00:00
our Azure Database Migration Service.
00:00
This could take about 15 minutes to create.
00:00
What I've done is I've created
00:00
the SQL database that I'll be using as my target
00:00
earlier and I've also created
00:00
a Database Migration Service so I'll go
00:00
ahead and use that for the rest of this demonstration.
00:00
The next thing that I'll do is I'll be
00:00
downloading the Database Migration Assistant to
00:00
our on-premises SQL database and I'll be doing
00:00
an assessment of its readiness to migrate to Azure SQL.
00:00
Here I am on my on-premises SQL database server.
00:00
I'm currently on the page to download
00:00
the Database Migration Assistant version 5.0.
00:00
All you need to do is
00:00
just search with your favorite search engine
00:00
for Microsoft Data Migration Assistant
00:00
and you'll get to this page.
00:00
I'll go ahead and click on
00:00
Download and I'll go ahead and save this too.
00:00
Download Completed, I'll go ahead and run,
00:00
it and I'll go
00:00
similar process that you're probably familiar with.
00:00
I'll select the option to launch
00:00
the Data Migration Assistant,
00:00
and I'll go ahead and click on "Finish".
00:00
Now we have the Data Migration Assistant opened,
00:00
I'll go ahead and create a new project,
00:00
I'll call it On-prem to Azure Projects.
00:00
The assessment type,
00:00
I want to access a database engine
00:00
or integration services,
00:00
I'll select "Database Engine".
00:00
What's the Source type?
00:00
It's a SQL Server.
00:00
What's the target server type?
00:00
Here's where I can select, do I want to move it to
00:00
the managed instance type
00:00
or do I just want to move it to Azure SQL database?
00:00
I'll leave that as Azure SQL database
00:00
and I can specify assessment or migration.
00:00
In this case, I will only be
00:00
using it for assessment while I will
00:00
use the Database Migration Service for migration.
00:00
If I go ahead and click on "Create",
00:00
I can select what I want to check for.
00:00
Database compatibility and feature parity,
00:00
>> in this case, I'll go ahead and click on "Next".
00:00
>> I can go ahead and add sources.
00:00
In this case, I can specify my server name.
00:00
I'll just type in local host,
00:00
and I'll specify SQL authentication.
00:00
I'll put in the credentials for my database.
00:00
>> I'll go ahead and select the option to
00:00
Trust service certificate and I'll click on "Connect".
00:00
It identified the database
00:00
that exists on my local server.
00:00
I'll go ahead and select that and I'll click on "Add".
00:00
Now, it added that database.
00:00
I'll click on "Start Assessment".
00:00
This will take a few minutes to complete.
00:00
It's completed in this case.
00:00
What it's letting me see,
00:00
it's showing me compatibility issues
00:00
or feature parity issues.
00:00
In this case, it's great because I have
00:00
no compatibility issues that were detected.
00:00
If I go to SQL server feature parity issues,
00:00
it's highlighting to me
00:00
that Azure SQL doesn't support any analysis service.
00:00
Because I have SaaS installed locally,
00:00
it's letting me know that I
00:00
wouldn't be able to use that,
00:00
I'll need to migrate
00:00
that service to Azure Analysis Service.
00:00
Also because I have SSRS deployed locally,
00:00
it's also letting me to know
00:00
that it's not supported in Azure SQL database.
00:00
Those are things that I need to find
00:00
sudden solutions to if those are
00:00
features that I intend to continue using.
00:00
I can use this option right here to
00:00
upload this data or this information to
00:00
Azure Migrate so I can
00:00
centralize divisibility of assessment across servers,
00:00
applications, and database.
00:00
The next thing that I'll be showing
00:00
you is the migration of
00:00
a SQL schema and migration of
00:00
the SQL data from
00:00
the on-premises server to Azure SQL Server.
00:00
Visual representation of what I'll be doing now
00:00
: I'll be using the Azure Database Migration Service,
00:00
I'll create a new project there that will
00:00
complete the migration for me.
00:00
I'm back in the Azure portal.
00:00
The database that I'm going to be using
00:00
in my case will be this database called mod 20,
00:00
mod 3 SQL DB.
00:00
Database Migration service that I'll be
00:00
using is this service over here.
00:00
I'll go ahead and select that service.
00:00
I'll go ahead and click on "New
00:00
Migration Project" and I'll give it a name.
00:00
The source server type will be SQL server,
00:00
the targets would be SQL database.
00:00
I want to take this moment to just show
00:00
you some of the other scenarios that are possible.
00:00
Now because my source is SQL Server,
00:00
I can select any of these targets.
00:00
Now if I change my source to any of these other options,
00:00
the target option changes.
00:00
If I change that to MySQL,
00:00
those are the targets that I can use for that.
00:00
If I change this to RDS,
00:00
these are the targets that I can use.
00:00
It does vary depending
00:00
on which source you are coming from.
00:00
If I'm selecting something like MongoDB,
00:00
I can only migrate that to Cosmos DB MongoDB API.
00:00
In this case, I'll just stick to SQL Server to
00:00
Azure SQL Database and
00:00
then I can select the type of activity.
00:00
In the first case, what I want to do is I want to
00:00
migrate the schema of my database only.
00:00
So I'll go ahead and select Schema only migration.
00:00
But this is where we can select if we want
00:00
an offline data migration, online data migration.
00:00
Remember that whatever option you select has
00:00
an impact on the downtime of your application.
00:00
Schema only is what I'll be doing for now.
00:00
I'll go ahead and click on "Save".
00:00
It's advising me to run DMA,
00:00
which I've already done,
00:00
to verify the readiness of my database to migrate.
00:00
I'll go ahead and create and run activity.
00:00
It's going to ask me for the source information,
00:00
which database am I coming from?
00:00
I'll put in the IP address of my on-premise SQL database.
00:00
I'll select SQL authentication and
00:00
I'll put in my username and my password.
00:00
I'll go ahead and select the option to trust
00:00
service certificate and I'll
00:00
go ahead and click on "Save".
00:00
Now I can specify the target,
00:00
which is the information about my Azure SQL database.
00:00
Now, one of the things that I'll need
00:00
here is I'll go ahead and specify
00:00
the server name of where my database exist in.
00:00
That's the Azure SQL server.
00:00
That's the information of my Azure SQL server.
00:00
I'll specify the authentication type,
00:00
which I'm using SQL authentication.
00:00
I'll specify the username and the password.
00:00
I'll go ahead and click on "Save".
00:00
Then I have the option to select the database,
00:00
SaaS, and then the target database.
00:00
In my case, that's that database over there.
00:00
I'll leave that option as generated from SaaS.
00:00
If I go ahead and click on "Save" here,
00:00
and I'll give the activity a name,
00:00
I'll click on validation option.
00:00
In order to save time,
00:00
I'll click on "Do not validate my databases".
00:00
But if you're doing this in production,
00:00
you want to do some validation.
00:00
I'll go ahead and click "Save" and I'll
00:00
go ahead and run the migration.
00:00
What I will do while this migration is running
00:00
is I'll go to another Azure portal,
00:00
I'll go under my SQL database over here,
00:00
and if I go under Query editor and I log in,
00:00
I should be able to verify what's the schema.
00:00
I can see here, so the schema has been migrated.
00:00
Everything's looking okay on this end.
00:00
It migrated schema,
00:00
but there's no data that's been migrated in there.
00:00
I can verify that very easily.
00:00
If I go ahead and execute that query,
00:00
I can see that there's nothing that was returned.
00:00
If I go under my result, absolutely nothing.
00:00
It's going to be the same for all the other tables.
00:00
It's just the schema at this point.
00:00
If I go back here and I do a refresh,
00:00
it's showing us Completed, which is great.
00:00
What I'll do is go while I'm
00:00
creating a new migration project.
00:00
I'm just going to select my existing project.
00:00
I'll click on "New activity" and
00:00
I'll select "Offline data migration".
00:00
That'll be the source, which is my on-premises SQL.
00:00
I have my username.
00:00
I'll specify my password in there.
00:00
I'll go ahead and click on "Save".
00:00
I will also specify information for my target.
00:00
I'll put in the password for
00:00
my Azure SQL and
00:00
I'll go ahead and click on "Save" to that.
00:00
This is where I can do some mapping.
00:00
That's the source. My target database is that.
00:00
If I wanted to, I could set this source as
00:00
DB read only to avoid changes being made,
00:00
but I'll just leave that option for now and I'll go
00:00
ahead and say "Save".
00:00
Once that is done, it's going to show me
00:00
the tables that exist within my database.
00:00
I'll click on "Save".
00:00
It's going to give the summary and
00:00
I'll give this activity a name.
00:00
I'll say "Do not validate."
00:00
Also, I'll click on "Save" and I
00:00
go ahead and run the migration.
00:00
The migration is going to start at this point.
00:00
It's going to take only a few seconds to complete
00:00
because I don't have that much data in this database.
00:00
It's only five megabytes.
00:00
It completed the migration.
00:00
Now, if I go back to my Azure SQL,
00:00
if I go ahead and run the same query again,
00:00
now you can see that we have
00:00
all those information that's now been migrated over.
00:00
If I do the same thing for dbu.Products,
00:00
run query, and I can see all this information,
00:00
that's now being populated,
00:00
so we're looking good.
00:00
The next thing that
00:00
>> I'll show you is the modification of
00:00
>> my Azure web app to use
00:00
the Azure SQL database that we just migrated to.
00:00
Just to verify, that currently,
00:00
I'm using the on-premises SQL database.
00:00
If I refresh my screen, that looks good.
00:00
I can see all my shopping catalog items
00:00
all listed there nice and fine.
00:00
I'll go back to my Azure portal.
00:00
In this case, I'll go to my application service.
00:00
Under my application service,
00:00
I'll be modifying some application settings.
00:00
In particular, the application settings that I'll be
00:00
modifying is this SQL connection string.
00:00
It's currently pointing on premises.
00:00
I'll need to go get the connection string for
00:00
my Azure SQL database
00:00
and I'll need to specify that in here.
00:00
I can find the information about
00:00
the connection string by going
00:00
>> to the database in Azure.
00:00
>> Click on "Connection strings" and I have the
00:00
ADO.NET connection string over here.
00:00
I can copy that,
00:00
modify the passwords to include
00:00
the password for this database, which is what I'll do.
00:00
I've just modified that.
00:00
I'll go ahead and click "Okay"
00:00
and I'll go ahead and click "Save" to that.
00:00
That's going to restart the web application,
00:00
which should take about a few seconds to restart.
00:00
Once that restarted, what I
00:00
expect to see is once I refresh the page,
00:00
the SQL server will now be pointing to my Azure SQL,
00:00
but I'll still have all the information that are
00:00
in my shopping catalog still being displayed correctly.
00:00
Let's reload the screen.
00:00
Now you can see it's pointing to my Azure SQL database,
00:00
but I can still see information about my catalog,
00:00
and everything looks good.
00:00
That brings me to the end of this demo.
00:00
Thanks very much for watching and I'll
00:00
see you in the next lesson.
00:00
This brings me to the end of this lesson.
00:00
Thanks very much for watching and
00:00
I'll see you in the next lesson.
Up Next