AZURESQLDBA - Migrate an on-premises SQL database to an Azure SQL database using DMA.

 

Activity: Migrate an on-premises SQL database to an Azure SQL database using DMA.

1.      Open Microsoft Data Migration Assistant on your computer. From the left ribbon, click the +.

2.      In the resulting window, you will need to set these fields.

For project type, Select Assessment

For Project Name,  type AdventureWorks2017

For  Source Server type , Select SQL Server

For Target Server Type, Select Azure SQL database   

 Click create to create a new assessment project.

 

 

 

3.      In the resulting Select repot type window, select the check database compatibility

And check feature parity check checkboxes. Click next to continue

 

4.      In the Connect server window.

For server name, provide the SQL server  name

For authentication type, Select Windows Authentication click connect to continue.

5.      In Add Source window, select the database. You can also  provide the extended events or the SQL profile trace files for assessment. This is useful for accessing queries from the application using ORM tools

 

Click Add to continue

DMA connect to the database and fetches the compatibility and database size.

 

 

 

Note: you can also, access queries from application such as .Net. This is useful when the application uses ad hoc queries to query the database instead of stored procedures.

 

Click Start Assessment o find the compatibility issues.

 

6.      DMA will apply the compatibility  rules to  find the compatibility  issues. It tells you the features that are not supported in the SQL Server  feature parity session.

 

 

According to DMA, you have one cross- database reference and one service broker instance, which  are not supported in azure SQL database.

 

7.      Under Option, select the compatibility issues radio button

 

 

DMA  list the stored procedures that  failed the compatibility test. To  fix the error.

.

8.      In top right corner, click restart Assessment.

DMA will re-start and notify, you that there are no compatibility issues.

 

9.      To  migrate the database, in the left  hand side navigation bar, click the + sign .

The resulting window do the following.

For project type, select migration

For project name, type Adventureworks2017migrationm

For Source server type, Select SQL Server

For target server type  , Select Azure SQL server

For migration scope , select  schema & data

Click on create to create the new assessment project.

 

10.   In the connect to the source window, do the following

For server name, provide the SQL server  name

For authentication type, select  windows authentication

Click Connect to continue

 

11.   Select Adventureworks2017 from the list of available databases and click Next

12.    In the connect to target server window, the following

For server name, Provide the azure SQL Server name.ss

For authentication type, select SQL Server authentication

For username, provide the azure SQL server admin user.

For password, provide the admin password.

Clean the encrypt connection checkbox.

Click connect to continue.

 

13.   In the resulting window, select the  Advnetureworks2017 database. And then click Next  to  continue.

Advnetureworks2017-  this database I have created in previous steps. Now this database is empty.  I have migrated the data from on-premises to azure SQL database.

 

14.   In the resulting window Select Objects window, we can select  which  objects to  move to azure SQL database. Select and click generate the SQL scripts button at the bottom of the window to continue.

 

 

DMA will generate the a T-SQL script to deploy the database schema. If you  wishto save the T-SQL script. You can click on save option in the generated script.

 

15.   In the Script & deploy schema window, click the deploy schema button to deploy the scheme to azure SQL database. DMA will execute the T-SQL script against the azure SQL database to create the selected database scripts.

 Once the schema is migration successful, click on migrate data.

 

16.   In the resulting window, selected tables window, we can choose what tables data to migrate. For now we have  left as default. For this example, selecting all tables and then click start data migration.

This migrates the data from selected tables in parallel and therefore can be used for large to vary targe databases.

 

After  migration we have verified through portal 

 

 

In this activity, we learned how to use data migration assistant to find the compatibility issues and migrate an sql server to an azure SQL database.

 

SQL - SQL Server interview questions

 1.⁠ ⁠Find the second highest salary per department using DENSE_RANK.

2.⁠ ⁠Get daily login count trend for the past 15 days.
3.⁠ ⁠Calculate user engagement duration using login/logout timestamps.
4.⁠ ⁠Identify duplicate transactions based on multiple columns.
5.⁠ ⁠Join user and transaction tables while handling NULLs correctly.
6.⁠ ⁠Retrieve top 5 products by revenue in the last 30 days.
7.⁠ ⁠Compute revenue delta between this month and last month.
8.⁠ ⁠Group data by week and count number of active users.
9.⁠ ⁠Perform LEFT JOIN with conditionally filtered rows.
10.⁠ ⁠Use CASE WHEN logic for tiering customers by total spend.
11.⁠ ⁠Check if transaction timestamps are in chronological order.
12.⁠ ⁠Handle missing values using COALESCE or default substitutions.
13.⁠ ⁠Track users with consecutive failed login attempts.
14.⁠ ⁠Find latest status per user using ROW_NUMBER partitioned by user ID.
15.⁠ ⁠Identify products with zero sales in the current quarter.
16.⁠ ⁠Join tables with SCD Type 1 dimension logic.
17.⁠ ⁠Apply HAVING clause to filter aggregate conditions.
18.⁠ ⁠Calculate average basket size per user per week.
19.⁠ ⁠Group and pivot sales data to display region-wise monthly totals.
20.⁠ ⁠Use Common Table Expressions (CTEs) to break complex logic into steps.