As discussed in the previous
article Data Migration Assistant (DMA) is one of the tools which we can use to
assess a database and it can also migrate the database. DMA is one of the tools
for performing Offline database migrations. So before performing a production
database migration using DMA we need to schedule sufficient maintenance windows
and thorough testing in a lower environment. Here we will migrate an on-premise
database named “AdventureWorks2016” to Azure SQL Database using DMA. Details of
DMA and different migrations are discussed in this article..
I
have already downloaded and created the “AdventureWorks2016” database. You can
read this article to get details from where this database can be downloaded.
Download DMA:
We
can download DMA for free from here.
Install DMA:
Installing
DMA is quite easy just need to click on 'Next', 'Next' as shown below.
Once
installation completes successfully, we should see below shortcut on the
desktop.
Assess A Database
using DMA:
Here
first, we will evaluate if my database is migrated from a SQL Server database to
Azure SQL Database, what existing features might not work in Azure, what issues
the database might face, and at the same time DMA will also suggest remedies
for the issues identified.
Open
DMA by double-clicking the icon. Now click on ‘+’ and create a new project as
shown below
Enter
the project name as “AssessDB”, assessment type as ‘Database Engine’. Select
the desired source and target server. I am selecting the source server type as “SQL
Server” and Target Server type as “Azure SQL Database” as shown below
As of
today, we can assess if the source database will have any compatibility issues
if migrated to azure, and also we can check if any of the existing features of
the source database will have issues if migrated to azure. Let’s check both the
options as shown below
Now
connect to the source SQL instance using either Windows or SQL authentication:
Now
select the database which we want to assess. Here we are assessing “AdventureWorks2016” database, so select it and click on 'Add':
Now
Click on ‘Start Assessment’ and the assessment will start
Once
the assessment is completed in the results we will have information about
different issues we might face if we migrate the current database to azure. As
you can see I already have some SQL Server jobs on source database hence the
report is saying there are no SQL Server Agent jobs in Azure. So if these jobs
are critical and required to be run after migration also we need to plan for a
fix for this. Either we can select another target platform like SQL Managed
Instance or SQL on Azure VM (Iaas) or use Elastic jobs. But in my case, this is
just a test environment I will ignore the warnings and proceed.
Same
way under ‘Compatibility Issues’ it says ‘Full-Text’ index has changed and so
on.
So we
need to review this Assessment report thoroughly and need to apply fixes for
any issues identified or might need to exclude any of the objects\components
from source, if we don’t need to use them in Azure and so on. We can save or
export this report.
Migrate Database
using DMA:
Once
database assessment is completed next we can use DMA itself to migrate the
database to Azure.
Now
click on ‘+’ symbol again and create a new project named “MigrateDB”. Same as
we did for assessing the database select the source and target servers. In the “Migration
scope”, we can choose what we want to migrate either only data or only schema
or both schema\data. Here we will migrate both the schema and data, so I am
selecting the option ‘Schema and data’.
Now
select the source server and authentication type either windows or SQL
authentication
Then
select the source database which we want to migrate. Here it is
‘AdventureWorks2016’ and click 'Next'
Connect
to the target Azure server using SQL authentication.
Now
select the target database to which we want to migrate the data and schema of
our source server. I have already created an empty Azure SQL database named
‘AdventureWorks2016’ and I will migrate my source database to this. Read here
to know how to create an empty Azure SQL database.
In
‘Select Objects’ page we can select what all objects we wish to migrate.
Also
on the same ‘Select Objects’ page, we can view object-specific issues that
might encounter after migration. Like in the below screenshot you can see
‘HumanResources.JobCandidate’ table has Full-Text index on a column and DMA
suggests ‘we can migrate this object but might face issue related Full-Text’.
So we can take necessary actions and alternatives as per suggestions provided
by DMA. Then click on ‘Generate SQL script’.
In
this page ‘Script & Deploy Schema’, first SQL script will be generated for
all the objects selected on the previous page.
Once the script is generated we can click on ‘Deploy Schema’ to start deployment of the schema.
Once
schema deployment is completed we can either Redeploy the schema or start
migrating the data. Click on ‘Migrate data’
In
the ‘Select tables’ page, select the tables to migrate. Here we will migrate
all the tables. After selecting tables click on ‘Start data migration’. The
migration process will start and we can view progress as below
Once
entire migration is complete let's connect to the Azure database server using
ADS(Azure Data Studio). Now we can see the schema and data from our source database
has been migrated to my Azure SQL database ‘AdventureWorks2016’.
Once
we verify and make sure the entire source database has been migrated completely
to the Azure SQL database and after applying fixes if any on the Azure SQL
database now the application connections need to be diverted from on-premise
SQL database to Azure SQL database. After diverting the connections once
thorough testing is done that marks the completion of the migration.
Note: Database Migration Service is one of the most widely used tools
for performing both Offline\Online migrations.
Thanks
VV!!
No comments:
Post a Comment