Identify compatibility issues for SQL migration

In our scenario, you’ve been asked to assess the readiness of a legacy SQL Server database for migration to Azure SQL Database. Your task is to perform an assessment of their legacy database and identify any potential compatibility issues or changes that need to be made before migration. You should also review the schema of the database and identify any features or configurations that aren’t supported in Azure SQL Database.

This exercise will take approximately 15 minutes.

Note: To complete this exercise, you need access to an Azure subscription to create Azure resources. If you don’t have an Azure subscription, create a free account before you begin.

Before you start

To run this exercise, ensure you have the following in place before proceeding:

You’ll need SQL Server 2019 or a later version, along with the AdventureWorksLT lightweight database that is compatible with your specific SQL Server instance.
Download and install Azure Data Studio. If it’s already installed, update it to make sure that you’re using the most recent version.
A SQL user with read access to the source database.

Restore a SQL Server database and run a command

Select the Windows Start button and type SSMS. Select Microsoft SQL Server Management Studio 18 from the list.
When SSMS opens, notice that the Connect to Server dialog will be pre-populated with the default instance name. Select Connect.
Select the Databases folder, and then New Query.
In the new query window, copy and paste the below T-SQL into it. Ensure that the database backup file name and path match your actual backup file. If they don’t, the command will fail. Execute the query to restore the database.
```
 RESTORE DATABASE AdventureWorksLT
 FROM DISK = 'C:\<FolderName>\AdventureWorksLT2019.bak'
 WITH RECOVERY,
       MOVE 'AdventureWorksLT2019_Data' 
         TO 'C:\<FolderName>\AdventureWorksLT2019.mdf',
       MOVE 'AdventureWorksLT2019_Log'
         TO 'C:\<FolderName>\AdventureWorksLT2019.ldf';
```
Note: Make sure you have the lightweight AdventureWorks backup file on the SQL Server machine before running the T-SQL command.
You should see a successful message after the restore is complete.
Run the following command on the AdventureWorksLT database in the SQL Server instance.

ALTER TABLE [SalesLT].[Customer] ADD [Next] VARCHAR(5);

Install and launch the Azure migration extension for Azure Data Studio

Follow these steps to install the migration extension. If Azure migration extension is already installed, you can skip these steps.

Open the extensions manager in Azure Data Studio. s
Search for Azure SQL Migration, and install the extension. Once you install it, the Azure SQL Migration extension is in the list of installed extensions.
Select the Connections icon, and then select New Connection.
In the new Connection tab, type the server name. Select Optional (False) for the Encrypt option.
Select Connect.
To launch the Azure migration extension, simply right-click on the name of the source instance and select Manage.
In the server menu, under General, select Azure SQL Migration. This will take you to the main page of the Azure SQL Migration extension.

Note: If you’re unable to see the Azure SQL Migration option in the server menu, or if the Azure SQL Migration page doesn’t load, reopen Azure Data Studio.

Run the compatibility assessment

The compatibility assessment helps identify potential migration issues and provides detailed guidance on how to resolve them before the migration process begins. This can save significant time and resources.

You’ll run the Azure migration extension for Azure Data Studio, run the compatibility assessment, and then view the results for an Azure SQL Database target.

In the Azure SQL Migration dashboard, select Migrate to Azure SQL to open the migration wizard.
On Step 1: Databases for assessment, select the AdventureWorks database, then select Next.
On Step 2: Assessment results and SKU recommendations, wait for the assessment to complete and select Next.

Review the assessment results

You are now able to review the recommendations generated by the migration extension.

On Step 3: Target platform & assessment results, select Azure SQL Database as the target platform.
Select the AdventureWorks database. Take a moment to review the assessment results on the right side.

Note: We can see that the Next column that was added previously was flagged, as it may lead to an error in Azure SQL Database.
Choose Azure SQL Managed Instance instead as the Azure SQL Database target platform.

Note: The Next column is no longer flagged for Azure SQL Managed Instance, why is that?

This means that the Next column can be safely used on Azure SQL Managed Instance.
Select Save assessment report to save the report in a JSON format.
Take a moment to review the JSON file, and its properties.

Fix the issue

Run the following T-SQL command on the AdventureWorks database.
```
 ALTER TABLE [SalesLT].[Customer] DROP COLUMN [Next];
```
Go back to the Step 2: Assessment results and SKU recommendations page in the wizard, and select Refresh assessment.
Select Azure SQL Database as the target platform.
Select the AdventureWorks database.

Note: The database is ready to migrate.

You’ve learned how to assess the readiness of a SQL Server database for migration to Azure SQL Database. By addressing compatibility issues and making essential schema changes or reporting them, you’ve taken an important step in mitigating potential technical issues that could arise in the future on Azure SQL Database.